ClickHouse中select final和optimize table final的区别
ClickHouse中select final和optimize table final的区别
使用 OPTIMIZE TABLE FINAL 该语句会对表的数据部分进行计划外的合并，通常不建议使用。见官档：传送门
而在select中当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。见官档：传送门
其中，需要特别注意的是：
1.optimize table final会立刻对表数据进行物理合并；select final只针对此次查询生效，并不会对实际物理分区进行合并。
2.optimize table final只会对同个分片同个分区数据进行物理合并；select final则可以对同个分片不同分区的数据也会进行合并，但是不同分片即使满足合并条件也无法合并
以下针对上述两个点进行实验。
1.optimize table final会立刻对表数据进行物理合并；select final只针对此次查询生效，并不会对实际物理分区进行合并。
创建单点表并插入测试数据，语句如下：防偷防爬
CREATE TABLE zkm ( `id` String DEFAULT 'NULL' COMMENT '用户编号', `repo` String DEFAULT 'NULL' COMMENT '仓库编号', `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间' ) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(CREATE_TIME) ORDER BY id SETTINGS old_parts_lifetime = 0; insert into zkm settings insert_deduplicate=0 values ('id1','repo1','2023-12-11 11:30:11') ; insert into zkm settings insert_deduplicate=0 values ('id1','repo1','2023-12-11 12:30:11') ; insert into zkm settings insert_deduplicate=0 values ('id2','repo1','2023-12-12 11:30:11') ;
此时可以看到对应路径中产生3个文件夹，分别时这3条insert产生的，如下：
其中有两条数据属于同一天即同一个分区。
[root@ck01 zkm]# ll total 4 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231211_1_1_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231211_2_2_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231212_3_3_0 drwxr-x--- 2 clickhouse clickhouse 6 Dec 12 17:26 detached -rw-r----- 1 clickhouse clickhouse 1 Dec 12 17:26 format_version.txt
https://www.cnblogs.com/PiscesCanon/p/17898382.html
此时，执行optimize table final，文件夹all_1_1_0和all_2_2_0将被合并为all_1_2_1，
文件夹20231212_3_3_0被合并为20231212_3_3_1（没错，即便只有它自己一个同分区数据）。
ck01 :) optimize table zkm final; OPTIMIZE TABLE zkm FINAL Query id: 0aadfe30-c489-45d8-a063-d29f8a72b73d Ok. 0 rows in set. Elapsed: 0.010 sec. [root@ck01 zkm]# ll total 4 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:27 20231211_1_2_1 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:27 20231212_3_3_1 drwxr-x--- 2 clickhouse clickhouse 6 Dec 12 17:26 detached -rw-r----- 1 clickhouse clickhouse 1 Dec 12 17:26 format_version.txt
如果将上述optimize table final换成select final操作(可删除表zkm且重复执行脚本)。
此时：
[root@ck01 zkm]# ll total 4 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_1_1_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_2_2_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231212_3_3_0 drwxr-x--- 2 clickhouse clickhouse 6 Dec 12 17:36 detached -rw-r----- 1 clickhouse clickhouse 1 Dec 12 17:36 format_version.txt
执行select final操作：
可以看到，select final并不会实际发生物理上的合并。
ck01 :) select * from zkm final; SELECT * FROM zkm FINAL Query id: b28a3986-39a6-4c0b-bf87-5474778317cf ┌─id──┬─repo──┬─────────CREATE_TIME─┐ │ id2 │ repo1 │ 2023-12-12 11:30:11 │ └─────┴───────┴─────────────────────┘ ┌─id──┬─repo──┬─────────CREATE_TIME─┐ │ id1 │ repo1 │ 2023-12-11 12:30:11 │ └─────┴───────┴─────────────────────┘ 2 rows in set. Elapsed: 0.006 sec. [root@ck01 zkm]# ll total 4 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_1_1_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_2_2_0 drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231212_3_3_0 drwxr-x--- 2 clickhouse clickhouse 6 Dec 12 17:36 detached -rw-r----- 1 clickhouse clickhouse 1 Dec 12 17:36 format_version.txt
2.optimize table final只会对同个分片同个分区数据进行物理合并；select final则可以对同个分片不同分区的数据也会进行合并，但是不同分片即使满足合并条件也无法合并。
不讨论optimize table final，因为此语句因为只涉及物理合并因此只限定在单台ck服务器，讨论集群情况无意义。
这之前先介绍我实验环境的ck集群架构：
共4个节点，其中1，2互为副本（分片1），3，4互为副本（分片2）。
创建分布式表和本地表，并且在不同节点（即控制不同分片插入不同数据）插入数据。
CREATE TABLE zkm on cluster ceb_cluster ( `id` String DEFAULT 'NULL' COMMENT '用户编号', `repo` String DEFAULT 'NULL' COMMENT '仓库编号', `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间' ) ENGINE = Distributed('ceb_cluster', 'zkm', 'zkm_local', hiveHash(toYYYYMMDD(CREATE_TIME))); CREATE TABLE zkm_local on cluster ceb_cluster ( `id` String DEFAULT 'NULL' COMMENT '用户编号', `repo` String DEFAULT 'NULL' COMMENT '仓库编号', `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间' ) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/ceb_cluster-{shard}/ceb/zkm_local', '{replica}') PARTITION BY toYYYYMMDD(CREATE_TIME) ORDER BY id SETTINGS old_parts_lifetime = 0; 行1：分片1（节点1或2执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian1','2023-12-11 01:01:01'); 行2：分片1（节点1或2执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian2','2023-12-12 02:02:02'); 行3：分片2（节点3或4执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian3','2023-12-11 03:03:03');
其中，行1，2属于同一个分片，不同分区
行1，3属于不同分片，物理上是不同分区，逻辑上则属于同一个分区
现在在节点1或者2执行如下语句，可以看到：
本地表可以看到行1，2，加上final的时候，即使行1，2属于不同分区也进行了合并
szeport-ck01 :) select * from zkm_local; SELECT * FROM zkm_local Query id: 8324bd02-484a-49c9-af0f-b87a9f108d9d ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian2 │ 2023-12-12 02:02:02 │ └─────┴──────────┴─────────────────────┘ ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian1 │ 2023-12-11 01:01:01 │ └─────┴──────────┴─────────────────────┘ 2 rows in set. Elapsed: 0.004 sec. szeport-ck01 :) select * from zkm_local final; SELECT * FROM zkm_local FINAL Query id: a81e2185-ab0f-40ce-84f1-0f27867e4f90 ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian2 │ 2023-12-12 02:02:02 │ └─────┴──────────┴─────────────────────┘ 1 row in set. Elapsed: 0.005 sec.
现在在节点1或者2执行如下语句，可以看到：
查询分布式表加上final的时候，即使行2，3属于不同分区也没有合并，而行1，2则还是不同分区但是合并。
szeport-ck01 :) select * from zkm; SELECT * FROM zkm Query id: d5b59807-f8cf-43fc-a6e1-e52095cf7da4 ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian2 │ 2023-12-12 02:02:02 │ └─────┴──────────┴─────────────────────┘ ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian1 │ 2023-12-11 01:01:01 │ └─────┴──────────┴─────────────────────┘ ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian3 │ 2023-12-11 03:03:03 │ └─────┴──────────┴─────────────────────┘ 3 rows in set. Elapsed: 0.010 sec. szeport-ck01 :) select * from zkm final; SELECT * FROM zkm FINAL Query id: 45562a30-f11e-484a-867b-67b90ac7093a ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian2 │ 2023-12-12 02:02:02 │ └─────┴──────────┴─────────────────────┘ ┌─id──┬─repo─────┬─────────CREATE_TIME─┐ │ id1 │ jiedian3 │ 2023-12-11 03:03:03 │ └─────┴──────────┴─────────────────────┘ 2 rows in set. Elapsed: 0.011 sec.
从现象看原因，由于分布式查询select * from zkm final实际上是被拆分为select * from zkm_local final分到各个分片服务器上执行，最终在合并各个服务器的结果。
结合select final官方的说明：“而在select中当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。”
select * from zkm_local在各自的分片查询后的返回结果的之前完全合并了数据，再合并汇总返回到执行select * from zkm final的节点（合并汇总则不会进行数据合并）。
因此，只有同个分片即便不同分区的数据能够合并，而不同分片即便从数值上看满足合并条件，但是依旧不会合并的原因。
至此。（头有点涨）
