clickhouse数据实时更新实现的三种方式
一、UPDATE+Optimize方式
--建表及插入数据 CREATE TABLE tb_test( ts DateTime, uid String, biz String ) ENGINE = MergeTree() ORDER BY (ts) SETTINGS index_granularity = 8192; INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'a', 'a1'); INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'b', 'b1'); INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'c', 'c1'); --执行更新 alter table tb_test update biz = 'ccccc' where uid = 'c'; optimize table tb_test;
物理文件的前后变化
刚插入时，因为是执行了三次insert，所以生成了3个part
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_2_2_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_3_3_0 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt
执行alter和optimize后
update字段的值就对应着mutation操作，可以看到part（all_1_1_0、all_2_2_0、all_3_3_0）每个都执行了mutation，生成了新的part（all_1_1_0_4、all_2_2_0_4、all_3_3_0_4），后面又执行了merge合并成了all_1_3_1_4这个part。
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:17 all_1_1_0_4 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_2_2_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:17 all_2_2_0_4 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:16 all_3_3_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:17 all_3_3_0_4 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt
由于clickhouse是异步删除的，所以过程中还会有part（all_1_1_0、all_2_2_0、all_3_3_0）短时间存在，但最终会变为如下文件
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:18 all_1_3_1_4 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt
如果在执行了alter和optimize后没有上述变化，可以将optimize语句final
optimize table tb_test final;
由于本次数据压力较小，所以mutation很快执行完了，但也不影响final的实验效果，执行完上述sql后，clickhouse会强制part更新，并且是同步操作，直至成功或失败。
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:18 all_1_3_1_4 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:19 all_1_3_2_4 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt
可以发现，新生成了all_1_3_2_4（all_{min}_{max}_{merge_verson}_{mutation_version}），虽然min和max没变，但是merge_verson加一了，并真正生成了新的part。
all_1_3_1_4后续也会被删掉，最终形态如下
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:19 all_1_3_2_4 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt
注意点：
利用该方法可以读取到最新的数据，但是是建立在强制clickhouse去做新part的生成去代替老part，如果part非常多，optimize的耗时会非常长甚至失败，可以根据实际情况和partiton的分布使数据的更新只涉及部分part，可以提高效率。
二、UPDATE+ SETTING mutations_sync方式
mutations_sync有3种配置，默认为0，即所有的mutation都为异步操作；为1，表示等待当前节点完成mutation操作；为2，表示等待所有节点都完成mutation操作。如果是MergeTree只会判断0或非0，如果是ReplicatedMergeTree才会支持1和2的配置
同样借助上面的表
alter table tb_test update biz = 'ddddd' where uid = 'c' settings mutations_sync = 1
物理文件变化
执行完上述sql就会生成新的part（all_1_3_2_5），并且mutation版本加一
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:19 all_1_3_2_4 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:29 all_1_3_2_5 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:29 mutation_5.txt
最终会删掉all_1_3_2_4，变为如下状态
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:29 all_1_3_2_5 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:15 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:15 format_version.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:17 mutation_4.txt -rw-r----- 1 clickhouse clickhouse 102 3月 7 14:29 mutation_5.txt
注意点：
该方法不需要执行optimize，但原理上还是通过新生成part去代替老part后才能提供新的准确的数据。如果part非常多同样会遇到第一种方式的问题。clickhouse在执行同步操作时可能会因为各种原因失败，但是该方法还是会在后台继续进行更新，具体情况可以根据system.mutations中的记录判断。
三、INSERT+Final方式
这种方法需要ReplacingMergeTree表引擎配置使用
CREATE TABLE tb_test_replacing( ts DateTime, uid String, biz String ) ENGINE = ReplacingMergeTree(ts) ORDER BY (ts) SETTINGS index_granularity = 8192; INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c1');
这种方式就不用执行alter了，而是以insert的形式来代替alter操作，即每次select时都是取最新的一条数据，sql语句如下
INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c2');
此时查询该表，发现还是两条数据，并没有实现更新
SELECT * FROM tb_test_replacing Query id: 02cfecf5-18f1-4f9f-ad55-e7b596935de8 ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ c │ c2 │ └─────────────────────┴─────┴─────┘ ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ c │ c1 │ └─────────────────────┴─────┴─────┘ 2 rows in set. Elapsed: 0.004 sec.
在sql后面加上final，发现只返回了最新的一条，是符合预期的
SELECT * FROM tb_test_replacing FINAL Query id: 614c7e93-48c2-4129-a734-c8a0dd722fcd ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ c │ c2 │ └─────────────────────┴─────┴─────┘ 1 rows in set. Elapsed: 0.007 sec.
ReplacingMergeTree在建表时可以看到ENGINE = ReplacingMergeTree(ts)，其中的ts就是版本信息，clickhouse会每次插入记录版本，就是依据这个字段，在查询时会返回最后最新的版本数据。所以第二个insert的ts和第一个insert的ts字段的值一样，所以会以第二条记录为准，即实现了更新。具体用法见：ReplacingMergeTree
这时物理文件并没有发生实质的合并
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:32 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:32 all_2_2_0 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:32 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:32 format_version.txt
这时如果再执行optimize，就会发生真正的merge，生成了all_1_2_1，后续all_1_1_0、all_2_2_0会被异步删除
drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:32 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:35 all_1_2_1 drwxr-x--- 2 clickhouse clickhouse 152 3月 7 14:32 all_2_2_0 drwxr-x--- 2 clickhouse clickhouse 6 3月 7 14:32 detached -rw-r----- 1 clickhouse clickhouse 1 3月 7 14:32 format_version.txt
再查询该表，不加final
SELECT * FROM tb_test_replacing Query id: 14ae82a1-7ce3-4b92-918b-b9f2496f3034 ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ c │ c2 │ └─────────────────────┴─────┴─────┘ 1 rows in set. Elapsed: 0.003 sec.
可知，ReplacingMergeTree在进行merge时是会进行去重的，最终只会保留最新版本数据。
注意点
这里要注意，ReplacingMergeTree是根据orderby做去重的，而不是根据primarykey。举个例子：
CREATE TABLE tb_test_replacing2 ( `ts` DateTime, `uid` String, `biz` String ) ENGINE = ReplacingMergeTree(ts) PRIMARY KEY uid ORDER BY (uid, ts) SETTINGS index_granularity = 8192; INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:01', 'a', 'a1'); INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:02', 'a', 'b1');
进行final的查询，会发现并没有根据主键（primarykey）去重，而是两条数据都查出来了，因为虽然两条记录的主键一样，但是ts字段不一样，一个是20:01:01，另一个是20:01:02。
SELECT * FROM tb_test_replacing2 FINAL Query id: e70e0d00-0ae0-4e42-bcbd-09b852467ce5 ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:02 │ a │ b1 │ └─────────────────────┴─────┴─────┘ ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ a │ a1 │ └─────────────────────┴─────┴─────┘ 2 rows in set. Elapsed: 0.007 sec.
既然是知道是这个规则，那再插入一条数据，保证uid和ts值都一样，biz换为a2
INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:01', 'a', 'a2'); --再次查询，可以发现只显示最新一条记录了，符合预期
SELECT * FROM tb_test_replacing2 FINAL Query id: da9b8464-e7bc-4a4f-97db-9e4304513f63 ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:02 │ a │ b1 │ └─────────────────────┴─────┴─────┘ ┌──────────────────ts─┬─uid─┬─biz─┐ │ 2019-06-07 20:01:01 │ a │ a2 │ └─────────────────────┴─────┴─────┘ 2 rows in set. Elapsed: 0.007 sec.
在原理上，使用select final是将数据读取后在内存排序才能根据orderby键找到最新的一条记录，虽然物理文件不需要做merge但是在内存中也做了类似merge的方式，会有性能损耗。
总结
综上所述，上述三种方法各有各的特点，使用时可以根据业务特点（实时数据、离线数据、T+1数据等）进行更适合的方式。
