作為世界上最快的實(shí)時(shí)分析數(shù)據(jù)庫，ClickHouse 的許多工作負(fù)載都涉及大量的數(shù)據(jù)，這些數(shù)據(jù)只寫一次，不經(jīng)常修改(例如，物聯(lián)網(wǎng)設(shè)備產(chǎn)生的遙測事件或電子商務(wù)網(wǎng)站產(chǎn)生的客戶點(diǎn)擊)。雖然這些通常是不可變的，但在分析期間提供上下文的其他關(guān)鍵數(shù)據(jù)集(例如，基于設(shè)備或客戶 ID 的信息查找表)可能需要修改。

根據(jù)你的目標(biāo)和性能要求，ClickHouse 有多種更新和刪除數(shù)據(jù)的方法。本文的其余部分將描述每種方法及其優(yōu)缺點(diǎn)，以及解決一些常見挑戰(zhàn)的輕量級刪除的一些最新進(jìn)展。我們推薦最佳實(shí)踐，并強(qiáng)調(diào)在考慮一種方法時(shí)需要考慮的一些重要事項(xiàng)。

在繼續(xù)之前，確定更新是否是解決問題的最佳方法。例如，對于不經(jīng)常更改的數(shù)據(jù)，對數(shù)據(jù)進(jìn)行版本控制可能是更好的選擇。在存儲效率和查詢性能方面，ClickHouse 是排名第一的分析數(shù)據(jù)庫，所以在許多情況下，僅僅保存多個(gè)版本的數(shù)據(jù)而不是更新可能會(huì)更好。

輕量級刪除

輕量級刪除代表首選和最有效的方式從 ClickHouse 刪除數(shù)據(jù)。通過 DELETE FROM 表語法，用戶可以指定一個(gè)條件來刪除特定的行，如下所示：

• https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals

DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2

默認(rèn)情況下，該操作是異步的，除非 mutations_sync 設(shè)置為 1(見下文)。執(zhí)行刪除時(shí)，ClickHouse 會(huì)為每一行保存一個(gè)掩碼，在 _row_exists 列中表示是否被刪除。隨后的查詢依次排除這些已刪除的行，如下所示。

圖片

在內(nèi)部，ClickHouse 將數(shù)據(jù)分成幾個(gè)部分，每個(gè)部分包含列數(shù)據(jù)文件和索引。常規(guī)的合并周期負(fù)責(zé)組合(合并)和重寫這些部分。這確保了文件的數(shù)量不會(huì)隨著插入更多數(shù)據(jù)而繼續(xù)增長，從而保持查詢的速度。這些合并考慮輕量級刪除，不包括那些在新形成的部分中標(biāo)記為要?jiǎng)h除的行。

圖片

它于 22.8 發(fā)布，在撰寫本文時(shí)仍處于試驗(yàn)階段，輕量級刪除將在下一個(gè)版本中成為生產(chǎn)就緒。在此之前，使用輕量級刪除需要設(shè)置allow_experimental_lightweight_delete=true。

用戶應(yīng)該意識到，依靠正常的后臺合并周期，行最終只會(huì)從磁盤中刪除。雖然從搜索結(jié)果中排除，但這些行將駐留在磁盤上，直到它們的部分被合并。這種情況發(fā)生所需的時(shí)間是不確定的。這有幾個(gè)含義:

• 節(jié)省的空間不會(huì)像通過突變發(fā)出刪除那樣直接——見下文。如果節(jié)省空間非常重要，例如磁盤空間不足，請考慮使用突變。
• 由于不能保證刪除，有遵從性需求的用戶可能希望使用突變來確保刪除數(shù)據(jù)。

輕量級刪除操作的成本取決于 WHERE 子句中匹配行的數(shù)量和當(dāng)前數(shù)據(jù)部分的數(shù)量。當(dāng)匹配少量行時(shí)，此操作將是最有效的。用戶還應(yīng)該意識到，輕量級刪除在寬部分(列數(shù)據(jù)文件單獨(dú)存儲)和緊湊部分(所有列數(shù)據(jù)使用單個(gè)文件)上表現(xiàn)最好。前者允許將掩碼 _row_exists 存儲為一個(gè)單獨(dú)的文件，從而允許它獨(dú)立于其他列進(jìn)行寫入。通常，緊湊的零件將在插入后成形。一旦部分超過一定的大小(例如，由于合并)，就使用寬格式。對于大多數(shù)工作負(fù)載，這不應(yīng)該是一個(gè)問題。

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-data-storage

