Docker快速搭建Clickhouse集群(3分片3副本)
16lz
2021-02-15
背景
前文简单介绍了下Clickhouse的安装和客户端使用，在实际生产环境中，Clickhouse常常是以集群模式部署的，由于很多系统不满足sse4.2指令，这里使用docker来搭建一个Clickhouse的集群。
1. 环境说明
1.1 机器列表
机器名 | IP | 配置 | 操作系统 | 部署的服务 | 备注 |
server01 | 192.168.21.21 | 8c8g | centos7.3 | clickhouserver（cs01-01）和 clickhouserver（cs01-02） | clickhouse01-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片1, 副本1 clickhouse01-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片2, 副本2 (clickhouse2的副本) |
server02 | 192.168.21.69 | 8c8g | centos7.3 | clickhouserver（cs02-01）和 clickhouserver（cs02-02） | clickhouse02-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片2, 副本1 clickhouse02-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片3, 副本2 (clickhouse3的副本) |
server03 | 192.168.21.6 | 8c8g | centos7.3 | clickhouserver（cs03-01）和 clickhouserver（cs03-02） | clickhouse03-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片3, 副本1 clickhouse03-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片1, 副本2 (clickhouse1的副本) |
1.2 机器初始化
1.2.1 配置host
执行：vi /etc/hosts， 加入下面三行：
192.168.21.21 server01
192.168.21.69 server02
192.168.21.6 server03
1.2.2 安装docker
每台机器上均安装相同版本的docker
参照：docker环境搭建
1.2.3 安装Zookeeper
参照zookper集群搭建（3节点）安装
1.3 目录初始化
1.3.1 创建对应本地路径
分别在三台服务器，创建数据存储目录：
mkdir /data/clickhouse
1.3.2 获取clickhouse-server的配置
1）在server01服务器做如下操作
先按照官方教程的docker命令启动Clickhouse-Server
docker run -d --name clickhouse-server --ulimit nofile=262144:262144 --volume=/data/clickhouse/:/var/lib/clickhouse yandex/clickhouse-server
2）启动完成后，复制容器内的配置文件到本机目录下
#拷贝容器内容的配置到/etc目录下docker cp clickhouse-server:/etc/clickhouse-server/ /etc/#将server01上的目录重命名cp -rf /etc/clickhouse-server/ /etc/clickhouse-server01/cp -rf /etc/clickhouse-server/ /etc/clickhouse-server02/
3）然后将/etc/clickhouse-server/ 分别拷贝到每个机器上
#拷贝配置到server02上scp /etc/clickhouse-server/ server02:/etc/clickhouse-server01/scp /etc/clickhouse-server/ server02:/etc/clickhouse-server02/#拷贝配置到server03上scp /etc/clickhouse-server/ server03:/etc/clickhouse-server01/scp /etc/clickhouse-server/ server03:/etc/clickhouse-server02/
2. 集群环境搭建
2.1 集群环境拓扑图
集群环境说明：
clickhouse01-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片1, 副本1
clickhouse01-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片2, 副本2 (clickhouse2的副本)
clickhouse02-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片2, 副本1
clickhouse02-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片3, 副本2 (clickhouse3的副本)
clickhouse03-01: 实例1, 端口: tcp 9000, http 8123, 同步端口9009, 类型: 分片3, 副本1
clickhouse03-02: 实例2, 端口: tcp 9001, http 8124, 同步端口9010, 类型: 分片1, 副本2 (clickhouse1的副本)
2.2 配置集群
2.2.1 待修改的配置文件
需要修改的配置有两个(如果有需要也可以配置user.xml)：
- /etc/clickhouse-server/config.xml
- /etc/clickhouse-server/metrika.xml（新增文件）
2.2.2 server1上配置clickhouse-server的实例
2.2.2.1 clickhouse-01-01的配置：
1）/etc/clickhouse-server01/config.xml(其他实例此配置内容和这个一样就行）
修改include from节点为实际的引用到的文件
<!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file. By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element. Values for substitutions are specified in /yandex/name_of_substitution elements in that file. --> <include_from>/etc/clickhouse-server/metrika.xml</include_from> <listen_host>0.0.0.0</listen_host> <listen_host>127.0.0.1</listen_host>
2）/etc/clickhouse-server01/metrika.xml（所有实例的配置内容都和这个一样就行）
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <!-- 集群配置 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片1, 副本1 --> <macros> <layer>01</layer> <shard>01</shard> <replica>cluster01-01-1</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.2.2.2 clickhouse-01-02的配置：
1）/etc/clickhouse-server02/metrika.xml（所有实例的配置内容都和这个一样就行）
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片2, 副本2--> <macros> <layer>01</layer> <shard>02</shard> <replica>cluster01-02-2</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.2.3 server2上配置clickhouse-server的实例
2.2.3.1 clickhouse-02-01的配置：
/etc/clickhouse-server01/metrika.xml
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片2, 副本1--> <macros> <layer>01</layer> <shard>02</shard> <replica>cluster01-02-1</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.2.3.2 clickhouse-02-02的配置：
/etc/clickhouse-server02/metrika.xml
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片3, 副本2--> <macros> <layer>01</layer> <shard>03</shard> <replica>cluster01-03-2</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.2.4 server3上配置clickhouse-server的实例
2.2.4.1 clickhouse-03-01的配置：
1）/etc/clickhouse-server01/metrika.xml
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片3, 副本1--> <macros> <layer>01</layer> <shard>03</shard> <replica>cluster01-03-1</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.2.4.2 clickhouse-03-02的配置：
1. /etc/clickhouse-server02/metrika.xml
<!--所有实例均使用这个集群配置，不用个性化 --><yandex> <!-- 集群配置 --> <!-- clickhouse_remote_servers所有实例配置都一样 --> <clickhouse_remote_servers> <cluster_3s_1r> <!-- 数据分片1 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server01</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server03</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片2 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server02</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server01</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> <!-- 数据分片3 --> <shard> <internal_replication>true</internal_replication> <replica> <host>server03</host> <port>9000</port> <user>default</user> <password></password> </replica> <replica> <host>server02</host> <port>9001</port> <user>default</user> <password></password> </replica> </shard> </cluster_3s_1r> </clickhouse_remote_servers> <!-- ZK --> <!-- zookeeper_servers所有实例配置都一样 --> <zookeeper-servers> <node index="1"> <host>192.168.21.66</host> <port>2181</port> </node> <node index="2"> <host>192.168.21.57</host> <port>2181</port> </node> <node index="3"> <host>192.168.21.17</host> <port>2181</port> </node> </zookeeper-servers> <!-- marcos每个实例配置不一样 分片1, 副本2--> <macros> <layer>01</layer> <shard>01</shard> <replica>cluster01-01-2</replica> </macros> <networks> <ip>::/0</ip> </networks> <!-- 数据压缩算法 --> <clickhouse_compression> <case> <min_part_size>10000000000</min_part_size> <min_part_size_ratio>0.01</min_part_size_ratio> <method>lz4</method> </case> </clickhouse_compression></yandex>
2.3 运行clickhouse集群
2.3.1 运行clickhouse01-01实例
登陆到server01：ssh server01
#先删除cs01-01容器docker rm -f cs01-01#运行Clickhouse-serverdocker run -d \--name cs01-01 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse01/:/var/lib/clickhouse \--volume=/etc/clickhouse-server01/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9000:9000 \-p 8123:8123 \-p 9009:9009 \yandex/clickhouse-server
说明：
--add-host参数：因为我们在配置文件中使用了hostname来指代我们的服务器，为了让容器能够识别，所以需要加此参数，对应的host配置会自动被添加到容器主机的/etc/hosts里面
--hostname参数：clickhouse中的system.clusters表会显示集群信息，其中is_local的属性如果不配置hostname的话clickhouse无法识别是否是当前本机。is_local都为0的话会影响集群操作，is_local通过clickhouse-client登录到任一clickhouse-server上查看：SELECT * FROM system.clusters;
--p参数：暴露容器中的端口到本机端口中。
2.3.2 运行clickhouse01-02实例
登陆到server01：ssh server01;
docker run -d \--name cs01-02 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse02/:/var/lib/clickhouse \--volume=/etc/clickhouse-server02/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9001:9000 \-p 8124:8123 \-p 9010:9009 \yandex/clickhouse-server
2.3.3 运行clickhouse02-01实例
登陆到server02：ssh server02
docker run -d \--name cs02-01 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse01/:/var/lib/clickhouse \--volume=/etc/clickhouse-server01/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9000:9000 \-p 8123:8123 \-p 9009:9009 \yandex/clickhouse-server
2.3.4 运行clickhouse02-02实例
登陆到server02：ssh server02
docker run -d \--name cs02-02 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse02/:/var/lib/clickhouse \--volume=/etc/clickhouse-server02/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9001:9000 \-p 8124:8123 \-p 9010:9009 \yandex/clickhouse-server
2.3.5 运行clickhouse03-01实例
登陆到server03：ssh server03
docker run -d \--name cs03-01 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse01/:/var/lib/clickhouse \--volume=/etc/clickhouse-server01/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9000:9000 \-p 8123:8123 \-p 9009:9009 \yandex/clickhouse-server
2.3.6 运行clickhouse03-02实例
登陆到server03：ssh server03
docker run -d \--name cs03-02 \--ulimit nofile=262144:262144 \--volume=/data/clickhouse02/:/var/lib/clickhouse \--volume=/etc/clickhouse-server02/:/etc/clickhouse-server/ \--add-host server01:192.168.21.21 \--add-host server02:192.168.21.69 \--add-host server03:192.168.21.6 \--add-host i-r9es2e0q:192.168.21.21 \--add-host i-o91d619w:192.168.21.69 \--add-host i-ldipmbwa:192.168.21.6 \--hostname $(hostname) \-p 9001:9000 \-p 8124:8123 \-p 9010:9009 \yandex/clickhouse-server
2.4 clickhouse集群的数据操作
2.4.1 运行客户端连接clickhouse server
1）随便在哪一台实例的机器上执行如下（连不同的clickhouse实例只需要改下host和port的值即可）
docker run -it \ --rm \ --add-host server01:192.168.21.21 \ --add-host server02:192.168.21.69 \ --add-host server03:192.168.21.6 \ yandex/clickhouse-client \ --host server01 \ --port 9000
2）执行下如下命令查看下配置信息：
#需要看下，是否和metrika.xml配置的分片和副本信息一致，如果不一致，需要check下每个clickhouse-server实例的配置
#应该is_local显示为0，且分片和副本信息都正确
SELECT * FROM system.clusters;
2.4.2 创建本地复制表和分布式表
所有实例配置完上面这些之后，分别执行启动命令启动，然后所有实例都执行下面语句创建数据库：
例如在实例01-02上执行：
#执行Clickhouse client进入Clickhousedocker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \ yandex/clickhouse-client --host server01 --port 9001ClickHouse client version 19.17.5.18 (official build).Connecting to server01:9001 as user default.Connected to ClickHouse server version 19.17.5 revision 54428.i-r9es2e0q :) CREATE DATABASE test;CREATE DATABASE testOk.
2.4.3 创建复制表
然后对于所有实例分别创建对应的复制表，这里测试创建一个简单的表：
#在clickhouse01-01 9000上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server01 --port 9000#然后执行CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-01/device_thing_data','cluster01-01-1') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=8192 #在clickhouse01-02 9001上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server01 --port 9001然后执行CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-02/device_thing_data','cluster01-02-2') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=8192 #在clickhouse02-01 9000上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server02 --port 9000#然后执行CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-02/device_thing_data','cluster01-02-1') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=8192 #在clickhouse02-02 9001上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server02 --port 9001 #然后执行CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-03/device_thing_data','cluster01-03-2') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=8192 #在clickhouse03-01 9000上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server03 --port 9000#然后执行CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-03/device_thing_data','cluster01-03-1') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=8192 #在clickhouse03-02 9001上执行:docker run -it --rm --add-host server01:192.168.21.21 --add-host server02:192.168.21.69 --add-host server03:192.168.21.6 \yandex/clickhouse-client --host server03 --port 9001 然后执行 CREATE TABLE test.device_thing_data ( time UInt64, user_id String, device_id String, source_id String, thing_id String, identifier String, value_int32 Int32, value_float Float32, value_double Float64, value_string String, value_enum Enum8('0'=0,'1'=1,'2'=2,'3'=3,'4'=4,'5'=5,'6'=6,'7'=7,'8'=8), value_string_ex String, value_array_string Array(String), value_array_int32 Array(Int32), value_array_float Array(Float32), value_array_double Array(Float64), action_date Date, action_time DateTime ) Engine= ReplicatedMergeTree('/clickhouse/tables/01-01/device_thing_data','cluster01-01-2') PARTITION BY toYYYYMM(action_date) ORDER BY (user_id,device_id,thing_id,identifier,time,intHash64(time)) SAMPLE BY intHash64(time) SETTINGS index_granularity=81922.4.3 创建分布式表（用于查询）
然后创建完上面复制表之后，可以创建分布式表，分布式表只是作为一个查询引擎，本身不存储任何数据，查询时将sql发送到所有集群分片，然后进行进行处理和聚合后将结果返回给客户端，因此clickhouse限制聚合结果大小不能大于分布式表节点的内存，当然这个一般条件下都不会超过；分布式表可以所有实例都创建，也可以只在一部分实例创建，这个和业务代码中查询的示例一致，建议设置多个，当某个节点挂掉时可以查询其他节点上的表，分布式表的建表语句如下：
#在clickhouse-server集群上一次性创建所有的分布式表，操作卡主了。原因不明CREATE TABLE device_thing_data_all ON CLUSTER cluster_3s_1r AS test.device_thing_data ENGINE = Distributed(cluster_3s_1r, default, device_thing_data, rand())#如下这个需要每个机器上都操作一遍CREATE TABLE device_thing_data_all AS test.device_thing_data ENGINE = Distributed(cluster_3s_1r, test, device_thing_data, rand())
2.4.4 测试可用性
#客户端连接到某个clickhouse-server实例（例如cs01-01）#查询分布式表，此时没有查询到数据select * from image_label_all;#在cs01-01上执行如下查看是否有数据#查询本地复制表，此时没有查询到数据select * from test.device_thing_data;#往复制表中表里插入一条数据INSERT INTO test.device_thing_data; (user_id) VALUES ('1')#由于刚才在cs01-01上插入一条数据，所以应该有数据了select * from test.device_thing_data;#查询分布式表,也有数据了select * from image_label_all;#在cs03-02上查询复制表的数据（由于cs03-02是cs01-01的副本，所以数据被自动同步过来了），所以应该有数据了select * from test.device_thing_data;#在cs03-01上插入一条数据，此时应该会把数据同步到cs02-02上INSERT INTO test.device_thing_data (user_id) VALUES ('2')#再次查询分布式表，此时应该查到cs03-01和cs01-01上的两条数据select * from image_label_all;博主：测试生财（一个不为996而996的测开码农）
座右铭：专注测试开发与自动化运维，努力读书思考写作，为内卷的人生奠定财务自由。
内容范畴：技术提升，职场杂谈，事业发展，阅读写作，投资理财，健康人生。
csdn：https://blog.csdn.net/ccgshigao
博客园：https://www.cnblogs.com/qa-freeroad/
51cto：https://blog.51cto.com/14900374
在码农的苦闷岁月里，期待我们一起成长，欢迎关注，感谢拍砖！
每一份赞赏源于懂得
赞赏
0人进行了赞赏支持
更多相关文章
- Linux下安装配置Cntlm代理
- SE75 采购申请创建抬头文本
- 系统发布用户通知以及相关增强
- HCDA 协议配置
- CentOS7配置IP和远程连接
- 挑战Redis单实例内存最大极限，“遭遇”NUMA陷阱！
- 第2章 0126-细说变量与常量，课程笔记、心得（数据类型的转换与检测
- 0128- 实例演示函数作用域与闭包,回调的使用场景与参数调用
- 实例演示抽象类和接口的区别