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

简介: 一文解析ClickHouse物化视图
作者:思勉、宙游

1. 导读

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

2. Materialized View介绍

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


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

CREATE MATERIALIZED VIEW mv TO destAS 
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()─┐
122112└───┴───┴─────────┘

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


✅ 正确范例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─┐
112121112121└───┴───┴─────┘

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

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

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


正确范例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)─┐
122114└───┴───┴─────────────────┘


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

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

640 (5).png

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

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

640.png

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

640 (3).png

4.5 MV和ReplicationMergeTree结合

使用过程中需要注意:

  1. ReplicatedMergeTree在副本(replica)之间的数据同步(replication)不会触发MV更新。
  2. MV在源表向目标表的数据同步,会传导到ReplicatedMergeTree进行副本间同步。

image.png


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


如下图,数据同步的过程如下:

  1. 在 Node1 中执行插入,数据写入本副本的Table;
  2. Insert 操作同步进行本副本内 MV 的同步;
  3. ReplicatedMergeTree 会将数据同步到 Node2 的 Table 中;
  4. Node1 的 MV 目标表为ReplicatedMergeTree引擎,会触发同步数据到 Node2 的 MV 目标表。

image.11.png

数据重复问题

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

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

image.33.png


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

🔗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

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

640 (2).png

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


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


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

5. 了解更多

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


阿里云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

相关文章
|
9月前
|
存储
ClickHouse物化视图
ClickHouse物化视图
251 1
|
存储 监控 OLAP
【ClickHouse 技术系列】- 在 ClickHouse 物化视图中使用 Join
本文翻译自 Altinity 针对 ClickHouse 的系列技术文章。面向联机分析处理(OLAP)的开源分析引擎 ClickHouse,因其优良的查询性能,PB级的数据规模,简单的架构,被国内外公司广泛采用。本系列技术文章,将详细展开介绍 ClickHouse。
【ClickHouse 技术系列】- 在 ClickHouse 物化视图中使用 Join
|
24天前
|
存储 监控 Cloud Native
ClickHouse物化视图里常见的7个坑,你踩过几个?
在 OLAP 的业务场景中,不仅要把数据存起来,还需要把数据处理好。在 ClickHouse 中,为了提高数据处理性能,使用 Materialized View 是有效的方法之一。本文主要探讨 Materialized View(下文称 MV) 的工作原理与最佳实践,并介绍了使用过程中容易踩坑的一些问题和解决方案。
716 4
|
9月前
|
存储 SQL Cloud Native
一文教会你使用强大的ClickHouse物化视图
在现实世界中,数据不仅需要存储,还需要处理。处理通常在应用程序端完成。但是,有些关键的处理点可以转移到ClickHouse,以提高数据的性能和可管理性。ClickHouse中最强大的工具之一就是物化视图。在这篇文章中,我们将探秘物化视图以及它们如何完成加速查询以及数据转换、过滤和路由等任务。 如果您想了解更多关于物化视图的信息,我们后续会提供一个免费的培训课程。
27156 10
一文教会你使用强大的ClickHouse物化视图
|
8天前
|
SQL 人工智能 关系型数据库
【DDIA笔记】【ch2】 数据模型和查询语言 -- 文档模型中Schema的灵活性
【6月更文挑战第8天】网状模型是层次模型的扩展,允许节点有多重父节点,但导航复杂,需要预知数据库结构。关系模型将数据组织为元组和关系,强调声明式查询,解耦查询语句与执行路径,简化了访问并通过查询优化器提高效率。文档型数据库适合树形结构数据,提供弱模式灵活性,但在Join支持和访问局部性上不如关系型。关系型数据库通过外键和Join处理多对多关系,适合高度关联数据。文档型数据库的模式灵活性体现在schema-on-read,写入时不校验,读取时解析,牺牲性能换取灵活性。适用于不同类型或结构变化的数据场景。
12 0
|
9天前
|
存储 关系型数据库 数据库
【DDIA笔记】【ch2】 数据模型和查询语言 -- 多对一和多对多
【6月更文挑战第7天】该文探讨数据模型,比较了“多对一”和“多对多”关系。通过使用ID而不是纯文本(如region_id代替"Greater Seattle Area"),可以实现统一、避免歧义、简化修改、支持本地化及优化搜索。在数据库设计中,需权衡冗余和范式。文档型数据库适合一对多但处理多对多复杂,若无Join,需应用程序处理。关系型数据库则通过外键和JOIN处理这些关系。文章还提及文档模型与70年代层次模型的相似性,层次模型以树形结构限制了多对多关系处理。为克服层次模型局限,发展出了关系模型和网状模型。
17 6
|
10天前
|
SQL JSON NoSQL
【DDIA笔记】【ch2】 数据模型和查询语言 -- 关系模型与文档模型
【6月更文挑战第6天】关系模型是主流数据库模型,以二维表形式展示数据,支持关系算子。分为事务型、分析型和混合型。尽管有其他模型挑战,如网状和层次模型,但关系模型仍占主导。然而,随着大数据增长和NoSQL的出现(如MongoDB、Redis),强调伸缩性、专业化查询和表达力,关系模型的局限性显现。面向对象编程与SQL的不匹配导致“阻抗不匹配”问题,ORM框架缓解但未完全解决。文档模型(如JSON)提供更自然的嵌套结构,适合表示复杂关系,具备模式灵活性和更好的数据局部性。
14 0
|
11天前
|
XML NoSQL 数据库
【DDIA笔记】【ch2】 数据模型和查询语言 -- 概念 + 数据模型
【6月更文挑战第5天】本文探讨了数据模型的分析,关注点包括数据元素、关系及不同类型的模型(关系、文档、图)与Schema模式。查询语言的考量涉及与数据模型的关联及声明式与命令式编程。数据模型从应用开发者到硬件工程师的各抽象层次中起着简化复杂性的关键作用,理想模型应具备简洁直观和可组合性。
13 2
|
12天前
|
敏捷开发 存储 缓存
【DDIA笔记】【ch1】 可靠性、可扩展性和可维护性 -- 可维护性
【6月更文挑战第4天】本文探讨了Twitter面临的一次发推文引发的巨大写入压力问题,指出用户粉丝数分布是决定系统扩展性的关键因素。为解决此问题,Twitter采用混合策略,大部分用户推文扇出至粉丝主页时间线,而少数名人推文则单独处理。性能指标包括吞吐量、响应时间和延迟,其中高百分位响应时间对用户体验至关重要。应对负载的方法分为纵向和横向扩展,以及自动和手动调整。文章强调了可维护性的重要性,包括可操作性、简单性和可演化性,以减轻维护负担和适应变化。此外,良好设计应减少复杂性,提供预测性行为,并支持未来改动。
14 0
|
13天前
|
缓存 关系型数据库 数据库
【DDIA笔记】【ch1】 可靠性、可扩展性和可维护性 -- 可扩展性
【6月更文挑战第3天】可扩展性关乎系统应对负载增长的能力,但在产品初期过度设计可能导致失败。理解基本概念以应对可能的负载增长是必要的。衡量负载的关键指标包括日活、请求频率、数据库读写比例等。推特的扩展性挑战在于"扇出",即用户关注网络的广度。两种策略包括拉取(按需查询数据库)和推送(预计算feed流)。推送方法在推特案例中更为有效,因为它减少了高流量时的实时计算压力。
19 0

热门文章

最新文章