簡介：本文翻譯自 Altinity 針對 ClickHouse 的系列技術(shù)文章。面向聯(lián)機(jī)分析處理（OLAP）的開源分析引擎 ClickHouse，因其優(yōu)良的查詢性能，PB級(jí)的數(shù)據(jù)規(guī)模，簡單的架構(gòu)，被國內(nèi)外公司廣泛采用。本系列技術(shù)文章，將詳細(xì)展開介紹 ClickHouse。

前言

本文翻譯自 Altinity 針對 ClickHouse 的系列技術(shù)文章。面向聯(lián)機(jī)分析處理（OLAP）的開源分析引擎 ClickHouse，因其優(yōu)良的查詢性能，PB 級(jí)的數(shù)據(jù)規(guī)模，簡單的架構(gòu)，被國內(nèi)外公司廣泛采用。

阿里云 EMR-OLAP 團(tuán)隊(duì)，基于開源 ClickHouse 進(jìn)行了系列優(yōu)化，提供了開源 OLAP 分析引擎 ClickHouse 的云上托管服務(wù)。EMR ClickHouse 完全兼容開源版本的產(chǎn)品特性，同時(shí)提供集群快速部署、集群管理、擴(kuò)容、縮容和監(jiān)控告警等云上產(chǎn)品功能，并且在開源的基礎(chǔ)上優(yōu)化了 ClickHouse 的讀寫性能，提升了 ClickHouse 與 EMR 其他組件快速集成的能力。訪問 ClickHouse - E-MapReduce - 阿里云 了解詳情。

譯者：何源（荊杭），阿里云計(jì)算平臺(tái)事業(yè)部高級(jí)產(chǎn)品專家

（圖源Altinity，侵刪）

在 ClickHouse 中處理實(shí)時(shí)更新

目錄

• ClickHouse 更新的簡短歷史
• 用例
• 實(shí)現(xiàn)更新
• 結(jié)論
• 后續(xù)

在 OLAP 數(shù)據(jù)庫中，可變數(shù)據(jù)通常不受歡迎。ClickHouse 也不歡迎可變數(shù)據(jù)。像其他一些 OLAP 產(chǎn)品一樣，ClickHouse 最初甚至不支持更新。后來添加了更新功能，但是像其他許多功能一樣，都是以“ClickHouse 方式”添加的。

即使是現(xiàn)在，ClickHouse 更新也是異步的，因此很難在交互式應(yīng)用程序中使用。盡管如此，在許多用例中，用戶需要對現(xiàn)有數(shù)據(jù)進(jìn)行修改，并期望立即看到效果。ClickHouse 能做到嗎？當(dāng)然可以。

ClickHouse 更新的簡短歷史

早在 2016 年，ClickHouse 團(tuán)隊(duì)就發(fā)布了一篇題為“如何在 ClickHouse 中更新數(shù)據(jù)”的文章。當(dāng)時(shí) ClickHouse 并不支持?jǐn)?shù)據(jù)修改，只能使用特殊的插入結(jié)構(gòu)來模擬更新，并且數(shù)據(jù)必須按分區(qū)丟棄。

為滿足 GDPR 的要求，ClickHouse 團(tuán)隊(duì)在 2018 年提供了 UPDATE 和 DELETE。后續(xù)文章ClickHouse 中的更新和刪除目前仍然是 Altinity 博客中閱讀量最多的文章之一。這種異步、非原子性的更新以 ALTER TABLE UPDATE 語句的形式實(shí)現(xiàn)，并且可能會(huì)打亂大量數(shù)據(jù)。這對于批量操作和不頻繁的更新是很有用的，因?yàn)樗鼈儾恍枰磿r(shí)的結(jié)果。盡管“正?！钡?SQL 更新每年都妥妥地出現(xiàn)在路線圖中，但依然沒能在 ClickHouse 中實(shí)現(xiàn)。如果需要實(shí)時(shí)更新行為，我們必須使用其他方法。讓我們考慮一個(gè)實(shí)際的用例，并比較在 ClickHouse 中的不同實(shí)現(xiàn)方法。

用例

考慮一個(gè)生成各種報(bào)警的系統(tǒng)。用戶或機(jī)器學(xué)習(xí)算法會(huì)不時(shí)查詢數(shù)據(jù)庫，以查看新的報(bào)警并進(jìn)行確認(rèn)。確認(rèn)操作需要修改數(shù)據(jù)庫中的報(bào)警記錄。一旦得到確認(rèn)，報(bào)警將從用戶的視圖中消失。這看起來像是一個(gè) OLTP 操作，與 ClickHouse 格格不入。

