ClickHouse客户端使用实践

前提条件

• 已安装MRS集群客户端，例如安装目录为“/opt/client”。
• 对于开启了Kerberos认证的集群，需提前在Manager中创建具有ClickHouse相关操作权限的用户，例如用户名为“clickhouseuser”。

使用ClickHouse客户端

1. 安装MRS集群客户端，具体请参考安装客户端章节。
2. 以客户端安装用户登录客户端所在的节点。
3. 执行以下命令，切换到客户端安装目录。

   cd /opt/client

4. 执行以下命令配置环境变量。

   source bigdata_env

5. 使用clickhouse client命令连接ClickHouse服务端。
   1. 如果当前集群未启用Kerberos认证，执行如下命令连接ClickHouse服务端：

      clickhouse client --host ClickHouse实例的IP地址 --port 9000 --user 用户名 --password

   2. 如果当前集群启用Kerberos认证，执行如下命令连接ClickHouse服务端：

      kinit 组件业务用户

      例如：

      kinit clickhouseuser

      clickhouse client --host ClickHouse实例的IP地址 --port 9440 --user --secure

      

      组件业务用户需要具有创建ClickHouse表的权限，具体请参见创建ClickHouse角色章节，在Manager中创建用户并绑定对应角色。

   表1 clickhouse client命令行参数说明

   参数名	参数说明
   --host	服务端节点名称，可以选择使用ClickHouse实例所在节点主机名或者IP地址。 登录集群FusionInsight Manager界面，然后单击“集群 > 服务 > ClickHouse > 实例”，获取ClickHouseServer实例对应的IP地址。
   --port	连接的端口。 开启Kerberos认证的集群默认使用SSL安全连接，默认端口为9440，并且需要携带参数--secure。 具体的端口值也可通过查询ClickHouseServer实例配置参数“tcp_port_secure”获取。 如果该场景下需要使用非SSL安全连接，则可登录集群FusionInsight Manager，选择“集群 > 服务 > ClickHouse > 配置”，搜索“SSL_NONESSL_BOTH_ENABLE”配置，将该配置修改为“true”并重启所有ClickHouse服务实例。 未开启Kerberos认证的集群默认使用非SSL安全连接，默认端口为9000，不需要携带参数--secure。 具体的端口值也可通过查询ClickHouseServer实例配置参数“tcp_port”参数获取。 如果该场景下需要使用SSL安全连接，则可登录集群FusionInsight Manager，选择“集群 > 服务 > ClickHouse > 配置”，搜索“SSL_NONESSL_BOTH_ENABLE”配置，将该配置修改为“true”并重启所有ClickHouse服务实例即可。 说明： 以上所使用的端口为开源端口，如果在创建集群时使用了定制端口策略，则请参考常见问题进行替换。 可执行clickhouse -h命令，查看ClickHouse组件命令帮助。
   --user	连接用户名。 如果当前集群已启用Kerberos认证（集群为安全模式），执行kinit认证成功后，客户端登录时可以不携带--user和--password参数。Kerberos集群场景下没有默认用户，必须在Manager上创建该用户名。 如果当前集群未启用Kerberos认证（集群为普通模式），客户端登录时如果需要指定用户名和密码。可以使用默认的default用户登录（default默认密码为空），也可以使用创建具有创建ClickHouse相关权限的用户登录。 说明： MRS 3.3.0-LTS及之后版本：未启用Kerberos认证（普通模式）的集群，创建用户可以参考创建ClickHouse角色。 MRS 3.2.0-LTS及之前版本：未启用Kerberos认证（普通模式）的集群，不能使用FusionInsight Manager页面创建的ClickHouse用户，需要使用客户端命令行执行create user SQL语句创建ClickHouse用户。
   --password	连接密码。该参数和--user参数配套使用。
   --query	使用非交互模式查询。
   --database	默认当前操作的数据库。 默认值为服务端的默认配置（default）。
   --multiline	如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。
   --multiquery	如果指定，允许处理用;号分隔的多个查询，只在非交互模式下生效。
   --format	使用指定的默认格式输出结果。
   --vertical	如果指定，默认情况下使用垂直格式输出结果。在这种格式中，每个值都在单独的行上打印，适用显示宽表的场景。
   --time	如果指定，非交互模式下会打印查询执行的时间到stderr中。
   --stacktrace	如果指定，如果出现异常，会打印堆栈跟踪信息。
   --config-file	配置文件的名称。
   --secure	如果指定，将通过SSL安全模式连接到服务器。
   --history_file	存放命令历史的文件的路径。
   --param_<name>	带有参数的查询，并将值从客户端传递给服务器。 具体用法详见https://clickhouse.tech/docs/zh/interfaces/cli/#cli-queries-with-parameters。

