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数据等)进行更适合的方式。

posted @ 2022-03-07 14:38  渐逝的星光  阅读(9142)  评论(0编辑  收藏  举报