Materialized view query rewrite

Example 1: Rewrite with filter conditions

1. Create a materialized view.

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

2. The following table provides rewrite examples for the materialized view.

   Original query	Rewritten query
   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;	Rewrite fails because the materialized view does not contain the column d.
   SELECT d, e FROM src WHERE a=10;	Rewrite fails because the materialized view does not contain the columns d and e.
   SELECT a, b FROM src WHERE a=1;	Rewrite fails because the materialized view does not contain data where a=1.

Example 2: Rewrite with aggregate functions

Example 3: Rewrite with a JOIN clause

These three JOIN rewrite scenarios can be combined.

Because the goal of materialized view query rewrite is to accelerate queries, MaxCompute prioritizes rewrite rules that offer the best performance. A rule is not applied if it introduces operations that would result in poor acceleration.

Example 4: Rewrite with a LEFT JOIN clause

1. 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;

2. The following table shows how a query is rewritten based on the materialized view.

   Original query	Rewritten query
   SELECT t1.user_id, 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 user_id, total_amount FROM mv;

Example 5: Rewrite with a UNION ALL clause

1. 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;

2. The following table shows how a query is rewritten based on the materialized view.

   Original query	Rewritten query
   SELECT user_id, tran_amount FROM alipay_tran UNION ALL SELECT user_id, tran_amount FROM unionpay_tran;	SELECT user_id, tran_amount FROM mv;

Example 6: Use case

1. Scenario

   Consider a page visit table named visit_records that logs the page ID, user ID, and visit time for each visit. A frequent analysis task is to count the number of visits for different pages.

   In this situation, you can create a materialized view on visit_records that groups by page ID and counts the visits for each page. You can then run subsequent queries against this materialized view.

   The structure of visit_records is as follows:

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

2. Create a materialized view.

   -- Create a materialized view for the visit_records table that groups by page ID and counts the visits for each page.
   
   CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

3. Run the following query:

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

   When this query statement is executed, MaxCompute automatically matches the materialized view count_mv and reads the pre-aggregated data from count_mv.

4. To verify that the query was rewritten using the materialized view, run the following EXPLAIN command:

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

   The following result is returned:

   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 in the returned result shows that the table read by the query is the doc_test_dev project's count_mv. This indicates that the materialized view is effective and the query rewrite was successful.