由于我們無法使用更新，因此只能轉(zhuǎn)而插入修改后的記錄。一旦數(shù)據(jù)庫中有兩條記錄，我們就需要一種有效的方法來獲取最新的記錄。為此，我們將嘗試 3 種不同的方法：

• ReplacingMergeTree
• 聚合函數(shù)
• AggregatingMergeTree

ReplacingMergeTree

我們首先創(chuàng)建一個(gè)用來存儲(chǔ)報(bào)警的表。

CREATE TABLE alerts(tenant_id UInt32,alert_id String,timestamp DateTime Codec(Delta, LZ4),alert_data String,acked UInt8 DEFAULT 0,ack_time DateTime DEFAULT toDateTime(0),ack_user LowCardinality(String) DEFAULT '' ) ENGINE = ReplacingMergeTree(ack_time) PARTITION BY tuple() ORDER BY (tenant_id, timestamp, alert_id);

為簡單起見，將所有報(bào)警特定列都打包到一個(gè)通用的“alert_data”列中。但是可以想象到，報(bào)警可能包含數(shù)十甚至數(shù)百列。此外，在我們的示例中，“alert_id”是一個(gè)隨機(jī)字符串。

請注意 ReplacingMergeTree 引擎。ReplacingMergeTee 是一個(gè)特殊的表引擎，它借助 ORDER BY ?語句按主鍵替換數(shù)據(jù)——具有相同鍵值的新版本行將替換舊版本行。在我們的用例中，“行數(shù)據(jù)的新舊程度”由“ack_time”列確定。替換是在后臺(tái)合并操作中進(jìn)行的，它不會(huì)立即發(fā)生，也不能保證會(huì)發(fā)生，因此查詢結(jié)果的一致性是個(gè)問題。不過，ClickHouse 有一種特殊的語法來處理這樣的表，我們在下面的查詢中就會(huì)用到該語法。

在運(yùn)行查詢之前，我們先用一些數(shù)據(jù)填充這個(gè)表。我們?yōu)?1000 個(gè)租戶生成 1000 萬個(gè)報(bào)警：

INSERT INTO alerts(tenant_id, alert_id, timestamp, alert_data) SELECTtoUInt32(rand(1)%1000+1) AS tenant_id,randomPrintableASCII(64) as alert_id,toDateTime('2020-01-01 00:00:00') + rand(2)%(3600*24*30) as timestamp,randomPrintableASCII(1024) as alert_data FROM numbers(10000000);

接下來，我們確認(rèn) 99% 的報(bào)警，為“acked”、“ack_user”和“ack_time”列提供新值。我們只是插入一個(gè)新行，而不是更新。

INSERT INTO alerts (tenant_id, alert_id, timestamp, alert_data, acked, ack_user, ack_time) SELECT tenant_id, alert_id, timestamp, alert_data, 1 as acked, concat('user', toString(rand()%1000)) as ack_user, now() as ack_time FROM alerts WHERE cityHash64(alert_id) % 99 != 0;

如果我們現(xiàn)在查詢這個(gè)表，會(huì)看到如下結(jié)果：

SELECT count() FROM alerts┌──count()─┐ │ 19898060 │ └──────────┘1 rows in set. Elapsed: 0.008 sec.

表中顯然既有已確認(rèn)的行，也有未確認(rèn)的行。所以替換還沒有發(fā)生。為了查看“真實(shí)”數(shù)據(jù)，我們必須添加 FINAL 關(guān)鍵字。

SELECT count() FROM alerts FINAL┌──count()─┐ │ 10000000 │ └──────────┘1 rows in set. Elapsed: 3.693 sec. Processed 19.90 million rows, 1.71 GB (5.39 million rows/s., 463.39 MB/s.)

現(xiàn)在計(jì)數(shù)是正確了，但是看看查詢時(shí)間增加了多少！使用 FINAL 后，ClickHouse 執(zhí)行查詢時(shí)必須掃描所有的行，并按主鍵合并它們。這樣能得到正確答案，但造成了大量開銷。讓我們看看，只篩選未確認(rèn)的行會(huì)不會(huì)有更好的效果。

SELECT count() FROM alerts FINAL WHERE NOT acked┌─count()─┐ │ 101940 │ └─────────┘1 rows in set. Elapsed: 3.570 sec. Processed 19.07 million rows, 1.64 GB (5.34 million rows/s., 459.38 MB/s.)

盡管計(jì)數(shù)顯著減少，但查詢時(shí)間和處理的數(shù)據(jù)量還是一樣。篩選無助于加快查詢速度。隨著表增大，成本可能會(huì)更加巨大。它不能擴(kuò)展。

