物化视图

ByteHouse 数据库中的视图（View），除了 普通视图（Normal View） 以外，还有一种 物化视图（Materialized View）。

• 普通视图：普通视图没有真正存储数据，不占用存储空间。只是读取数据的执行操作，可以看作是一条保存过的 SQL 查询语句。
• 物化视图：物化视图 存储了 SQL 查询语句包含的数据，并提供更新机制。用查询物化视图来替代直接查询数据表，可以避免对数据进行再次的计算与聚合，能够以空间换时间的方式节省查询时间，达到查询加速和简化查询逻辑的目的。

物化视图作为一种预计算的优化方式，广泛应用于传统数据库中，如Oracle，MS SQL Server等。随着大数据技术的普及，各类数仓及查询引擎在业务中扮演着越来越重要的数据分析角色，而物化视图作为数据查询的加速器，将极大增强用户在数据分析工作中的使用体验。
物化视图使用查询重写（Query Rewrite）机制，不需要修改原有的查询语句，引擎优化器会自动选择合适的物化视图进行查询重写，完全对应用透明。

物化视图是将查询结果预先计算并存储的一张特殊的表。“物化”（Materialized）这个词是相对于普通视图而言。普通视图较普通的表提供了易用性和灵活性，但无法加快数据访问的速度。物化视图像是视图的缓存，它不是在运行时构建和计算数据集，而是在创建的时候预先计算、存储和优化数据访问，并自动刷新来保证数据的实时性。
物化视图最重要的功能就是查询加速。数据仓库中存在大量在大型表上执行复杂的查询，这些查询会消耗大量资源和时间。物化视图可以通过预计算的结果回答查询，消除昂贵的Join和聚合计算所带来的开销，大幅度改善查询处理时间，降低系统负载。对于可以预见并反复使用相同子查询结果的查询，物化视图特别有用。
物化视图最核心的内容是数据更新和查询改写。

1. 提前准备好底表，并按需导入数据；
2. 创建物化视图的目标表；

• 当前每张底表最多仅允许创建三个物化视图，超出该数量时将被将被禁止创建。
• 唯一键引擎 (CnchMergeTree) 暂不支持使用物化视图。
• 不支持 JOIN/SUB QUERY。
• 不支持被嵌套的聚合函数。如 sum(c + 1) 支持，而sum(c) + 1不支持。
• 如果该视图 SQL 计算后没有任何对应结果，此时无法创建。
• 所有 GROUP BY 字段必须出现在 SELECT 中。
• 查询语句中 where 条件中的用到的所有列都需要在 select 语句中进行定义，否则查询时可能无法成功改写匹配。
• 建议创建视图的字段尽量保持源表中的列，如select (a + 1) / 2 from table group by a可以改写成select a from table group by a，这样可以在查询的时候使用一张视图覆盖尽可能多的查询场景。
• 视图语句中字段别名不支持以下划线 "_" 开头。

创建物化视图

SQL 语法

通过手动定义目标表（target_table_name）的物化视图创建，语法参考：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name [TO [db_name.]target_table_name] 
AS SELECT select_statement FROM base_table_name;

通过界面操作

通过用户界面“SQL工作表”，参考上面的 SQL 语法填写物化视图语句，并运行。

更新物化视图

在创建物化视图时刻起，物化视图的数据与原始表的数据同步更新，如果需要对以往历史的数据分区进行物化，对于运行中的物化视图，我们提供了刷新分区功能。

更新语法

REFRESH MATERIALIZED VIEW xxx PARTITION xxx

例子：

• refresh materialized view test partition '2019-01-01' （同步'2019-01-01'分区的原始数据）

更新过程

该功能用来更新物化视图表的某个分区数据，并默认进行级联操作，即更新该视图表分区数据的同时，会同时更新依赖于当前视图表的所有物化视图的同一 partition，并一直级联传递下去。如果不想级联，可以加上 SETTINGS，设置 cascading_refresh_materialized_view 为 0，即：

refresh MATERIALIZED VIEW xxx PARTITION xxx SETTINGS cascading_refresh_materialized_view = 0

在更新视图分区数据时，相应底表分区的数据量可能十分巨大，更新会占用许多 CPU 和内存，还可能会导致更新失败，这时可以使用参数 max_rows_to_refresh_by_partition。在 Clickhouse 中一个 partition 由多个数据 part 组成，使用该参数，我们可以控制在该 partition 单机数据总行数超过该参数定义的值时，基于 part 级别一部分一部分的更新该分区，而不是在整个 partition 上进行更新，这样可以控制资源使用量。当然，如果视图是聚合表，按 part 一部分一部分的进行更新会导致最后视图数据的聚合效果不如在整个 partition 上进行更新，需要自己进行平衡。该参数默认值是 100000000 （1亿），使用例子：

REFRESH MATERIALIZED VIEW xxx PARTITION xxx SETTINGS max_rows_to_refresh_by_partition = xxx

此外，可以使用 partitionStatus 函数获取一张物化视图表对应分区的状态，状态有三类：None （表示分区不存在），Normal （表示分区存在并处于正常状态），Refreshing （表示分区正在被更新）。例如：

select partitionStatus(test, test_mv, '2020-01-01')

管理物化视图

入口：数据库 > 物化视图

Bytehouse会列出数据库中的所有物化视图，以及它们的底表/目标表行数比例，以及命中率。

• 底表/目标表行数比例：当该比例>10，则表示该物化视图比较有效率
• 命中率：当该命中率较高时，则表示该物化视图在60天内经常被访问

查询物化视图

用户可以直接查询物化视图，但一般 推荐直接查询底表。Bytehouse优化器会自动做出查询改写，以大幅度改善查询处理时间。

删除物化视图

用户可以通过界面或者SQL删除物化视图。

SQL语法

可以通过下面的。

DROP VIEW [IF EXISTS] [db.]viewname

通过界面操作

如果用户创建了目标表，也需要手动drop目标表。

以下提供一个样例，演示“物化视图”的创建过程。

-- 创建数据库
CREATE database test;

-- 创建底表
CREATE TABLE test.event_metric (
`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) 
SETTINGS index_granularity = 8192

-- 底表插入数据
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (1, 1642149961, 'show', 121245, 3454, 64, '2022-09-14');
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (2, 1642149961 , 'send', 2345, 476, 64, '2022-09-14');
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (3, 1642150683, 'show', 544545, 87, 5434, '2022-09-14');
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (3, 1642150683, 'show', 544545, 930, 232, '2022-09-14');
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (4, 1642150685, 'slide', 234545, 123, 98, '2022-09-15');
insert into table test.event_metric(app_id, server_time, event_name, uid, cost, duration, event_date) values (5, 1642150688, 'click', 131312, 2644, 26, '2022-09-15');

-- 创建目标表
CREATE TABLE test.aggregate_data (
`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) 
SETTINGS index_granularity = 8192

-- 创建物化视图
CREATE MATERIALIZED VIEW test.aggregate_view  TO test.aggregate_data (
`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 test.event_metric  
GROUP BY app_id, event_name, event_date

-- 刷新物化视图
refresh materialized view test.aggregate_view partition '2022-09-14';
refresh materialized view test.aggregate_view partition '2022-09-15';