MaxCompute:Materialized view query rewrite

Example 1: Filter conditions

Create a materialized view:

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

The following table shows how queries on src are rewritten to use mv.

Example 2: Aggregate functions

When the materialized view and the query use the same aggregation key, all aggregate functions can be rewritten. When the aggregation keys differ, only SUM, MIN, and MAX are supported.

Create a materialized view:

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

The following table shows how queries are rewritten.

Aggregate functions with DISTINCT

When an aggregate function contains DISTINCT, the rewrite requires the materialized view and the query to have the same aggregation key.

Create a materialized view:

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

Example 3: JOIN clause

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;

-- Non-partitioned materialized views
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;
-- Partitioned materialized view
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;

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

Example 4: LEFT JOIN clause

Enable substitute rewrite before running the query:

SET odps.sql.materialized.view.enable.substitute.rewriting=true;

Create a materialized view:

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;

Example 5: UNION ALL clause

Enable substitute rewrite before running the query:

SET odps.sql.materialized.view.enable.substitute.rewriting=true;

Create a materialized view:

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;

Example 6: End-to-end scenario

This example shows the full flow: creating a materialized view, enabling automatic query rewrite, and verifying that the rewrite took effect.

Scenario: The visit_records table logs page visits. You frequently query page visit counts grouped by page ID.

The table schema is:

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

1. Create a materialized view that pre-aggregates visit counts by page:

   CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

2. Enable automatic query rewrite and run the query:

   SET odps.sql.materialized.view.enable.auto.rewriting=true;
   SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   MaxCompute automatically rewrites this query to read from count_mv.

3. Verify the rewrite using EXPLAIN:

   EXPLAIN SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   The output shows:

   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

   The Data source field shows doc_test_dev.count_mv, confirming the query reads from the materialized view and the rewrite is successful.