­

ReplacingMergeTree：實現Clickhouse數據更新

摘要：Clickhouse作為一個OLAP資料庫，它對事務的支援非常有限。本文主要介紹通過ReplacingMergeTree來實現Clickhouse數據的更新、刪除。

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

Clickhouse作為一個OLAP資料庫，它對事務的支援非常有限。Clickhouse提供了MUTATION操作（通過ALTER TABLE語句）來實現數據的更新、刪除，但這是一種「較重」的操作，它與標準SQL語法中的UPDATE、DELETE不同，是非同步執行的，對於批量數據不頻繁的更新或刪除比較有用，可參考//altinity.com/blog/2018/10/16/updates-in-clickhouse。除了MUTATION操作，Clickhouse還可以通過CollapsingMergeTree、VersionedCollapsingMergeTree、ReplacingMergeTree結合具體業務數據結構來實現數據的更新、刪除，這三種方式都通過INSERT語句插入最新的數據，新數據會「抵消」或「替換」掉老數據，但是「抵消」或「替換」都是發生在數據文件後台Merge時，也就是說，在Merge之前，新數據和老數據會同時存在。因此，我們需要在查詢時做一些處理，避免查詢到老數據。Clickhouse官方文檔提供了使用CollapsingMergeTree、VersionedCollapsingMergeTree的指導，//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);

表中數據如圖所示：

假如現在我們要行政區編碼為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);

插入最新數據後，表中數據如圖所示：

可以看到，此時新插入的數據與老數據同時存在於表中，因為後台數據文件還沒有進行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;

查詢結果如下圖：

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

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;

通過該視圖，可以查詢到最新的數據：

假如現在我們又需要刪除用戶ID為0的數據，我們需要插入一條User_ID欄位為0，Deleted欄位為1的數據：

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

查詢視圖，發現User_ID為0的數據已經查詢不到了：

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

 

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

Tags:

VirMach 便宜 VPS

QNews

QNews