ClickHouse物化视图里常见的7个坑，你踩过几个？

阿里云瑶池数据库 2024-05-24

1407

作者：思勉、宙游

1. 导读

在OLAP的业务场景中，不仅要把数据存起来，还需要把数据处理好。在ClickHouse中，为了提高数据处理性能，使用物化视图（Materialized View）是有效的方法之一。本文主要探讨Materialized View（下文称 MV）的工作原理与最佳实践，并介绍了使用过程中容易踩坑的一些问题和解决方案。

2. Materialized View介绍

物化视图（Materialized View）可以看做是一种特殊的触发器，当数据被插入时，它将数据上执行SELECT查询的结果存储为到一个目标表中。

其设计初衷可以概括为：通过在数据插入时的预处理来加速查询。


CREATE MATERIALIZED VIEW mv TO dest
AS SELECT a, b, count() AS cnt
FROM source
GROUP BY a, b

其中source为MV的源表（source table），dest为MV的目标表（target table）。

在使用MV时，需要谨记以下三个原则：

1. 只有源表插入（Insert）才会触发MV的更新。MV不感知对源表的truncate、 alter delete、alter update、 drop partition、 drop table、 rename等所有其他操作。需要注意两种特殊场景：ReplicatedMergeTree在副本（replica）之间的数据同步（replication）不会触发MV更新，分布式表（Distributed）向其本地表（Local）转发会触发MV更新。

2. MV不会对源表做查询，只会查询本次插入的数据块（insert block）。有个例外是在创建MV时如果指定了populate会对源表执行一次查询并将结果初始化到MV中。

3. MV支持任意引擎源表，包括Null引擎或者Kafka引擎这种没有保存数据能力的引擎。

3. 典型使用场景

3.1 用于数据预聚合(pre-aggregate)

这里主要使用SummingMergeTree或者AggregatingMergeTree作为MV的目标表引擎。MV中保存源表的数据的聚合结果，在对源表执行Insert时，触发对MV聚合数据的更新。MV中的数据量相比源表可以大幅下降，查询MV可以直接获取聚合结果，而不需要每次查询对源表的大量数据扫描。

比如下面的例子使用了SummingMergeTree表引擎为每个date/project汇总hits值。


-- 创建 SummingMergeTree 引擎的目标表
CREATE TABLE wikistat_top_projects
(
    `date` Date,
    `project` LowCardinality(String),
    `hits` UInt32
) ENGINE = SummingMergeTree
ORDER BY (date, project);


-- 创建物化视图
CREATE MATERIALIZED VIEW wikistat_top_projects_mv TO wikistat_top_projects AS
SELECT
    date(time) AS date,
    project,
    sum(hits) AS hits
FROM wikistat
GROUP BY date, project;

3.2 数据冗余以支持不同维度的数据查询

Clickhouse MergeTree引擎的查询效率受表的主键（primary key）和排序键（order by）的设计影响较大。我们可以通过MV来创建一张同步表，两张表具有不同的主键和排序键，可以满足不同的查询需求。

3.3 数据提取转换

比如有一个空表（Null table），有多个不同数据源的数据插入到这个空表中。空表有多个MV，它们执行不同的数据转换并将结果写入不同的目的地。

3.4 配合Kafka或者RabbitMQ引擎

Kafka引擎和RabbitMQ引擎自身没有数据持久化能力，与MV配合可以进行数据的持久化。

4. 常见问题

常见问题

MV的逻辑比较简单，仅用一句话就可以概括：在数据插入源表时，将数据同步到目标表。

ClickHouse的CK几乎没有对使用做任何限制。用户可以根据需要灵活的使用，并可以与其他的能力进行搭配，比如MV+Distributed Table, MV+ReplicatedMergeTree等。在实际应用中我们时常遭遇如下情形：尽管已成功构建了MV，且确信已将数据成功插入源表。然而，当我们在MV内执行查询时，结果却未能与预期相符，出现数据缺失或者数据重复。

4.1 数据聚合应该使用SummingMergeTree或者AggregatingMergeTree

考虑如下需求，使用MV对源表数据进行聚合，客户错误地使用非聚合引擎（MergeTree）作为MV的目标表。

错误范例：使用普通MergeTree引擎做聚合


CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);


-- 目标表错误的使用 MergeTree 引擎
CREATE TABLE dest (a Int64, b Int64, cnt Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);


-- 创建基于 dest 表创建物化视图
CREATE MATERIALIZED VIEW mv to dest
AS SELECT a, b, count() as cnt
FROM source GROUP BY a, b;


-- 执行源表插入
insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);


-- MV 实际查询结果，不符合预期
SELECT a,b,count() FROM dest GROUP BY a, b;
┌─a─┬─b─┬─count()─┐
│ 1 │ 2 │       2 │
│ 1 │ 1 │       2 │
└───┴───┴─────────┘


-- 预期的正确结果
SELECT a, b, count() as cnt FROM source GROUP BY a, b
┌─a─┬─b─┬─sum─┐
│ 1 │ 2 │   2 │
│ 1 │ 1 │   4 │
└───┴───┴─────┘

✅ 正确范例1：使用SummingMergeTree

MV中涉及到聚合操作，应该使用SummingMergeTree或AggregatingMergeTree。

首先如果聚合操作为sum操作，可以使用SummingMergeTree。


CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);


-- 创建 SummingMergeTree 引擎目标表
-- 需要注意这里通过 ORDER BY(a,b ) 指定 a,b 作为SummingMergeTree的聚合键
CREATE TABLE dest_2 (a Int64, b Int64, sum UInt64)
ENGINE = SummingMergeTree ORDER BY (a, b);


-- 创建 MV 
CREATE MATERIALIZED VIEW mv_2 to dest_2
AS SELECT a, b, sum(a) as sum
FROM source GROUP BY a, b;


insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);


-- 为什么数据并没有根据a,b聚合?
-- 这里是因为 SummingMergeTree 是在后台merge时异步进行聚合的
select * from mv_2;
┌─a─┬─b─┬─sum─┐
│ 1 │ 1 │   2 │
│ 1 │ 2 │   1 │
│ 1 │ 1 │   2 │
│ 1 │ 2 │   1 │
└───┴───┴─────┘


-- 因此查询 SummingMergeTree 时，需要添加 group by
select a,b,sum(a) as sum from mv_2 group by a;
┌─a─┬─b─┬───sum──┐
│ 1 │ 1 │      2 │
│ 1 │ 2 │      4 │
└───┴───┴────────┘


-- 或者手动执行 optimize table，手动触发 SummingMergeTree 的聚合
optimize table dest_2 final;


-- optimize 之后不需要 group by 
select * from mv_2;
┌─a─┬─b─┬─sum─┐
│ 1 │ 1 │   4 │
│ 1 │ 2 │   2 │
└───┴───┴─────┘

✅ 正确范例2：使用AggregatingMergeTree引擎

如果包含有其他聚合操作，需选择AggregatingMergeTree，下面给出AggregatingMergeTree的实现。


CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);


-- 创建 AggregatingMergeTree 引擎目标表
CREATE TABLE dest_3(
  a Int64, b Int64, 
  cnt AggregateFunction(count, Int64))
ENGINE = AggregatingMergeTree order by (a, b);


-- 创建 MV 
CREATE MATERIALIZED VIEW mv_3 TO dest_3 AS
              SELECT a, b, countState(a) AS cnt
              FROM source GROUP BY a, b


insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);


-- 使用 Merge 进行查询
select a,b,countMerge(cnt) from mv_3 group by a,b;
┌─a─┬─b─┬─countMerge(cnt)─┐
│ 1 │ 2 │               2 │
│ 1 │ 1 │               4 │
└───┴───┴─────────────────┘

4.2 聚合引擎需要注意排序键（ORDER BY）与SQL中聚合键（GROUP BY）保持一致

使用SummingMergeTree时，需要注意其排序键(ORDER BY)和 GROUP BY 聚合字段保持一致。

4.3 始终谨记MV同步只会查询Insert Block，不会查询原表

如下面的MV定义中，每次插入只会对插入的数据执行SELECT查询，而不是查询source整张表。

4.4 MV的字段名需要与查询结果字段完全一致

4.5 MV和ReplicationMergeTree结合

使用过程中需要注意：

1. ReplicatedMergeTree在副本(replica)之间的数据同步(replication)不会触发MV更新。

2. MV在源表向目标表的数据同步，会传导到ReplicatedMergeTree进行副本间同步。

如果需要在replica之间保障MV的数据一致，需要将MV的目标表定义为 ReplicatedMergeTree。

如下图，数据同步的过程如下：

1. 在 Node1 中执行插入，数据写入本副本的Table；

2. Insert 操作同步进行本副本内 MV 的同步；

3. ReplicatedMergeTree 会将数据同步到 Node2 的 Table 中；

4. Node1 的 MV 目标表为ReplicatedMergeTree引擎，会触发同步数据到 Node2 的 MV 目标表。

数据重复问题

MV 和 ReplicatedMergeTree 有一个场景会导致数据重复。

如果对 ReplicatedMergeTree 执行 Populate 操作时，会触发数据重复。

关于该问题细节可以参考：

🔗https://github.com/ClickHouse/ClickHouse/issues/9081

4.6 MV和JOIN

需注意一点：只有驱动表的INSERT操作才会触发MV更新，被驱动表不会。

如果希望驱动表和被驱动表插入时都会触发MV的更新，需要创建两个MV。实例如下：


CREATE MATERIALIZED VIEW mv1 TO dest
SELECT ...
FROM source left join some_dimension on (...)


CREATE MATERIALIZED VIEW mv2 TO dest
SELECT ...
FROM some_dimension right join source on (...)

4.7 MV和Distributed Table

可以分成四种情况分别讨论：

方案①种较为常见，用于单shard场景。该方案最简单、效率最高、且不会出现数据不同步问题。

方案②和③用于多shard场景，数据基于sharding key规则分布于不同的shard中。

其中方案②如果数据实际执行写入到Local表中，则会导致MV中的数据不同步。

5. 了解更多

阿里云瑶池旗下的云数据库ClickHouse版是全托管云原生Serverless实时数仓服务。具有开箱即用，高吞吐写入，秒级实时分析、自动弹性优势。广泛应用于流量分析、广告营销分析、行为分析、人群划分、客户画像、敏捷BI、数据集市、网络监控、分布式服务和链路监控等业务场景。点击文末阅读原文即可了解产品详情。

阿里云ClickHouse企业版已于4月26日正式商业化，并推出了指定规格资源包首购优惠的折扣活动：首次购买规格在3000-9500CCU*H的计算包，以及首次购买1个月450G及以下规格的存储包，均可享 0.4折 的超优惠价格！首次计算和存储资源组合购买不超过99.58元，欢迎登录阿里云ClickHouse官网进行选购。

🔍 复制搜索以下链接即刻体验：

计算包：

🔗https://common-buy.aliyun.com/?commodityCode=clickhouse_serverless_dp_cn

存储包：

🔗https://common-buy.aliyun.com/?commodityCode=clickhouse_storage_dp_cn

附录

[1]https://clickhouse.com/blog/using-materialized-views-in-clickhouse

[2]https://developer.aliyun.com/article/1327456

[3]https://dencrane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

[4]https://github.com/ClickHouse/ClickHouse/issues/9081

[5]https://github.com/ClickHouse/ClickHouse/issues/8336