同步物化视图保存了经常使用的查询，以便在查询时直接复用，实现查询加速。本文介绍了同步物化视图的语法及不同场景下同步物化视图的使用方法。

同步物化视图的本质类似一种触发器，当基表有数据写入，会触发视图执行定义的 SQL，写入另外一张表，更新粒度是导入基表的数据块，基表与视图目的表同时写入成功事务才能结束，由此可以看出同步视图会影响基表的导入性能，而且只能支持单表的场景，对于基表通过 merge 进行数据合并的场景也不能支持，因为基表变化不能传导视图目的表，不能支持类似 unique table 作为基表。
目前在 ByteHouse 根据物化视图的用途分为如下使用场景：

• Aggregate 聚合物化视图，提升特定聚合查询的性能。
• Normal 修改主键排序物化视图，提升对含有非主键列过滤条件查询性能。
• Realtime 实时消费物化视图，用于对实时数据进行加工，产出数据。

相关语法

--创建同步视图
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]<mv_name> [TO [database.]<target_name>]  AS <query_statement> [SETTINGS <mv_query_settings>];

--手动刷新视图
REFRESH MATERIALIZED VIEW db_name.mv_name [PARTITION partition_name] [WHERE predicate_expr] [ASYNC | SYNC];

支持场景

• 基表类型支持 CnchMergeTree 表，普通视图，实时消费表。
• 单表聚合，支持各种聚合算子，支持配置过滤条件。
• 单表无聚合，修改排序键，缩减读取字段，支持配置过滤条件。

限制场景

• 不支持多视图级联更新。
• 基表不支持 Unique 表。
• 由于存在数据不一致的风险，默认不支持视图查询改写。
• 由于存在数据不一致的风险，默认不支持多表查询视图。

如果存在如上要求，推荐使用异步物化视图。
下面以一个行为分析系统的事件表来说明上述视图的使用方法。

ByteHouse 云数仓版 2.2 及以上版本支持此功能。

--创建数据库
create database mv;

--数据源表
CREATE TABLE mv.events(
  app_id UInt32,
  server_time UInt64,
  event_name String,
  uid UInt64,
  cost UInt64,
  duration UInt64,
  event_date Date
) ENGINE = CnchMergeTree PARTITION BY toDate(event_date)
ORDER BY
  (app_id, uid, event_name);

Aggregate 聚合视图是物化视图最为常用的一种场景，基于特定的聚合查询对源数据抽取存为物化视图，由于聚合查询已经聚合为中间数据状态，查询视图会减少聚合计算，提高查询性能，后续的查询能命中视图，引擎对原始查询进行改写，直接查询聚合视图表。下面看具体的场景

视图定义

--视图目标表
CREATE TABLE mv.events_aggregation (
  app_id UInt32,
  event_name String,
  event_date Date,
  sum_cost AggregateFunction(sum, UInt64),
  max_duration AggregateFunction(max, UInt64)
) ENGINE = CnchAggregatingMergeTree() PARTITION BY toDate(event_date)
ORDER BY
  (app_id, event_name, event_date);

--视图定义
CREATE MATERIALIZED VIEW mv.events_aggregate_view to mv.events_aggregation (app_id UInt32,  event_name String, event_date Date, sum_cost AggregateFunction(sum, UInt64), max_duration AggregateFunction(max, UInt64)) AS SELECT
     app_id,
     event_name,
     event_date,
     sumState(cost) AS sum_cost,
     maxState(duration) AS max_duration
FROM mv.events
GROUP BY app_id, event_name, event_date; 

建表实践

• 源表一般引擎定义为 CnchMergeTree，暂时不支持带 UNIQUE KEY 的表 (由于 unique key 会自动合并相同 unique key 的行，但是物化视图并不能感知这个变化，会造成源表和视图的数据不一致)。
• 目标表引擎为 CnchAggregatingMergeTree, 此引擎类型会在 Merge 阶段，对聚合 SQL 的 group by 相同字段进行合并，减少数据量，例子中对 app_id，event_name，event_date 相同行进行聚合计算。
• 目标表中对带 State 后缀的聚合函数得到的结果的数据类型是 AggregateFunction 类型，sumState(cost) 对应 AggregateFunction(sum, UInt64)，UIn64 为 cost 的类型。
• 视图定义中建议使用 to 指明目标表，这样比较明确容易理解，聚合函数需要在后面添加后缀 State，例如 sumState(cost), maxState(duration), 之所以如此，是因为在物化视图的单一数据文件中保存的聚合值只是部分数据的聚合结果（Partial Aggregate Result），是个中间状态的数据，实际查询时需要把不同数据节点上不同数据分片的相同分组的中间态结果 merge 到一起。
• 视图定义中 group by 字段的顺序决定目标的排序键顺序，需要根据业务需求，决定排序，把查询过滤条件中经常用到，并且维度基数较低的字段排在最前面，这样会提高查询性能，例子中，目标的 order by 字段与 group by 字段相同。
• 目标表和源表的分区 partition 定义必须一致，否则 refresh 命令将不能运行。

