关于阿里云:ClickHouse-技术系列-在-ClickHouse-中处理实时更新

简介：本文翻译自 Altinity 针对 ClickHouse 的系列技术文章。面向联机剖析解决（OLAP）的开源剖析引擎 ClickHouse，因其低劣的查问性能，PB 级的数据规模，简略的架构，被国内外公司宽泛采纳。本系列技术文章，将具体开展介绍 ClickHouse。

本文翻译自 Altinity 针对 ClickHouse 的系列技术文章。面向联机剖析解决（OLAP）的开源剖析引擎 ClickHouse，因其低劣的查问性能，PB 级的数据规模，简略的架构，被国内外公司宽泛采纳。

阿里云 EMR-OLAP 团队，基于开源 ClickHouse 进行了系列优化，提供了开源 OLAP 剖析引擎 ClickHouse 的云上托管服务。EMR ClickHouse 齐全兼容开源版本的产品个性，同时提供集群疾速部署、集群治理、扩容、缩容和监控告警等云上产品性能，并且在开源的根底上优化了 ClickHouse 的读写性能，晋升了 ClickHouse 与 EMR 其余组件疾速集成的能力。拜访 https://help.aliyun.com/docum… 理解详情。

译者：何源（荆杭），阿里云计算平台事业部高级产品专家

（图源 Altinity，侵删）

ClickHouse 更新的简短历史
用例
实现更新
论断
后续

在 OLAP 数据库中，可变数据通常不受欢迎。ClickHouse 也不欢送可变数据。像其余一些 OLAP 产品一样，ClickHouse 最后甚至不反对更新。起初增加了更新性能，然而像其余许多性能一样，都是以“ClickHouse 形式”增加的。

即便是当初，ClickHouse 更新也是异步的，因而很难在交互式应用程序中应用。尽管如此，在许多用例中，用户须要对现有数据进行批改，并冀望立刻看到成果。ClickHouse 能做到吗？当然能够。

早在 2016 年，ClickHouse 团队就公布了一篇题为“如何在 ClickHouse 中更新数据”的文章。过后 ClickHouse 并不反对数据批改，只能应用非凡的插入构造来模仿更新，并且数据必须按分区抛弃。

为满足 GDPR 的要求，ClickHouse 团队在 2018 年提供了 UPDATE 和 DELETE。后续文章 ClickHouse 中的更新和删除目前依然是 Altinity 博客中浏览量最多的文章之一。这种异步、非原子性的更新以 ALTER TABLE UPDATE 语句的模式实现，并且可能会打乱大量数据。这对于批量操作和不频繁的更新是很有用的，因为它们不须要即时的后果。只管“失常”的 SQL 更新每年都妥妥地呈现在路线图中，但仍然没能在 ClickHouse 中实现。如果须要实时更新行为，咱们必须应用其余办法。让咱们思考一个理论的用例，并比拟在 ClickHouse 中的不同实现办法。

思考一个生成各种报警的零碎。用户或机器学习算法会不断查询数据库，以查看新的报警并进行确认。确认操作须要批改数据库中的报警记录。一旦失去确认，报警将从用户的视图中隐没。这看起来像是一个 OLTP 操作，与 ClickHouse 心心相印。

因为咱们无奈应用更新，因而只能转而插入批改后的记录。一旦数据库中有两条记录，咱们就须要一种无效的办法来获取最新的记录。为此，咱们将尝试 3 种不同的办法：

ReplacingMergeTree
聚合函数
AggregatingMergeTree

咱们首先创立一个用来存储报警的表。

CREATE TABLE alerts(
  tenant_id     UInt32,
  alert_id      String,
  timestamp     DateTime Codec(Delta, LZ4),
  alert_data    String,
  acked         UInt8 DEFAULT 0,
  ack_time      DateTime DEFAULT toDateTime(0),
  ack_user      LowCardinality(String) DEFAULT ''
)
ENGINE = ReplacingMergeTree(ack_time)
PARTITION BY tuple()
ORDER BY (tenant_id, timestamp, alert_id);

为简略起见，将所有报警特定列都打包到一个通用的“alert_data”列中。然而能够设想到，报警可能蕴含数十甚至数百列。此外，在咱们的示例中，“alert_id”是一个随机字符串。

请留神 ReplacingMergeTree 引擎。ReplacingMergeTee 是一个非凡的表引擎，它借助 ORDER BY 语句按主键替换数据——具备雷同键值的新版本即将替换旧版本行。在咱们的用例中，“行数据的新旧水平”由“ack_time”列确定。替换是在后盾合并操作中进行的，它不会立刻产生，也不能保障会产生，因而查问后果的一致性是个问题。不过，ClickHouse 有一种非凡的语法来解决这样的表，咱们在上面的查问中就会用到该语法。

