CREATE MATERIALIZED VIEW - 云原生大数据计算服务 MaxCompute

基于满足物化视图场景的数据创建物化视图，支持分区和聚簇场景。

背景信息

视图是一种虚拟表，任何对视图的查询，都会转换为视图SQL语句的查询。而物化视图是一种特殊的物理表，物化视图会存储实际的数据，占用存储资源。更多物化视图计费信息，请参见计费规则。

物化视图适用于如下场景：

模式固定、且执行频次高的查询。
查询包含非常耗时的操作，比如聚合、连接操作等。
查询仅涉及表中的很小部分数据。

物化视图与传统查询的对比如下。

对比项

传统查询方式

物化视图查询方式

查询语句

直接使用SQL语句查询数据。

select empid，deptname  
from emps join depts 
on emps.deptno=depts.deptno 
where hire_date >= '2018-01-01';

您需要创建物化视图，然后基于物化视图查询数据。

创建物化视图语句如下：

create materialized view mv
as select empid，deptname, hire_date 
from emps join depts 
on emps.deptno=depts.deptno 
where hire_date >= '2016-01-01';

基于创建的物化视图查询数据：

select empid, deptname from mv 
where hire_date >= '2018-01-01';

如果物化视图开启了查询改写功能，使用如下SQL语句查询数据时会直接从物化视图中查询数据：

select empid，deptname 
from emps join depts 
on emps.deptno=depts.deptno 
where hire_date >= '2018-01-01';
--相当于如下语句。
select empid, deptname from mv 
where hire_date >= '2018-01-01';

查询特点

查询涉及读表、JOIN、过滤（where）操作。当源表数据量很大时，查询速度会很慢。操作复杂度较高，运行效率低。

查询涉及读表、过滤操作。不涉及JOIN操作。MaxCompute会自动匹配到最优物化视图，并直接从物化视图中读取数据，从而大大提高查询效率。

计费规则

物化视图费用包含如下两部分：

存储费用
物化视图会占用物理存储空间，会产生存储费用，按量计费。更多计费信息，请参见存储费用（按量计费）。
计算费用
创建、更新、查询物化视图及查询改写（物化视图有效）过程中涉及到查询数据，会消耗计算资源产生计算费用。
- 当MaxCompute项目的规格类型为包年包月时，不单独收费。
- 当MaxCompute项目的规格类型为按量计费时，按照SQL复杂度及输入数据量计算费用。更多计费信息，请参见SQL标准计费。您需要注意如下信息：
  - 更新物化视图执行的SQL与创建物化视图执行的SQL相同，费用取决于执行SQL时输入的数据量和复杂度。
  - 当物化视图处于生效状态时查询改写会从物化视图中读取数据，查询语句的输入数据量（从物化视图读取部分）与物化视图相关，与物化视图源表无关。当物化视图处于失效状态时不支持查询改写，查询语句会直接查询源表，查询语句的输入数据量与源表相关。更多查询物化视图状态信息，请参见DESC TABLE/VIEW。
  - 由于多表关联生成物化视图会产生数据膨胀等原因，从物化视图读取的数据量不一定绝对小于源表，MaxCompute不能保证读取物化视图一定比读取源表节省费用。

使用限制

物化视图的使用限制如下。
- 不支持窗口函数。
- 不支持UDTF函数。
- 默认不支持非确定性函数（例如UDF、UDAF等）。当您的业务场景必须要使用非确定性函数时，请在Session级别设置属性set odps.sql.materialized.view.support.nondeterministic.function=true;。
创建物化视图时，需满足以下限制条件。
- 物化视图的名称不允许和当前已有表、视图、物化视图名称重复。您可以通过show tables;命令查看项目中的全部表及物化视图名称。
- 不支持基于存在的物化视图创建新的物化视图。
- 不支持基于外部表创建物化视图。

注意事项

当查询语句执行失败时，物化视图也会创建失败。
物化视图分区列必须来源于某张源表，其顺序和列数目必须和源表一样，列名称可以不一样。
列注释需要指定所有列，包含分区列。如果只指定部分列，会报错。
可以同时指定分区和聚簇，此时每个分区中的数据都有指定的聚簇属性
当查询语句中包含不支持的算子时会报错。物化视图支持的算子列表，请参见基于物化视图执行查询改写操作。
MaxCompute默认不支持使用非确定性函数（例如UDF、UDAF等）创建物化视图。当您的业务场景必须要使用非确定性函数时，请在Session级别设置属性set odps.sql.materialized.view.support.nondeterministic.function=true;。

命令格式

