MaxCompute:Materialized view operations

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

Operations

Limits

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.

Create a materialized view that supports clustering or partitioning

You can create a materialized view that supports clustering or partitioning based on the data for materialized view scenarios.

• Limits

  • The name of the materialized view that you want to create cannot be the same as the name of an existing table, view, or materialized view in the current project. You can run the SHOW TABLES; command to view the names of all tables and materialized views in a project.

  • You cannot create a materialized view based on an existing materialized view.

  • You cannot create a materialized view based on an external table.

• 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 Query and rewrite for the 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, 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>;

• The following table describes the parameters in the code.

  Parameter	Required	Description
  IF NOT EXISTS	No	If you do not specify IF NOT EXISTS and the materialized view already exists, an error is returned.
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view that you want to create.
  days	No	The lifecycle of the materialized view that you want to create. Unit: days. Valid values: 1 to 37231.
  BUILD DEFERRED	No	If this keyword is added, only the schema is created and the data is not updated when you create the materialized view.
  col_name	No	The name of a column in the materialized view that you want to create.
  col_comment	No	The comment on a column in the materialized view that you want to create.
  DISABLE REWRITE	No	Specifies whether to disable the query rewrite operation that is performed based on this materialized view. If you do not configure this parameter, the query rewrite operation based on this materialized view is allowed. In this case, you can execute the ALTER MATERIALIZED VIEW [project_name.]<mv_name> DISABLE REWRITE; statement to disable the query rewrite operation that is performed based on the materialized view. You can also execute the ALTER MATERIALIZED VIEW [project_name.]<mv_name> ENABLE REWRITE; statement to enable the query rewrite operation that is performed based on the materialized view.
  PARTITIONED BY	No	The partition key columns in the materialized view that you want to create. If you want to create a partitioned materialized view, you must configure this parameter.
  CLUSTERED BY|RANGE CLUSTERED BY	No	The shuffle attribute of the materialized view that you want to create. If you want to create a clustered materialized view, you must specify the clustered by or range clustered by parameter.
  SORTED BY	No	The sort attribute of the materialized view that you want to create. If you want to create a clustered materialized view, you must configure this parameter.
  REFRESH EVERY	No	Configure the scheduled update interval for materialized views. Available units are minutes, hours, or days.
  number_of_buckets	No	The number of buckets in the materialized view that you want to create. If you want to create a clustered materialized view, you must specify this parameter.
  TBLPROPERTIES	No	compressionstrategy specifies the compression policy for data storage of the materialized view that you want to create. Valid values: normal, high, and extreme. enable_auto_substitute specifies whether to automatically query data from the source table if the materialized view does not contain the partition from which you want to query data. For more information, see Query and rewrite for the materialized view. enable_auto_refresh: optional. Set this parameter to true if you want the system to automatically update data. refresh_interval_minutes: conditionally optional. If enable_auto_refresh is set to true, you need to configure this parameter. Unit: minutes. only_refresh_max_pt: optional. This parameter is valid for partitioned materialized views. If this parameter is set to true, the latest partition data in the source table is updated to the materialized view.
  select_statement	Yes	The SELECT statement. For more information about the syntax of the SELECT statement, see SELECT syntax.

• Examples

  • Example 1: 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';

  • Example 2: Update a materialized view whose source table contains an empty partition. An empty partition is generated in the materialized view.

    CREATE TABLE mf_blank_pts(id bigint ,name string) PARTITIONED BY (ds bigint); 
    ALTER TABLE mf_blank_pts ADD PARTITION (ds = 1); 
    ALTER TABLE mf_blank_pts ADD PARTITION (ds = 2); 
    INSERT INTO TABLE mf_blank_pts PARTITION(ds=1) VALUES (1,"aba"),(2,"cbd");  
    
    CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_blank_pts PARTITIONED BY (ds) 
    AS SELECT id,name,ds FROM mf_blank_pts;
    
    ALTER MATERIALIZED VIEW mf_mv_blank_pts REBUILD PARTITION (ds>0); 
    
    SHOW PARTITIONS mf_mv_blank_pts; 
    -- The partition ds=2 in the source table does not contain any data. After you execute an ALTER statement that contains the expression ds>0, an empty partition is generated in the materialized view.
    ds=1
    ds=2
    
    SELECT * FROM mf_mv_blank_pts WHERE ds>0; 
    -- The returned result shows that only the ds=1 partition has data.
    +------------+------------+------------+
    | id         | name       | ds         |
    +------------+------------+------------+
    | 1          | aba        | 1          |
    | 2          | cbd        | 1          |
    +------------+------------+------------+

Update a materialized view

If you perform operations, such as insert, overwrite, update, and delete operations on the table or partition that corresponds to a materialized view, the materialized view becomes invalid and cannot be used for query rewrite operations. You can check the status of a materialized view. If the materialized view is invalid, you must update the materialized view. For more information about how to query the status of a materialized view, see Query information about a materialized view.

