使用场景举例

物化视图的本质就是类似一种触发器，当源表有数据写入，会触发视图执行定义的 SQL，写入另外一张表。
目前在 ByteHouse 根据物化视图的用途分为如下使用场景:

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

下面以一个行为分析系统的事件表来说明上述视图的使用方法。

--创建数据库
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);

--users维表
CREATE TABLE mv.users
(
    uid UInt64,
    params String
)
ENGINE = CnchMergeTree
ORDER BY uid;

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');

---维表
insert into table mv.users(uid, params) values (544545, 'male 35 from beijing');
insert into table mv.users(uid, params) values (121245, 'female 20 from nanjing');

查询改写

物化视图查询有两种方式

• 直接查询目的表mv.events_aggregation，注意聚合算子需要加Merge后缀才能查询到正确的解决，否则会查询到乱码，带State后缀的聚合函数得到的结果的数据类型是 AggregateFunction 类型，这样类型的数据是二进制的，直接查询出来并不可读，从不同的数据节点返回的聚合结果也是 AggregateFunction 类型，最后由 Coordinator 节点将这些数据 merge 到一起，得到最终的结果。

SELECT
    app_id,
    event_name,
    event_date,
    sumMerge(sum_cost) AS sum_cost
FROM mv.events_aggregation
WHERE (toString(app_id) = '3') AND (event_name = 'show') AND (toDate(event_date) = '2022-06-14')
GROUP BY
    app_id,
    event_name,
    event_date

• 查询源表，通过优化器进行查询改写，优化器会根据语法，查询代价，数据一致性，来判断查询是否能改写为物化视图，这种是最理想的方式，对用户透明，也不用写sumMerge这种查询函数，更加通用，在查询时需要在settings中打开优化器（enable_optimizer）和允许视图匹配（enable_materialized_view_rewrite），默认情况开启优化器会自动打开物化视图匹配。

set enable_optimizer = 1;
set enable_materialized_view_rewrite = 1;
 
SELECT
    app_id,
    event_name,
    event_date,
    sum(cost) AS sum_cost
FROM mv.events
WHERE (toString(app_id) = '3') AND (event_name = 'show') AND (toDate(event_date) = '2022-06-14')
GROUP BY
    app_id,
    event_name,
    event_date settings enable_optimizer = 1,enable_materialized_view_rewrite = 1;

可以通过explain SQL的方式获取查询计划，如果计划中存在note: Materialized Views is applied for 1 times，说明命中了物化视图，可以对比一下如下命中视图和关闭视图改写执行计划的差别。
命中视图：

关闭视图改写：

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

视图定义

--视图目标表
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字段

刷新数据

--- 视图刷新
refresh materialized view mv.events_normal_view partition '2022-06-14'

查询改写

set enable_optimizer = 1;
set enable_materialized_view_rewrite = 1;

SELECT
    uid,
    sum(cost)
FROM mv.events
WHERE uid = 544545
GROUP BY uid

优化器会评估读取代价，选择读取代价最小的视图进行匹配。

实时消费以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_cluster = 'bmq_data',
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;

建表实践

• 视图定义中的源表是实时消费表，例子中是mv.events_consumer
• 目标表可以根据需要，可以适配各种引擎类型，CnchMergeTree, CnchMergeTree(带unique key), CnchAggregatingMergeTree等
• 目前定义新的视图，需要重启实时消费，system restart consume mv.events_consumer
• 上述定义三个视图会产生三份数据，定义很多视图或者复杂的聚合视图，会影响实时消费的性能
• 由于实时物化视图是consumer，所以没有必要进行查询改写，一般直接使用消费的目标表

对于一些ETL的场景，单纯的是为对数据进行加工产生物化视图，单表的跟上面的视图定义类似，下面介绍多表的视图定义，目前bytehouse仅支持简单的inner join，subquery场景，后续会支持更加复杂的多表SQL场景。

视图定义

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

--join视图定义
CREATE MATERIALIZED VIEW mv.events_join_view to mv.events_join (app_id UInt32,
  uid UInt64,
  cost UInt64,
  event_date Date,
  params String
  ) AS SELECT
     app_id,
     uid, 
     cost, 
     event_date,
     params
FROM mv.events as v inner join mv.users as u on v.uid = u.uid;

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

--subset视图定义
CREATE MATERIALIZED VIEW mv.events_extract_subset_view to mv.events_subset (app_id UInt32,
  uid UInt64,
  cost UInt64,
  event_date Date
  ) AS SELECT
     app_id,
     uid, 
     cost, 
     event_date
FROM mv.events where uid in (select uid from mv.users);

建表实践

• Join, subsquery要求维表尽量保持不变，否则需要手动刷新全部数据
• Join只支持inner join和left join
• Join中的驱动表events有新数据写入时才会触发执行视图SQL，维表更新不会更新目标表数据

刷新视图

-- 刷新join视图
refresh materialized view mv.events_join_view partition '2022-06-14';

-- 刷新subquery视图
refresh materialized view mv.events_extract_subset_view partition '2022-06-14';