大数据（5q）ClickHouse客户端

1、命令行客户端

clickhouse-client
1

常用下面2组参数，1是-m在ClickHouse服务所在的机器，进入命令行交互模式

clickhouse-client -m --password '密码'
1

2是-q执行任务，配合-n允许多个SQL(用;分隔)

clickhouse-client -n -h '主机' --port 9000 -u '用户' --password '密码' -d '库' -q "SHOW TABLES;"
1

个人建议，少用-n

1.1、ClickHouse服务的版本号查询

SELECT version();
1

1.2、clickhouse-client下载方法

对于CentOS7，可去官网或国内镜像站下载clickhouse-client、clickhouse-common-static等rpm，然后安装

2、Python连ClickHouse

安装客户端

conda create -n ck_env python=3.9.12
conda activate ck_env
pip install clickhouse_driver==0.2.4
1
2
3

安装成功的屏幕显示

Installing collected packages: pytz, tzdata, pytz-deprecation-shim, tzlocal, clickhouse_driver
Successfully installed clickhouse_driver-0.2.4 pytz-2022.5 pytz-deprecation-shim-0.1.0.post0 tzdata-2022.5 tzlocal-4.2
1
2

连接代码

from clickhouse_driver import Client

c = Client(host='主机', port=9000, user='用户', password='密码', database='库')
result_list = c.execute('SELECT * FROM 表')
print(result_list)  # list of tuple [('a2',9,datetime.date(2022,3,8)),('b3',3,datetime.date(2021,5,4))]
1
2
3
4
5

3、Java连接ClickHouse

<dependency>
    <groupId>ru.yandex.clickhousegroupId>
    <artifactId>clickhouse-jdbcartifactId>
    <version>0.3.2version>
dependency>
1
2
3
4
5

import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
    public static void main(String[] args) {
        // 连接配置
        ClickHouseProperties p = new ClickHouseProperties();
        p.setUser("用户");
        p.setPassword("密码");
        p.setDatabase("库");
        p.setSocketTimeout(30000);
        String url = "jdbc:clickhouse://主机地址:8123";
        ClickHouseDataSource ds = new ClickHouseDataSource(url, p);
        try {
            // 创建连接
            ClickHouseConnection conn = ds.getConnection();
            try {
                Statement statement = conn.createStatement();
                // 查询
                ResultSet result = statement.executeQuery("SELECT * FROM 表");
                while (result.next()) {
                    ResultSetMetaData metaData = result.getMetaData();
                    // 获取列，索引从1开始
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        String columnName = metaData.getColumnName(i);
                        Object data = result.getObject(columnName);
                        System.out.println(columnName + "=>" + data.toString());
                    }
                    System.out.println();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

4、DBeaver连接ClickHouse

jdbc:clickhouse://主机:8123/库?socket_timeout=600000