MaxCompute:CREATE MATERIALIZED VIEW

Background information

A view is a stored query accessible as a virtual table. Each time you query a view, the query statement is converted into the SQL statement that is used to define the view. A materialized view is a special physical table that occupies storage resources to store real data. For more information about the billing rules of materialized views, see the Billing rules section in this topic.

The following table compares traditional queries and queries based on materialized views.

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';
-- The preceding statement is equivalent to the following statement: 
SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

Billing rules

When you use materialized views, you are charged for the following items:

• Storage

  Materialized views occupy physical storage space. You are charged for the physical storage space that is occupied by materialized views. For more information about storage pricing, see Storage pricing (pay-as-you-go).

• Computing

  Data is queried when you create, update, and query materialized views, and rewrite queries (if materialized views are valid). These operations consume computing resources and generate computing costs.

  • If your MaxCompute project uses the subscription billing method, no extra computing costs are generated.

  • If your MaxCompute project uses the pay-as-you-go billing method, fees are calculated based on the SQL complexity and the amount of input data. For more information about billing, see the "Billing for standard SQL jobs" section in Computing pricing. Take note of the following points:

    • The SQL statements used to update a materialized view are the same as the SQL statements used to create a materialized view. If the project to which the materialized view belongs is bound to a subscription resource group, the purchased subscription resources are used, and no additional fees are generated. If the project is bound to a pay-as-you-go resource group, the fees vary based on the amount of input data and complexity of the SQL statements. After you update a materialized view, you are charged storage fees based on the storage space that is used.

    • If the materialized view is valid, data is read from the materialized view when the query rewrite operation is performed. The amount of input data that is read from the materialized view of the query statement is related to the materialized view and is irrelevant to the source table of the materialized view. If the materialized view is invalid, the query rewrite operation cannot be performed, and data is queried from the source table. The amount of input data for the query statement is related to the source table. For more information about how to query the status of a materialized view, see Query the status of a materialized view in this topic.

    • Data bloat may occur if a materialized view is generated based on the association of multiple tables. Therefore, the amount of data read by a materialized view may not be absolutely less than the amount of data in the source table. MaxCompute cannot ensure that data reading from a materialized view costs less than data reading from the source table.

Limits

Before you use materialized views, take note of the following limits:

• Window functions are not supported.

• User-defined table-valued functions (UDTFs) are not supported.

• By default, non-deterministic functions, such as user-defined scalar functions (UDFs) and user-defined aggregate functions (UDAFs), are not supported. If you must use non-deterministic functions, run the set odps.sql.materialized.view.support.nondeterministic.function=true; command at the session level.

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>]    -- The lifecycle of the materialized view.
[BUILD DEFERRED]    -- Specifies that only the schema is created and no data is updated when you create the materialized view.
[(<col_name> [COMMENT <col_comment>],...)]    -- The column comments.
[DISABLE REWRITE] -- Specifies whether to disable the query rewrite operation that is performed based on the materialized view.
[COMMENT 'table comment']    -- The materialized view comments.
[PARTITIONED BY (<col_name> [, <col_name>, ...])    -- The partitions in the materialized view. This parameter is required when you create a partitioned materialized view.
[CLUSTERED BY|RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
     [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                 INTO <number_of_buckets> BUCKETS]    -- The shuffle and sort attributes of the materialized view. This parameter is required when you create a clustered materialized view.
[REFRESH EVERY <num> MINUTES/HOURS/DAYS]
[TBLPROPERTIES("compressionstrategy"="normal/high/extreme",    -- The compression policy for data storage of the materialized view.
                "enable_auto_substitute"="true",    -- Specifies whether to query data from the source partitioned table if the partition data that you want to query is not contained in the materialized view.
                "enable_auto_refresh"="true",    -- Specifies whether to enable the scheduled update feature.
                "refresh_interval_minutes"="120",    -- The update interval.
                "only_refresh_max_pt"="true"    -- Specifies whether to automatically update the latest partition data in the source table to the materialized view.
                )]
AS <select_statement>;

Parameters

Examples

Create a materialized view

1. Create tables named mf_t and mf_t1 and insert data into the tables.

   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'; 
   -- The following result is returned: 
   +------------+------------+------------+------------+
   | 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';
   -- The following result is returned: 
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 3          | 20         | john       | 1          |
   +------------+------------+------------+------------+

2. Create a materialized view.

   • Sample 1: Create a materialized view that contains a partition key column named ds.

     CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7
     (
       key comment 'unique id',
       value comment 'input value',
       ds comment 'partitiion'
       )
     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 data from the created materialized view.
     SELECT * FROM mf_mv WHERE ds =1;
     +------------+------------+------------+
     | key        | value      | ds         |
     +------------+------------+------------+
     | 1          | 10         | 1          |
     +------------+------------+------------+

   • Sample 2: Create a non-partitioned materialized view that is clustered.

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

   • Sample 3: Create a partitioned materialized view that is clustered.

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

Implement query rewrite based on a materialized view

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

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

Perform a penetration query on a materialized view

A partitioned materialized view does not contain the data of all partitions in a source partitioned table if only the latest partition data is updated to the partitioned materialized view. If the partition data that you want to query does not exist in a partitioned materialized view, the system performs a penetration query to query data from the source partitioned table. The following figure shows how to perform a penetration query.

The following example shows how to perform a penetration query based on a materialized view.

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

   -- Create a table named src. 
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- Insert data into the table. 
   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 the data of the 20210101 partition in the src table from the mv materialized view.

   SELECT * FROM mv WHERE dt='20210101';

3. Query the data of the 20210102 partition in the src table from the mv materialized view. The mv materialized view does not contain the partition data. Therefore, a penetration query is performed to query the partition data from the src table.

   SELECT * FROM mv WHERE dt = '20210102';
   -- The preceding statement is equivalent to the following statement because the mv materialized view does not contain the data of the 20210102 partition and the partition data needs to be queried from the src table.
   SELECT * FROM (SELECT id, name, dt FROM src WHERE dt='20210102') t;

4. Query the data of partitions 20201230 to 20210102 from the mv materialized view. The mv materialized view does not contain all partition data. Therefore, a penetration query is performed to obtain the data that is not contained in the mv materialized view, and a UNION operation is performed on the obtained data and the data that is queried from the mv materialized view to return the final result.

   SELECT * FROM mv WHERE dt >= '20201230' AND dt<='20210102' AND id='5'; 
   -- The mv materialized view does not contain the data of the 20210102 partition and a penetration query is performed to query the data of the 20210102 partition from the source table. The preceding statement is equivalent to the following statement:
   SELECT * FROM
   (SELECT id, name, dt FROM src WHERE dt='20211231' 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.