6. 执行quit;命令，可退出ClickHouse服务端连接。

查看ClickHouse服务cluster等环境参数信息

1. 使用ClickHouse客户端连接到ClickHouse服务端。
2. 查询集群标识符cluster等其他环境参数信息。
   select cluster,shard_num,replica_num,host_name from system.clusters;

   SELECT 
       cluster, 
       shard_num, 
       replica_num, 
       host_name
   FROM system.clusters
   
   ┌─cluster───────────┬─shard_num─┬─replica_num─┬─host_name──────── ┐
   │ default_cluster_1             │         1   │           1   │ node-master1dOnG           │
   │ default_cluster_1             │         1   │           2   │ node-group-1tXED0001       │
   │ default_cluster_1             │         2   │           1   │ node-master2OXQS           │
   │ default_cluster_1             │         2   │           2   │ node-group-1tXED0002       │
   │ default_cluster_1             │         3   │           1   │ node-master3QsRI           │
   │ default_cluster_1             │         3   │           2   │ node-group-1tXED0003       │
   └─────────────── ┴────── ┴─────── ┴──────────────┘
   
   6 rows in set. Elapsed: 0.001 sec. 

3. 查询分片标识符shard和副本标识符replica。
   select * from system.macros;

   SELECT *
   FROM system.macros
   
   ┌─macro───┬─substitution─────┐
   │ id          │ 76                     │
   │ replica     │ 2                      │
   │ shard       │ 3                      │
   └────── ┴────────────┘
   
   3 rows in set. Elapsed: 0.001 sec. 

创建本地复制表和分布式表

1. 使用ReplicatedMergeTree引擎创建复制表。

   详细的语法说明请参考：https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/replication/#creating-replicated-tables。

   例如，在default_cluster_1集群节点上和default数据库下创建表名为test的ReplicatedMergeTree表：

   CREATE TABLE default.test ON CLUSTER default_cluster_1

   (

   `EventDate` DateTime,

   `id` UInt64

   )

   ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')

   PARTITION BY toYYYYMM(EventDate)

   ORDER BY id;

   参数说明如下：

   • ON CLUSTER语法表示分布式DDL，即执行一次就可在集群所有实例上创建同样的本地表。
   • default_cluster_1为查看ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。
     

     ReplicatedMergeTree引擎族接收两个参数：

     • ZooKeeper中该表相关数据的存储路径。

       该路径必须在/clickhouse目录下，否则后续可能因为ZooKeeper配额不够导致数据插入失败。

       为了避免不同表在ZooKeeper上数据冲突，目录格式必须按照如下规范填写：

       /clickhouse/tables/{shard}/default/test，其中/clickhouse/tables/{shard}为固定值，default为数据库名，test为创建的表名。

     • 副本名称，一般用{replica}即可。

   CREATE TABLE default.test ON CLUSTER default_cluster_1
   (
       `EventDate` DateTime, 
       `id` UInt64
   )
   ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')
   PARTITION BY toYYYYMM(EventDate)
   ORDER BY id
   
   ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ node-group-1tXED0002                   │  9000  │      0   │         │                   5   │                3   │
   │ node-group-1tXED0003                   │  9000  │      0   │         │                   4   │                3   │
   │ node-master1dOnG                       │  9000  │      0   │         │                   3   │                3   │
   └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
   ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ node-master3QsRI                       │  9000  │      0   │         │                   2   │                0   │
   │ node-group-1tXED0001                   │  9000  │      0   │         │                   1   │                0   │
   │ node-master2OXQS                       │  9000  │      0   │         │                   0   │                0   │
   └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
   
   6 rows in set. Elapsed: 0.189 sec. 