最后，請注意，輕量級刪除使用與我們下面描述的相同的突變隊(duì)列和后臺線程。關(guān)于內(nèi)部實(shí)現(xiàn)的更多細(xì)節(jié)，我們推薦使用這里的文檔。

• https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals

突變

使用突變更新數(shù)據(jù)

更新 ClickHouse 表中的數(shù)據(jù)最簡單的方法是使用 ALTER…UPDATE 語句。

• https://clickhouse.com/docs/en/sql-reference/statements/alter/update

ALTER TABLE table
    UPDATE col1 = 'Hi' WHERE col2 = 2

該查詢將使用給定的過濾器更新表 table 上的 col1。

與一些數(shù)據(jù)庫不同，ClickHouse 的 ALTER UPDATE 語句默認(rèn)情況下是異步的。這意味著更新發(fā)生在后臺，您不會(huì)立即對表產(chǎn)生影響。這個(gè)更新表的過程稱為突變。

• https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations

圖片

這里需要注意的是，更新數(shù)據(jù)是一個(gè)繁重的查詢，因?yàn)?ClickHouse 必須做大量的工作來優(yōu)化存儲和處理。突變操作強(qiáng)制重寫包含要?jiǎng)h除的行的所有數(shù)據(jù)部分，在形成新部分時(shí)排除目標(biāo)行。這可能會(huì)導(dǎo)致相當(dāng)大的 I/O 和集群開銷，因此要謹(jǐn)慎使用它，或者考慮下面討論的替代方案。

使用突變刪除數(shù)據(jù)

與更新一樣，刪除也可以通過突變進(jìn)行，并提供輕量級刪除的另一種選擇。在大多數(shù)情況下，由于重寫所有列的突變成本，輕量級刪除更適合于數(shù)據(jù)刪除。更具體地說，與輕量級刪除不同，所有列都被重寫，而不僅僅是一個(gè) _row_exists 掩碼列。

然而，考慮到輕量級刪除的 “最終從磁盤刪除數(shù)據(jù)” 屬性，用戶可能更喜歡這種基于突變的方法來實(shí)現(xiàn)有保證的磁盤空間節(jié)省。此外，當(dāng)用戶需要保證從磁盤中刪除數(shù)據(jù)時(shí)，例如由于遵從性原因，這種方法是合適的。

ALTER TABLE table
    DELETE WHERE col2 = 3

在此查詢中，刪除 col2 值為 3 的所有行。與其他修改類似，默認(rèn)情況下，刪除也是異步的。可以使用上述相同的 mutations_sync 設(shè)置使其同步。

檢查突變進(jìn)展

由于突變是異步運(yùn)行的，因此可以通過 system.mutations 表進(jìn)行監(jiān)測。這允許用戶需要檢查他們在表上的特定突變的進(jìn)度。

SELECT
    command,
    is_done
FROM system.mutations
WHERE table = 'tablename'

┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 2     │       1 │
│ UPDATE col1 = 'All hi' WHERE col2 > 0 │       0 │
└───────────────────────────────────────┴─────────┘

如果 is_done 的值對于特定的突變是 0，那么它仍然在執(zhí)行。對每個(gè)表部分執(zhí)行突變，突變后的部分立即可用：

圖片

同步更新

對于需要同步更新的用戶，mutations_sync 參數(shù)可以設(shè)置為 1(或者 2，如果我們也想等到所有副本也被更新):

• https://clickhouse.com/docs/en/operations/settings/settings/#mutations_sync

SET mutations_sync = 1

現(xiàn)在我們的更新查詢將等待突變完成:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec.

注意，當(dāng) ClickHouse 等待后臺突變完成時(shí)，這個(gè)查詢花了 1 秒鐘的時(shí)間。注意，此參數(shù)也適用于輕量級刪除。

更新整個(gè)表

在某些情況下，用戶需要更新整個(gè)列的值。最初，用戶可能會(huì)嘗試使用不帶 WHERE 子句的 ALTER TABLE 查詢來實(shí)現(xiàn)這一點(diǎn)。然而，這是失敗的，如下所示:

ALTER TABLE table UPDATE col1 = 'bye';

Syntax error: failed at position 38 (end of query):
ALTER TABLE table UPDATE col1 = 'bye';

ClickHouse 不會(huì)讓你更新整個(gè)表，因?yàn)楦率欠敝氐摹?qiáng)迫 ClickHouse 接受此操作的一種方法是使用始終為真過濾器:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true

