CREATE MATERIALIZED VIEW

背景信息

视图是一种虚拟表，任何对视图的查询，都会转换为视图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';

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

计费规则

使用限制

• 物化视图的使用限制如下。

  • 不支持窗口函数。

  • 不支持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分组，统计各个页面访问次数的物化视图，并基于物化视图执行后续查询操作：

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

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

  2. 执行查询语句如下。

     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中读取聚合好的数据。

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

     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中物化视图的查询改写支持的算子类型及与其他产品的对照关系如下。

使用物化视图查询改写的原则是查询语句中需要的数据必须从物化视图中得到，包括输出列、筛选条件中需要的列、聚合函数需要的列、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;

物化视图支持查询穿透

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

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

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

   --将分区列折叠为常量，实现分区动态可变。
   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;

2. 查询表src中的分区为20210101的数据。命令示例如下。

   select * from mv where dt='20210101';

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

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

4. 查询物化视图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';

相关命令

• ALTER MATERIALIZED VIEW：更新物化视图、修改物化视图的生命周期、开启或禁用物化视图的生命周期和删除物化视图分区。

• DESC TABLE/VIEW：查看MaxCompute物化视图的信息。

• SELECT MATERIALIZED VIEW：查询物化视图状态。

• DROP MATERIALIZED VIEW：删除已创建的物化视图。