• Precautions

  • Data in a materialized view or a partition in a materialized view is updated only when data in the source table or a partition in the source table changes.

  • You can enable the scheduled update feature for materialized views to update data periodically, see Scheduled update of materialized views.

• Syntax

  ALTER MATERIALIZED VIEW [<project_name>.]<mv_name>
        REBUILD [PARTITION (<ds>=max_pt(<table_name>),<expression1>...)];

• The following table describes the parameters in the code.

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view that you want to update.
  ds	No	The name of the partition field in the materialized view.
  max_pt	No	The maximum partition value of the table or materialized view that is specified by table_name.
  expression	No	The expression that is used to specify the partitions that you want to update. If you want to update a partitioned materialized view, you must configure this parameter.

• Examples

  • Example 1: Update a non-partitioned materialized view.

    -- Create a non-partitioned table.
    CREATE TABLE count_test(a BIGINT, b BIGINT); 
    -- Create a non-partitioned materialized view.
    CREATE MATERIALIZED VIEW count_mv LIFECYCLE 7 AS SELECT COUNT(*) FROM count_test; 
    -- Update the non-partitioned materialized view. 
    ALTER MATERIALIZED VIEW count_mv rebuild; 

  • Example 2: Update a partition of a partitioned materialized view.

    ALTER MATERIALIZED VIEW mf_mv_blank_pts REBUILD PARTITION (ds='1');

  • Example 3: Update the partitions of a partitioned materialized view. The partitions meet the specified conditions.

    ALTER MATERIALIZED VIEW mf_mv_blank_pts REBUILD PARTITION (ds>='1', ds<='2');

  • Example 4: Update the latest partition data to a partitioned materialized view.

    -- Create a partitioned table.
    CREATE TABLE IF NOT EXISTS sale_detail_jt 
    (shop_name STRING , 
    customer_id STRING , 
    total_price DOUBLE ) 
    PARTITIONED BY (sale_date STRING ,region STRING );
    
    ALTER TABLE  sale_detail_jt ADD PARTITION (sale_date='2013',region='china');
    
    INSERT INTO  sale_detail_jt PARTITION (sale_date='2013',region='china') VALUES 
        ('s1','c1',100.1), 
        ('s2','c2',100.2), 
        ('s3','c3',100.3);
    
    ALTER TABLE  sale_detail_jt ADD PARTITION (sale_date='2013',region='en');
    INSERT INTO  sale_detail_jt PARTITION (sale_date='2013',region='en') VALUES 
        ('t1','c5',200.0), 
        ('t2','c6',300.0);
        
    -- View partition data.
    SELECT * FROM sale_detail_jt WHERE sale_date='2013' AND region='china';
    +-----------+-------------+-------------+-----------+--------+
    | shop_name | customer_id | total_price | sale_date | region |
    +-----------+-------------+-------------+-----------+--------+
    | s1        | c1          | 100.1       | 2013      | china  |
    | s2        | c2          | 100.2       | 2013      | china  |
    | s5        | c2          | 100.2       | 2013      | china  |
    +-----------+-------------+-------------+-----------+--------+
    -- View partition data.
    SELECT * FROM sale_detail_jt WHERE sale_date='2013' AND region='en';
    +-----------+-------------+-------------+-----------+--------+
    | shop_name | customer_id | total_price | sale_date | region |
    +-----------+-------------+-------------+-----------+--------+
    | t1        | c5          | 200.0       | 2013      | en     |
    | t2        | c6          | 300.0       | 2013      | en     |
    +-----------+-------------+-------------+-----------+--------+
    
    -- Create a materialized view.
    CREATE MATERIALIZED VIEW mv_deferred BUILD DEFERRED AS SELECT * FROM sale_detail_jt;
    
    -- Query data from the mv_deferred materialized view.
    SELECT * FROM mv_deferred;
    -- The following result is returned:
    +-----------+-------------+-------------+-----------+--------+
    | shop_name | customer_id | total_price | sale_date | region |
    +-----------+-------------+-------------+-----------+--------+
    +-----------+-------------+-------------+-----------+--------+
    
    -- Create a partitioned table.
    CREATE TABLE mf_part (id bigint,name string) PARTITIONED BY (dt string);
    -- Insert data into the table.
    INSERT INTO mf_part PARTITION(dt='2013') VALUES(1,'name1'),(2,'name2');
    -- Query data from the table.
    SELECT * FROM mf_part WHERE dt='2013';
    -- The following result is returned:
    +------------+------+----+
    | id         | name | dt |
    +------------+------+----+
    | 1          | name1 | 2013 |
    | 2          | name2 | 2013 |
    +------------+------+----+
    
    -- Create a partitioned materialized view.
    CREATE MATERIALIZED VIEW mv_rebuild BUILD DEFERRED PARTITIONED ON (dt) AS SELECT * FROM mf_part;
    
    -- Query data from the materialized view.
    SELECT * FROM mv_rebuild WHERE dt='2013';
    -- The following result is returned:
    +------------+------+----+
    | id         | name | dt |
    +------------+------+----+
    +------------+------+----+
    -- Update the latest partition data to the materialized view.
    ALTER MATERIALIZED VIEW mv_rebuild REBUILD PARTITION(dt=max_pt('mf_part'));
    
    -- Query data from the materialized view.
    SELECT * FROM mv_rebuild WHERE dt='2013'; 
    -- The following result is returned:
    +------------+------+----+
    | id         | name | dt |
    +------------+------+----+
    | 1          | name1 | 2013 |
    | 2          | name2 | 2013 |
    +------------+------+----+