注：為了提高可讀性，所有查詢和查詢時(shí)間都像在“clickhouse-client”中運(yùn)行一樣顯示。實(shí)際上，我們嘗試了多次查詢，以確保結(jié)果一致，并使用“clickhouse-benchmark”實(shí)用程序進(jìn)行確認(rèn)。

好吧，查詢整個(gè)表沒什么幫助。我們的用例還能使用 ReplacingMergeTree 嗎？讓我們隨機(jī)選擇一個(gè) tenant_id，然后選擇所有未確認(rèn)的記錄——想象用戶正在查看監(jiān)控視圖。我喜歡 Ray Bradbury，那就選 451 好了。由于“alert_data”的值只是隨機(jī)生成的，因此我們將計(jì)算一個(gè)校驗(yàn)和，用來確認(rèn)多種方法的結(jié)果相同：

SELECT count(), sum(cityHash64(*)) AS data FROM alerts FINAL WHERE (tenant_id = 451) AND (NOT acked)┌─count()─┬─────────────────data─┐ │ 90 │ 18441617166277032220 │ └─────────┴──────────────────────┘1 rows in set. Elapsed: 0.278 sec. Processed 106.50 thousand rows, 119.52 MB (383.45 thousand rows/s., 430.33 MB/s.)

太快了！我們只用了 278 毫秒就查詢了所有未確認(rèn)的數(shù)據(jù)。為什么這次很快？區(qū)別就在于篩選條件。“tenant_id”是某個(gè)主鍵的一部分，所以 ClickHouse 可以在 FINAL 之前篩選數(shù)據(jù)。在這種情況下，ReplacingMergeTree 就變得高效了。

我們也試試用戶篩選器，并查詢由特定用戶確認(rèn)的報(bào)警數(shù)量。列的基數(shù)是相同的——我們有 1000 個(gè)用戶，可以試試 user451。

SELECT count() FROM alerts FINAL WHERE (ack_user = 'user451') AND acked┌─count()─┐ │ 9725 │ └─────────┘1 rows in set. Elapsed: 4.778 sec. Processed 19.04 million rows, 1.69 GB (3.98 million rows/s., 353.21 MB/s.)

這個(gè)速度非常慢，因?yàn)闆]有使用索引。ClickHouse 掃描了全部 1904 萬行。請注意，我們不能將“ack_user”添加到索引，因?yàn)樗鼘⑵茐?ReplacingMergeTree 語義。不過，我們可以用 PREWHERE 進(jìn)行一個(gè)巧妙的處理：

SELECT count() FROM alerts FINAL PREWHERE (ack_user = 'user451') AND acked┌─count()─┐ │ 9725 │ └─────────┘1 rows in set. Elapsed: 0.639 sec. Processed 19.04 million rows, 942.40 MB (29.80 million rows/s., 1.48 GB/s.)

PREWHERE 是一個(gè)特別的妙招，能讓 ClickHouse 以不同方式應(yīng)用篩選器。通常情況下 ClickHouse 是足夠智能的，可以自動(dòng)將條件移動(dòng)到 PREWHERE，因此用戶不必在意。這次沒有發(fā)生，幸好我們檢查過了。

聚合函數(shù)

ClickHouse 因支持各種聚合函數(shù)而聞名，最新版本可支持 100 多種。結(jié)合 9 個(gè)聚合函數(shù)組合子（參見 Combinators | ClickHouse Documentation），這為有經(jīng)驗(yàn)的用戶提供了很高的靈活性。對于此用例，我們不需要任何高級(jí)函數(shù)，僅使用以下 3 個(gè)函數(shù)：“argMax”、“max”和“any”。

可以使用“argMax”聚合函數(shù)執(zhí)行針對第 451 個(gè)租戶的相同查詢，如下所示：

SELECT count(), sum(cityHash64(*)) data FROM (SELECT tenant_id, alert_id, timestamp, argMax(alert_data, ack_time) alert_data, argMax(acked, ack_time) acked,max(ack_time) ack_time_,argMax(ack_user, ack_time) ack_userFROM alerts GROUP BY tenant_id, alert_id, timestamp ) WHERE tenant_id=451 AND NOT acked;┌─count()─┬─────────────────data─┐ │ 90 │ 18441617166277032220 │ └─────────┴──────────────────────┘1 rows in set. Elapsed: 0.059 sec. Processed 73.73 thousand rows, 82.74 MB (1.25 million rows/s., 1.40 GB/s.)

同樣的結(jié)果，同樣的行數(shù)，但性能是之前的 4 倍！這就是 ClickHouse 聚合的效率。缺點(diǎn)在于，查詢變得更加復(fù)雜。但是我們可以讓它變得更簡單。