2. 使用Distributed引擎创建分布式表。

   例如，以下将在default_cluster_1集群节点上和default数据库下创建名为test_all的Distributed表：

   CREATE TABLE default.test_all ON CLUSTER default_cluster_1

   (

   `EventDate` DateTime,

   `id` UInt64

   )

   ENGINE = Distributed(default_cluster_1, default, test, rand());

   CREATE TABLE default.test_all ON CLUSTER default_cluster_1
   (
       `EventDate` DateTime, 
       `id` UInt64
   )
   ENGINE = Distributed(default_cluster_1, default, test, rand())
   
   ┌─host─────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ node-group-1tXED0002                   │  9000  │      0   │         │                   5   │                0   │
   │ node-master3QsRI                       │  9000  │      0   │         │                   4   │                0   │
   │ node-group-1tXED0003                   │  9000  │      0   │         │                   3   │                0   │
   │ node-group-1tXED0001                   │  9000  │      0   │         │                   2   │                0   │
   │ node-master1dOnG                       │  9000  │      0   │         │                   1   │                0   │
   │ node-master2OXQS                       │  9000  │      0   │         │                   0   │                0   │
   └────────────────────┴────┴─────┴──── ┴─────────── ┴──────────┘
   
   6 rows in set. Elapsed: 0.115 sec. 
   

   

   Distributed引擎需要以下几个参数：

   • default_cluster_1为查看ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。
   • default本地表所在的数据库名称。
   • test为本地表名称。
   • （可选的）分片键（sharding key）

     该键与config.xml中配置的分片权重（weight）一同决定写入分布式表时的路由，即数据最终落到哪个物理表上。它可以是表中一列的原始数据（如site_id），也可以是函数调用的结果，如上面的SQL语句采用了随机值rand()。注意该键要尽量保证数据均匀分布，另外一个常用的操作是采用区分度较高的列的哈希值，如intHash64(user_id)。

ClickHouse表数据操作

1. 创建表后，可以插入数据到本地表。

   例如插入数据到本地表test。

   insert into test values(toDateTime(now()), rand());

2. 查询本地表信息。

   例如查询表test数据信息：

   select * from test;

   SELECT *
   FROM test
   
   ┌───────────EventDate─┬─────────id─┐
   │ 2020-11-05 21:10:42             │ 1596238076           │
   └──────────────── ┴───────────┘
   
   1 rows in set. Elapsed: 0.002 sec. 
    

3. 查询Distributed分布式表。

   例如分布式表test_all基于test创建，所以test_all表也能查询到和test相同的数据。

   select * from test_all;

   SELECT *
   FROM test_all
   
   ┌───────────EventDate─┬─────────id─┐
   │ 2020-11-05 21:10:42             │ 1596238076           │
   └──────────────── ┴───────────┘
   
   1 rows in set. Elapsed: 0.004 sec. 

4. 切换登录节点为相同shard_num的shard节点，并且查询当前表信息，能查询到相同的表数据。

   例如，退出原有登录节点：exit;

   切换到节点node-group-1tXED0003：

   clickhouse client --host node-group-1tXED0003 --multiline --port 9440 --secure;

   show tables;

   SHOW TABLES
   
   ┌─name─────┐
   │ test           │
   │ test_all       │
   └────────┘
    

5. 查询本地表数据。例如在节点node-group-1tXED0003查询test表数据。
   select * from test;

   SELECT *
   FROM test
   
   ┌───────────EventDate─┬─────────id─┐
   │ 2020-11-05 21:10:42             │ 1596238076           │
   └──────────────── ┴───────────┘
   
   1 rows in set. Elapsed: 0.005 sec. 
   

6. 切换到不同shard_num的shard节点，并且查询之前创建的表数据信息。

   例如退出之前的登录节点node-group-1tXED0003：

   exit;

   切换到node-group-1tXED0001节点。

   clickhouse client --host node-group-1tXED0001 --multiline --port 9440 --secure;

   查询test本地表数据，因为test是本地表所以在不同分片节点上查询不到数据。

   select * from test;

   SELECT *
   FROM test
   
   Ok.

   查询test_all分布式表数据，能正常查询到数据信息。

   select * from test_all;

   SELECT *
   FROM test
   
   ┌───────────EventDate─┬─────────id─┐
   │ 2020-11-05 21:12:19             │ 3686805070           │
   └──────────────── ┴───────────┘
   
   1 rows in set. Elapsed: 0.002 sec. 
    

常见问题

执行连接ClickHouse组件客户端命令后，登录报错“Connection refused”。

请检查当前集群是否为定制端口（在创建集群时将“组件端口”参数选择为“定制”），如果为定制端口，则需要将连接ClickHouse组件客户端命令中所使用的端口替换为下表中的“定制默认端口”。