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

為了保證統計數據的準確性，比如訂單金額，一個常用的方法是在查詢時增加final關鍵字。那final關鍵字是如何合併數據的，以及合併的數據範圍是怎樣的，本文就對此做一個簡單的探索。 ...

前言

在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的合併，會對不同分區的數據進行合併，合併是按照排序鍵進行合併的，如果想避免不同分區間的合併可以在排序鍵中增加分區欄位

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

作者：京東零售曹建奇

來源：京東雲開發者社區