物化视图(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)的用户。 本文中的命令您可以在如下工具平台执行:
查询物化视图信息 查询物化视图的基本信息。 具备读取表元信息权限(Describe)的用户。
查询物化视图状态 查看物化视图有效或无效。 具备读取表元信息权限(Describe)的用户。
更新物化视图 更新已创建的物化视图。 具备修改表权限(Alter)的用户。
删除物化视图 删除已创建的物化视图。 具备删除表权限(Drop)的用户。

使用限制

物化视图的使用限制如下:
  • 不支持窗口函数。
  • 不支持LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN操作。
  • 不支持UDTF函数。
  • 默认不支持非确定性函数(例如UDF、UDAF等)。当您的业务场景必须要使用非确定性函数时,请在Session级别设置属性set odps.sql.materialized.view.support.nondeterministic.function=true;
  • 不支持UNION操作。

创建物化视图

基于满足物化视图场景的数据创建物化视图。

  • 使用限制
    • 物化视图的名称不允许和当前已有表、视图、物化视图名称重复。您可以通过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> [{disable|enable rewrite] as <select_statement>;
  • 参数说明
    • if not exists:可选。如果没有指定if not exists且物化视图已经存在会返回报错。
    • project_name:可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台,左上角切换地域后,即可在项目管理页签查看到具体的MaxCompute项目名称。
    • mv_name:必填。新建物化视图的名称。
    • disable|enable rewrite:可选。设置禁止或允许通过物化视图执行查询改写操作。不指定时表示允许通过物化视图执行查询改写操作,您可以执行alter materialized view [project_name.]<mv_name> disable rewrite;命令禁止通过物化视图执行查询改写操作。同理您可以执行alter materialized view [project_name.]<mv_name> enable rewrite;命令允许通过物化视图执行查询改写操作。
    • select_statement:必填。查询语句,详细格式请参见SELECT语法
  • 使用示例
    create materialized view mv as select page_id, count(*) from src;

查询物化视图信息

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

  • 命令格式
    desc extended [<project_name>.]<mv_name>;
  • 参数说明
    • project_name:可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台,左上角切换地域后,即可在项目管理页签查看到具体的MaxCompute项目名称。
    • mv_name:必填。待查询物化视图的名称。
  • 使用示例
    desc extended mv;
    返回结果如下:
    +------------------------------------------------------------------------------------+
    | Owner: ALIYUN$****@aliyunid.com | Project: ****                                    |
    | TableComment:                                                                      |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2021-08-01 17:50:15                                      |
    | LastDDLTime:              2021-08-01 17:50:15                                      |
    | LastModifiedTime:         2021-08-01 17:50:15                                      |
    +------------------------------------------------------------------------------------+
    | InternalTable: YES      | Size: 0                                                  |
    +------------------------------------------------------------------------------------+
    | Native Columns:                                                                    |
    +------------------------------------------------------------------------------------+
    | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
    +------------------------------------------------------------------------------------+
    | page_id  | string |       |               | true     | NULL         |              |
    | _c1      | bigint |       |               | true     | NULL         |              |
    +------------------------------------------------------------------------------------+
    | Extended Info:                                                                     |
    +------------------------------------------------------------------------------------+
    | TableID:                  e4a7f1169588400ab39bc30764261908                         |
    | IsArchived:               false                                                    |
    | PhysicalSize:             0                                                        |
    | FileNum:                  0                                                        |
    +------------------------------------------------------------------------------------+

查询物化视图状态

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

    执行查询语句时,MaxCompute会从物化视图中直接查询数据,不会从源数据中查询数据。

  • 物化视图无效

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

MaxCompute暂不支持通过命令方式查询物化视图状态,您可以通过如下方式查看查询语句中是否使用了物化视图来判断物化视图是否有效:
  • 方式一:通过EXPLAIN命令查看。

    如下图所示,在Data source中出现物化视图证明查询使用了物化视图。

    物化视图
  • 方式二:通过Logview的作业执行图或Json Summary页签查看。

    如果您可以在作业执行图或Json Summary页签搜索到物化视图名称,表明物化视图有效;否则物化视图失效。

更新物化视图

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

  • 注意事项
    • 物化视图的更新操作只支持全量更新,不支持增量更新。
    • 您可以借助于DataWorks调度能力实现定时更新。DataWorks的调度操作信息,请参见调度配置
  • 命令格式
    alter materialized view [<project_name>.]<mv_name> rebuild;
  • 参数说明
    • project_name:可选。物化视图所属目标MaxCompute项目名称。不填写时表示当前所在MaxCompute项目。您可以登录MaxCompute控制台,左上角切换地域后,即可在项目管理页签查看到具体的MaxCompute项目名称。
    • mv_name:必填。待更新物化视图的名称。
  • 使用示例
    alter materialized view count_mv rebuild;

删除物化视图

删除已创建的物化视图。

  • 命令格式
    drop materialized view [if exists] [<project_name>.]<mv_name>;
  • 参数说明
    • if exists:可选。如果没有指定if exists且物化视图不存在会返回报错。
    • project_name:可选。物化视图所属目标MaxCompute项目名称。您可以登录MaxCompute控制台,左上角切换地域后,即可在项目管理页签查看到具体的MaxCompute项目名称。
    • mv_name:必填。待删除物化视图的名称。
  • 使用示例
    drop materialized view 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_name2>;配置指定其他MaxCompute项目列表。

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

算子类型 分类 MaxCompute BigQuery Amazon RedShift Hive
FILTER 表达式完全匹配 支持 支持 支持 支持
表达式部分匹配 支持 支持 支持 支持
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不相同,只支持SUMMINMAX
      假设创建的物化视图如下。
      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 materialzied view mv1 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a;
      create materialzied view mv2 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a where j1.a > 10;
      基于创建的物化视图执行查询语句,查询改写对照如下。
      原始查询语句 改写后的查询语句
      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 j1.a,j1.b from j1 join j2 on j1.a=j2.a where j2.a=4; 改写不成功,因为物化视图没有j2.a列。
    • JOIN增加表
      假设创建的物化视图如下。
      create materialzied 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会选择最佳的改写规则运行,如果改写后增加了一些操作,不是最优运行计划,最终也不会被选中。

使用示例

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

计费规则

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