然而，更優(yōu)的方法是創(chuàng)建一個(gè)新列，將新值作為默認(rèn)值，然后在新舊列之間切換。例如：

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';

ALTER TABLE table
    RENAME COLUMN col1 TO col1_old,
    RENAME COLUMN col1_new TO col1,
    DROP COLUMN col1_old;

我們使用 col1_new 列的默認(rèn)值來指定要使用的更新值。這是安全且高效得多的，因?yàn)槲覀兲^了這里的重突變操作。

使用 JOIN 進(jìn)行更新和刪除

有時(shí)，我們需要根據(jù)關(guān)系刪除或更新行; 因此，我們必須 join 表。在 ClickHouse 中，使用 Join 表引擎和 joinGet 函數(shù)可以最好地實(shí)現(xiàn)這一點(diǎn)。假設(shè)我們有兩個(gè)表-一個(gè)與所有的頁面瀏覽量和另一個(gè)其他所有的登錄跟蹤：

CREATE TABLE pageviews
(
    `user_id` UInt64,
    `time` DateTime,
    `session_id` UInt64
)
ENGINE = MergeTree
ORDER BY time;

CREATE TABLE logins
(
    `user_id` UInt64,
    `time` DateTime
)
ENGINE = MergeTree
ORDER BY time;

這兩個(gè)表之間的區(qū)別在于，logins 表每個(gè)會(huì)話只存儲一個(gè)事件。假設(shè)在某個(gè)時(shí)間點(diǎn)，我們決定將 session_id 列添加到 logins 表中:

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

我們現(xiàn)在需要使用 user_id 和 time 上的 JOIN，用 pageviews 表中的相應(yīng)值更新 logins.session_id 列：

SELECT *
FROM logins AS l
JOIN pageviews AS p ON (p.user_id = l.user_id) AND (p.time = l.time)

┌─user_id─┬────────────────time─┬─p.user_id─┬──────────────p.time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │         2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │         1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘

首先，我們需要?jiǎng)?chuàng)建并填充一個(gè)特殊的 Join 表:

CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews

該表將允許我們在執(zhí)行更新查詢時(shí)使用 joinGet 函數(shù)來基于 JOIN 獲取值:

ALTER TABLE logins
    UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 0

我們可以看到，logins 表被相應(yīng)的更新了 JOIN:

SELECT * FROM logins

┌─user_id─┬────────────────time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴────────────┘

因?yàn)槲覀円呀?jīng)通過添加 session_id 列更改了 logins 表，所以我們可以在更改完成后 DROP pageviews_join 表(刪除之前請檢查 system.mutations 表以確定):

DROP TABLE pageviews_join

同樣的方法也可以用于通過輕量級或基于突變的刪除來刪除數(shù)據(jù)。

高效刪除大塊數(shù)據(jù)塊

如果我們必須刪除大的數(shù)據(jù)塊，用戶可以對表進(jìn)行分區(qū)，以便根據(jù)需要?jiǎng)h除分區(qū)。這是一個(gè)輕量級操作。假設(shè)我們有以下表:

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)

通過 project 列對該表進(jìn)行分區(qū)，我們可以通過刪除整個(gè)分區(qū)來刪除具有特定 project 值的行。讓我們刪除所有帶有 project = c 的內(nèi)容:

ALTER TABLE hits
    DROP PARTITION 'c'

這里，c 是我們想要?jiǎng)h除的 project 列值:

圖片

可用分區(qū)的列表可以在 system.parts 表中找到：

SELECT partition
FROM system.parts
WHERE table = 'hits'

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘

我們還可以使用 DETACH 和 ATTACH 語句在表之間移動(dòng)分區(qū)(例如，如果我們想將數(shù)據(jù)移動(dòng)到 trash 表而不是刪除它)。

在 DDL 中設(shè)置分區(qū)時(shí)，要注意按具有高基數(shù)的列或表達(dá)式進(jìn)行分區(qū)的常見缺陷。這可能導(dǎo)致創(chuàng)建許多部件，從而導(dǎo)致性能問題。

定期刪除舊數(shù)據(jù)

對于時(shí)間序列數(shù)據(jù)，我們可能希望定期刪除過時(shí)的數(shù)據(jù)。ClickHouse 對于這個(gè)確切的用例具有 TTL 特性。這需要配置一個(gè)表，并指定我們希望刪除哪些數(shù)據(jù)以及何時(shí)刪除。假設(shè)我們想從 hits 表中刪除超過一個(gè)月的數(shù)據(jù):

