MaxCompute:CREATE MATERIALIZED VIEW

When to use a materialized view

Materialized views are suitable for the following queries:

• Queries that run in a fixed pattern and execute frequently.

• Queries that involve time-consuming operations such as JOINs or aggregations.

• Queries that read only a small portion of a large table.

If none of these conditions apply — for example, the query pattern changes often or the dataset is small — a regular view or direct query may be more appropriate.

Create a materialized view

The following examples use two partitioned source tables: mf_t and mf_t1.

Step 1: Create the source tables and insert data.

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';
-- Result:
-- +------------+------------+------------+------------+
-- | 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';
-- Result:
-- +------------+------------+------------+------------+
-- | id         | value      | name       | ds         |
-- +------------+------------+------------+------------+
-- | 1          | 10         | kyle       | 1          |
-- | 3          | 20         | john       | 1          |
-- +------------+------------+------------+------------+

Step 2: Create a materialized view. The following examples show three common configurations.

Example 1: Partitioned materialized view

Creates a materialized view with a partition key column ds, a 7-day lifecycle, and column comments.

CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7
(
    key   COMMENT 'unique id',
    value COMMENT 'input value',
    ds    COMMENT 'partition'
)
PARTITIONED BY (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';

-- Query the materialized view:
SELECT * FROM mf_mv WHERE ds=1;
-- Result:
-- +------------+------------+------------+
-- | key        | value      | ds         |
-- +------------+------------+------------+
-- | 1          | 10         | 1          |
-- +------------+------------+------------+

Example 2: Non-partitioned clustered materialized view

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

Example 3: Partitioned and clustered materialized view

Combines partitioning and clustering. Data within each partition is distributed into buckets.

CREATE MATERIALIZED VIEW mf_mv3 LIFECYCLE 7
PARTITIONED BY (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';

Use query rewrite to accelerate queries

This example uses a page access table visit_records that logs page_id, user_id, and visit_time. Traffic analysis queries that count visits per page run frequently.

Step 1: Create a materialized view that precomputes per-page visit counts.

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

Step 2: Enable query rewrite and run the original query.

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

MaxCompute automatically matches the query to count_mv and reads aggregated results from it instead of scanning visit_records.

Step 3: Verify that the query is rewritten using EXPLAIN.

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

Expected output:

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 confirms that the query reads from count_mv in the doc_test_dev project, not from the original visit_records table.

Configure refresh strategies

The three examples below demonstrate different ways to keep a materialized view current.

Manual refresh only (default)

No refresh clause is specified. Refresh the view explicitly using ALTER MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW sales_mv
AS SELECT region, sum(amount) AS total FROM sales GROUP BY region;

Scheduled refresh using `REFRESH EVERY`

Refreshes the view automatically every 2 hours.

CREATE MATERIALIZED VIEW sales_mv
REFRESH EVERY 2 HOURS
AS SELECT region, sum(amount) AS total FROM sales GROUP BY region;

Scheduled refresh using `TBLPROPERTIES`

Sets a 120-minute refresh interval and automatically refreshes only the latest partition from the source table.

CREATE MATERIALIZED VIEW sales_mv LIFECYCLE 7
PARTITIONED BY (ds)
TBLPROPERTIES (
    "enable_auto_refresh"      = "true",
    "refresh_interval_minutes" = "120",
    "only_refresh_max_pt"      = "true"
)
AS SELECT region, ds, sum(amount) AS total FROM sales GROUP BY region, ds;

Supported operators for query rewrite

For query rewrite to succeed, all columns needed by the query — output columns, filter columns, aggregate function columns, and JOIN columns — must be present in the materialized view.

Rewrite queries with filter conditions

All examples in this section use the following materialized view:

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

Rewrite queries with aggregate functions

When aggregation keys match the materialized view, rewriting is supported for all aggregate functions. When aggregation keys differ, only SUM, MIN, and MAX are supported.

All examples use:

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

When `DISTINCT` is used, rewriting is supported only when aggregation keys match the materialized view exactly.

All examples use:

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

Rewrite queries with JOIN

Rewrite JOIN inputs

When a query's JOIN inputs match a materialized view's definition, MaxCompute replaces those inputs with the materialized view.

Create materialized views:

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;

JOIN with filter conditions

Create materialized views:

-- Non-partitioned
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
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;

JOIN with additional tables

Create materialized view:

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

The three JOIN rewrite types can be combined. If a query meets the conditions for multiple rewrite rules, MaxCompute selects the most efficient execution plan. If the rewritten plan does not outperform the original, the rewrite is not applied.

Rewrite queries with LEFT JOIN

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

Rewrite queries with UNION ALL

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