set odps.sql.materialized.view.initial.partition={"分区列": "分区值"};
--可选项，指定初始分区，避免生成过多无用分区。
create materialized view [if not exists] [<project_name>.]<mv_name> 
[lifecycle <days>]
[(<col_name> [comment <col_comment>], ...)]
[{disable rewrite] 
[partitioned on (<col_name> [, <col_name>, ...]) 
[clustered by|range clustered by (<col_name> [, <col_name>, ...])
     [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])]  
           into <number_of_buckets> buckets]   
[tblproperties("compressionstrategy"="normal/high/extreme","enable_auto_substitute"="true")]
as <select_statement>;

参数说明

if not exists：可选。如果没有指定if not exists且物化视图已经存在会返回报错。
project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
mv_name：必填。新建物化视图的名称。
days：可选。指定物化视图的生命周期，单位为天。
col_name：可选。指定物化视图的列名称。
col_comment：可选。指定物化视图的列的注释。
disable rewrite：可选。设置禁止通过物化视图执行查询改写操作。不指定时表示允许通过物化视图执行查询改写操作，您可以执行alter materialized view [project_name.]<mv_name> disable rewrite;命令禁止通过物化视图执行查询改写操作。同理您可以执行alter materialized view [project_name.]<mv_name> enable rewrite;命令允许通过物化视图执行查询改写操作。
partitioned on：可选。指定物化视图分区字段，表示创建的物化视图表为分区表。
clustered by|range clustered by：可选。用于创建聚簇表时设置表的Shuffle属性。
sorted by：可选。用于创建聚簇表时设置表的Sort属性。
number_of_buckets：可选。用于创建聚簇表时设置表分桶数。
tblproperties：可选。compressionstrategy指定表数据的存储压缩策略，可以选normal、high或extreme。enable_auto_substitute指定当分区不存在时是否自动穿透到原始分区表去查询数据，详细信息请参见物化视图支持查询穿透。
select_statement：必填。查询语句，详细格式请参见SELECT语法。

使用示例

示例一：创建以ds为分区列的物化视图。命令示例如下。

create materialized view mv lifecycle 7
(
  key comment 'unique id',
  ds comment 'date'
) 
partitioned on (ds) 
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

示例二：创建带有聚簇属性的非分区物化视图。命令示例如下。

create materialized view mv lifecycle 7 
clustered by id sorted by value into 1024 buckets  
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

示例三：创建带有聚簇属性的分区物化视图。命令示例如下。

create materialized view mv lifecycle 7 
partitioned on (ds) 
clustered by id sorted by value into 1024 buckets 
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

示例四：假设有一张页面访问表visit_records，记录了各个用户访问的页面ID、用户ID、访问时间。用户经常要对不同页面的访问量进行查询分析。visit_records的结构如下。

+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| page_id         | string     |       |                                             |
| user_id         | string     |       |                                             |
| visit_time      | string     |       |                                             |
+------------------------------------------------------------------------------------+

此时，可以给visit_records表创建一个以页面ID分组，统计各个页面访问次数的物化视图，并基于物化视图执行后续查询操作：

执行如下语句创建物化视图。

create materialized view count_mv as select page_id, count(*) from visit_records group by page_id;

执行查询语句如下。
```
set odps.sql.materialized.view.enable.auto.rewriting=true; 
select page_id, count(*) from visit_records group by page_id;
```
执行该查询语句时，MaxCompute能自动匹配到物化视图count_mv，从count_mv中读取聚合好的数据。

执行如下命令检验查询语句是否匹配到物化视图。

explain select page_id, count(*) from visit_records group by page_id;

返回结果如下。

job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    Data source: doc_test_dev.count_mv
    TS: doc_test_dev.count_mv
        FS: output: Screen
            schema:
              page_id (string)
              _c1 (bigint)


OK

从返回结果中的Data source可查看到当前查询读取的表是count_mv，说明物化视图有效，查询改写成功。

基于物化视图执行查询改写操作

物化视图最重要的作用就是对查询语句进行查询改写，如果期望查询语句能利用物化视图进行查询改写，则需要在查询语句前添加set odps.sql.materialized.view.enable.auto.rewriting=true;配置。当物化视图处于失效状态时不支持查询改写，查询语句会直接查询源表而无法获得加速作用。

说明

默认每个MaxCompute项目只能利用自身的物化视图进行查询改写，如果需要利用其他项目中的物化视图进行改写，您需要在查询语句前添加set odps.sql.materialized.view.source.project.white.list=<project_name1>,<project_name2>,<project_name3>;配置指定其他MaxCompute项目列表。