Change the lifecycle of an existing materialized view

You can change the lifecycle of an existing materialized view.

• Syntax

  ALTER MATERIALIZED VIEW [<project_name>.]<mv_name> SET LIFECYCLE <days>;

• The following table describes the parameters in the code.

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view that you want to update.
  days	Yes	The new lifecycle of the materialized view. Unit: days.

• Examples

  -- Change the lifecycle of an existing materialized view to 10 days. 
  ALTER MATERIALIZED VIEW count_mv SET LIFECYCLE 10;

Enable or disable the lifecycle feature for an existing materialized view

You can enable or disable the lifecycle feature for an existing materialized view.

• Syntax

  ALTER MATERIALIZED VIEW  [<project_name>.]<mv_name> [<pt_spec>] enable|disable LIFECYCLE;

• The following table describes the parameters in the code.

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view for which you want to enable or disable the lifecycle feature.
  pt_spec	No	The partition information of the materialized view for which you want to enable or disable the lifecycle feature. Format: (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col indicates the partition field name. partition_col_value indicates the partition value.
  enable|disable	Yes	The enable option specifies that the lifecycle feature is enabled for a materialized view or a partition of a materialized view. The disable option specifies that the lifecycle feature is disabled for a materialized view or a partition of a materialized view. If you disable the lifecycle feature, lifecycle management is not required for the materialized view or partition.

• Examples

  • Example 1: Enable the lifecycle feature for a materialized view.

    ALTER MATERIALIZED VIEW mf_mv_blank_pts  PARTITION (ds='1') enable LIFECYCLE;

  • Example 2: Disable the lifecycle feature for a materialized view.

    ALTER MATERIALIZED VIEW mf_mv_blank_pts  PARTITION (ds='1') disable LIFECYCLE;

Query information about a materialized view

You can query the information about a materialized view, including the schema and modification time of the materialized view.

• Syntax

  DESC EXTENDED [<project_name>.]<mv_name>;

• The following table describes the parameters in the code.

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view that you want to query.

• Examples

  DESC EXTENDED mv;

  The following code shows a sample query result:

  Note

  The following sample result can be returned only if the version of the MaxCompute client is V0.43 or later. For more information, see the "Version updates" section in MaxCompute client (odpscmd).

  +------------------------------------------------------------------------------------+
  | Owner:                    ALIYUN$$****@***.aliyunid.com                       |
  | Project:                  m****                                                |
  | TableComment:                                                                      |
  +------------------------------------------------------------------------------------+
  | CreateTime:               2023-05-30 13:16:07                                      |
  | LastDDLTime:              2023-05-30 13:16:07                                      |
  | LastModifiedTime:         2023-05-30 13:16:07                                      |
  +------------------------------------------------------------------------------------+
  | MaterializedView: YES                                                              |
  | ViewText: select id,name from mf_refresh                                           |
  | Rewrite Enabled: true                                                              |
  | AutoRefresh Enabled: true                                                          |
  | Refresh Interval Minutes: 10                                                       |
  +------------------------------------------------------------------------------------+
  | Native Columns:                                                                    |
  +------------------------------------------------------------------------------------+
  | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
  +------------------------------------------------------------------------------------+
  | id       | bigint |       |               | true     | NULL         |              |
  | name     | string |       |               | true     | NULL         |              |
  +------------------------------------------------------------------------------------+
  | Extended Info:                                                                     |
  +------------------------------------------------------------------------------------+
  | IsOutdated:               false                                                    |
  | TableID:                  569ec712873e44b3868e79b7a8beabab                         |
  | IsArchived:               false                                                    |
  | PhysicalSize:             1875                                                     |
  | FileNum:                  2                                                        |
  | StoredAs:                 CFile                                                    |
  | CompressionStrategy:      normal                                                   |
  | odps.timemachine.retention.days: 1                                                        |
  | ColdStorageStatus:        N/A                                                      |
  | encryption_enable:        false                                                    |
  +------------------------------------------------------------------------------------+
  | AutoRefresh History:                                                               |
  +------------------------------------------------------------------------------------+
  | InstanceId                | Status     | StartTime           | EndTime             |
  +------------------------------------------------------------------------------------+
  | 20230619070546735ghwl1****** | TERMINATED | 2023-06-19 15:05:46 | 2023-06-19 15:05:47 |
  | 20230619065545586gwllc****** | TERMINATED | 2023-06-19 14:55:45 | 2023-06-19 14:55:46 |
  | 20230619064544463gcjgom****** | TERMINATED | 2023-06-19 14:45:44 | 2023-06-19 14:45:45 |
  | 20230619063543334gzxs2d****** | TERMINATED | 2023-06-19 14:35:43 | 2023-06-19 14:35:44 |
  | 2023061906254257gi21w2****** | TERMINATED | 2023-06-19 14:25:42 | 2023-06-19 14:25:43 |
  | 20230619061540813giacg8****** | TERMINATED | 2023-06-19 14:15:41 | 2023-06-19 14:15:41 |
  | 20230619060539674gswjq9****** | TERMINATED | 2023-06-19 14:05:39 | 2023-06-19 14:05:40 |
  | 20230619055538578gvdjk****** | TERMINATED | 2023-06-19 13:55:38 | 2023-06-19 13:55:40 |
  | 20230619054537356glqdne****** | TERMINATED | 2023-06-19 13:45:37 | 2023-06-19 13:45:38 |
  | 2023061905353687gcc5pl****** | TERMINATED | 2023-06-19 13:35:36 | 2023-06-19 13:35:37 |
  +------------------------------------------------------------------------------------+

Query the status of a materialized view

List all materialized views within a project

List all materialized views within the project, or those that meet certain rules.

• Syntax

  --List all materialized views within the project.
  SHOW MATERIALIZED VIEWS;
  --List all materialized views within the project whose names match 'materialized_view'.
  SHOW MATERIALIZED VIEWS LIKE '<materialized_view>';

• Examples

  --List all materialized view names within the project that start with 'test'. ('*' denotes any following characters.)
  SHOW MATERIALIZED VIEWS LIKE 'test*';         

  The following code shows a sample result:

  ALIYUN$account_name:test_two_mv
  ALIYUN$account_name:test_create_one_mv

Drop an existing materialized view

You can drop an existing materialized view.

• Syntax

  DROP MATERIALIZED VIEW [IF EXISTS] [<project_name>.]<mv_name> [purge];

• The following table describes the parameters in the code.

  Parameter	Required	Description
  IF EXISTS	No	If you do not specify IF EXISTS and the materialized view does not exist, an error is returned.
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the materialized view that you want to drop.
  purge	No	If you specify the purge parameter, data in a materialized view is deleted when you drop the materialized view.

• Examples

  • Drop the mv materialized view.

    DROP MATERIALIZED VIEW count_mv;

  • Drop the mv materialized view and delete the data in the materialized view at the same time.

    DROP MATERIALIZED VIEW count_mv purge;

Drop partitions from an existing materialized view

You can drop one or more partitions from an existing materialized view.

• Syntax

  ALTER MATERIALIZED VIEW [<project_name>.]<mv_name> DROP [IF EXISTS] PARTITION <pt_spec> [PARTITION <pt_spec>, PARTITION <pt_spec>....];

• The following table describes the parameters in the code.

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project to which the materialized view belongs. If you do not configure this parameter, the current MaxCompute project is used. To view the name of a MaxCompute project, perform the following steps: Log on to the MaxCompute console. In the top navigation bar, select a region. View the name of the MaxCompute project on the Workspace > Projects page.
  mv_name	Yes	The name of the partitioned materialized view from which you want to drop partitions.
  IF EXISTS	No	If you do not specify IF EXISTS and the materialized view does not exist, an error is returned.
  pt_spec	Yes	The partitions that you want to drop. You must specify at least one partition. Format: (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col indicates the partition field name. partition_col_value indicates the partition value.

• Examples

  • Example 1: Drop a partition from a partitioned materialized view. Sample statement:

    ALTER MATERIALIZED VIEW mf_mv_blank_pts DROP PARTITION (ds='1');

  • Example 2: Drop the partitions that meet the specific conditions from a partitioned materialized view. Sample statement:

    ALTER MATERIALIZED VIEW mf_mv_blank_pts DROP PARTITION (ds>='1' AND ds<='2');

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

Billing rules

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

• Storage

  Materialized views occupy physical storage. You are charged for the physical storage 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.

References

For more information about how to rewrite a common query into a query from a materialized view, see Query and rewrite for the materialized view.

For more information about the scheduled update feature of materialized views, see Scheduled update of materialized views.