# ClickHouse客户端使用实践 #### 操作场景 ClickHouse是面向联机分析处理的列式数据库，支持SQL查询，且查询性能好，特别是基于大宽表的聚合分析查询性能非常优异，比其他分析型数据库速度快一个数量级。 ClickHouse依靠ReplicatedMergeTree引擎与ZooKeeper实现了复制表机制，用户在创建表时可以通过指定引擎选择该表是否高可用，每张表的分片与副本都是互相独立的。 同时ClickHouse依靠Distributed引擎实现了分布式表机制，在所有分片（本地表）上建立视图进行分布式查询，使用很方便。ClickHouse有数据分片（shard）的概念，这也是分布式存储的特点之一，即通过并行读写提高效率。 本章节指导用户在创建MRS集群后通过集群客户端快速连接ClickHouse服务。 #### 前提条件 - 已安装MRS集群客户端，例如安装目录为"/opt/client"。 - 对于开启了Kerberos认证的集群，需提前在Manager中创建具有ClickHouse相关操作权限的用户，例如用户名为"clickhouseuser"。 #### 操作视频 该视频为您介绍如何在MRS集群创建成功后，通过ClickHouse客户端实现创建表，往表中插入数据等功能。 因不同版本操作界面可能存在差异，相关视频供参考，具体以实际环境为准。 #### 使用ClickHouse客户端 1. 安装MRS集群客户端，具体请参考[安装客户端](https://support.huaweicloud.com/usermanual-mrs/mrs_01_0089.html)章节。 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服务端： ``` clickhouse client --host ClickHouse实例的IP地址 --port 9440 --secure ``` ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png) 组件业务用户需要具有创建ClickHouse表的权限，具体请参见[创建具有ClickHouse权限的用户](https://support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_24057.html)章节，在Manager中创建用户并绑定对应角色。 表1clickhouse 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权限的用户](https://support.huaweicloud.com/cmpntguide-lts-mrs/mrs_01_24057.html)。 MRS 3.2.0-LTS及之前版本：未启用Kerberos认证（普通模式）的集群，不能使用FusionInsight Manager页面创建的ClickHouse用户，需要使用客户端命令行执行create user SQL语句创建ClickHouse用户。 MRS 3.2.0-LTS版本：未启用Kerberos认证（普通模式）的集群，不建议使用默认的**default**用户。 | | --password | 连接密码。该参数和--user参数配套使用。 | | --query | 使用非交互模式查询。 | | --database | 默认当前操作的数据库。 默认值为服务端的默认配置（default）。 | | --multiline | 如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。 | | --multiquery | 如果指定，允许处理用;号分隔的多个查询，只在非交互模式下生效。 | | --format | 使用指定的默认格式输出结果。 | | --vertical | 如果指定，默认情况下使用垂直格式输出结果。在这种格式中，每个值都在单独的行上打印，适用显示宽表的场景。 | | --time | 如果指定，非交互模式下会打印查询执行的时间到stderr中。 | | --stacktrace | 如果指定，如果出现异常，会打印堆栈跟踪信息。 | | --config-file | 配置文件的名称。 | | --secure | 如果指定，将通过SSL安全模式连接到服务器。 | | --history_file | 存放命令历史的文件的路径。 | | --param_\ | 带有参数的查询，并将值从客户端传递给服务器。 具体用法详见。 | 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引擎创建复制表。 详细的语法说明请参考：。 例如，在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; ``` 表2参数解释 | 参数 | 参数说明 | |:---|:---| | ON CLUSTER | 表示分布式DDL，即执行一次就可在集群所有实例上创建同样的本地表。 | | default_cluster_1 | ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。 | ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/caution_3.0-zh-cn.png) **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()); ``` 表3Distributed引擎参数解释 | 参数 | 参数说明 | |:---|:---| | default_cluster_1 | ClickHouse服务cluster等环境参数信息中查询到的cluster集群标识符。 | | default | 本地表所在的数据库名称。 | | test | 本地表名称。 | | rand | （可选）分片键（sharding key）。 该键与config.xml中配置的分片权重（weight）一同决定写入分布式表时的路由，即数据最终落到哪个物理表上。它可以是表中一列的原始数据（如site_id），也可以是函数调用的结果，如上面的SQL语句采用了随机值rand()。注意该键要尽量保证数据均匀分布，另外一个常用的操作是采用区分度较高的列的哈希值，如intHash64(user_id)。 | 结果如下： ``` ┌─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. ``` #### 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组件客户端命令中所使用的端口替换为下表中的"定制默认端口"。 | 配置参数 | 开源默认端口 | 定制默认端口 | 端口说明 | |:---|:---|:---|:---| | interserver_http_port | 9009 | 9009 | 用于在ClickHouse server间通信的http端口。 | | interserver_https_port | 9010 | 9010 | 用于在ClickHouse server间通信的https端口。 | | http_port | 8123 | 8123 | 用于通过http连接到ClickHouse server的端口。 | | https_port | 8443 | 8443 | 用于通过https连接到ClickHouse server的端口。 | | tcp_port | 9000 | 9000 | 用于客户端通过TCP连接到ClickHouse server的端口。 | | tcp_port_secure | 9440 | 9440 | 用于客户端通过TCP SSL连接到ClickHouse server的端口。 | | lb_tcp_port | 21424 | 21424 | ClickHouseBalancer的TCP通信端口号。 | | lb_http_port | 21425 | 21425 | ClickHouseBalancer的Http通信端口号。 | | lb_https_port | 21426 | 21426 | ClickHouseBalancer的Https通信端口号。 | | lb_tcp_secure_port | 21428 | 21428 | ClickHouseBalancer的TCP SSL通信端口号。 |