AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL SQL参考 DDL语句 物化视图 物化视图查询改写

物化视图查询改写

更新时间：
产品详情
我的收藏

MaxCompute支持将包含过滤条件或部分算子类型的原始SQL查询语句改写为物化视图进行查询，以便于您获取所需数据。本文将详细介绍相关SQL改写的具体操作。

使用限制

  • 如果期望查询语句能利用物化视图进行查询改写，则需要在查询语句前添加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;配置。

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

改写示例

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

算子类型

分类

MaxCompute

BigQuery

Amazon RedShift

Hive

FILTER

表达式完全匹配

支持

支持

支持

支持

表达式部分匹配

支持

支持

支持

支持

AGGREGATE

单个AGGREGATE

支持

支持

支持

支持

多个AGGREGATE

不支持

不支持

不支持

不支持

JOIN

JOIN类型

INNER JOIN

不支持

INNER JOIN

INNER JOIN

单个JOIN

支持

不支持

支持

支持

多个JOIN

支持

不支持

支持

支持

AGGREGATE+JOIN

-

支持

不支持

支持

支持

改写带过滤条件的查询语句

  1. 创建物化视图示例如下。

    CREATE MATERIALIZED VIEW mv AS SELECT a,b,c FROM src WHERE a>5;

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

    原始查询语句

    改写后的查询语句

    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。

    1. 创建的物化视图如下。

      CREATE MATERIALIZED VIEW mv AS 
SELECT a, b, sum(c) AS sum, count(d) AS cnt FROM src GROUP BY a, b;

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

      原始查询语句

      改写后的查询语句

      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 mv GROUP BY a;

      改写不成功，视图对a、b列进行过聚合，不能再对b进行聚合。

      SELECT a, count(c) FROM src GROUP BY a;

      改写不成功，对于COUNT函数不支持重新聚合。

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

    1. 创建的物化视图如下。

      CREATE MATERIALIZED VIEW mv AS 
SELECT a, b, sum(DISTINCT c) AS sum, count(DISTINCT d) AS cnt FROM src GROUP BY a, b;

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

      原始查询语句

      改写后的查询语句

      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输入

    1. 创建的物化视图如下。

      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;

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

      原始查询语句

      改写后的查询语句

      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) j1 
JOIN (SELECT a,b FROM j2 WHERE b > 10) j2 ON j1.a=j2.a;
      SELECT mv1.a,mv1.b,mv2.a FROM mv1 JOIN mv2 ON mv1.a=mv2.a;

  • JOIN带过滤条件

    1. 创建的物化视图如下。

      --创建非分区物化视图。
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 BY (ds) AS SELECT t1.id, t1.ds AS ds FROM t1 JOIN t2 ON t1.id = t2.id;

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

      原始查询语句

      改写后的查询语句

      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增加表

    1. 创建的物化视图如下。

      CREATE MATERIALIZED VIEW mv AS SELECT j1.a, j1.b FROM j1 JOIN j2 ON j1.a=j2.a;

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

      原始查询语句

      改写后的查询语句

      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的查询语句

  1. 创建的物化视图如下。

    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;

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

    原始查询语句

    改写后的查询语句

    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的查询语句

  1. 创建的物化视图如下。

    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;

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

    原始查询语句

    改写后的查询语句

    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;

使用示例

场景：假设有一张页面访问表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可查看到当前查询读取的表是doc_test_dev项目下的count_mv，说明物化视图有效，查询改写成功。

相关文档

上一篇：物化视图操作下一篇：物化视图定时更新
文档推荐