請注意，當(dāng)確認(rèn)報(bào)警時(shí)，我們只更新以下 3 列：

• acked: 0 => 1
• ack_time: 0 => now()
• ack_user: ‘’ => ‘user1’

在所有 3 種情況下，列值都會(huì)增加！因此，我們可以使用“max”代替略顯臃腫的“argMax”。由于我們不更改“alert_data”，因此不需要對此列進(jìn)行任何實(shí)際聚合。ClickHouse 有一個(gè)很好用的“any”聚合函數(shù)，可以實(shí)現(xiàn)這一點(diǎn)。它可以在沒有額外開銷的情況下選取任何值：

SELECT count(), sum(cityHash64(*)) data FROM (SELECT tenant_id, alert_id, timestamp, any(alert_data) alert_data, max(acked) acked, max(ack_time) ack_time,max(ack_user) ack_userFROM alertsGROUP BY tenant_id, alert_id, timestamp ) WHERE tenant_id=451 AND NOT acked;┌─count()─┬─────────────────data─┐ │ 90 │ 18441617166277032220 │ └─────────┴──────────────────────┘1 rows in set. Elapsed: 0.055 sec. Processed 73.73 thousand rows, 82.74 MB (1.34 million rows/s., 1.50 GB/s.)

查詢變簡單了，而且更快了一點(diǎn)！原因就在于使用“any”函數(shù)后，ClickHouse 不需要對“alert_data”列計(jì)算“max”！

AggregatingMergeTree

AggregatingMergeTree 是 ClickHouse 最強(qiáng)大的功能之一。與物化視圖結(jié)合使用時(shí)，它可以實(shí)現(xiàn)實(shí)時(shí)數(shù)據(jù)聚合。既然我們在之前的方法中使用了聚合函數(shù)，那么能否用 AggregatingMergeTree 使其更加完善呢？實(shí)際上，這并沒有什么改善。

我們一次只更新一行，所以一個(gè)組只有兩行要聚合。對于這種情況，AggregatingMergeTree 不是最好的選擇。不過我們有個(gè)小技巧。我們知道，報(bào)警總是先以非確認(rèn)狀態(tài)插入，然后再變成確認(rèn)狀態(tài)。用戶確認(rèn)報(bào)警后，只有 3 列需要修改。如果我們不重復(fù)其他列的數(shù)據(jù)，可以節(jié)省磁盤空間并提高性能嗎？

讓我們創(chuàng)建一個(gè)使用“max”聚合函數(shù)來實(shí)現(xiàn)聚合的表。我們也可以用“any”代替“max”，但列必須是可以設(shè)置為空的——“any”會(huì)選擇一個(gè)非空值。

DROP TABLE alerts_amt_max;CREATE TABLE alerts_amt_max (tenant_id UInt32,alert_id String,timestamp DateTime Codec(Delta, LZ4),alert_data SimpleAggregateFunction(max, String),acked SimpleAggregateFunction(max, UInt8),ack_time SimpleAggregateFunction(max, DateTime),ack_user SimpleAggregateFunction(max, LowCardinality(String)) ) Engine = AggregatingMergeTree() ORDER BY (tenant_id, timestamp, alert_id);

由于原始數(shù)據(jù)是隨機(jī)的，因此我們將使用“alerts”中的現(xiàn)有數(shù)據(jù)填充新表。我們將像之前一樣分兩次插入，一次是未確認(rèn)的報(bào)警，另一次是已確認(rèn)的報(bào)警：

INSERT INTO alerts_amt_max SELECT * FROM alerts WHERE NOT acked;INSERT INTO alerts_amt_max SELECT tenant_id, alert_id, timestamp,'' as alert_data, acked, ack_time, ack_user FROM alerts WHERE acked;

請注意，對于已確認(rèn)的事件，我們會(huì)插入一個(gè)空字符串，而不是“alert_data”。我們知道數(shù)據(jù)不會(huì)改變，我們只能存儲(chǔ)一次！聚合函數(shù)將填補(bǔ)空白。在實(shí)際應(yīng)用中，我們可以跳過所有不變的列，讓它們獲得默認(rèn)值。

有了數(shù)據(jù)后，我們先檢查數(shù)據(jù)大小：

SELECT table, sum(rows) AS r, sum(data_compressed_bytes) AS c, sum(data_uncompressed_bytes) AS uc, uc / c AS ratio FROM system.parts WHERE active AND (database = 'last_state') GROUP BY table┌─table──────────┬────────r─┬───────────c─┬──────────uc─┬──────────────ratio─┐ │ alerts │ 19039439 │ 20926009562 │ 21049307710 │ 1.0058921003373666 │ │ alerts_amt_max │ 19039439 │ 10723636061 │ 10902048178 │ 1.0166372782501314 │ └────────────────┴──────────┴─────────────┴─────────────┴────────────────────┘