在运行查问之前，咱们先用一些数据填充这个表。咱们为 1000 个租户生成 1000 万个报警：

INSERT INTO alerts(tenant_id, alert_id, timestamp, alert_data)
SELECT
  toUInt32(rand(1)%1000+1) AS tenant_id,
  randomPrintableASCII(64) as alert_id,
  toDateTime('2020-01-01 00:00:00') + rand(2)%(3600*24*30) as timestamp,
  randomPrintableASCII(1024) as alert_data
FROM numbers(10000000);

接下来，咱们确认 99% 的报警，为“acked”、“ack_user”和“ack_time”列提供新值。咱们只是插入一个新行，而不是更新。

INSERT INTO alerts (tenant_id, alert_id, timestamp, alert_data, acked, ack_user, ack_time)
SELECT tenant_id, alert_id, timestamp, alert_data, 
  1 as acked, 
  concat('user', toString(rand()%1000)) as ack_user,       now() as ack_time
FROM alerts WHERE cityHash64(alert_id) % 99 != 0;

如果咱们当初查问这个表，会看到如下后果：

SELECT count() FROM alerts

┌──count()─┐
│ 19898060 │
└──────────┘

1 rows in set. Elapsed: 0.008 sec.

表中显然既有已确认的行，也有未确认的行。所以替换还没有产生。为了查看“实在”数据，咱们必须增加 FINAL 关键字。

SELECT count() FROM alerts FINAL

┌──count()─┐
│ 10000000 │
└──────────┘

1 rows in set. Elapsed: 3.693 sec. Processed 19.90 million rows, 1.71 GB (5.39 million rows/s., 463.39 MB/s.)

当初计数是正确了，然而看看查问工夫减少了多少！应用 FINAL 后，ClickHouse 执行查问时必须扫描所有的行，并按主键合并它们。这样能失去正确答案，但造成了大量开销。让咱们看看，只筛选未确认的行会不会有更好的成果。

SELECT count() FROM alerts FINAL WHERE NOT acked

┌─count()─┐
│  101940 │
└─────────┘

1 rows in set. Elapsed: 3.570 sec. Processed 19.07 million rows, 1.64 GB (5.34 million rows/s., 459.38 MB/s.)

只管计数显著缩小，但查问工夫和解决的数据量还是一样。筛选无助于放慢查问速度。随着表增大，老本可能会更加微小。它不能扩大。

注：为了进步可读性，所有查问和查问工夫都像在“clickhouse-client”中运行一样显示。实际上，咱们尝试了屡次查问，以确保后果统一，并应用“clickhouse-benchmark”实用程序进行确认。

好吧，查问整个表没什么帮忙。咱们的用例还能应用 ReplacingMergeTree 吗？让咱们随机抉择一个 tenant_id，而后抉择所有未确认的记录——设想用户正在查看监控视图。我喜爱 Ray Bradbury，那就选 451 好了。因为“alert_data”的值只是随机生成的，因而咱们将计算一个校验和，用来确认多种办法的后果雷同：

SELECT 
  count(), 
  sum(cityHash64(*)) AS data
FROM alerts FINAL
WHERE (tenant_id = 451) AND (NOT acked)

┌─count()─┬─────────────────data─┐
│      90 │ 18441617166277032220 │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.278 sec. Processed 106.50 thousand rows, 119.52 MB (383.45 thousand rows/s., 430.33 MB/s.)

太快了！咱们只用了 278 毫秒就查问了所有未确认的数据。为什么这次很快？区别就在于筛选条件。“tenant_id”是某个主键的一部分，所以 ClickHouse 能够在 FINAL 之前筛选数据。在这种状况下，ReplacingMergeTree 就变得高效了。

咱们也试试用户筛选器，并查问由特定用户确认的报警数量。列的基数是雷同的——咱们有 1000 个用户，能够试试 user451。

SELECT count() FROM alerts FINAL
WHERE (ack_user = 'user451') AND acked

┌─count()─┐
│    9725 │
└─────────┘

1 rows in set. Elapsed: 4.778 sec. Processed 19.04 million rows, 1.69 GB (3.98 million rows/s., 353.21 MB/s.)

这个速度十分慢，因为没有应用索引。ClickHouse 扫描了全副 1904 万行。请留神，咱们不能将“ack_user”增加到索引，因为它将毁坏 ReplacingMergeTree 语义。不过，咱们能够用 PREWHERE 进行一个奇妙的解决：

SELECT count() FROM alerts FINAL
PREWHERE (ack_user = 'user451') AND acked

┌─count()─┐
│    9725 │
└─────────┘

1 rows in set. Elapsed: 0.639 sec. Processed 19.04 million rows, 942.40 MB (29.80 million rows/s., 1.48 GB/s.)

