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

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

UPDATE+Optimize方式

用法

建表及插入数据

  1. CREATE TABLE tb_test(
  2.      ts  DateTime,
  3.      uid String,
  4.      biz String
  5. ) ENGINE = MergeTree() ORDER BY (ts) SETTINGS index_granularity = 8192;
  7. INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'a', 'a1');
  8. INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'b', 'b1');
  9. INSERT INTO tb_test VALUES ('2019-06-07 20:01:01', 'c', 'c1');

执行更新

  1. alter table tb_test update biz = 'ccccc' where uid = 'c';
  2. optimize table tb_test;

物理文件的前后变化

刚插入时，因为是执行了三次insert，所以生成了3个part

  1. drwxr-x---  6 clickhouse clickhouse 4096 Jun 17 02:06 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_1_1_0/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_2_2_0/
  5. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:06 all_3_3_0/
  6. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  7. -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。

  1. drwxr-x--- 10 clickhouse clickhouse 4096 Jun 17 02:18 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_1_1_0/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_1_1_0_4/
  5. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:18 all_1_3_1_4/
  6. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:05 all_2_2_0/
  7. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_2_2_0_4/
  8. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:06 all_3_3_0/
  9. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:15 all_3_3_0_4/
  10. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  11. -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
  12. -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）短时间存在，但最终会变为如下文件

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

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

  1. optimize table tb_test final;

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

  1. drwxr-x---  5 clickhouse clickhouse 4096 Jun 17 02:27 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:18 all_1_3_1_4/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_4/
  5. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  6. -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
  7. -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后续也会被删掉，最终形态如下

  1. drwxr-x---  4 clickhouse clickhouse 4096 Jun 17 02:35 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_4/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  5. -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
  6. -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的配置

同样借助上面的表

  1. alter table tb_test update biz = 'ddddd' where uid = 'c' settings mutations_sync = 1

物理文件变化

执行完上述sql就会生成新的part（all_1_3_2_5），并且mutation版本加一

  1. drwxr-x---  5 clickhouse clickhouse 4096 Jun 17 02:42 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:27 all_1_3_2_5/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:42 all_1_3_2_5/
  5. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  6. -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
  7. -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt
  8. -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:42 mutation_5.txt

最终会删掉all_1_3_2_4，变为如下状态

  1. drwxr-x---  4 clickhouse clickhouse 4096 Jun 17 02:50 ./
  2. drwxr-x--- 26 clickhouse clickhouse 4096 Jun 17 02:00 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:42 all_1_3_2_5/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 02:00 detached/
  5. -rw-r-----  1 clickhouse clickhouse    1 Jun 17 02:00 format_version.txt
  6. -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:15 mutation_4.txt
  7. -rw-r-----  1 clickhouse clickhouse  102 Jun 17 02:42 mutation_5.txt

注意点

该方法不需要执行optimize，但原理上还是通过新生成part去代替老part后才能提供新的准确的数据。如果part非常多同样会遇到第一种方式的问题。clickhouse在执行同步操作时可能会因为各种原因失败，但是该方法还是会在后台继续进行更新，具体情况可以根据system.mutations中的记录判断。

INSERT+Final方式

用法

这种方法需要ReplacingMergeTree表引擎配置使用

  1. CREATE TABLE tb_test_replacing(
  2.      ts  DateTime,
  3.      uid String,
  4.      biz String
  5. ) ENGINE = ReplacingMergeTree(ts) ORDER BY (ts) SETTINGS index_granularity = 8192;
  7. INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c1');

这种方式就不用执行alter了，而是以insert的形式来代替alter操作，即每次select时都是取最新的一条数据，sql语句如下

  1. INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c2');

此时查询该表，发现还是两条数据，并没有实现更新

  1. SELECT *
  2. FROM tb_test_replacing
  4. Query id: 7bc9a845-bdec-4a60-bf88-5cafd3944b0e
  6. ┌──────────────────ts─┬─uid─┬─biz─┐
  7. │ 2019-06-07 20:01:01 │ c   │ c1  │
  8. └─────────────────────┴─────┴─────┘
  9. ┌──────────────────ts─┬─uid─┬─biz─┐
  10. │ 2019-06-07 20:01:01 │ c   │ c2  │
  11. └─────────────────────┴─────┴─────┘

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

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

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

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

  1. drwxr-x---  7 clickhouse clickhouse 4096 Jun 17 03:11 ./
  2. drwxr-x--- 27 clickhouse clickhouse 4096 Jun 17 03:08 ../
  3. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_1_1_0/
  4. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:09 all_2_2_0/
  5. drwxr-x---  2 clickhouse clickhouse 4096 Jun 17 03:08 detached/
  6. -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会被异步删除

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

再查询该表，不加final

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

可知，ReplacingMergeTree在进行merge时是会进行去重的，最终只会保留最新版本数据。

注意点

这里要注意，ReplacingMergeTree是根据orderby做去重的，而不是根据primarykey。举个例子：

  1. CREATE TABLE tb_test_replacing2
  2. (
  3.     `ts` DateTime,
  4.     `uid` String,
  5.     `biz` String
  6. )
  7. ENGINE = ReplacingMergeTree(ts)
  8. PRIMARY KEY uid
  9. ORDER BY (uid, ts)
  10. SETTINGS index_granularity = 8192;
  12. INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:01', 'a', 'a1');
  13. INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:02', 'a', 'b1');

进行final的查询，会发现并没有根据主键（primarykey）去重，而是两条数据都查出来了，因为虽然两条记录的主键一样，但是ts字段不一样，一个是20:01:01，另一个是20:01:02。

  1. SELECT *
  2. FROM tb_test_replacing2
  3. FINAL
  5. Query id: 3f1479b1-0448-41f3-8a78-1e7880e569cf
  7. ┌──────────────────ts─┬─uid─┬─biz─┐
  8. │ 2019-06-07 20:01:01 │ a   │ a1  │
  9. └─────────────────────┴─────┴─────┘
  10. ┌──────────────────ts─┬─uid─┬─biz─┐
  11. │ 2019-06-07 20:01:02 │ a   │ b1  │
  12. └─────────────────────┴─────┴─────┘

既然是知道是这个规则，那再插入一条数据，保证uid和ts值都一样，biz换为a2

  1. INSERT INTO tb_test_replacing2 VALUES ('2019-06-07 20:01:01', 'a', 'a2');

再次查询，可以发现只显示最新一条记录了，符合预期

  1. SELECT *
  2. FROM tb_test_replacing2
  3. FINAL
  5. Query id: eee6b076-bba9-4d78-99d2-1ec1ffdace84
  7. ┌──────────────────ts─┬─uid─┬─biz─┐
  8. │ 2019-06-07 20:01:02 │ a   │ b1  │
  9. └─────────────────────┴─────┴─────┘
  10. ┌──────────────────ts─┬─uid─┬─biz─┐
  11. │ 2019-06-07 20:01:01 │ a   │ a2  │
  12. └─────────────────────┴─────┴─────┘

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

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

总结

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

发表评论

表情：
评论列表 （有 0 条评论，289人围观）

还没有评论，来说两句吧...

相关阅读