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]: Nchown -R clickhouse:clickhouse '/etc/clickhouse-server'ClickHouse has been successfully installed.Start clickhouse-server with:sudo clickhouse startStart 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() //无密码认证时