物化视图的相关命令,语法格式与使用示例_云原生大数据计算服务 MaxCompute-阿里云帮助中心

物化视图（Materialized View）本质是一种预计算，即把某些耗时的操作（例如JOIN、AGGREGATE）的结果保存下来，以便在查询时直接复用，从而避免这些耗时的操作，最终达到加速查询的目的。本文为您介绍物化视图相关命令、语法格式并提供使用示例。

背景信息

视图是一种虚拟表，任何对视图的查询，都会转换为视图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会自动匹配到最优物化视图，并直接从物化视图中读取数据，从而大大提高查询效率。

物化视图相关操作命令如下。


类型	功能	角色	操作入口
创建物化视图支持分区和聚簇	基于查询语句创建物化视图。	具备项目创建表权限（CreateTable）的用户。	本文中的命令您可以在如下工具平台执行： MaxCompute客户端 MaxCompute控制台（查询编辑器） DataWorks控制台 MaxCompute Studio
更新物化视图	更新已创建的物化视图。	具备修改表权限（Alter）的用户。
修改物化视图的生命周期	修改已创建的物化视图的生命周期。	具备修改表权限（Alter）的用户。
开启或禁用物化视图的生命周期	开启或禁用已创建的物化视图的生命周期。	具备修改表权限（Alter）的用户。
查询物化视图信息	查询物化视图的基本信息。	具备读取表元信息权限（Describe）的用户。
查询物化视图状态	查看物化视图有效或无效。	具备读取表元信息权限（Describe）的用户。
删除物化视图	删除已创建的物化视图。	具备删除表权限（Drop）的用户。
删除物化视图分区	删除已创建的物化视图的分区。	具备删除表权限（Drop）的用户。
物化视图支持查询穿透	当查询的分区数据不存在时，需要自动实现到原始分区表去查询数据。	具备项目写权限（Write）及创建表权限（CreateTable）的用户。
物化视图定时更新	定时更新已创建物化视图的数据。	具备修改表权限（Alter）的用户。

使用限制

物化视图的使用限制如下：

不支持窗口函数。
不支持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;。
- 物化视图支持生成空分区，原始表分区为空的时候，刷新物化视图，自动生成空分区。

命令格式