PREWHERE 是一个特地的妙招，能让 ClickHouse 以不同形式利用筛选器。通常状况下 ClickHouse 是足够智能的，能够主动将条件挪动到 PREWHERE，因而用户不用在意。这次没有产生，幸好咱们查看过了。

ClickHouse 因反对各种聚合函数而闻名，最新版本可反对 100 多种。联合 9 个聚合函数组合子（参见 https://clickhouse.tech/docs/…），这为有教训的用户提供了很高的灵活性。对于此用例，咱们不须要任何高级函数，仅应用以下 3 个函数：“argMax”、“max”和“any”。

能够应用“argMax”聚合函数执行针对第 451 个租户的雷同查问，如下所示：

SELECT count(), sum(cityHash64(*)) data FROM (
  SELECT tenant_id, alert_id, timestamp, 
         argMax(alert_data, ack_time) alert_data, 
         argMax(acked, ack_time) acked,
         max(ack_time) ack_time_,
         argMax(ack_user, ack_time) ack_user
  FROM alerts 
  GROUP BY tenant_id, alert_id, timestamp
) 
WHERE tenant_id=451 AND NOT acked;

┌─count()─┬─────────────────data─┐
│      90 │ 18441617166277032220 │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.059 sec. Processed 73.73 thousand rows, 82.74 MB (1.25 million rows/s., 1.40 GB/s.)

同样的后果，同样的行数，但性能是之前的 4 倍！这就是 ClickHouse 聚合的效率。毛病在于，查问变得更加简单。然而咱们能够让它变得更简略。

请留神，当确认报警时，咱们只更新以下 3 列：

acked: 0 => 1
ack_time: 0 => now()
ack_user:‘’=>‘user1’

在所有 3 种状况下，列值都会减少！因而，咱们能够应用“max”代替略显臃肿的“argMax”。因为咱们不更改“alert_data”，因而不须要对此列进行任何理论聚合。ClickHouse 有一个很好用的“any”聚合函数，能够实现这一点。它能够在没有额定开销的状况下选取任何值：

SELECT count(), sum(cityHash64(*)) data FROM (
  SELECT tenant_id, alert_id, timestamp, 
    any(alert_data) alert_data, 
    max(acked) acked, 
    max(ack_time) ack_time,
    max(ack_user) ack_user
  FROM alerts
  GROUP BY tenant_id, alert_id, timestamp
) 
WHERE tenant_id=451 AND NOT acked;

┌─count()─┬─────────────────data─┐
│      90 │ 18441617166277032220 │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.055 sec. Processed 73.73 thousand rows, 82.74 MB (1.34 million rows/s., 1.50 GB/s.)

查问变简略了，而且更快了一点！起因就在于应用“any”函数后，ClickHouse 不须要对“alert_data”列计算“max”！

AggregatingMergeTree 是 ClickHouse 最弱小的性能之一。与物化视图联合应用时，它能够实现实时数据聚合。既然咱们在之前的办法中应用了聚合函数，那么是否用 AggregatingMergeTree 使其更加欠缺呢？实际上，这并没有什么改善。

咱们一次只更新一行，所以一个组只有两行要聚合。对于这种状况，AggregatingMergeTree 不是最好的抉择。不过咱们有个小技巧。咱们晓得，报警总是先以非确认状态插入，而后再变成确认状态。用户确认报警后，只有 3 列须要批改。如果咱们不反复其余列的数据，能够节俭磁盘空间并进步性能吗？

让咱们创立一个应用“max”聚合函数来实现聚合的表。咱们也能够用“any”代替“max”，但列必须是能够设置为空的——“any”会抉择一个非空值。

DROP TABLE alerts_amt_max;

CREATE TABLE alerts_amt_max (
  tenant_id     UInt32,
  alert_id      String,
  timestamp     DateTime Codec(Delta, LZ4),
  alert_data    SimpleAggregateFunction(max, String),
  acked         SimpleAggregateFunction(max, UInt8),
  ack_time      SimpleAggregateFunction(max, DateTime),
  ack_user      SimpleAggregateFunction(max, LowCardinality(String))
)
Engine = AggregatingMergeTree()
ORDER BY (tenant_id, timestamp, alert_id);

因为原始数据是随机的，因而咱们将应用“alerts”中的现有数据填充新表。咱们将像之前一样分两次插入，一次是未确认的报警，另一次是已确认的报警：

INSERT INTO alerts_amt_max SELECT * FROM alerts WHERE NOT acked;

INSERT INTO alerts_amt_max 
SELECT tenant_id, alert_id, timestamp,
  '' as alert_data, 
  acked, ack_time, ack_user 
FROM alerts WHERE acked;

请留神，对于已确认的事件，咱们会插入一个空字符串，而不是“alert_data”。咱们晓得数据不会扭转，咱们只能存储一次！聚合函数将填补空白。在理论利用中，咱们能够跳过所有不变的列，让它们取得默认值。

