云原生大数据计算服务 MaxCompute：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';

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

计费规则

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

• 存储费用

  物化视图会占用物理存储空间，会产生存储费用，按量计费。更多计费信息，请参见存储费用（按量计费）。

• 计算费用

  创建、更新、查询物化视图及查询改写（物化视图有效）过程中涉及到查询数据，会消耗计算资源产生计算费用。

  • 当MaxCompute项目的规格类型为包年包月时，不单独收费。

  • 当MaxCompute项目的规格类型为按量计费时，按照SQL复杂度及输入数据量计算费用。更多计费信息，请参见SQL标准计费。您需要注意如下信息：

    • 更新物化视图执行的SQL与创建物化视图执行的SQL相同，如果该物化视图所在项目绑定的是预付费（包年包月）计算资源组，那么会使用已经购买的预付费资源，不会有额外费用；如果绑定的是后付费资源组，费用取决于执行SQL时输入的数据量和复杂度。同时刷新物化视图后会按照实际存储大小收取存储费用。

    • 当物化视图处于生效状态时查询改写会从物化视图中读取数据，查询语句的输入数据量（从物化视图读取部分）与物化视图相关，与物化视图源表无关。当物化视图处于失效状态时不支持查询改写，查询语句会直接查询源表，查询语句的输入数据量与源表相关。更多查询物化视图状态信息，请参见查询物化视图状态。

    • 由于多表关联生成物化视图会产生数据膨胀等原因，从物化视图读取的数据量不一定绝对小于源表，MaxCompute不能保证读取物化视图一定比读取源表节省费用。

使用限制

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

• 不支持窗口函数。

• 不支持UDTF函数。

• 默认不支持非确定性函数（例如UDF、UDAF等）。当您的业务场景必须要使用非确定性函数时，请在Session级别设置属性set odps.sql.materialized.view.support.nondeterministic.function=true;。

注意事项

• 当查询语句执行失败时，物化视图也会创建失败。

• 物化视图分区列必须来源于某张源表，其顺序和列数目必须和源表一样，列名称可以不一样。

• 列注释需要指定所有列，包含分区列。如果只指定部分列，会报错。

• 可以同时指定分区和聚簇，此时每个分区中的数据都有指定的聚簇属性。

• 当查询语句中包含不支持的算子时会报错。物化视图支持的算子列表，请参见基于物化视图执行查询改写操作。

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

参数说明

使用示例

创建物化视图

1. 创建mf_t和mf_t1两张表并插入数据。

   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          |
   +------------+------------+------------+------------+
   
   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          |
   +------------+------------+------------+------------+

2. 创建物化视图。

   • 示例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_mv2 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_mv3 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';

改写物化视图

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

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

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

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

物化视图查询穿透

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

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

1. 创建物化视图支持分区并且支持查询穿透。

   -- 创建src表。
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- 插入数据。
   INSERT INTO src PARTITION(dt='20210101') VALUES(1,'Alex');
   INSERT INTO src PARTITION(dt='20210102') VALUES(2,'Flink');
   
   --将分区列折叠为常量，实现分区动态可变。
   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 
   PARTITIONED ON (dt) 
   tblproperties("enable_auto_substitute"="true") 
   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：删除已创建的物化视图。