好吧，由于有隨機(jī)字符串，我們幾乎沒有壓縮。但是，由于我們不必存儲(chǔ)“alerts_data”兩次，所以相較于不聚合，聚合后數(shù)據(jù)規(guī)模可以縮小一半。

現(xiàn)在我們試試對聚合表進(jìn)行查詢：

SELECT count(), sum(cityHash64(*)) data FROM (SELECT tenant_id, alert_id, timestamp, max(alert_data) alert_data, max(acked) acked, max(ack_time) ack_time,max(ack_user) ack_userFROM alerts_amt_maxGROUP BY tenant_id, alert_id, timestamp ) WHERE tenant_id=451 AND NOT acked;┌─count()─┬─────────────────data─┐ │ 90 │ 18441617166277032220 │ └─────────┴──────────────────────┘1 rows in set. Elapsed: 0.036 sec. Processed 73.73 thousand rows, 40.75 MB (2.04 million rows/s., 1.13 GB/s.)

多虧了 AggregatingMergeTree，我們處理的數(shù)據(jù)更少（之前是 82MB，現(xiàn)在是 40MB），效率更高。

實(shí)現(xiàn)更新

ClickHouse 會(huì)盡最大努力在后臺(tái)合并數(shù)據(jù)，從而刪除重復(fù)的行并執(zhí)行聚合。然而，有時(shí)強(qiáng)制合并是有意義的，例如為了釋放磁盤空間。這可以通過 OPTIMIZE FINAL 語句來實(shí)現(xiàn)。OPTIMIZE 操作速度慢、代價(jià)高，因此不能頻繁執(zhí)行。讓我們看看它對查詢性能有什么影響。

OPTIMIZE TABLE alerts FINAL Ok. 0 rows in set. Elapsed: 105.675 sec.OPTIMIZE TABLE alerts_amt_max FINAL Ok. 0 rows in set. Elapsed: 70.121 sec.

執(zhí)行 OPTIMIZE FINAL 后，兩個(gè)表的行數(shù)相同，數(shù)據(jù)也相同。

┌─table──────────┬────────r─┬───────────c─┬──────────uc─┬────────────ratio─┐ │ alerts │ 10000000 │ 10616223201 │ 10859490300 │ 1.02291465565429 │ │ alerts_amt_max │ 10000000 │ 10616223201 │ 10859490300 │ 1.02291465565429 │ └────────────────┴──────────┴─────────────┴─────────────┴──────────────────┘

不同方法之間的性能差異變得不那么明顯了。匯總表如下：

結(jié)論

ClickHouse 提供了豐富的工具集來處理實(shí)時(shí)更新，如 ReplacingMergeTree、CollapsingMergeTree（本文未提及）、AggregatingMergeTree 和聚合函數(shù)。所有這些方法都具有以下三個(gè)共性：

• 通過插入新版本來“修改”數(shù)據(jù)。ClickHouse 中的插入速度非常快。
• 有一些有效的方法來模擬類似于 OLTP 數(shù)據(jù)庫的更新語義。
• 然而，實(shí)際的修改并不會(huì)立即發(fā)生。

具體方法的選擇取決于應(yīng)用程序的用例。對用戶來說，ReplacingMergeTree 是直截了當(dāng)?shù)?#xff0c;也是最方便的方法，但只適用于中小型的表，或者數(shù)據(jù)總是按主鍵查詢的情況。使用聚合函數(shù)可以提供更高的靈活性和性能，但需要大量的查詢重寫。最后，AggregatingMergeTree 可以節(jié)約存儲(chǔ)空間，只保留修改過的列。這些都是 ClickHouse DB 設(shè)計(jì)人員的好工具，可根據(jù)具體需要來應(yīng)用。

后續(xù)

您已經(jīng)了解了在 ClickHouse 中處理實(shí)時(shí)更新相關(guān)內(nèi)容，本系列還包括其他內(nèi)容：

• 使用新的 TTL move，將數(shù)據(jù)存儲(chǔ)在合適的地方
• 在 ClickHouse 物化視圖中使用 Join
• ClickHouse 聚合函數(shù)和聚合狀態(tài)
• ClickHouse 中的嵌套數(shù)據(jù)結(jié)構(gòu)

原文鏈接
本文為阿里云原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的【ClickHouse 技术系列】- 在 ClickHouse 中处理实时更新的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。