![cover](https://img-blog.csdnimg.cn/direct/e9cbd5ac89f04476afddfaa898114347.png)
【最佳实践指南】在ClickHouse中更新和删除数据
对于一个分析型数据库而言,更新和删除数据是有挑战的,它会显著影响数据库的性能。为了解决这个问题,ClickHouse为不同场景提供了不同的高效更新和删除数据方式:Lightweight删除,通过语句从ClickHouse里删除数据。这是删除数据最高效的方式,适合无需立即释放磁盘空间、允许数据继续存留在磁盘上的场景;Mutation删除,通过语句删除。适合需要立即释放磁盘空间的场景。例如,合规要求需
本文翻译自: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
默认情况下,此操作是异步的,除非将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
此查询将根据给定的条件更新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
在此查询中,将删除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 │
└───────────────────────────────────────┴─────────┘
如果is_done
的值为0
,则代表Mutations操作仍在执行中。Mutations对每个表格数据Part执行变更,变更完成后的Part将会立即可用:
同步更新
对于需要同步更新的用户,mutations_sync参数可以设置为1
(如果我们还想等待所有副本也更新完,则可以设置为2
):
SET mutations_sync = 1
现在,我们的更新查询将等待Mutations完成:
ALTER TABLE table
UPDATE col1 = 'bye' WHERE col2 > 0
0 rows in set. Elapsed: 1.182 sec.
请注意,为了等待后台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';
ClickHouse不允许您更新整个表,因为这个工作量很大。强制ClickHouse接受此操作的一种方法是使用始终为true
的where条件:
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;
我们使用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;
这两个表的区别在于,logins
表在每个会话中只存储一个事件。假设在某个时间点,我们决定将session_id
列添加到logins
表中:
ALTER TABLE logins
ADD COLUMN `session_id` UInt64
现在我们需要使用基于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 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘
首先,我们需要创建和填充一张特殊的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 │
└─────────┴─────────────────────┴────────────┘
因为我们已经给logins
表添加了session_id
列,所以一旦更改完成,我们就可以删除pageviews_join
表(在删除之前请检查system.mutations
表以确定Mutations操作已完成):
DROP TABLE pageviews_join
这种方法也可以用来删除数据(Lightweight删除 或 Mutations删除)。
高效删除大量数据
如果我们必须删除大量数据,用户可以对表进行分区,以便根据分区来删除数据,这是一个轻量级的操作。假设我们有下表:
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语句中给表设置分区时,请注意不要按高基数的列进行分区,这可能会导致创建许多Part,从而导致性能问题。
定期删除旧数据
对于一些时间相关的数据,我们可能希望定期删除过时的数据。ClickHouse为此提供了TTL功能。这需要对表进行配置,指定要删除哪些数据以及何时删除。假设我们想从hits
表中删除一个月以上的数据:
ALTER TABLE hits
MODIFY TTL time + INTERVAL 1 MONTH
在这里我们要求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)
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);
现在表格里已有一行数据:
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
列来获取准确的结果:
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引擎会根据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)
请注意特殊的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为不同场景提供了不同的高效更新和删除数据方式:
- Lightweight删除,通过
DELETE FROM
语句从ClickHouse里删除数据。这是删除数据最高效的方式,适合无需立即释放磁盘空间、允许数据继续存留在磁盘上的场景; - Mutation删除,通过
ALTER…DELETE
语句删除。适合需要立即释放磁盘空间的场景。例如,合规要求需要确保数据从磁盘上删除。 - Mutation更新,通过
ALTER…UPDATE
语句更新。适合不会频繁更新数据的场景 - 使用TTLs基于日期/时间定期删除过期的数据
- 使用CollapsingMergeTree引擎,适合频繁更新或删除单独行的场景
- 使用ReplacingMergeTree引擎,基于版本号实现upsert(update/insert)
- 删除分区,适合用于定期删除大量数据
- 对于更新一整个表来说,创建一个新的列(和移除旧的列)可能是一种更高效的方式
鸣谢
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)