MaxCompute中物化视图的查询改写支持的算子类型及与其他产品的对照关系如下。

算子类型	分类	MaxCompute	BigQuery	Amazon RedShift	Hive
FILTER	表达式完全匹配	支持	支持	支持	支持
FILTER	表达式部分匹配	支持	支持	支持	支持
AGGREGATE	单个AGGREGATE	支持	支持	支持	支持
AGGREGATE	多个AGGREGATE	不支持	不支持	不支持	不支持
JOIN	JOIN类型	INNER JOIN	不支持	INNER JOIN	INNER JOIN
	单个JOIN	支持	不支持	支持	支持
	多个JOIN	支持	不支持	支持	支持
AGGREGATE+JOIN	-	支持	不支持	支持	支持

使用物化视图查询改写的原则是查询语句中需要的数据必须从物化视图中得到，包括输出列、筛选条件中需要的列、聚合函数需要的列、JOIN条件需要的列。如果查询语句中需要的列不包含在物化视图中或聚合函数不支持，则无法基于物化视图进行查询改写。具体示例如下：

改写带过滤条件的查询语句。假设创建的物化视图如下。

create materialized view mv as select a,b,c from src where a>5;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select a,b from src where a>5;`	`select a,b from mv;`
`select a, b from src where a=10;`	`select a,b from mv where a=10;`
`select a, b from src where a=10 and b=3;`	`select a,b from mv where a=10 and b=3;`
`select a, b from src where a>3;`	`(select a,b from src where a>3 and a<=5) union (select a,b from mv);`
`select a, b from src where a=10 and d=4;`	改写不成功，因为mv中没有d列。
`select d, e from src where a=10;`	改写不成功，因为mv中没有d、e列。
`select a, b from src where a=1;`	改写不成功，因为mv中没有a=1的数据。

改写带聚合函数的查询语句

如果物化视图的SQL语句和查询语句的聚合Key相同，那么所有聚合函数都可以改写，如果聚合Key不相同，只支持SUM、MIN和MAX。

假设创建的物化视图如下。

create materialized view mv as 
select a, b, sum(c) as sum, count(d) as cnt from src group by a, b;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select a, sum(c) from src group by a;`	`select a, sum(sum) from mv group by a;`
`select a, count(d) from src group by a, b;`	`select a, cnt from mv;`
`select a, count(b) from (select a, b from src group by a, b) group by a;`	`select a,count(b) from mv group by a;`
`select a, count(b) from src group by a;`	改写不成功，视图对a、b列进行过聚合，不能再对b进行聚合。
`select a, count(c) from src group by a;`	改写不成功，对于COUNT函数不支持重新聚合。

如果聚合函数中有distinct，当物化视图语句和查询语句聚合Key相同，可以改写，否则不可以改写。

假设创建的物化视图如下。

create materialized view mv as 
select a, b, sum(distinct c) as sum, count(distinct d) as cnt from src group by a, b;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select a, count(distinct d) from src group by a, b;`	`select a, cnt from mv;`
`select a, count(c) from src group by a, b;`	改写不成功，对于COUNT函数不支持重新聚合。
`select a, count(distinct c) from src group by a;`	改写不成功，因为需要对a再进行聚合。

改写带join的查询语句

改写join输入

假设创建的物化视图如下。

create materialized view mv1 as select a, b from j1 where b > 10;
create materialized view mv2 as select a, b from j2 where b > 10;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select j1.a,j1.b,j2.a from (select a,b from j1 where b > 10) j1 join j2 on j1.a=j2.a;`	`select mv1.a, mv1.b, j2.a from mv1 join j2 on mv1.a=j2.a;`
`select j1.a,j1.b,j2.a from(select a,b from j1 where b > 10) j1join(select a,b from j2 where b > 10) j2on j1.a=j2.a;`	`select mv1.a,mv1.b,mv2.a from mv1 join mv2 on mv1.a=mv2.a;`

join带过滤条件

假设创建的物化视图如下。

--创建非分区物化视图。
create materialized view mv1 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a;
create materialized view mv2 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a where j1.a > 10;
--创建分区物化视图。
create materialized view mv lifecycle 7 partitioned on (ds) as select t1.id, t1.ds as ds from t1 join t2 on t1.id = t2.id;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a=4;`	`select a, b from mv1 where a=4;`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 20;`	`select a,b from mv2 where a>20;`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 5;`	`(select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 5 and j1.a <= 10) union select * from mv2;`
`select key from t1 join t2 on t1.id= t2.id where t1.ds='20210306';`	`select key from mv where ds='20210306';`
`select key from t1 join t2 on t1.id= t2.id where t1.ds>='20210306';`	`select key from mv where ds>='20210306';`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j2.a=4;`	改写不成功，因为物化视图没有j2.a列。

join增加表

假设创建的物化视图如下。

create materialized view mv as select j1.a, j1.b from j1 join j2 on j1.a=j2.a;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select j1.a, j1.b from j1 join j2 join j3 on j1.a=j2.a and j1.a=j3.a;`	`select mv.a, mv.b from mv join j3 on mv.a=j3.a;`
`select j1.a, j1.b from j1 join j2 join j3 on j1.a=j2.a and j2.a=j3.a;`	`select mv.a,mv.b from mv join j3 on mv.a=j3.a;`

说明

以上三种语句可以相互结合，如果查询语句符合改写条件，则可以改写。
MaxCompute会选择最佳的改写规则运行，如果改写后增加了一些操作，不是最优运行计划，最终也不会被选中。

改写带left join的查询语句

假设创建的物化视图如下。

create materialized view mv lifecycle 7(
        user_id,
        job,
        total_amount
) as select t1.user_id, t1.job, sum(t2.order_amout) as total amount 
      from user_info as t1 left join sale_order as t2 on t1.user_id=t2.user_id group by t1.user_id;

基于创建的物化视图执行查询语句，查询改写对照如下。

原始查询语句	改写后的查询语句
`select t1.user_id, sum(t2.order_amout) as total amount from user_info as t1 left join sale_order as t2 on t1.user_id=t2.user_id group by t1.user_id;`	`select user_id, total_amount from mv;`

改写带union all的查询语句
假设创建的物化视图如下。
```
create materialized view mv lifecycle 7(
        user_id,
        tran_amount,
        tran_date
) as (select user_id, tran_amount, tran_date from alipay_tran union all
 select user_id, tran_amount, tran_date from unionpay_tran);
```
基于创建的物化视图执行查询语句，查询改写对照如下。
原始查询语句
改写后的查询语句
select user_id, tran_amount from alipay_tran union all select user_id, tran_amount from unionpay_tran;
select user_id, total_amount from mv;

物化视图支持查询穿透

对于分区物化视图，不一定所有分区都有数据，可能只刷新了最新的一些分区数据。但用户查询数据时，实际并不知道查询的所有分区数据都存在，当查询的分区数据不存在时，需要自动实现到原始分区表去查询数据，流程如下图所示。

如果需要物化视图支持穿透查询能力，您需要设置如下参数：

在Session级别设置set odps.optimizer.cbo.rule.filter.black=re;，分区列会被折叠成常量。
创建物化视图时，在tblproperties属性中添加"enable_auto_substitute"="true"配置。

物化视图支持穿透查询示例如下。

创建物化视图支持分区并且支持查询穿透。命令示例如下。

--将分区列折叠为常量，实现分区动态可变。
set odps.optimizer.cbo.rule.filter.black=re;
--初始化20210101分区数据。
set odps.sql.materialized.view.initial.partition={"dt": "20210101"};
--创建分区物化视图支持穿透。
create materialized view if not exists mv lifecycle 7 
tblproperties("enable_auto_substitute"="true") 
partitioned on (dt)
as select id, name, dt from src;

查询表src中的分区为20210101的数据。命令示例如下。
```
select * from mv where dt='20210101';
```

查询物化视图mv中的分区为20210102的数据，自动穿透到源表查询数据。命令示例如下。

select * from mv where dt = '20210102';
--因为20210102的数据没有物化，则需要把查询转化到对应的源表，等价于
select * from (select id, name, dt from src where dt='20210102') t;

查询物化视图mv中分区为20201230~20210102的数据，自动穿透到源表查询的数据与物化视图的数据执行UNION操作后再返回结果。

select * from mv where dt >= '20201230' and dt<='20210102' and id='5';
--因为20210102的数据没有物化，则需要把查询转化到对应的源表。等价于：
select * from
(select id, name, dt from src where dt='20211231' or dt='20210102';
 union all 
 select * from mv where dt='20210101'
) t where id = '5';

云原生大数据计算服务 MaxCompute：CREATE MATERIALIZED VIEW

背景信息

计费规则

使用限制

注意事项

命令格式

参数说明

使用示例

基于物化视图执行查询改写操作

物化视图支持查询穿透

相关命令

原始查询语句	改写后的查询语句
`select user_id, tran_amount from alipay_tran union all select user_id, tran_amount from unionpay_tran;`	`select user_id, total_amount from mv;`