[Clickhouse] Clickhouse 客户端

1 概述：Clickhouse 的连接方式与客户端

• clickhouse 的连接方式，包括但不限于:

• cli: 适合Deubg与排查问题时
• http：适合在生产环境、程序的正式代码中使用；适合Deubg与排查问题时
• jdbc：适合在生产环境、程序的正式代码中使用

• 假定 clickhouse server 版本为: 23.3.2.37 / 21.3.4.25

select version();

2 CLI(命令行终端) : clickhouse-client

安装

方式1 手动安装

• 查看 clickhouse-server 版本

确保准备安装的clickhouse-client版本同clickhouse-server保持一致

若不一致，可能引发报错: clickhouse client expected Hello or Exception, got Unknown packet

• 下载安装包

① clickhouse-common-static-xxxxxx.rpm
② clickhouse-client-xxxxxx.rpm (依赖common-static)

GitHub: https://github.com/ClickHouse/ClickHouse/releases
阿里云镜像： https://mirrors.aliyun.com/clickhouse/rpm/stable/ (推荐)

• RPM 包安装

rpm -ivh clickhouse-common-static-xxxxxx.rpm
rpm -ivh clickhouse-client-xxxxxx.rpm (运行依赖common-static)

• clickhouse-client 连接

亲测(此命令)

clickhouse-client --host 127.0.0.1 --port 9000 --user xxx --password 'xxx'

• clickhouse-client连接的端口: TCP协议端口(默认9000)
• clickhouse http 连接的端口: 8123

方式2 一键脚本安装（客户端+服务端）

Ubuntu Linux 22.04 (亲测)

• 下载一键安装脚本

# mkdir -p /opt/clickhouse-client
# cd /opt/clickhouse-client

# curl https://clickhouse.com/ | sh

• 一键式安装（客户端+服务端）

root@xxx:/opt/clickhouse-client# sudo ./clickhouse install
Decompressing the binary......
Copying ClickHouse binary to /usr/bin/clickhouse.new
Renaming /usr/bin/clickhouse.new to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-server to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-client to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-local to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-benchmark to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-obfuscator to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-git-import to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-compressor to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-format to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-extract-from-config to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-keeper to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-keeper-converter to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-disks to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-chdig to /usr/bin/clickhouse.
Creating symlink /usr/bin/chdig to /usr/bin/clickhouse.
Creating symlink /usr/bin/ch to /usr/bin/clickhouse.
Creating symlink /usr/bin/chl to /usr/bin/clickhouse.
Creating symlink /usr/bin/chc to /usr/bin/clickhouse.
Creating clickhouse group if it does not exist.
 groupadd -r clickhouse
Creating clickhouse user if it does not exist.
 useradd -r --shell /bin/false --home-dir /nonexistent -g clickhouse clickhouse
Will set ulimits for clickhouse user in /etc/security/limits.d/clickhouse.conf.
Creating config directory /etc/clickhouse-server.
Creating config directory /etc/clickhouse-server/config.d that is used for tweaks of main server configuration.
Creating config directory /etc/clickhouse-server/users.d that is used for tweaks of users configuration.
Data path configuration override is saved to file /etc/clickhouse-server/config.d/data-paths.xml.
Log path configuration override is saved to file /etc/clickhouse-server/config.d/logger.xml.
User directory path configuration override is saved to file /etc/clickhouse-server/config.d/user-directories.xml.
OpenSSL path configuration override is saved to file /etc/clickhouse-server/config.d/openssl.xml.
Creating log directory /var/log/clickhouse-server.
Creating data directory /var/lib/clickhouse.
Creating pid directory /var/run/clickhouse-server.
 chown -R clickhouse:clickhouse '/var/log/clickhouse-server'
 chown -R clickhouse:clickhouse '/var/run/clickhouse-server'
 chown  clickhouse:clickhouse '/var/lib/clickhouse'
Set up the password for the default user: 
Password for the default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.
Setting capabilities for clickhouse binary. This is optional.
Allow server to accept connections from the network (default is localhost only), [y/N]: N
 chown -R clickhouse:clickhouse '/etc/clickhouse-server'

ClickHouse has been successfully installed.

Start clickhouse-server with:
 sudo clickhouse start

Start clickhouse-client with:
 clickhouse-client --password

推荐文献

• Linux上clickhouse-client安装 - CSDN

• Clickhouse Client

• https://clickhouse.com/docs/zh/install
• https://clickhouse.com/docs/zh/interfaces/cli

3 HTTP + Curl

安装 Curl

• Ubuntu Linux | Debian Linux 为例

apt install curl

• Airpline Linux 为例:

apk add curl

CASE 网络可达性验证 / 健康检测

# curl -v http://localhost:8123

# curl -v http://localhost:8123/ping
Ok.

# curl 'http://localhost:8123/replicas_status'
Ok.

CASE 查询数据

//未启用密码认证时
# curl 'http://localhost:8123/?query=show%20databases'

//启用密码认证时
//方式[1] 使用 HTTP 基本身份验证
echo 'SELECT 1' | curl 'http://user:password@localhost:8123/' -d @-

//方式[2] 在 user 和 password URL 参数中 (建议使用此方法，因为参数可能被 Web 代理记录并在浏览器中缓存)
echo 'SELECT 1' | curl 'http://localhost:8123/?user=user&password=password' -d @-

//方式[3] 使用 'X-ClickHouse-User' 和 'X-ClickHouse-Key' 请求头
echo 'SELECT 1' | curl -H 'X-ClickHouse-User: user' -H 'X-ClickHouse-Key: password' 'http://localhost:8123/' -d @-
  (如果未指定用户名，则使用 default 名称。如果未指定密码，则使用空密码。 您还可以使用 URL 参数来指定单个查询的任何设置或整个设置配置文件。)

推荐文献

• HTTP 接口 - 驱动程序和接口 - 原生客户端和接口 - Clickhouse
• [Linux/Bash/Shell] curl & wget - 博客园/千千寰宇
• [网络] 网络可达性测试方法（汇总） - 博客园/千千寰宇

4 JDBC

依赖坐标(Maven)

<!-- clickhouse datasource jdbc dependency -->
<!-- https://search.maven.org/artifact/ru.yandex.clickhouse/clickhouse-jdbc -->
<dependency>
	<groupId>com.clickhouse</groupId>
	<artifactId>clickhouse-jdbc</artifactId>
	<version>${clickhouse.jdbc.version}</version>
	<!-- fix-bug "LZ4 is not supported. Please disable compression(compress=0)" from [LZ4 is not supported. Please disable compression(compress=0) - github/clickhouse-java](https://github.com/ClickHouse/clickhouse-java/issues/1317) | 0.3.2 无此问题，但 0.4.6 存在此问题 -->
	<classifier>http</classifier>
</dependency>

• clickhouse.jdbc.version: 0.4.6

建立连接

//com.clickhouse.client.http.config.ClickHouseHttpOption#CONNECTION_PROVIDER/...
//com.clickhouse.client.http.config.HttpConnectionProvider#APACHE_HTTP_CLIENT
javax.sql.DataSource dataSource = new com.clickhouse.jdbc.ClickHouseDataSource(url, properties);

javax.sql.DataSource#getConnection(java.lang.String:username, java.lang.String:password) //有密码认证时
javax.sql.DataSource#getConnection() //无密码认证时

X 参考文献