• https://docs.google.com/document/d/1CAmuBror9fGLZwFCiBUH5rt89LKq-ylA1Lpa6-FrJns/edit
• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH

在這里，我們要求 ClickHouse 刪除當(dāng)前時(shí)間的時(shí)間列值超過一個(gè)月的所有行。還可以在列上設(shè)置 TTL，以便在一段時(shí)間后將其值重置為默認(rèn)值。通過按日期進(jìn)行分區(qū)，四舍五入到適當(dāng)?shù)臅r(shí)間單位，例如，days，可以使該操作更有效。在執(zhí)行 TTL 規(guī)則時(shí)，ClickHouse 將以最有效的方式自動(dòng)刪除數(shù)據(jù)。同樣，表不應(yīng)該按照高基數(shù)(例如毫秒粒度)的時(shí)間列進(jìn)行分區(qū)，以避免高部分計(jì)數(shù)。通常按天或月劃分對于大多數(shù) TTL 操作來說就足夠了。

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-column-ttl

使用 CollapsingMergeTree 刪除和更新

如果我們必須頻繁地更新單個(gè)行，我們可以使用 CollapsingMergeTree 引擎來有效地管理數(shù)據(jù)更新。

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/

假設(shè)我們有一個(gè)包含文章統(tǒng)計(jì)信息的表，用于跟蹤每篇文章的閱讀深度。我們希望用一行顯示每個(gè)用戶閱讀每篇文章的深度。這里的挑戰(zhàn)在于，我們必須在用戶閱讀文章時(shí)更新實(shí)際的閱讀進(jìn)度。讓我們?yōu)閿?shù)據(jù)創(chuàng)建一個(gè)表:

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_start` DateTime,
    `read_end` DateTime,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (read_start, article_id, user_id)

特殊的 sign 列用于 CollapsingMergeTree，作為告訴 ClickHouse 我們想要更新特定行的一種方式。如果我們在 sign 列中插入 -1，整行就會(huì)被刪除。如果我們插入一行 sign = 1，ClickHouse 將保留這一行。要更新的行是根據(jù)創(chuàng)建表時(shí)在 ORDER BY () DDL 語句中使用的排序鍵來標(biāo)識的：

圖片

為了滿足排序鍵上的重復(fù)數(shù)據(jù)刪除條件，我們必須為 read_start, article_id, user_id 列插入相同的值來更新一行。例如，當(dāng)用戶開始閱讀一篇文章時(shí)，我們插入以下行:

INSERT INTO article_reads
            VALUES(1, 12, 0, now(), now(), 1);

現(xiàn)在我們在表中有了一行:

SELECT *
FROM article_reads

┌─user_id─┬─article_id─┬─read_to─┬──────────read_start─┬────────────read_end─┬─sign─┐
│       1 │         12 │       0 │ 2023-01-06 15:20:32 │ 2023-01-06 15:20:32 │    1 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘

一分鐘后，當(dāng)用戶達(dá)到文章的 70% 時(shí)，我們插入以下 2 行:

INSERT INTO article_reads
            VALUES(1, 12, 0, '2023-01-06 15:20:32', now(), -1),
                  (1, 12, 70, '2023-01-06 15:20:32', now(), 1);

第一行的 sign=-1 是用來告訴 ClickHouse，它應(yīng)該刪除現(xiàn)有的行(基于值在 ORDER BY 元組- read_start, article_id 和 user_id 列)。第二個(gè)插入的行( sign=1 )是 read_to 列設(shè)置為新值 70 的新行。

由于數(shù)據(jù)更新是在后臺進(jìn)行的，因此最終的結(jié)果是一致的，我們應(yīng)該對 sign 列進(jìn)行過濾以獲得正確的結(jié)果:

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/#table_engine-collapsingmergetree-collapsing-algorithm

SELECT
    article_id,
    user_id,
    max(read_end),
    max(read_to)
FROM article_reads
WHERE sign = 1
GROUP BY
    user_id,
    article_id

┌─article_id─┬─user_id─┬───────max(read_end)─┬─max(read_to)─┐
│         12 │       1 │ 2023-01-06 15:21:59 │           70 │
└────────────┴─────────┴─────────────────────┴──────────────┘

1 row in set. Elapsed: 0.004 sec.

現(xiàn)在，CollapsingMergreTree 引擎會(huì)在后臺有效地從存儲中刪除被取消的行，所以我們不必手動(dòng)刪除它們。你可以在這里找到更多使用 CollapsingMergeTree 引擎的例子。

• https://www.google.com/url?q=https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/%23example-of-use&sa=D&source=docs&ust=1678440218544759&usg=AOvVaw3vOSZ1Rbl24kvzHT48kuyp

使用版本控制和 ReplacingMergeTree 的 Upserts

對于更復(fù)雜的情況，我們可能希望使用基于 ReplacingMergeTree 引擎的版本控制。這個(gè)引擎實(shí)現(xiàn)了一種在其他dbms中執(zhí)行UPSERT的有效方法，即使用一個(gè)特殊的版本列來跟蹤應(yīng)該刪除哪些行。如果存在多個(gè)具有相同排序鍵的行，則只有具有最大版本的行保留在存儲中，而其他行則被刪除:

圖片

對于前面的閱讀文章的例子，我們可以使用下面的結(jié)構(gòu):

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_time` DateTime,
    `version` Int32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (article_id, user_id)

