技術文章 » 【Clickhouse】ReplaceingMergeTree引擎final實現合併去重探索

【Clickhouse】ReplaceingMergeTree引擎final實現合併去重探索

2023-06-08 12:01:22

前言

在OLAP實踐中，在有資料更新的場景中，比如儲存訂單資料，我們經常會用到ReplaceingMergeTree引擎來去重資料，以獲取資料的最新狀態。但是ReplaceingMergeTree引擎實現資料的去重合並的操作是非同步的，這樣在實際查詢的時候，其實是仍然有一部分資料是未進行合併的。為了保證統計資料的準確性，比如訂單金額，一個常用的方法是在查詢時增加final關鍵字。那final關鍵字是如何合併資料的，以及合併的資料範圍是怎樣的，本文就對此做一個簡單的探索。

知識準備

分片：分片就是clickhouse的範例節點，不同的分片就代表不同的節點或機器，分片之間是物理隔離的 分割區：分割區是一個表中通過指定的規則劃分而成的邏輯資料集，比如日期分割區，分割區是一種邏輯上的，不同的分片上會有相同的分割區

探索過程

探索過程比較長，請大家保持耐心，如果不想看過程，可以直接看結論哈，馬上開始～

本文基於的clickhouse版本為version 23.3.1.2823

建立表

建立ReplacingMergeTree引擎的表，分散式表union_order_onl_all_test，本地表union_order_onl_local_test，以日期為分割區，order_id作為排序鍵，mid是訊息ID，用訊息ID作為資料變更的版本號，同時order_id欄位作為分片hash欄位，不同的訂單會被寫入到不同的範例上。

CREATE TABLE gbn_onl_mix.union_order_onl_local_test on cluster lf6ckcnts05
(
    `order_id` UInt64 COMMENT '訂單號',
    `after_prefr_amount_1` Float64 COMMENT '訂單金額',
    `deal_flag` UInt8 COMMENT '成交標識',
    `mid` String COMMENT '訊息ID',
    `update_time` String COMMENT '更新時間',
    `ver` UInt64 DEFAULT toUInt64OrZero(mid) COMMENT '版本號',
    `dt`Date DEFAULT toDate(update_time) COMMENT '分割區'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/lf6ckcnts05/jdob_ha/gbn_onl_mix/lf6ckcnts05/{shard}', '{replica}', ver)
PARTITION BY toYYYYMMDD(dt)
ORDER BY (order_id)
TTL dt + toIntervalDay(7)
SETTINGS storage_policy = 'jdob_ha', index_granularity = 3


CREATE TABLE gbn_onl_mix.union_order_onl_all_test on cluster lf6ckcnts05 as gbn_onl_mix.union_order_onl_local_test
engine=Distributed(lf6ckcnts05, gbn_onl_mix, union_order_onl_local_test, cityHash64(order_id)) ;

資料初始化

初始資料包括2個訂單，111和222，初始版本都是0，初始成交狀態也都是0，日期是2023-05-28

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,0, 0,'2023-05-28'),('222',2,0,0,'2023-05-28');

查詢分割區資訊和資料如下：可以看到資料被寫入到了1個分割區的2個part中，分割區都是20230528，part名都是20230528_0_0_0

知識點詳見 https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/custom-partitioning-key 分割區資訊有重複是因為lf6ckcnts05叢集的設定是有一個副本

驗證同分片同分割區資料合併

final合併

order_id=111有資料更新，mid變成了1，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,0, 1,'2023-05-28');

查詢分割區資訊如下，可見增加了一個part，分割區為20230528，part名為20230528_1_1_0

查詢資料如下，可見order_id=111的訂單，版本0和版本1的資料都是存在的

SELECT * FROM gbn_onl_mix.union_order_onl_all_test WHERE dt = '2023-05-28'

查詢資料使用final結果如下，可見order_id=111的訂單，只查詢出最新版本1的資料

SELECT * FROM gbn_onl_mix.union_order_onl_all_test final WHERE dt = '2023-05-28'

再查詢一下實際的資料如下，結果order_id=111的2個版本的資料還是都被查詢出來了，可見final查詢對實際物理資料的儲存沒有影響

SELECT * FROM gbn_onl_mix.union_order_onl_all_test WHERE dt = '2023-05-28'

小結：final可以合併同分片同分割區的資料，並且final合併資料只是針對當次查詢，不會對資料進行物理合並

引擎合併

order_id=111有資料更新，mid變成了2，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,0, 2,'2023-05-28');

查詢分割區和資料如下，分割區20230528，增加一個part，名為20230528_2_2_0

order_id=111有資料更新，mid變成了3，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,0, 3,'2023-05-28');

分割區20230528，增加名為20230528_2_2_0的part

此時資料還沒有被引擎合併，先去吃個飯吧～

Later For a Moment ～～～

