ReplacingMergeTree：實現Clickhouse資料更新

華為雲開發者社群發表於2021-11-04
摘要：Clickhouse作為一個OLAP資料庫，它對事務的支援非常有限。本文主要介紹通過ReplacingMergeTree來實現Clickhouse資料的更新、刪除。

本文分享自華為雲社群《Clickhouse如何實現資料更新》，作者： 小霸王。

Clickhouse作為一個OLAP資料庫，它對事務的支援非常有限。Clickhouse提供了MUTATION操作（通過ALTER TABLE語句）來實現資料的更新、刪除，但這是一種“較重”的操作，它與標準SQL語法中的UPDATE、DELETE不同，是非同步執行的，對於批量資料不頻繁的更新或刪除比較有用，可參考https://altinity.com/blog/2018/10/16/updates-in-clickhouse。除了MUTATION操作，Clickhouse還可以通過CollapsingMergeTree、VersionedCollapsingMergeTree、ReplacingMergeTree結合具體業務資料結構來實現資料的更新、刪除，這三種方式都通過INSERT語句插入最新的資料，新資料會“抵消”或“替換”掉老資料，但是“抵消”或“替換”都是發生在資料檔案後臺Merge時，也就是說，在Merge之前，新資料和老資料會同時存在。因此，我們需要在查詢時做一些處理，避免查詢到老資料。Clickhouse官方文件提供了使用CollapsingMergeTree、VersionedCollapsingMergeTree的指導，https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/。相比於CollapsingMergeTree、VersionedCollapsingMergeTree需要標記位欄位、版本欄位，用ReplacingMergeTree來實現資料的更新刪除會更加方便，這裡著重介紹一下如何用ReplacingMergeTree來實現資料的更新刪除。

我們假設一個需要頻繁資料更新的場景，如某市使用者用電量的統計，我們知道，使用者的用電量每分每秒都有可能發生變化，所以會涉及到資料頻繁的更新。首先，建立一張表來記錄某市所有使用者的用電量。

CREATE TABLE IF NOT EXISTS default.PowerConsumption_local ON CLUSTER default_cluster
(
    User_ID             UInt64                              COMMENT '使用者ID',
    Record_Time         DateTime    DEFAULT toDateTime(0)   COMMENT '電量記錄時間',
    District_Code       UInt8                               COMMENT '使用者所在行政區編碼',
    Address             String                              COMMENT '使用者地址',
    Power               UInt64                              COMMENT '用電量',
    Deleted             BOOLEAN     DEFAULT 0               COMMENT '資料是否被刪除'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/default.PowerConsumption_local/{shard}', '{replica}', Record_Time)
ORDER BY (User_ID, Address)
PARTITION BY District_Code;
CREATE TABLE default.PowerConsumption ON CLUSTER default_cluster AS default.PowerConsumption_local
ENGINE = Distributed(default_cluster, default, PowerConsumption_local, rand());

PowerConsumption_local為本地表，PowerConsumption為對應的分散式表。其中PowerConsumption_local使用ReplicatedReplacingMergeTree表引擎，第三個引數‘Record_Time’表示相同主鍵的多條資料，只會保留Record_Time最大的一條，我們正是利用ReplacingMergeTree的這一特性來實現資料的更新刪除。因此，在選擇主鍵時，我們需要確保主鍵唯一。這裡我們選擇(User_ID, Address)來作為主鍵，因為使用者ID加上使用者的地址可以確定唯一的一個電錶，不會出現第二個相同的電錶，所以對於某個電錶多條資料，只會保留電量記錄時間最新的一條。

然後我們向表中插入10條資料：

INSERT INTO default.PowerConsumption VALUES (0, '2021-10-30 12:00:00', 3, 'Yanta', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (1, '2021-10-30 12:10:00', 2, 'Beilin', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (2, '2021-10-30 12:15:00', 1, 'Weiyang', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (3, '2021-10-30 12:18:00', 1, 'Gaoxin', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (4, '2021-10-30 12:23:00', 2, 'Qujiang', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (5, '2021-10-30 12:43:00', 3, 'Baqiao', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (6, '2021-10-30 12:45:00', 1, 'Lianhu', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (7, '2021-10-30 12:46:00', 3, 'Changan', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (8, '2021-10-30 12:55:00', 1, 'Qianhan', rand64() % 1000 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (9, '2021-10-30 12:57:00', 4, 'Fengdong', rand64() % 1000 + 1, 0);

表中資料如圖所示：

ReplacingMergeTree：實現Clickhouse資料更新

假如現在我們要行政區編碼為1的所有使用者資料都需要更新，我們插入最新的資料：

INSERT INTO default.PowerConsumption VALUES (2, now(), 1, 'Weiyang', rand64() % 100 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (3, now(), 1, 'Gaoxin', rand64() % 100 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (6, now(), 1, 'Lianhu', rand64() % 100 + 1, 0);
INSERT INTO default.PowerConsumption VALUES (8, now(), 1, 'Qianhan', rand64() % 100 + 1, 0);

插入最新資料後，表中資料如圖所示：

ReplacingMergeTree：實現Clickhouse資料更新

可以看到，此時新插入的資料與老資料同時存在於表中，因為後臺資料檔案還沒有進行Merge，“替換”還沒有發生，這時就需要對查詢語句做一些處理來過濾掉老資料，函式argMax(a, b)可以按照b的最大值取a的值，所以通過如下查詢語句就可以只獲取到最新資料：

SELECT
    User_ID,
    max(Record_Time) AS R_Time,
    District_Code,
    Address,
    argMax(Power, Record_Time) AS Power,
    argMax(Deleted, Record_Time) AS Deleted
FROM default.PowerConsumption
GROUP BY
    User_ID,
    Address,
    District_Code
HAVING Deleted = 0;

查詢結果如下圖：

ReplacingMergeTree：實現Clickhouse資料更新

為了更方便我們查詢，這裡可以建立一個檢視：

CREATE VIEW PowerConsumption_view ON CLUSTER default_cluster AS
SELECT
    User_ID,
    max(Record_Time) AS R_Time,
    District_Code,
    Address,
    argMax(Power, Record_Time) AS Power,
    argMax(Deleted, Record_Time) AS Deleted
FROM default.PowerConsumption
GROUP BY
    User_ID,
    Address,
    District_Code
HAVING Deleted = 0;

通過該檢視，可以查詢到最新的資料：

ReplacingMergeTree：實現Clickhouse資料更新

假如現在我們又需要刪除使用者ID為0的資料，我們需要插入一條User_ID欄位為0，Deleted欄位為1的資料：

INSERT INTO default.PowerConsumption VALUES (0, now(), 3, 'Yanta', null, 1);

查詢檢視，發現User_ID為0的資料已經查詢不到了：

ReplacingMergeTree：實現Clickhouse資料更新

通過如上方法，我們可以實現Clickhouse資料的更新、刪除，就好像在使用OLTP資料庫一樣，但我們應該清楚，實際上老資料真正的刪除是在資料檔案Merge時發生的，只有在Merge後，老資料才會真正物理意義上的刪除掉。

 

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章