【最佳实践指南】在ClickHouse中更新和删除数据_clickhouse 更新数据

本文翻译自：https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse

• 译者英文水平一般，如有错漏请见谅
• 一些专有名词没有译成中文，例如Mutations、Part等等，这些名词会用斜体表示

作为世界上实时分析速度最快的数据库，ClickHouse的工作经常涉及大量数据，这些数据只写入一次，很少会修改（例如，物联网设备产生的遥测事件或电子商务网站产生的客户点击事件）。虽然这些数据通常是不变的，但对在分析过程中提供上下文至关重要的附加数据集（例如，基于设备或客户ID的信息的查找表）可能需要修改。

根据你的目的和性能要求，目前有多种方法可以更新和删除ClickHouse中的数据。下文讲述了每种方法及其权衡，以及Lightweight Deletes（轻量级删除）功能的一些最新进展，这个新功能解决了一些常见的问题。我们会重点讲述在选择不同的方法时，需要考虑哪些重要因素。

在处理数据前，先确定这些数据是否需要更改，是解决问题的最佳方式。例如，对于不经常更改的数据，对数据进行版本控制可能是更好的选择。在存储效率和查询性能方面，ClickHouse是排名第一的分析数据库，因此在许多情况下，相比更改现有数据，保存多个版本的数据是一个更好的选择。

Lightweight Deletes

Lightweight Deletes是从ClickHouse中删除数据的首选和最有效的方式。通过DELETE FROM table语句，用户可以指定条件删除数据，如下所示：

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

默认情况下，此操作是异步的，除非将mutations_sync参数设置为1（见下文）。执行删除操作时，ClickHouse会为每一行数据保存一个名为_row_exists的字段，用于标记该行是否已删除。随后的查询会排除这些已标记为删除的行，如下所示：

在内部，ClickHouse将数据分为多个Part（片段），每个Part都包含列数据文件和索引。后台会不定时merge（合并）和重写这些Part，这样可以确保文件数量不会随着插入更多数据而继续增长，从而保持查询的快速。merge操作考虑了Lightweight Deletes，在新生成的Part中会排除标记为已删除的行。

Lightweight Deletes功能于22.8版本中发布，截至撰写本文时仍处于实验阶段，有望在下一个版本中投入生产。在此之前，使用Lightweight Deletes功能需要设置allow_experimental_lightweight_delete=true。

用户应该了解，通过后台的merge操作，数据行最终会从磁盘中删除。当从搜索结果中排除时，这些行仍保留在磁盘上，直到它们所在的Part被合并，发生这种情况所需的时间是不确定的，这有几个含义：

• 磁盘空间不会像通过 Mutations 方式删除那样立即释放（见下文）。如果空间释放很紧迫，例如磁盘空间不足，请考虑使用Mutations
• 由于无法保证及时从磁盘上删除，有合规要求的用户可能会希望使用Mutations来删除数据。

Lightweight Deletes操作的成本取决于WHERE子句中匹配到的行数量和当前数据 Part 的数量。当匹配少量行时，此操作将是最有效的。用户还应注意，Lightweight Deletes 在 Wide parts（每列的数据文件单独存储）上的表现，比在Compact parts（所有列数据都存储在一个文件）上的更好。前者允许将字段_row_exists存储为单独的文件，从而允许独立写入。通常，Compact parts是在插入数据后形成的，一旦Compact parts的文件超过一定尺寸（例如，由于Merge），就会改为使用Wide parts格式。对于大多数情况，都不用关心这个问题。

最后，请注意，Lightweight Deletes使用与Mutations相同的队列和后台线程。关于Lightweight Deletes内部实现原理的更多详细信息，请参阅这里。

Mutations

使用Mutations更改数据

在ClickHouse中更改表格中数据的最简单方法是使用ALTER…UPDATE语句。

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

此查询将根据给定的条件更新table表格上的col1列数据。

与某些数据库不同，默认情况下，ClickHouse的ALTER UPDATE语句是异步的，这意味着更新操作是发生在后台的，不会立即见效。这个更新表数据的过程被称为Mutations。

