【clickhouse系列】如何做到数据实时更新

悠悠 2022-10-07 12:52 206阅读 0赞

### 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---  6 clickhouse clickhouse 4096 Jun 17 02:06 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_1_1_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_2_2_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:06 all_3_3_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 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--- 10 clickhouse clickhouse 4096 Jun 17 02:18 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_1_1_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_1_1_0_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:18 all_1_3_1_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_2_2_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_2_2_0_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:06 all_3_3_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_3_3_0_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt

由于clickhouse是异步删除的，所以过程中还会有part（all\_1\_1\_0、all\_2\_2\_0、all\_3\_3\_0）短时间存在，但最终会变为如下文件

drwxr-x---  7 clickhouse clickhouse 4096 Jun 17 02:23 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:18 all_1_3_1_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt

如果在执行了alter和optimize后没有上述变化，可以将optimize语句final

optimize table tb_test final;

由于本次实验没什么压力，所以mutation很快执行完了，但也不影响final的实验效果，执行完上述sql后，clickhouse会强制part更新，并且是同步操作，直至成功或失败。

drwxr-x---  5 clickhouse clickhouse 4096 Jun 17 02:27 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:18 all_1_3_1_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 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---  4 clickhouse clickhouse 4096 Jun 17 02:35 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_4/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 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---  5 clickhouse clickhouse 4096 Jun 17 02:42 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_5/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:42 all_1_3_2_5/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:42 mutation_5.txt

最终会删掉all\_1\_3\_2\_4，变为如下状态

drwxr-x---  4 clickhouse clickhouse 4096 Jun 17 02:50 ./
    drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:42 all_1_3_2_5/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt
    -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:42 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: 7bc9a845-bdec-4a60-bf88-5cafd3944b0e
    
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ c   │ c1  │
    └─────────────────────┴─────┴─────┘
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ c   │ c2  │
    └─────────────────────┴─────┴─────┘

在sql后面加上final，发现只返回了最新的一条，是符合预期的

SELECT *
    FROM tb_test_replacing
    FINAL
    
    Query id: 41817495-ebb3-4110-b226-e70b395b024f
    
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ c   │ c2  │
    └─────────────────────┴─────┴─────┘

ReplacingMergeTree在建表时可以看到ENGINE = ReplacingMergeTree(ts)，其中的ts就是版本信息，clickhouse会每次插入记录版本，就是依据这个字段，在查询时会返回最后最新的版本数据。所以第二个insert的ts和第一个insert的ts字段的值一样，所以会以第二条记录为准，即实现了更新。具体用法见：[ReplacingMergeTree][]

这时物理文件并没有发生实质的合并

drwxr-x---  7 clickhouse clickhouse 4096 Jun 17 03:11 ./
    drwxr-x--- 27 clickhouse clickhouse 4096 Jun 17 03:08 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_1_1_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_2_2_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:08 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 03:08 format_version.txt

这时如果再执行optimize，就会发生真正的merge，生成了all\_1\_2\_1，后续all\_1\_1\_0、all\_2\_2\_0会被异步删除

drwxr-x---  8 clickhouse clickhouse 4096 Jun 17 03:13 ./
    drwxr-x--- 27 clickhouse clickhouse 4096 Jun 17 03:08 ../
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_1_1_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:13 all_1_2_1/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_2_2_0/
    drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:08 detached/
    -rw-r-----  1 clickhouse clickhouse    1 Jun 17 03:08 format_version.txt

再查询该表，不加final

SELECT *
    FROM tb_test_replacing
    
    Query id: ce1d887a-daa4-47b3-8a86-90e795fb83f1
    
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ c   │ c2  │
    └─────────────────────┴─────┴─────┘

可知，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: 3f1479b1-0448-41f3-8a78-1e7880e569cf
    
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ a   │ a1  │
    └─────────────────────┴─────┴─────┘
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:02 │ a   │ b1  │
    └─────────────────────┴─────┴─────┘

既然是知道是这个规则，那再插入一条数据，保证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: eee6b076-bba9-4d78-99d2-1ec1ffdace84
    
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:02 │ a   │ b1  │
    └─────────────────────┴─────┴─────┘
    ┌──────────────────ts─┬─uid─┬─biz─┐
    │ 2019-06-07 20:01:01 │ a   │ a2  │
    └─────────────────────┴─────┴─────┘

所以在使用ReplacingMergeTree时不要被使用mysql等的固有思维影响了，还是多关注一下细节。

在原理上，使用select final是将数据读取后在内存排序才能根据orderby键找到最新的一条记录，虽然物理文件不需要做merge但是在内存中也做了类似merge的方式，会有性能损耗。

### 总结 ###

综上所述，上述三种方法各有各的特点，使用时可以根据业务特点（实时数据、离线数据、T+1数据等）进行更适合的方式。

[ReplacingMergeTree]: https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replacingmergetree/