吃飯回來，查詢分割區，發現資料已經被引擎合併了，合併後的分割區為20230528_0_3_1，但是同分割區不同分片的資料沒有被合併

小結：ReplaceingMergeTree引擎合併資料，合併的是同分片同分割區的資料

驗證同分片不同分割區資料合併

final合併

order_id=111資料繼續更新，mid變成了4，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,1, 4,'2023-05-29');

查詢分割區和資料如下，可見增加了一個part，分割區是20230529，part名為20230529_0_0_0，order_id=111訂單資料版本3和版本4同時儲存，資料還未合併

使用final查詢資料，結果如下，我們會發現，order_id=111的訂單在2個分割區2023-05-28和2023-05-29中的資料被合併了

SELECT * FROM gbn_onl_mix.union_order_onl_all_test final

小結：final可以跨分割區進行合併

引擎合併

order_id=111資料繼續更新，mid變成5、6、7，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,1, 5,'2023-05-29','111',1,1, 6,'2023-05-29','111',1,1, 7,'2023-05-29');

查詢分割區和資料如下，可見增加part 20230529_1_1_0，只插入了一條最新訊息為7的資料，即插入資料時，資料就已經合併了

order_id=111資料繼續更新，mid變成8、9，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,1, 8,'2023-05-29');
INSERT into gbn_onl_mix.union_order_onl_all_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('111',1,1, 9,'2023-05-29');

查詢分割區和資料如下，新增part 20230529_2_2_0和20230529_3_3_0

使用final同時查詢2個分割區資料，以及單獨查詢單個分割區的資料，結果如下，可以看到卡不同的分割區，最後合併的結果也不同，（這不是廢話嘛～～）

SELECT * FROM gbn_onl_mix.union_order_onl_all_test final


SELECT * FROM gbn_onl_mix.union_order_onl_all_test final WHERE dt = '2023-05-29'


SELECT * FROM gbn_onl_mix.union_order_onl_all_test final WHERE dt = '2023-05-28'

Later For a Moment ～～～

資料合併完成，結果如下，part 20230529_0_0_0、20230529_1_1_0、20230529_2_2_0、20230529_3_3_0變成active=0，合併後part為20230529_0_3_1，但是分割區20230508的part 20230528_0_3_1並沒有被合併

查詢分割區資料，結果如下

SELECT * FROM gbn_onl_mix.union_order_onl_all_test WHERE dt = '2023-05-28'
SELECT * FROM gbn_onl_mix.union_order_onl_all_test WHERE dt = '2023-05-29'

小結：無論是從分割區資訊還是從資料結果來看，ReplaceingMergeTree引擎是不會合並同分片不同分割區的資料的

驗證不同分片資料合併

final合併

考慮order_id=222的訂單資料，金額修改成22以做區分，在不同的分片上插入變更資料，本次插入改用向本地表中插入資料，可達到跨分片範例的效果，如下

order_id=222的訂單，mid變成1，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_local_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('222',22,0,1,'2023-05-28');

查詢資料，發現居然和版本0插入到同一個分片上了

SELECT * FROM gbn_onl_mix.union_order_onl_local_test WHERE dt = '2023-05-28'

再來一次，order_id=222的訂單，mid變成2，即插入如下資料

INSERT into gbn_onl_mix.union_order_onl_local_test (order_id,after_prefr_amount_1,deal_flag,mid,update_time) values ('222',22,0,2,'2023-05-28');

查詢資料，可見這次資料是插入到了不同的分片範例上

SELECT * FROM gbn_onl_mix.union_order_onl_local_test WHERE dt = '2023-05-28'

檢視目前分割區20230528的資料，如下

SELECT * FROM gbn_onl_mix.union_order_onl_all_test WHERE dt = '2023-05-28'

使用final查詢結果如下，可見final查詢不能合併跨分片的資料，（order_id=222，ver=1和ver=2是儲存在不同分片上的資料）

SELECT * FROM gbn_onl_mix.union_order_onl_all_test final WHERE dt = '2023-05-28'

引擎合併

手動觸發引擎合併，如下

optimize table union_order_onl_local_test on cluster lf6ckcnts05 FINAL;

查詢資料結果，如下，結果同final查詢

小結：無論是final查詢還是引擎合併，不同分片上的資料都不會被合併，即使是同分割區的也不會被合併

結論

囉哩囉嗦這麼多，總結一下吧～～

1.對於不同分片上的資料來說，ReplaceingMergeTree引擎合併和查詢時加final的合併，都不會合並不同分片上的資料

2.對於相同分片上的資料來說，ReplaceingMergeTree引擎合併，只合並同分割區的資料，不同分割區的資料不會合並；查詢時加final的合併，會對不同分割區的資料進行合併，合併是按照排序鍵進行合併的，如果想避免不同分割區間的合併可以在排序鍵中增加分割區欄位

如有問題請指正，歡迎大家溝通交流，感謝～～

作者：京東零售 曹建奇

來源：京東雲開發者社群