这里需要注意的一点是，更新数据是一个繁重的任务，因为ClickHouse必须做大量的工作来优化存储和处理。Mutations操作会删除所有包含目标行的数据Part，然后重新生成新的Part。这可能会导致相当大的I/O和集群开销，因此请谨慎使用，或者考虑下面讨论的替代方案。

使用Mutaions删除数据

与更新一样，删除也可以使用Mutations，它是Lightweight Deletes的替代方案。在大多数情况下，由于Mutations需要重写所有列，成本很高，因此Lightweight Deletes更适合于大多数场景下的数据删除。更具体地说，Mutations需要重写所有列，而Lightweight Deletes仅需重写_row_exists一个标记列。

然而，考虑到Lightweight Deletes的“最终从磁盘删除数据”属性，用户可能更喜欢这种基于Mutations的方法，以确保释放磁盘空间。此外，当用户需要确保从磁盘中删除数据时，例如由于合规原因，Mutations是合适的。

ALTER TABLE table
    DELETE WHERE col2 = 3
1
2

在此查询中，将删除col2值为3的所有行。与其他Mutations类似，默认情况下删除也是异步的。这可以使用上文讲到的mutations_sync参数改为同步。

检查Mutations的执行进度

Mutations是异步执行的，可以通过system.mutations表进行监测。这允许用户查看某张表格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 │
└───────────────────────────────────────┴─────────┘
1
2
3
4
5
6
7
8
9
10

如果is_done的值为0，则代表Mutations操作仍在执行中。Mutations对每个表格数据Part执行变更，变更完成后的Part将会立即可用：

同步更新

对于需要同步更新的用户，mutations_sync参数可以设置为1（如果我们还想等待所有副本也更新完，则可以设置为2）：

SET mutations_sync = 1
1

现在，我们的更新查询将等待Mutations完成：

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

0 rows in set. Elapsed: 1.182 sec. 
1
2
3
4

请注意，为了等待后台Mutations完成，此查询花了1秒的时间。注意此参数也适用于Lightweight Deletes。

更新整张表

在某些情况下，用户需要更新一整列的值。最初，用户可能会尝试通过使用不带WHERE子句的ALTER TABLE查询来实现这一点。但是这会失败，如下所示：

ALTER TABLE table UPDATE col1 = 'bye';

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

ClickHouse不允许您更新整个表，因为这个工作量很大。强制ClickHouse接受此操作的一种方法是使用始终为true的where条件：

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true
1
2

然而，一种更好的方法是创建一个以新值为默认值的新列，然后切换新旧列。例如：

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;
1
2
3
4
5
6

我们使用update的新值作为col1_new列的默认值，这是安全的，而且效率高得多，因为我们跳过了很重的Mutations操作。

使用Joins更新和删除

有时我们需要根据关系来删除或更新行，因此我们必须连接表，在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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

这两个表的区别在于，logins表在每个会话中只存储一个事件。假设在某个时间点，我们决定将session_id列添加到logins表中：

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64
1
2

现在我们需要使用基于user_id、time字段的JOIN，来将logins.session_id列的值更新为pageviews表中对应的值：

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 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘
1
2
3
4
5
6
7
8

首先，我们需要创建和填充一张特殊的JOIN引擎表：

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

此表将允许我们在执行更新查询时使用joinGet函数来获取基于JOIN的值：

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

我们可以看到，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 │
└─────────┴─────────────────────┴────────────┘
1
2
3
4
5
6

因为我们已经给logins表添加了session_id列，所以一旦更改完成，我们就可以删除pageviews_join表（在删除之前请检查system.mutations表以确定Mutations操作已完成）：

DROP TABLE pageviews_join
1

这种方法也可以用来删除数据（Lightweight删除 或 Mutations删除）。

高效删除大量数据

如果我们必须删除大量数据，用户可以对表进行分区，以便根据分区来删除数据，这是一个轻量级的操作。假设我们有下表：

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)
1
2
3
4
5
6
7
8
9
10

通过按project列对该表进行分区，我们可以通过删除整个分区来删除具有特定project值的行。让我们删除project=c中的所有内容：

ALTER TABLE hits
    DROP PARTITION 'c'
1
2

在这里，c是我们要删除的project列值：

一个表的分区可以在system.parts表中查到：

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

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘
1
2
3
4
5
6
7
8
9

