Clickhouse的clickhouse-client使用、Java/Scala使用JDBC连接Clickhouse、Pyspark连接Clickhouse

1. clickhouse-client

底层是基于tcp协议

1.1 交互式协议

[root@clickhouse1 ~]#
[root@clickhouse1 ~]# clickhouse-client -h clickhouse1 --port 9000 -u default --password default123 -m -n
ClickHouse client version 21.6.5.37 (official build).
Connecting to clickhouse1:9000 as user default.
Connected to ClickHouse server version 21.6.5 revision 54448.

clickhouse1 :)
clickhouse1 :) select 1;

SELECT 1

Query id: a58a5e62-75cb-4b3c-a8e8-5ed8d2917b85

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.016 sec. 

clickhouse1 :)
clickhouse1 :) select 2;

SELECT 2

Query id: 35771efe-e306-403d-b39a-7a58abba4412

┌─2─┐
│ 2 │
└───┘

1 rows in set. Elapsed: 0.006 sec. 

clickhouse1 :)
clickhouse1 :) select hostName();

SELECT hostName()

Query id: 7d08f34a-489e-4e05-91be-a2e72f4c08fa

┌─hostName()──┐
│ clickhouse1 │
└─────────────┘

1 rows in set. Elapsed: 0.010 sec. 

clickhouse1 :) 

执行的记录被保存clickhouse-client-history

[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# pwd
/root
[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# cat .clickhouse-client-history
......省略部分......
### 2021-07-09 04:37:28.414
select 1;
### 2021-07-09 04:39:48.674
select 2;
### 2021-07-09 04:39:51.683
select hostName();

1.2 非交互模式

导入数据

[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# pwd
/root
[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# cat insert.csv 
3,san
4,si
[root@clickhouse1 ~]#
[root@clickhouse1 ~]# clickhouse-client -h clickhouse1 --port 9000 -u default --password default123 -m -n --format_csv_delimiter="," --query "insert into distribute_test_all format CSV" < /root/insert.csv 
[root@clickhouse1 ~]#

导出数据

[root@clickhouse1 ~]#
[root@clickhouse1 ~]# clickhouse-client -h clickhouse1 --port 9000 -u default --password default123 -m -n --query "select * from distribute_test_all" > /root/select.csv 
[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# pwd
/root
[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# cat select.csv 
1	一
4	si
1	yi
2	er
3	san
[root@clickhouse1 ~]#

2. Java/Scala使用JDBC连接Clickhouse

底层基于HTTP接口，Java版本如下：

pom.xml

        
            com.clickhouse
            clickhouse-jdbc
            0.3.2-patch8
        

Clickhouse_JDBC_test.java

import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Clickhouse_JDBC_test {

    public static void main(String[] args) throws Exception {

        String url = "jdbc:clickhouse://192.168.23.41:8123,192.168.23.42:8123,192.168.23.43:8123,192.168.23.44:8123/default";
        ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
        clickHouseProperties.setUser("default");
        clickHouseProperties.setPassword("default123");

        BalancedClickhouseDataSource balanced = new BalancedClickhouseDataSource(url, clickHouseProperties);
        //对每个host进行ping操作, 排除不可用的连接
        balanced.actualize();
        Connection conn = balanced.getConnection();

        String insertSql = "insert into test.java_table_test_all(id, name) values(?, ?)";
        PreparedStatement pst = conn.prepareStatement(insertSql);


        for (int i = 1; i