导入数据

---明细表
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (1, 1642149961, 'show', 121245, 3454, 64, '2022-06-14');
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (2, 1642149961 , 'send', 2345, 476, 64, '2022-06-14');
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (3, 1642150683, 'show', 544545, 87, 5434, '2022-06-14');
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (3, 1642150683, 'show', 544545, 930, 232, '2022-06-14');
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (4, 1642150683, 'slide', 234545, 123, 98, '2022-06-14');
insert into table mv.events(app_id, server_time, event_name, uid, cost, duration, event_date) values (5, 1642150683, 'click', 131312, 2644, 26, '2022-06-14');

在业务频繁迭代的场景，经常需要使用非主键过滤条件进行查询，但是主表的主键顺序又不能修改，基于这种需求，可以定义物化视图来修改主键顺序，根据业务需求裁切部分列或者根据某些条件过滤数据，来产出视图。

视图定义

--视图目标表
CREATE TABLE mv.events_normal (
  app_id UInt32,
  event_name String,
  event_date Date,
  uid UInt64,
  cost UInt64
) ENGINE = CnchMergeTree() PARTITION BY toDate(event_date)
ORDER BY (uid, event_name);

--视图定义
CREATE MATERIALIZED VIEW mv.events_normal_view to mv.events_normal (app_id UInt32,
  event_name String,
  event_date Date,
  uid UInt64,
  cost UInt64) AS SELECT
     app_id,
     event_name,
     event_date,
     uid,
     cost
FROM mv.events;

建表实践

• 目标表与源表引擎类型相同一般都为 CnchMergeTree，分区键保持一致，主键顺序根据业务需求定义。
• 视图定义直接根据目标表字段类型和要求进行选取，无需定义 order by 字段。

实时消费以 Kafka 的消费为例，视图的 SQL 定义需要从 consumer 获取数据，可以全部获取作为明细表，可以进行聚合，过滤，投影等操作，可以是并联视图，或者串联视图。

视图定义

--实时消费 consumer 表定义
CREATE TABLE mv.events_consumer (
  app_id UInt32,
  server_time UInt64,
  event_name String,
  uid UInt64,
  cost UInt64,
  duration UInt64,
  event_date Date
) ENGINE = CnchKafka() SETTINGS kafka_broker_list = 'XXXX:9092',
kafka_topic_list = 'ch_qa_cnch_staging_yg',
kafka_group_name = 'events_consumer_group',
kafka_format = 'JSONEachRow',
kafka_row_delimiter = '\n',
kafka_num_consumers = 5,
kafka_max_block_size = 65536;

---明细表视图定义
CREATE MATERIALIZED VIEW mv.events_real_all_view to mv.events (
  app_id UInt32,
  server_time UInt64,
  event_name String,
  uid UInt64,
  cost UInt64,
  duration UInt64,
  event_date Date
) AS
SELECT * FROM mv.events_consumer;

---聚合表视图定义
CREATE MATERIALIZED VIEW mv.events_real_aggregate_view to mv.events_aggregation (app_id UInt32,  event_name String, event_date Date, sum_cost AggregateFunction(sum, UInt64), max_duration AggregateFunction(max, UInt64)) AS SELECT
     app_id,
     event_name,
     event_date,
     sumState(cost) AS sum_cost,
     maxState(duration) AS max_duration
FROM mv.events_consumer
GROUP BY app_id, event_name, event_date; 

--normal 表实时消费表
CREATE MATERIALIZED VIEW mv.events_real_normal_view to mv.events_normal (app_id UInt32,  event_name String, event_date Date, sum_cost AggregateFunction(sum, UInt64), max_duration AggregateFunction(max, UInt64)) AS  SELECT
     app_id,
     event_name,
     event_date,
     uid,
     cost
FROM mv.events_consumer where uid = 5434;

建表实践

• 视图定义中的源表是实时消费表。
• 目前定义新的视图，需要重启实时消费，否则会造成数据不更新。
• 上述定义三个视图会产生三份数据，定义很多视图或者复杂的聚合视图，会影响实时消费的性能。