我们还可以使用DETACH和ATTACH语句在不同的表之间移动分区（例如我们想将数据移动到trash表而不是删除它）。

在DDL语句中给表设置分区时，请注意不要按高基数的列进行分区，这可能会导致创建许多Part，从而导致性能问题。

定期删除旧数据

对于一些时间相关的数据，我们可能希望定期删除过时的数据。ClickHouse为此提供了TTL功能。这需要对表进行配置，指定要删除哪些数据以及何时删除。假设我们想从hits表中删除一个月以上的数据：

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH
1
2

在这里我们要求ClickHouse删除所有time字段在1个月以前的数据行。TTL还可以用来设置在列上，用于一段时间后把列的值重置为默认值。通过按日期分区，四舍五入到适当的时间单位，例如天，可以使此操作更加高效。ClickHouse将在执行TTL规则时以最高效的方式自动删除数据。再强调一次，表格不应按高基数（如毫秒粒度）的时间列进行分区，以避免产生过多的数据Part。通常，对于大多数TTL操作，按天或月进行分区就足够了。

使用CollapsingMergeTree引擎更新和删除数据

如果我们需要频繁更新单独的行，我们可以使用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)
1
2
3
4
5
6
7
8
9
10
11

sign作为CollapsingMergeTree引擎的一个特殊列，它的作用是告知ClickHouse我们要更新特定的行。如果我们在sign列中插入-1，那么整行都将被删除。如果我们插入一个sign为1的行，ClickHouse将保留该行。要更新的行是根据创建表时DDL语句中的ORDER BY()排序键标识的：

为了满足排序键上的重复数据消除条件，我们必须为read_start,article_id,user_id列插入相同的值来更新一行。例如，当用户开始阅读文章时，我们插入以下行：

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

现在表格里已有一行数据：

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 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘
1
2
3
4
5
6

一分钟后，当用户阅读到我们文章的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);
1
2
3

第一个插入的行的sign=-1是告诉ClickHouse删除已存在的行（基于Order By排序键 - read_start,article_id,user_id列）。而第二个插入的行（sign=1）是拥有最新进度值（read_to=70）的行。

因为更新操作是在后台进行的，为了结果的准确性，我们应该过滤sign列来获取准确的结果：

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CollapsingMergreTree引擎会根据sign字段在后台小心的、高效的删除无效的数据，因此我们无需手动去删除。你可以在这里查阅更多CollapsingMergeTree引擎的用法示例。

使用版本号和ReplacingMergeTree引擎来更新和删除数据

对于更复杂的情况，我们可能希望使用基于ReplacingMergeTree引擎的版本控制。该引擎通过使用特殊版本（version）列来跟踪应该删除的行，高效实现了在其他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)
1
2
3
4
5
6
7
8
9
10

请注意特殊的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);
1
2
3
4
5

在这里我们增加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 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘
1
2
3
4
5
6
7
8
9

或者我们可以使用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 │
└─────────┴────────────┴─────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13

再说明一下，我们无需关心旧版本数据的删除 - 这是ClickHouse在后台自动完成的。

总结

对于一个分析型数据库而言，更新和删除数据是有挑战的，它会显著影响数据库的性能。为了解决这个问题，ClickHouse为不同场景提供了不同的高效更新和删除数据方式：

• Lightweight删除，通过DELETE FROM语句从ClickHouse里删除数据。这是删除数据最高效的方式，适合无需立即释放磁盘空间、允许数据继续存留在磁盘上的场景；
• Mutation删除，通过ALTER…DELETE语句删除。适合需要立即释放磁盘空间的场景。例如，合规要求需要确保数据从磁盘上删除。
• Mutation更新，通过ALTER…UPDATE语句更新。适合不会频繁更新数据的场景
• 使用TTLs基于日期/时间定期删除过期的数据
• 使用CollapsingMergeTree引擎，适合频繁更新或删除单独行的场景
• 使用ReplacingMergeTree引擎，基于版本号实现upsert（update/insert）
• 删除分区，适合用于定期删除大量数据
• 对于更新一整个表来说，创建一个新的列（和移除旧的列）可能是一种更高效的方式

鸣谢

爱搜AI工具资源导航站