MaxCompute:CREATE MATERIALIZED VIEW

Background

A view is a virtual table defined by a query. A materialized view, by contrast, is a physical table that stores pre-computed query results and consumes storage resources. For billing details, see Billing rules.

The following table compares traditional queries with materialized view queries.

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

SELECT empid, deptname 
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2018-01-01';
-- This is equivalent to the following statement.
SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

Billing rules

Materialized view costs consist of two components:

• Storage fees

  Materialized views consume physical storage, incurring storage fees on a pay-as-you-go basis. For more information, see Storage pricing (pay-as-you-go).

• Computing costs

  Creating, updating, and querying a materialized view — including query rewrites when the view is valid — consume computing resources and incur computing costs.

  • If your MaxCompute project is on a subscription plan, no separate fees are charged.

  • If your MaxCompute project is on a pay-as-you-go plan, MaxCompute calculates costs based on SQL complexity and input data volume. For more information, see Standard SQL pricing. Note the following:

    • The SQL statement used to refresh a materialized view is the same as its defining query. If the project is bound to a subscription computing resource group, the operation uses your purchased resources at no extra cost. If the project uses a pay-as-you-go resource group, the cost depends on input data volume and SQL complexity. After a refresh, storage fees are charged based on the actual size of the materialized view.

    • When a materialized view is valid, query rewrite reads data from the view. The input data volume depends on the materialized view, not the source table. If the view is invalid, query rewrite is unavailable and queries read from the source table directly. For more information, see Query materialized view status.

    • When a materialized view is built from multi-table joins, data bloat can occur. Reading from the materialized view does not always reduce costs compared to reading from the source tables.

Limitations

Window functions, user-defined table-valued functions (UDTFs), and non-deterministic functions such as user-defined scalar functions (UDFs) and user-defined aggregate functions (UDAFs) are not supported.

Precautions

• If the query statement based on which you create a materialized view fails to be executed, you cannot create the materialized view.

• Partition key columns in a materialized view must be derived from a source table. The sequence and number of the columns in the materialized view must be the same as the sequence and number of the columns in the source table. Column names can be different.

• You must specify comments for all columns, including partition key columns. If you specify comments only for some columns, an error is returned.

• You can specify both the partitioning and clustering attributes for a materialized view. In this case, the data in each partition has the specified clustering attribute.

• If the query statement based on which you create a materialized view contains operators that are not supported by the materialized view, an error is returned. For more information about the operators that are supported by materialized views, see Perform a query rewrite operation based on a materialized view.

• By default, MaxCompute does not allow you to create materialized views by using non-deterministic functions, such as UDFs or UDAFs. If you must use non-deterministic functions based on your business requirements, run the set odps.sql.materialized.view.support.nondeterministic.function=true; command at the session level.

• If the source table of a materialized view contains an empty partition, you can refresh the materialized view to generate an empty partition in the materialized view.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]<mv_name>
[LIFECYCLE <days>]    --Specifies the lifecycle.
[BUILD DEFERRED]    --Creates the schema without populating data.
[(<col_name> [COMMENT <col_comment>], ...)]    --Column comments.
[DISABLE REWRITE]    --Specifies whether the materialized view can be used for query rewrite.
[COMMENT 'table comment']    --Table comment.
[PARTITIONED BY (<col_name> [, <col_name>, ...])]    --Creates the materialized view as a partitioned table.
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
  [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
    INTO <number_of_buckets> BUCKETS]    --Sets the shuffle and sort properties for a clustered table.
[REFRESH EVERY <num> {MINUTES | HOURS | DAYS}] 
[TBLPROPERTIES("compressionstrategy"="{normal|high|extreme}",    --Specifies the data storage compression strategy for the table.
                "enable_auto_substitute"="true",    --Specifies whether to enable query passthrough to the source table when a partition does not exist.
                "enable_auto_refresh"="true",    --Specifies whether to enable automatic refresh.
                "refresh_interval_minutes"="120",    --Specifies the refresh interval.
                "only_refresh_max_pt"="true"    --For partitioned materialized views, automatically refreshes only the latest partition from the source table.
                )]
AS <select_statement>;

Parameters

Examples

Perform a query rewrite operation based on a materialized view

The most important feature of materialized views is to perform query rewrite operations on query statements. To perform query rewrite operations on a query statement based on a materialized view, you must add set odps.sql.materialized.view.enable.auto.rewriting=true; before the query statement. If a materialized view is invalid, the materialized view cannot be used for query rewrite operations. In this case, data is queried from the source table, and the query speed is not accelerated.

The following table compares the query rewrite operator types supported by MaxCompute with those of other products.

The query rewrite operations based on a materialized view require that the data in a query statement be obtained from the materialized view. The data includes output columns, the columns required by filter operations, the columns required by aggregate functions, and the columns required by JOIN operations. If the columns that are required in the query statement are not included in the materialized view or are not supported by the aggregate functions, you cannot perform query rewrite operations based on the materialized view.

Materialized view query penetration

A partitioned materialized view may not contain data for all partitions — for example, if you refresh only the most recent ones. When a query targets a partition that lacks data in the materialized view, the system automatically falls back to the source partitioned table. The following figure illustrates this process.

The following example demonstrates how to use a materialized view that supports penetration queries.

1. Create a partitioned materialized view that supports penetration queries.

   -- Create a source table named src.
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- Insert data.
   INSERT INTO src PARTITION(dt='20210101') VALUES(1,'Alex');
   INSERT INTO src PARTITION(dt='20210102') VALUES(2,'Flink');
   
   -- Create a partitioned materialized view that supports penetration query.
   CREATE MATERIALIZED VIEW IF NOT EXISTS mv LIFECYCLE 7 
   PARTITIONED BY (dt) 
   tblproperties("enable_auto_substitute"="true") 
   AS SELECT id, name, dt FROM src;

2. Query data from the 20210101 partition in the materialized view mv.

   SELECT * FROM mv WHERE dt='20210101';

3. Query data from the 20210102 partition in the materialized view mv. The system automatically performs a penetration query on the source table because this partition is not materialized.

   SELECT * FROM mv WHERE dt = '20210102';
   -- Because the data for the 20210102 partition is not materialized, the query is rewritten to access the source table. This is equivalent to:
   SELECT * FROM (SELECT id, name, dt FROM src WHERE dt='20210102') t;

4. Query data from a range of partitions in the materialized view mv. The system automatically performs a penetration query on the source table for the non-materialized data and combines it with the materialized data using a UNION operation before returning the result.

   SELECT * FROM mv WHERE dt >= '20201230' AND dt<='20210102' AND id=5; 
   -- Because data for partitions 20201230 and 20210102 is not materialized, the query is rewritten to access the source table. This is equivalent to:
   SELECT * FROM
   (SELECT id, name, dt FROM src WHERE dt='20201230' OR dt='20210102'
    UNION ALL  
    SELECT * FROM mv WHERE dt='20210101'
   ) t WHERE id = 5;

Related statements

• ALTER MATERIALIZED VIEW: updates a materialized view, changes the lifecycle of a materialized view, enables or disables the lifecycle feature for a materialized view, or drops partitions from a materialized view.

• DESC TABLE/VIEW: views the information about a materialized view in a MaxCompute project.

• SELECT MATERIALIZED VIEW: queries the status of a materialized view.

• DROP MATERIALIZED VIEW: drops an existing materialized view.