有了数据后，咱们先检查数据大小：

SELECT 
    table, 
    sum(rows) AS r, 
    sum(data_compressed_bytes) AS c, 
    sum(data_uncompressed_bytes) AS uc, 
    uc / c AS ratio
FROM system.parts
WHERE active AND (database = 'last_state')
GROUP BY table

┌─table──────────┬────────r─┬───────────c─┬──────────uc─┬──────────────ratio─┐
│ alerts         │ 19039439 │ 20926009562 │ 21049307710 │ 1.0058921003373666 │
│ alerts_amt_max │ 19039439 │ 10723636061 │ 10902048178 │ 1.0166372782501314 │
└────────────────┴──────────┴─────────────┴─────────────┴────────────────────┘

好吧，因为有随机字符串，咱们简直没有压缩。然而，因为咱们不用存储“alerts_data”两次，所以相较于不聚合，聚合后数据规模能够放大一半。

当初咱们试试对聚合表进行查问：

SELECT count(), sum(cityHash64(*)) data FROM (
   SELECT tenant_id, alert_id, timestamp, 
          max(alert_data) alert_data, 
          max(acked) acked, 
          max(ack_time) ack_time,
          max(ack_user) ack_user
     FROM alerts_amt_max
   GROUP BY tenant_id, alert_id, timestamp
) 
WHERE tenant_id=451 AND NOT acked;

┌─count()─┬─────────────────data─┐
│      90 │ 18441617166277032220 │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.036 sec. Processed 73.73 thousand rows, 40.75 MB (2.04 million rows/s., 1.13 GB/s.)

多亏了 AggregatingMergeTree，咱们解决的数据更少（之前是 82MB，当初是 40MB），效率更高。

ClickHouse 会尽最大致力在后盾合并数据，从而删除反复的行并执行聚合。然而，有时强制合并是有意义的，例如为了开释磁盘空间。这能够通过 OPTIMIZE FINAL 语句来实现。OPTIMIZE 操作速度慢、代价高，因而不能频繁执行。让咱们看看它对查问性能有什么影响。

OPTIMIZE TABLE alerts FINAL
Ok.
0 rows in set. Elapsed: 105.675 sec.

OPTIMIZE TABLE alerts_amt_max FINAL
Ok.
0 rows in set. Elapsed: 70.121 sec.

执行 OPTIMIZE FINAL 后，两个表的行数雷同，数据也雷同。

┌─table──────────┬────────r─┬───────────c─┬──────────uc─┬────────────ratio─┐
│ alerts         │ 10000000 │ 10616223201 │ 10859490300 │ 1.02291465565429 │
│ alerts_amt_max │ 10000000 │ 10616223201 │ 10859490300 │ 1.02291465565429 │
└────────────────┴──────────┴─────────────┴─────────────┴──────────────────┘

不同办法之间的性能差别变得不那么显著了。汇总表如下：

ClickHouse 提供了丰盛的工具集来解决实时更新，如 ReplacingMergeTree、CollapsingMergeTree（本文未提及）、AggregatingMergeTree 和聚合函数。所有这些办法都具备以下三个共性：

通过插入新版原本“批改”数据。ClickHouse 中的插入速度十分快。
有一些无效的办法来模仿相似于 OLTP 数据库的更新语义。
然而，理论的批改并不会立刻产生。

具体方法的抉择取决于应用程序的用例。对用户来说，ReplacingMergeTree 是含糊其辞的，也是最不便的办法，但只实用于中小型的表，或者数据总是按主键查问的状况。应用聚合函数能够提供更高的灵活性和性能，但须要大量的查问重写。最初，AggregatingMergeTree 能够节约存储空间，只保留批改过的列。这些都是 ClickHouse DB 设计人员的好工具，可依据具体须要来利用。

您曾经理解了在 ClickHouse 中解决实时更新相干内容，本系列还包含其余内容：

应用新的 TTL move，将数据存储在适合的中央
在 ClickHouse 物化视图中应用 Join
ClickHouse 聚合函数和聚合状态
ClickHouse 中的嵌套数据结构

原文链接
本文为阿里云原创内容，未经容许不得转载。

关于阿里云:ClickHouse-技术系列-在-ClickHouse-中处理实时更新

前言

在 ClickHouse 中解决实时更新

目录

ClickHouse 更新的简短历史

用例

ReplacingMergeTree

聚合函数

AggregatingMergeTree

实现更新

论断

后续

Just My Socks（注册教程内含优惠码）

关于阿里云:ClickHouse-技术系列-在-ClickHouse-中处理实时更新

前言

在 ClickHouse 中解决实时更新

目录

ClickHouse 更新的简短历史

用例

ReplacingMergeTree

聚合函数

AggregatingMergeTree

实现更新

论断

后续

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）