Clickhouse如何實現(xiàn)數(shù)據(jù)更新

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

我們假設(shè)一個需要頻繁數(shù)據(jù)更新的場景，如某市用戶用電量的統(tǒng)計，我們知道，用戶的用電量每分每秒都有可能發(fā)生變化，所以會涉及到數(shù)據(jù)頻繁的更新。首先，創(chuàng)建一張表來記錄某市所有用戶的用電量。

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 '用戶所在行政區(qū)編碼', Address String COMMENT '用戶地址', Power UInt64 COMMENT '用電量', Deleted BOOLEAN DEFAULT 0 COMMENT '數(shù)據(jù)是否被刪除' ) 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為對應(yīng)的分布式表。其中PowerConsumption_local使用ReplicatedReplacingMergeTree表引擎，第三個參數(shù)‘Record_Time’表示相同主鍵的多條數(shù)據(jù)，只會保留Record_Time最大的一條，我們正是利用ReplacingMergeTree的這一特性來實現(xiàn)數(shù)據(jù)的更新刪除。因此，在選擇主鍵時，我們需要確保主鍵唯一。這里我們選擇(User_ID, Address)來作為主鍵，因為用戶ID加上用戶的地址可以確定唯一的一個電表，不會出現(xiàn)第二個相同的電表，所以對于某個電表多條數(shù)據(jù)，只會保留電量記錄時間最新的一條。

然后我們向表中插入10條數(shù)據(jù)：

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);

表中數(shù)據(jù)如圖所示：

假如現(xiàn)在我們要行政區(qū)編碼為1的所有用戶數(shù)據(jù)都需要更新，我們插入最新的數(shù)據(jù)：

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);

插入最新數(shù)據(jù)后，表中數(shù)據(jù)如圖所示：

可以看到，此時新插入的數(shù)據(jù)與老數(shù)據(jù)同時存在于表中，因為后臺數(shù)據(jù)文件還沒有進行Merge，“替換”還沒有發(fā)生，這時就需要對查詢語句做一些處理來過濾掉老數(shù)據(jù)，函數(shù)argMax(a, b)可以按照b的最大值取a的值，所以通過如下查詢語句就可以只獲取到最新數(shù)據(jù)：

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;

查詢結(jié)果如下圖：

為了更方便我們查詢，這里可以創(chuàng)建一個視圖：

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;

通過該視圖，可以查詢到最新的數(shù)據(jù)：

假如現(xiàn)在我們又需要刪除用戶ID為0的數(shù)據(jù)，我們需要插入一條User_ID字段為0，Deleted字段為1的數(shù)據(jù)：

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

查詢視圖，發(fā)現(xiàn)User_ID為0的數(shù)據(jù)已經(jīng)查詢不到了：

通過如上方法，我們可以實現(xiàn)Clickhouse數(shù)據(jù)的更新、刪除，就好像在使用OLTP數(shù)據(jù)庫一樣，但我們應(yīng)該清楚，實際上老數(shù)據(jù)真正的刪除是在數(shù)據(jù)文件Merge時發(fā)生的，只有在Merge后，老數(shù)據(jù)才會真正物理意義上的刪除掉。

上云必讀 EI企業(yè)智能 FusionInsight ClickHouse

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。