【最佳实践指南】在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

默认情况下,此操作是异步的,除非将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 DeletesWide 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 = 21 │
│ UPDATE col1 = 'All hi' WHERE col2 > 00 │
└───────────────────────────────────────┴─────────┘

如果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─┐
│       22023-01-09 12:23:1622023-01-09 12:23:162752888102 │
│       12023-01-09 13:23:1612023-01-09 13:23:164135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘

首先,我们需要创建和填充一张特殊的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─┐
│       22023-01-09 12:23:162752888102 │
│       12023-01-09 13:23:164135462640 │
└─────────┴─────────────────────┴────────────┘

因为我们已经给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         │
└───────────┘

我们还可以使用DETACHATTACH语句在不同的表之间移动分区(例如我们想将数据移动到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,那么整行都将被删除。如果我们插入一个sign1的行,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─┐
│       11202023-01-06 15:20:322023-01-06 15:20:321 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘

一分钟后,当用户阅读到我们文章的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)─┐
│         1212023-01-06 15:21:5970 │
└────────────┴─────────┴─────────────────────┴──────────────┘

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─┐
│       112802023-01-06 15:23:105 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘

或者我们可以使用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─┐
│       11280 │
└─────────┴────────────┴─────────┘

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

总结

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

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

鸣谢

爱搜AI工具资源导航站

  • 3
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
### 回答1: 数据质量改进实践指南 PDF 是一份专门为数据质量改进工作提供指导的文件,旨在帮助组织有效管理和优化其数据质量。该指南为企业提供了一系列工具和方法,以确保它们的数据质量符合最佳实践和标准。 数据质量是指数据在其完整性、准确性、一致性、时效性和可用性方面的能力。这些方面可以通过纠正错误、重复和缺少数据,确保数据来源的可靠性,以及制定数据管理规则和流程来实现。 该指南提供了一些最佳实践,以确保组织能够管理和优化其数据质量。首先,需要制定明确的数据管理规则,并对整个组织进行宣传,以保证所有人都能够理解和遵守这些规则。其次,需要对数据进行分类和清洗,以确保数据的准确性和一致性,同时清除无用或重复的数据。 此外,该指南还提供了一些工具和面向实践的建议,以帮助企业改进其数据质量。这些建议包括使用数据质量指标来跟踪数据质量,建立数据审查程序以确保数据的一致性和准确性,以及通过数据质量检查来提高系统性能和可靠性。 总之,数据质量改进实践指南 PDF 是一份值得企业管理人员和数据管理员阅读的指南文件,可以帮助他们更好地管理和优化其数据质量。通过采用这些最佳实践,企业可以实现更高的数据质量、更高的数据价值和更高的组织效率。 ### 回答2: 《数据质量改进实践指南》PDF是一份关于数据质量管理的实践手册。通过该手册,我们可以了解到数据质量管理的重要性及如何实施数据质量管理。 数据质量管理是企业正确使用数据的前提,数据质量差可能导致决策失误,进而影响到企业的运营和发展。该手册从数据的搜集、存储、处理、使用方面为我们详细说明了如何提高数据质量。 手册介绍了一系列质量管理工具,如数据质量检查表、数据质量度量工具、数据质量改进工具等。同时,还提供了一些数据质量管理的经验技巧和最佳实践,帮助企业建立自己的数据质量管理体系。 该手册实践性强,内容详尽,对于数据质量管理有需求的企业和个人来说都是非常有价值的资料。通过该手册的应用,可以帮助企业更好地利用自己的数据,提高决策的准确性和效率,从而为企业的运营和发展提供有力的支持。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值