請注意特殊的 version 數(shù)字列，ReplacingMergeTree 引擎將使用它來標(biāo)記要?jiǎng)h除的行。讓我們模擬一個(gè)用戶在 0 到 80% 的時(shí)間內(nèi)閱讀一篇文章:

INSERT INTO article_reads
           VALUES(1, 12, 0, '2023-01-06 15:20:32', 1),
                 (1, 12, 30, '2023-01-06 15:21:42', 2),
                 (1, 12, 45, '2023-01-06 15:22:13', 3),
                 (1, 12, 80, '2023-01-06 15:23:10', 4);

這里，我們在跟蹤讀取進(jìn)度時(shí)增加 version 列的值。刪除行的過程也是通過正常的合并周期在后臺執(zhí)行的，所以我們需要根據(jù)查詢時(shí)的最新版本進(jìn)行過濾:

SELECT *
FROM article_reads
WHERE (user_id = 1) AND (article_id = 12)
ORDER BY version DESC
LIMIT 1

┌─user_id─┬─article_id─┬─read_to─┬───────────read_time─┬─version─┐
│       1 │         12 │      80 │ 2023-01-06 15:23:10 │       5 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘

或者我們可以使用 LIMIT 1 BY 來獲取最新版本的行列表:

SELECT
    user_id,
    article_id,
    read_to
FROM article_reads
ORDER BY version DESC
LIMIT 1 BY
    user_id,
    article_id

┌─user_id─┬─article_id─┬─read_to─┐
│       1 │         12 │      80 │
└─────────┴────────────┴─────────┘

再次，我們不必關(guān)心舊版本的刪除-這是由 ClickHouse 在后臺自動(dòng)完成。

總結(jié)

在分析環(huán)境中更新和刪除數(shù)據(jù)可能具有挑戰(zhàn)性，并且會(huì)極大地影響數(shù)據(jù)庫性能。為了解決這個(gè)問題，ClickHouse 提供了多種強(qiáng)大的方法來更新和刪除不同情況下有效的數(shù)據(jù):

• 輕量刪除通過 DELETE FROM 語法刪除數(shù)據(jù)從 ClickHouse。這是最有效的刪除數(shù)據(jù)的方法，前提是不需要立即節(jié)省磁盤空間，并且用戶可以容忍刪除的數(shù)據(jù)“存在”在磁盤上。
• 在需要立即節(jié)省磁盤空間的情況下，通過 ALTER…DELETE 進(jìn)行基于突變的刪除。例如，遵從性需求需要保證從磁盤中刪除數(shù)據(jù)。
• 在不規(guī)律和不頻繁的變化情況下，使用 ALTER…UPDATE 進(jìn)行基于突變的更新
• 使用 TTLs 定期刪除基于日期/時(shí)間的(過時(shí)的)數(shù)據(jù);

• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl

• 使用 CollapsingMergeTree 頻繁地更新或刪除單個(gè)行。
• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/
• 使用 ReplacingMergeTree 實(shí)現(xiàn)基于版本控制的 upsert(插入/更新)。
• https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/
• 定期刪除大數(shù)據(jù)塊時(shí)刪除分區(qū)。
• https://clickhouse.com/docs/en/sql-reference/statements/alter/partition/#drop-partitionpart
• 創(chuàng)建新列(并刪除舊列)可能也是更新整個(gè)表的更有效的方法。

Refs

• https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse