在 ClickHouse 中處理更新和刪除以及 Upserts

作者：為少 2023-09-26 00:00:00

根據你的目標和性能要求，ClickHouse 有多種更新和刪除數據的方法。本文的其余部分將描述每種方法及其優缺點，以及解決一些常見挑戰的輕量級刪除的一些最新進展。我們推薦最佳實踐，并強調在考慮一種方法時需要考慮的一些重要事項。

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

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

輕量級刪除

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

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

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

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

圖片

在內部，ClickHouse 將數據分成幾個部分，每個部分包含列數據文件和索引。常規的合并周期負責組合(合并)和重寫這些部分。這確保了文件的數量不會隨著插入更多數據而繼續增長，從而保持查詢的速度。這些合并考慮輕量級刪除，不包括那些在新形成的部分中標記為要刪除的行。

圖片

它于 22.8 發布，在撰寫本文時仍處于試驗階段，輕量級刪除將在下一個版本中成為生產就緒。在此之前，使用輕量級刪除需要設置allow_experimental_lightweight_delete=true。

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

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

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

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

最后，請注意，輕量級刪除使用與我們下面描述的相同的突變隊列和后臺線程。關于內部實現的更多細節，我們推薦使用這里的文檔。

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

突變

使用突變更新數據

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

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

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

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

與一些數據庫不同，ClickHouse 的 ALTER UPDATE 語句默認情況下是異步的。這意味著更新發生在后臺，您不會立即對表產生影響。這個更新表的過程稱為突變。

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

圖片

這里需要注意的是，更新數據是一個繁重的查詢，因為 ClickHouse 必須做大量的工作來優化存儲和處理。突變操作強制重寫包含要刪除的行的所有數據部分，在形成新部分時排除目標行。這可能會導致相當大的 I/O 和集群開銷，因此要謹慎使用它，或者考慮下面討論的替代方案。

使用突變刪除數據

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

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

ALTER TABLE table
    DELETE WHERE col2 = 3

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

檢查突變進展

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

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，那么它仍然在執行。對每個表部分執行突變，突變后的部分立即可用：

圖片

同步更新

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

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

SET mutations_sync = 1

現在我們的更新查詢將等待突變完成:

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

0 rows in set. Elapsed: 1.182 sec.

注意，當 ClickHouse 等待后臺突變完成時，這個查詢花了 1 秒鐘的時間。注意，此參數也適用于輕量級刪除。

更新整個表

在某些情況下，用戶需要更新整個列的值。最初，用戶可能會嘗試使用不帶 WHERE 子句的 ALTER TABLE 查詢來實現這一點。然而，這是失敗的，如下所示:

ALTER TABLE table UPDATE col1 = 'bye';

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

ClickHouse 不會讓你更新整個表，因為更新是繁重的。強迫 ClickHouse 接受此操作的一種方法是使用始終為真過濾器:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true

然而，更優的方法是創建一個新列，將新值作為默認值，然后在新舊列之間切換。例如：

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 列的默認值來指定要使用的更新值。這是安全且高效得多的，因為我們跳過了這里的重突變操作。

使用 JOIN 進行更新和刪除

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

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;

這兩個表之間的區別在于，logins 表每個會話只存儲一個事件。假設在某個時間點，我們決定將 session_id 列添加到 logins 表中:

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

我們現在需要使用 user_id 和 time 上的 JOIN，用 pageviews 表中的相應值更新 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 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘

首先，我們需要創建并填充一個特殊的 Join 表:

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

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

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

我們可以看到，logins 表被相應的更新了 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 │
└─────────┴─────────────────────┴────────────┘

因為我們已經通過添加 session_id 列更改了 logins 表，所以我們可以在更改完成后 DROP pageviews_join 表(刪除之前請檢查 system.mutations 表以確定):

DROP TABLE pageviews_join

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

高效刪除大塊數據塊

如果我們必須刪除大的數據塊，用戶可以對表進行分區，以便根據需要刪除分區。這是一個輕量級操作。假設我們有以下表:

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

通過 project 列對該表進行分區，我們可以通過刪除整個分區來刪除具有特定 project 值的行。讓我們刪除所有帶有 project = c 的內容:

ALTER TABLE hits
    DROP PARTITION 'c'

這里，c 是我們想要刪除的 project 列值:

圖片

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

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

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

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

在 DDL 中設置分區時，要注意按具有高基數的列或表達式進行分區的常見缺陷。這可能導致創建許多部件，從而導致性能問題。

定期刪除舊數據

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

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 刪除當前時間的時間列值超過一個月的所有行。還可以在列上設置 TTL，以便在一段時間后將其值重置為默認值。通過按日期進行分區，四舍五入到適當的時間單位，例如，days，可以使該操作更有效。在執行 TTL 規則時，ClickHouse 將以最有效的方式自動刪除數據。同樣，表不應該按照高基數(例如毫秒粒度)的時間列進行分區，以避免高部分計數。通常按天或月劃分對于大多數 TTL 操作來說就足夠了。

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

使用 CollapsingMergeTree 刪除和更新

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

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

假設我們有一個包含文章統計信息的表，用于跟蹤每篇文章的閱讀深度。我們希望用一行顯示每個用戶閱讀每篇文章的深度。這里的挑戰在于，我們必須在用戶閱讀文章時更新實際的閱讀進度。讓我們為數據創建一個表:

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，整行就會被刪除。如果我們插入一行 sign = 1，ClickHouse 將保留這一行。要更新的行是根據創建表時在 ORDER BY () DDL 語句中使用的排序鍵來標識的：

圖片

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

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

現在我們在表中有了一行:

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 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘

一分鐘后，當用戶達到文章的 70% 時，我們插入以下 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，它應該刪除現有的行(基于值在 ORDER BY 元組- read_start, article_id 和 user_id 列)。第二個插入的行( sign=1 )是 read_to 列設置為新值 70 的新行。

由于數據更新是在后臺進行的，因此最終的結果是一致的，我們應該對 sign 列進行過濾以獲得正確的結果:

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.

現在，CollapsingMergreTree 引擎會在后臺有效地從存儲中刪除被取消的行，所以我們不必手動刪除它們。你可以在這里找到更多使用 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

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

圖片

對于前面的閱讀文章的例子，我們可以使用下面的結構:

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 數字列，ReplacingMergeTree 引擎將使用它來標記要刪除的行。讓我們模擬一個用戶在 0 到 80% 的時間內閱讀一篇文章:

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

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

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 │
└─────────┴────────────┴─────────┘

再次，我們不必關心舊版本的刪除-這是由 ClickHouse 在后臺自動完成。

總結

在分析環境中更新和刪除數據可能具有挑戰性，并且會極大地影響數據庫性能。為了解決這個問題，ClickHouse 提供了多種強大的方法來更新和刪除不同情況下有效的數據:

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

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

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

Refs

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

責任編輯：武曉燕來源：黑客下午茶

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看