CREATE MATERIALIZED VIEW [IF NOT EXISTS][project_name.]<mv_name>
[LIFECYCLE <days>]    --指定生命周期
[BUILD DEFERRED]    -- 指定是在创建时只生成表结构，不生成数据
[(<col_name> [COMMENT <col_comment>],...)]    --列注释
[DISABLE REWRITE]    --指定是否用于改写
[COMMENT 'table comment']    --表注释
[PARTITIONED ON/BY (<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]    --用于创建聚簇表时设置表的Shuffle和Sort属性
[TBLPROPERTIES("compressionstrategy"="normal/high/extreme",    --指定表数据存储压缩策略
                "enable_auto_substitute"="true",    --指定当分区不存在时是否转化视图来查询
                "enable_auto_refresh"="true",    --指定是否开启自动刷新
                "refresh_interval_minutes"="120",    --指定刷新时间间隔
                "only_refresh_max_pt"="true"    --针对分区物化视图，只自动刷新源表最新分区
                )]
AS <select_statement>;

参数说明
- if not exists：可选。如果没有指定if not exists且物化视图已经存在会返回报错。
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。新建物化视图的名称。
- days：可选。指定物化视图的生命周期，单位为天。
- BUILD DEFERRED：可选，如果加上这个关键字代表创建物化视图时，只生成表结构，不刷新数据。
- 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指定当分区不存在时是否自动穿透到原始分区表去查询数据，详细信息请参见物化视图支持查询穿透。enable_auto_refresh：可选，当需要自动刷新数据时需要设置为true。refresh_interval_minutes：条件可选，当enable_auto_refresh为true时，需要配置刷新间隔时间，单位为分钟。only_refresh_max_pt：可选，只适用于带分区的物化视图，但设置为true时，只会刷新源表最新分区。
- select_statement：必填。查询语句，详细格式请参见SELECT语法。

使用示例

示例一：创建物化视图。命令示例如下。

--示例table1
create table if not exists mf_t(
     id     bigint,
     value   bigint,
     name   string)
partitioned by (ds STRING);
alter table mf_t add partition(ds='1');
--插入分区数据
insert into mf_t partition (ds='1') values(1,10,'kyle'),(2,20,'xia') ;
--查询数据
select * from mf_t where ds ='1';
+------------+------------+------------+------------+
| id         | value      | name       | ds         |
+------------+------------+------------+------------+
| 1          | 10         | kyle       | 1          |
| 2          | 20         | xia        | 1          |
+------------+------------+------------+------------+

--示例table2
create table if not exists mf_t1(
     id     bigint,
     value   bigint,
     name   string)
partitioned by (ds STRING);
alter table mf_t1 add partition(ds='1');
--插入分区数据
insert into mf_t1 partition (ds='1') values(1,10,'kyle'),(3,20,'john') ;
--查询数据
select * from mf_t1 where ds ='1';
+------------+------------+------------+------------+
| id         | value      | name       | ds         |
+------------+------------+------------+------------+
| 1          | 10         | kyle       | 1          |
| 3          | 20         | john       | 1          |
+------------+------------+------------+------------+


--示例1：创建以ds为分区列的物化视图
create materialized view mf_mv lifecycle 7
(
  key comment 'unique id',
  value comment 'input value',
  ds comment 'partitiion'
  )
partitioned on (ds)
as select t1.id as key, t1.value as value, t1.ds as ds
     from mf_t as t1 join mf_t1 as t2
       on t1.id = t2.id and t1.ds=t2.ds and t1.ds='1';
--查询物化视图
select * from mf_mv where ds =1;
+------------+------------+------------+
| key        | value      | ds         |
+------------+------------+------------+
| 1          | 10         | 1          |
+------------+------------+------------+

--示例2：创建带有聚簇属性的非分区物化视图
create materialized view mf_mv lifecycle 7
clustered by (key) sorted by (value) into 1024 buckets
as  select t1.id as key, t1.value as value, t1.ds as ds
      from mf_t as t1 join mf_t1 as t2
        on t1.id = t2.id and t1.ds=t2.ds and t1.ds='1';

--示例3：创建带有聚簇属性的分区物化视图
create materialized view mf_mv lifecycle 7
partitioned on (ds)
clustered by (key) sorted by (value) into 1024 buckets
as  select t1.id as key, t1.value as value, t1.ds as ds
      from mf_t as t1 join mf_t1 as t2
        on t1.id = t2.id and t1.ds=t2.ds and t1.ds='1';

示例二：原始表分区为空的时候，刷新物化视图，自动生成空分区。

create table mf_blank_pts(id bigint ,name string) partitioned by (ds bigint);
alter table mf_blank_pts add partition (ds = 1);
alter table mf_blank_pts add partition (ds = 2);
insert into table mf_blank_pts partition(ds=1) values(1,"aba"),(2,"cbd");

set odps.sql.materialized.view.initial.partition={"ds": 1};
create materialized view if not exists mf_mv_blank_pts partitioned on (ds)
as select id,name,ds from mf_blank_pts;

alter materialized view mf_mv_blank_pts rebuild partition (ds>0);

show partitions mf_mv_blank_pts;
--原表分区ds=2是没有值，当刷新ds>0分区时，物化视图里有了空分区
ds=1
ds=2
select * from mf_mv_blank_pts where ds>0;
--返回只有分区1有值
+------------+------------+------------+
| id         | name       | ds         |
+------------+------------+------------+
| 1          | aba        | 1          |
| 2          | cbd        | 1          |
+------------+------------+------------+

更新物化视图

当物化视图的数据对应的表或分区产生插入、覆写、更新、删除等操作时，物化视图会自动失效，无法用于查询改写。您可以先查看物化视图状态，当物化视图失效时，执行更新操作。查看物化视图状态操作，请参见查询物化视图状态。

注意事项
- 物化视图的更新操作只会更新源表有数据变化的表或者分区。
- 您可以借助于DataWorks调度能力实现定时更新。DataWorks的调度操作信息，请参见调度配置。

命令格式

alter materialized view [<project_name>.]<mv_name>
      rebuild [partition(<ds>=max_pt(<table_name>),<expression1>...)];

参数说明
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待更新物化视图的名称。
- ds：可选，物化视图分区字段名称。
- max_pt：可选，取指定表或者物化视图（table_name）的最大分区值。
- expression：可选。当更新分区物化视图时，需要指定待更新分区信息，支持表达式。

使用示例

示例一：更新非分区物化视图。命令示例如下。
```
alter materialized view count_mv rebuild;
```
示例二：更新分区物化视图的某个分区。命令示例如下。
```
alter materialized view mv rebuild partition (ds='20210101');
```
示例三：更新分区物化视图的满足指定条件的分区。命令示例如下。
```
alter materialized view mv rebuild partition(ds>='20210101', ds<='20210105');
```

示例四：更新分区物化视图的最新分区数据。命令示例如下。

--查看分区数据
select * from sale_detail_jt where sale_date='2013' and region='china';
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1        | c1          | 100.1       | 2013      | china  |
| s2        | c2          | 100.2       | 2013      | china  |
| s5        | c2          | 100.2       | 2013      | china  |
+-----------+-------------+-------------+-----------+--------+
--查看分区数据
select * from sale_detail_jt where sale_date='2013' and region='en';
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| t1        | c5          | 200.0       | 2013      | en     |
| t2        | c6          | 300.0       | 2013      | en     |
+-----------+-------------+-------------+-----------+--------+

--创建物化视图
CREATE MATERIALIZED VIEW mv_deferred
BUILD DEFERRED
AS select * from sale_detail_jt;

--查询物化视图 mv_deferred;
select * from mv_deferred;
--返回
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
+-----------+-------------+-------------+-----------+--------+

--创建分区表
create table mf_part (id bigint,name string) partitioned by (dt string);
--插入数据
insert into mf_part partition(dt='2013') values(1,'name1'),(2,'name2');
--查询数据
select * from mf_part where dt='2013';
--返回：
+------------+------+----+
| id         | name | dt |
+------------+------+----+
| 1          | name1 | 2013 |
| 2          | name2 | 2013 |
+------------+------+----+
--创建带分区的物化视图
create materialized view mv_rebuild
BUILD DEFERRED
partitioned on (dt) as
select * from mf_part;

--查询物化视图数据
select * from mv_rebuild where dt='2013';
--返回
+------------+------+----+
| id         | name | dt |
+------------+------+----+
+------------+------+----+
--刷新最新分区数据
alter materialized view mv_rebuild
      rebuild partition(dt=max_pt('mf_part'));

--查询物化视图数据
select * from mv_rebuild where dt='2013';
--返回
+------------+------+----+
| id         | name | dt |
+------------+------+----+
| 1          | name1 | 2013 |
| 2          | name2 | 2013 |
+------------+------+----+

修改物化视图的生命周期

修改已创建的物化视图的生命周期。

命令格式

alter materialized view [<project_name>.]<mv_name> set lifecycle <days>;

参数说明
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待更新物化视图的名称。
- days：必填。设置物化视图的新生命周期。单位为天。

使用示例

--修改物化视图的生命周期为10天。
alter materialized view mv set lifecycle 10;

开启或禁用物化视图的生命周期

开启或禁用已创建的物化视图的生命周期。

命令格式

alter materialized view [<project_name>.]<mv_name> [<pt_spec>] enable|disable lifecycle;

参数说明
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待开启或禁用生命周期的物化视图的名称。
- pt_spec：可选。待开启或禁用生命周期的物化视图的分区。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。
- enable|disable：必填。enable代表开启，disable代表禁用，禁用后该分区或表就不涉及生命周期管理。
使用示例
- 示例一：开启物化视图的生命周期管理。命令示例如下。
```
alter materialized view mv partition (ds='20210101') enable lifecycle;
```
- 示例二：禁用物化视图的生命周期管理。命令示例如下。
```
alter materialized view mv partition (ds='20210101') disable lifecycle;
```

查询物化视图信息

查看物化视图的结构、修改时间等信息。

命令格式

desc extended [<project_name>.]<mv_name>;

参数说明
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待查询物化视图的名称。

使用示例

desc extended mv;

返回结果如下：

说明如下结果示例需要MaxCompute客户端升级至v0.43及以上版本，详情请参见版本更新记录。

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$****@***.aliyunid.com                  |
| Project:                  ***                                                 |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2023-03-20 14:09:37                                      |
| LastDDLTime:              2023-03-20 14:09:37                                      |
| LastModifiedTime:         2023-03-20 14:09:37                                      |
| Lifecycle:                7                                                        |
+------------------------------------------------------------------------------------+
| MaterializedView: YES                                                              |
| ViewText: select t1.id as key, t1.value as value, t1.ds as ds      from mf_t as t1 join mf_t1 as t2        on t1.id = t2.id and t1.ds=t2.ds and t1.ds='1' |
| Rewrite Enabled: true                                                              |
| AutoRefresh Enabled: false                                                         |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| key      | bigint |       |               | true     | NULL         | unique id    |
| value    | bigint |       |               | true     | NULL         | input value  |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| ds              | string     | partitiion                                          |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| IsOutdated:               false                                                    |
| TableID:                  d3bf18c757064b3587d5602317bd652e                         |
| IsArchived:               false                                                    |
| PhysicalSize:             1212                                                     |
| FileNum:                  1                                                        |
| StoredAs:                 CFile                                                    |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                        |
| encryption_enable:        false                                                    |
+------------------------------------------------------------------------------------+

查询物化视图状态

查询物化视图状态，以便及时知晓源表变更，确保物化视图有效。物化视图状态分为如下两种：

物化视图有效
执行查询语句时，MaxCompute会从物化视图中直接查询数据，不会从源数据中查询数据。
物化视图无效
执行查询语句时，MaxCompute无法从物化视图中直接查询数据，会从源数据中查询数据，无法实现查询加速。

用户可以通过下面的函数查看物化视图的数据是否有效。

函数声明。

Boolean materialized_view_is_valid(<mv_name>,<partition_value>);

使用示例。
检查mf_mv_refresh4的数据是否与原表最新的数据一致，如果一致返回true，否则返回false。
- ```
select materialized_view_is_valid("mf_mv_refresh4");
```
- ```
select materialized_view_is_valid("mf_mv_refresh_pts","20220810");
```

删除物化视图

删除已创建的物化视图。

命令格式

drop materialized view [if exists] [<project_name>.]<mv_name>;

参数说明
- if exists：可选。如果没有指定if exists且物化视图不存在会返回报错。
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待删除物化视图的名称。
使用示例
```
drop materialized view mv;
```

删除物化视图分区

删除已创建的物化视图的单个或多个分区。

命令格式

alter materialized view [<project_name>.]<mv_name> drop [if exists] partition <pt_spec> [partition <pt_spec>, partition <pt_spec>....];

参数说明
- project_name：可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台，左上角切换地域后，即可在项目管理页签查看到具体的MaxCompute项目名称。
- mv_name：必填。待删除分区的分区物化视图的名称。
- if exists：可选。如果没有指定if exists且物化视图不存在会返回报错。
- pt_spec：至少要指定一个分区。待删除的分区。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。
使用示例
- 示例一：删除分区物化视图的某个分区。命令示例如下。
```
alter materialized view mv drop partition (ds='20210101');
```
- 示例二：删除分区物化视图的满足指定条件的分区。命令示例如下。
```
alter materialized view mv drop partition (ds>='20210101', ds<='20210105');
```

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

物化视图最重要的作用就是对查询语句进行查询改写，如果期望查询语句能利用物化视图进行查询改写，则需要在查询语句前添加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项目列表。如果物化视图中带有left/right join和union all关键字，需要添加：set odps.sql.materialized.view.enable.substitute.rewriting=true;Flag。

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

原始查询语句	改写后的查询语句
`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';

物化视图定时更新

使用说明。
- 物化视图定时更新本质上是系统后台提交了一个任务，完成时间和当前集群资源、任务大小有关。
- 当前定时更新是全量刷新模式，即对于分区物化视图，开启定时更新后，是对整个物化视图进行更新；对于非分区物化视图，会自动检查本次更新是否有必要，即假如涉及的源表没有发生变化，则会跳过此次更新，分区物化视图暂时还不具备这个能力。
- 如果某次刷新所用的时间超过自动刷新指定的间隔，会自动跳过超过的时间点，等到下次最近的时间点再刷新。假设定时刷新设置的每20分钟刷新一次，那么预计第一次刷新是10:00:00，第二次刷新是10:20:00，第三次刷新是10:40:00。如果10:20:00时候开始了某次刷新，一直到10:30:00刷新才结束，那么设置的10:20:00刷新会被跳过，直到10:40:00再开始刷新。
- 当前MaxCompute无法实时感知到设置了定时更新的物化视图，有一定延时（30分钟以内）。

打开物化视图定时更新功能。

MaxCompute提供了三种设置物化视图定时刷新数据的方式。目前MaxCompute暂不支持自动刷新带分区的物化视图。

方式一：通过every 参数设置。

创建物化视图时指定更新频率，每N分钟/小时/天更新一次。下一次更新时间点为上次更新数据时间点加N分钟/小时/天。

语法命令。

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]--用于创建聚簇表时设置表的Shuffle和Sort属性

[REFRESH EVERY xx MINUTES/HOURS/DAYS] -- 设置物化视图定时更新间隔
TBLPROPERTIES("enable_auto_refresh"="true") -- 开启自动更新

AS <select_statement>;

使用示例。

创建一个每10分钟更新一次的物化视图。

--create table
create table mf_refresh (id bigint, name string);
--insert values
insert into table mf_refresh values(1,'kyle'),(2,'tom');
--create mv
create materialized view if not exists mf_mv_refresh
refresh every 10 minutes
tblproperties("enable_auto_refresh"="true")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
+------------+------------+

--after created mv,insert value to table
insert into table mf_refresh values(3,'jen'),(4,'cathy');

--after 10m, select the data from mv
select * from mf_mv_refresh;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+

方式二：通过tblproperties属性的refresh_interval_minutes参数设置。

创建物化视图时指定更新频率，每N分钟更新一次。下一次更新时间点为上次更新数据时间点加N分钟。

语法命令。

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]--用于创建聚簇表时设置表的Shuffle和Sort属性
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx")
-- 开启定时更新
AS <select_statement>;

使用示例。

创建一个每20分钟更新一次的物化视图。

--create mv with fresh data by20 min
create materialized view if not exists mf_mv_refresh2
tblproperties("enable_auto_refresh"="true"，"refresh_interval_minutes"="20")
as select id,name from mf_refresh;

--select data from mv
select * from mf_mv_refresh2;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+
--after mv,insert value to table
insert into table mf_refresh values(5,'roger'),(6,'david');

--after 30m，select data from mv
--返回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

方式三：通过tblproperties属性的refresh_cron参数设置。

根据cron定时器，可以配置定时间隔更新或者定点更新，间隔定点更新等。

语法命令。

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]--用于创建聚簇表时设置表的Shuffle和Sort属性

TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx")
-- 开启定时更新
AS <select_statement>;

其中refresh_cron对应的参数是QUARTZ Cron格式的字符串，使用详情请参见Cron expression examples。

使用示例。

创建一个每5分钟更新一次的物化视图。

--cron base create mv
create materialized view if not exists mf_mv_refresh3
tblproperties("enable_auto_refresh"="true"，"refresh_cron"="* */5 * * * ? *")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh3;
--返回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+
--insert value to table
insert into table mf_refresh values(7,'ethan'),(8,'cal');
--after 5min,select data from mv
select * from mf_mv_refresh3;
--返回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

创建一个每天晚上8点更新一次的物化视图。

--cron base create mv
create materialized view if not exists mf_mv_refresh4
tblproperties("enable_auto_refresh"="true"，"refresh_cron"="0 0 20 * * ? *")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh4;
--返回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+
--insert value to table
insert into table mf_refresh values(9,'annie'),(10,'zoe');
--when 20:00,select data from mv
select * from mf_mv_refresh4;
--返回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 9          | annie      |
| 10         | zoe        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

更新物化视图刷新时间。

可以使用如下命令对已存在的物化视图设置定时更新。

ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx");
ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx");

关闭定时刷新功能。
可以使用如下命令关闭物化视图定时更新功能。
```
ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="false");
```

使用示例

场景：假设有一张页面访问表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，说明物化视图有效，查询改写成功。

计费规则

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

存储费用
物化视图会占用物理存储空间，会产生存储费用。
- 当MaxCompute项目的规格类型为包年包月标准版或按量计费标准版时，存储费用按量计费。更多计费信息，请参见存储费用。
- 当MaxCompute项目的规格类型为包年包月套餐版时，超出存储限制部分按量计费。更多计费信息，请参见套餐计费。
计算费用
创建、更新、查询物化视图及查询改写（物化视图有效）过程中涉及到查询数据，会消耗计算资源产生计算费用。
- 当MaxCompute项目的规格类型为包年包月标准版或包年包月套餐版时，不单独收费。
- 当MaxCompute项目的规格类型为按量计费标准版时，按照SQL复杂度及输入数据量计算费用。更多计费信息，请参见SQL作业按量计费。您需要注意如下信息：
  - 更新物化视图执行的SQL与创建物化视图执行的SQL相同，如果该物化视图所在项目绑定的是预付费（包年包月）计算资源组，那么会使用已经购买的预付费资源，不会有额外费用；如果绑定的是后付费资源组，费用取决于执行SQL时输入的数据量和复杂度。同时刷新物化视图后会按照实际存储大小收取存储费用。
  - 当物化视图处于生效状态时查询改写会从物化视图中读取数据，查询语句的输入数据量（从物化视图读取部分）与物化视图相关，与物化视图源表无关。当物化视图处于失效状态时不支持查询改写，查询语句会直接查询源表，查询语句的输入数据量与源表相关。更多查询物化视图状态信息，请参见查询物化视图状态。
  - 由于多表关联生成物化视图会产生数据膨胀等原因，从物化视图读取的数据量不一定绝对小于源表，MaxCompute不能保证读取物化视图一定比读取源表节省费用。