- clickhouse分区表调研
- 1.搭建本地环境
- 1.1.搜索镜像是否存在
- 1.2.下载镜像
- 1.3.运行容器
- 1.4.修改密码
- 1.4.1.进入容器
- 1.4.2.生成自定义密码密文
- 1.4.3.重启clickhouse-server
- 1.5.连接clickhouse
- 1.6.测试本地环境
- 2.分区表
- 2.1.概念
- 2.2.作用
- 2.3.分区键的表现形式
- 2.4.分区键的使用注意事项
- 2.4.1.如何查询一张表的分区情况
- 2.4.2.数据写入与分区合并
- 3.分布式表
- 3.1.概念
- 3.2.作用
- 3.3.实现原理
- 3.3.1 写入数据
- 3.3.2 读取数据
- 3.4.语句
- 4.参考资料
本次clickhouse的环境搭建在docker中进行；
windows或者mac系统均需要先安装好docker
1.1.搜索镜像是否存在window系统打开cmd，mac系统在命令行里直接敲入
docker search clickhouse
搜索结果
C:UsersPengyu.Ren>docker search clickhouse NAME DESCRIPTION STARS OFFICIAL AUTOMATED yandex/clickhouse-server ClickHouse is an open-source column-oriented… 365 [OK] clickhouse/clickhouse-server ClickHouse is an open-source column-oriented… 57 clickhouse/kerberos-kdc 1 antrea/clickhouse-server 1 antrea/clickhouse-operator 0 clickhouse/integration-helper 0 clickhouse/python-bottle 0 clickhouse/dotnet-client ClickHouse.NET client for ClickHouse integra… 0 clickhouse/s3-proxy 0 clickhouse/stateless-test 0 clickhouse/stateful-test 0 clickhouse/unit-test 0 clickhouse/postgresql-java-client 0 clickhouse/binary-builder 0 clickhouse/integration-test 0 clickhouse/mysql-java-client 0 clickhouse/mysql-php-client 0 clickhouse/deb-builder 0 clickhouse/mysql-golang-client 0 clickhouse/mysql-js-client 0 clickhouse/stress-test 0 clickhouse/integration-tests-runner 0 clickhouse/fuzzer 0 clickhouse/performance-comparison 0 clickhouse/kerberized-hadoop 0 C:UsersPengyu.Ren>1.2.下载镜像
window系统打开cmd，mac系统在命令行里直接敲入
docker pull yandex/clickhouse-server # 如果需要下载指定版本的clickhouse镜像，可在后面加上版本号，如： docker pull yandex/clickhouse-server:21.3.201.3.运行容器
window系统打开cmd，mac系统在命令行里直接敲入
docker run -d --name clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server
启动成功后，在docker desktop中可以看到新启动的clickhouse-server
1.4.修改密码 1.4.1.进入容器要修改clickhouse-server的默认密码，需要先进入容器，使用如下命令：
docker exec -it clickhouse-server /bin/bash # sample C:UsersPengyu.Ren>docker exec -it clickhouse-server /bin/bash root@77720e63254e:/#
进入容器后，进入/etc/clickhouse-server/users.xml
vim /etc/clickhouse-server/users.xml
如果提示容器内没有编辑器vim，则需要我们手动安装
# 先后执行如下两条命令 apt-get update apt-get install vim -y apt-get update，这个命令的作用是：同步 /etc/apt/sources.list 和 /etc/apt/sources.list.d 中列出的源的索引，这样才能获取到最新的软件包1.4.2.生成自定义密码密文
指令及执行结果如下：
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "123456"; echo -n "123456" | sha256sum | tr -d '-'
root@77720e63254e:/# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "123456"; echo -n "123456" | sha256sum | tr -d '-' 123456 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
123456是明文
8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92是密文
由于官方不建议直接写明文密码，因此我们在这里讲密码修改为密文密码；
继续刚才打开的vim /etc/clickhouse-server/users.xml
默认新安装的，里面有一条,这个标签是设置明文密码的，我们将这个标签改为
1.4.3.重启clickhouse-server8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
mac系统可使用如下指令
systemctl restart clickhouse-server
如使用docker desktop可点击容器后面的restart按钮进行重启即可
1.5.连接clickhouse本次连接使用了DBeaver工具
在DBeaver中新建连接，类型选择clickhouse
点击“下一步”，在地址栏中填入localhost，如果docker假设在本地的话；
用户名default
密码就是刚刚设置的密文密码对应的明文，我这里设置的是123456；
检查驱动可以自动下载所需的驱动，需要之前配好过maven仓库和环境变量；
填写完成，并且下载好驱动之后，点击“测试连接”，测试成功会弹出连接成功和clickhouse-server的版本信息
最后点击“完成”，客户端连接的配置工作就完成了。
1.6.测试本地环境新建sql窗口，执行如下：
SELECT version();
得到版本信息，说明测试通过。
2.分区表 2.1.概念分区是逻辑分区的概念。
2.2.作用分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ClickHouse 尽量使用这些分区的最小子集。
分区是在 建表 时通过 PARTITION BY expr 子句指定的。
2.3.分区键的表现形式是降低扫描的范围，优化查询速度；
-
分区键可以是表中列的任意表达式。sample：指定按月分区，表达式为 toYYYYMM(date_column)：
CREATE TABLE visits ( VisitDate Date, Hour UInt8, ClientID UUID ) ENGINE = MergeTree() PARTITION BY toYYYYMM(VisitDate) ORDER BY Hour; -
分区键也可以是表达式元组（类似 主键 ）。sample：
ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign) PARTITION BY (toMonday(StartDate), EventType) ORDER BY (CounterID, StartDate, intHash32(UserID));
官方文档建议，不要用太精细的分区方案（超过一千个分区）。否则，会因为文件系统中的文件数量过多和需要打开的文件描述符过多，导致 SELECT 查询效率不佳。
2.4.1.如何查询一张表的分区情况SELECT
partition,
name,
active
FROM system.parts
WHERe table = 'visits'
这里我们用一个现存的例子来看，这张表是dev环境中的一张m241的表，表字段较多，因此做了省略，只保留了几个关键字段；
-- 建表
CREATE TABLE dwd_tsp_m241_newenergy_hi
(
`dt` String COMMENT 'odps表分区字段',
`time` String COMMENT '毫秒时间戳',
`second_time` String COMMENT '秒时间戳',
`date_time_str` DateTime COMMENT '基于time算出的yyyy-mm-dd hh:mm:ss',
`date_hour` String COMMENT '基于time算出的分区：yyyymmddhh',
`vin` String,
`configid` String,
`sequenceid` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(date_time_str)
ORDER BY (date_time_str,vin);
-- 初始化数据
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961748','1654672962','2022-06-08 15:23:00','2022060815','L6T79P2N5MP009118','newenergy','178');
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961748','1654672962','2022-06-08 15:23:00','2022060815','L6T79P2N5MP009118','newenergy','178');
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961750','1654672962','2022-06-08 15:23:00','2022060815','L6T79P2N5MP009119','newenergy','178');
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961750','1654672962','2022-06-08 15:23:00','2022060815','L6T79P2N5MP009119','newenergy','178');
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961750','1654672962','2022-06-09 15:23:00','2022060815','L6T79P2N5MP009119','newenergy','178');
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672961750','1654672962','2022-06-09 15:23:00','2022060815','L6T79P2N5MP009119','newenergy','178');
-- 查询分片情况
SELECt partition, name, active FROM system.parts WHERe table = 'dwd_tsp_m241_newenergy_hi';
可以查询得到如下的分片结果：
partition|name |active| ---------|--------------|------| 20220608 |20220608_1_1_0| 1| 20220608 |20220608_2_2_0| 1| 20220608 |20220608_3_3_0| 1| 20220608 |20220608_4_4_0| 1| 20220609 |20220609_5_5_0| 1| 20220609 |20220609_6_6_0| 1|
因为建表时使用的分片键是PARTITION BY toYYYYMMDD(date_time_str)，精确到年月日，初始化数据中存在20220608和20220609的数据，因此存在2个partition；
20220608_1_1_0这个命名的规则如下：
- 20220608是分区的名称；
- 第一个1是数据块的最小编号；
- 第二个1是数据块的最大编号；
- 第三个0是块级别(即在由块组成的合并树中，该块在树中的深度);
active 列为片段状态。1 代表激活状态；0 代表非激活状态。非激活片段是那些在合并到较大片段之后剩余的源数据片段。损坏的数据片段也表示为非活动状态。
再次执行如下语句
INSERT INTO vdp.dwd_tsp_m241_newenergy_hi (dt,`time`,second_time,date_time_str,date_hour,vin,configid,sequenceid)
VALUES ('12345678','1654672966666','1654372962','2022-06-08 15:23:00','2022060815','L6T79P2N5MP009120','newenergy','178');
OPTIMIZE TABLE dwd_tsp_m241_newenergy_hi PARTITION 20220608;
SELECt partition, name, active FROM system.parts WHERe table = 'dwd_tsp_m241_newenergy_hi';
可以得到新的分片在合并之后的结果
partition|name |active| ---------|--------------|------| 20220608 |20220608_1_1_0| 0| 20220608 |20220608_1_7_1| 1| 20220608 |20220608_2_2_0| 0| 20220608 |20220608_3_3_0| 0| 20220608 |20220608_4_4_0| 0| 20220608 |20220608_7_7_0| 0| 20220609 |20220609_5_5_0| 1| 20220609 |20220609_6_6_0| 1|
这里可以看到，相同计算分片键结果的数据块进行了合并；
还可以通过进入表目录的方式查询分片的结果:
root@77720e63254e:/var/lib/clickhouse/data/vdp/dwd_tsp_m241_newenergy_hi# ll total 48 drwxr-x--- 11 clickhouse clickhouse 4096 Jun 8 07:34 ./ drwxr-x--- 3 clickhouse clickhouse 4096 Jun 8 07:07 ../ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:24 20220608_1_1_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:34 20220608_1_7_1/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:28 20220608_2_2_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:28 20220608_3_3_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:28 20220608_4_4_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:34 20220608_7_7_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:30 20220609_5_5_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:30 20220609_6_6_0/ drwxr-x--- 2 clickhouse clickhouse 4096 Jun 8 07:07 detached/ -rw-r----- 1 clickhouse clickhouse 1 Jun 8 07:07 format_version.txt root@77720e63254e:/var/lib/clickhouse/data/vdp/dwd_tsp_m241_newenergy_hi# pwd /var/lib/clickhouse/data/vdp/dwd_tsp_m241_newenergy_hi2.4.2.数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区；
写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中；
非激活片段(active为0)会在合并后的10分钟左右被删除。
上文中的例子，过一段时间再次查询分区情况可得：
partition|name |active| ---------|--------------|------| 20220608 |20220608_1_7_1| 1| 20220609 |20220609_5_5_0| 1| 20220609 |20220609_6_6_0| 1|3.分布式表 3.1.概念
物理分片，数据经过路由，存储到不同的节点上；每个节点上存储部分的数据，分而治之的思想；
3.2.作用根本目的是解决单机数据量太大的问题；
3.3.实现原理clickhouse中，是通过水平切分的方式，将完整的数据切分成不同的分片，这些分片只保存一部分数据，分布在不同的节点上，通过Distributed表引擎来将数据拼成一个完整的表来使用的。Distributed表本身不存储数据，但是能作为查询的表来使用。
在这里，将distributed表称为分布式表，将实际存储数据的节点上的表叫做数据表，每一个片区称为一个shard，每个片区的权重叫weight，每个片区的节点（副本）叫replica，以便于后续的阐述区分。
负载的策略具体通过users.xml配置进行实现
10000000000
random
1
集群的配置通过config.xml实现
以下是clickhouse-server默认自带的配置
3.3.1 写入数据localhost 9000 false 127.0.0.1 9000 127.0.0.2 9000 127.0.0.3 9000 localhost 9000 localhost 9000 127.0.0.1 9000 127.0.0.2 9000 true 127.0.0.1 9000 true 127.0.0.2 9000 localhost 9440 1 localhost 9000 localhost 1
官方提供了向集群写入数据的两种方式：
- 由应用程序决定数据写入什么数据表，并直接在指定的分片上写入；查询的时候，使用分布式表进行查询；这样是最灵活的方案，
你可以使用任何分片方案，对于复杂业务特性的需求，这可能是非常重要的。 这也是最佳解决方案，因为数据可以完全独立地写入不同的分片。
- 在分布式表上进行insert，这种情况下，由分布式表来进行跨服务分发数据。
3.3.2 读取数据二，在分布式表上执行 INSERT。在这种情况下，分布式表会跨服务器分发插入数据。 为了写入分布式表，必须要配置分片键（最后一个参数）。当然，如果只有一个分片，则写操作在没有分片键的情况下也能工作，因为这种情况下分片键没有意义。
每个分片都可以在配置文件中定义权重。默认情况下，权重等于1。数据依据分片权重按比例分发到分片上。例如，如果有两个分片，第一个分片的权重是9，而第二个分片的权重是10，则发送 9 / 19 的行到第一个分片， 10 / 19 的行到第二个分片。
分片可在配置文件中定义 ‘internal_replication’ 参数。默认是false，推荐使用true。
此参数设置为«true»时，写操作只选一个正常的副本写入数据。如果分布式表的子表是复制表(*ReplicaMergeTree)，请使用此方案。换句话说，这其实是把数据的复制工作交给实际需要写入数据的表本身而不是分布式表。
若此参数设置为«false»（默认值），写操作会将数据写入所有副本。实质上，这意味着要分布式表本身来复制数据。这种方式不如使用复制表的好，因为不会检查副本的一致性，并且随着时间的推移，副本数据可能会有些不一样。
通过分布式表读取数据，分布式表会自动整合各个分片的数据；
3.4.语句当查询一个Distributed表时，SELECT查询被发送到所有的分片，不管数据是如何分布在分片上的(它们可以完全随机分布)。当您添加一个新分片时，您不必将旧数据传输到它。相反，您可以使用更重的权重向其写入新数据——数据的分布会稍微不均匀，但查询将正确有效地工作。
Distributed(cluster_name, database, table, [sharding_key]);
参数解析：
cluster_name：服务器配置文件中的集群名，在/etc/metrika.xml中配置的。具体配置见前文。
database：数据库名。
table：表名。
sharding_key：数据分片键。
create table dis_table(id UInt16, name String) engine=Distributed(clickhouse_cluster, default, t, id);
在配置好集群的remote_server配置以及负载的策略后，重启clickhouse-server服务即可生效，再执行上述语句可以实现节点同步新增表dis_table，新增数据时，只会在其中一个shard新增。
clickhouse-server restart4.参考资料
https://clickhouse.com/docs/zh/getting-started/
https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/custom-partitioning-key#alter_attach-partition
https://juejin.cn/post/7073021690875215885
https://juejin.cn/post/6897418615075635214
https://blog.csdn.net/congge_study/article/details/123822299
https://www.bilibili.com/video/BV1xg411w7AP?p=11
