Scheduled update of materialized views - MaxCompute - Alibaba Cloud Documentation Center

This topic describes the scheduled update feature of materialized views and related operations.

Usage notes

A scheduled update of materialized views is essentially a task submitted by the system background. The update completion time varies based on the amount of available cluster resources and the size of the task.
When a scheduled update is triggered, all data is updated.
- If data in a partition of the partitioned base table that corresponds to the partition key columns in a partitioned materialized view changes, only data in the associated partition of the materialized view is updated. Data in other partitions is not affected. If data in other tables associated with the partitioned materialized view changes, full data in the materialized view is updated.
- If you enable the scheduled update feature for a non-partitioned materialized view, the system automatically checks whether an update operation is required. If data in the source table is not updated, the update operation is skipped.
If a scheduled update is not complete before the next update time, the system automatically cancels the next scheduled update and triggers a new update at the latest scheduled time.
For example, a scheduled update is triggered at an interval of 20 minutes. If the first update is scheduled at 10:00:00, the second update is planned to be scheduled at 10:20:00, and the third update is planned to be scheduled at 10:40:00.
If the first update starts at 10:00:00 and ends at 10:30:00, the update that is planned to be scheduled at 10:20:00 is canceled, and the next update is scheduled at 10:40:00.
MaxCompute cannot detect the scheduled update setting of a materialized view in real time and may submit an automatic update task for the materialized view with a delay of less than 30 minutes.

Enable the scheduled update feature for a materialized view

You can enable the scheduled update feature for a materialized view in MaxCompute by using one of the following methods.

In the examples in this topic, materialized views are created based on the mf_refresh table.

CREATE TABLE mf_refresh (id bigint, name string);
INSERT INTO TABLE mf_refresh VALUES (1,'kyle'),(2,'tom');

Use the EVERY parameter

When you create a materialized view, specify the interval at which the material view is updated. The interval can be N minutes, hours, or days. The next update time is the previous update time plus N minutes, hours, or days.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name 
[LIFECYCLE days] -- The lifecycle of 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.
[PARTITIONED BY (col_name [, col_name, ...]) -- The partition key columns 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 xx MINUTES/HOURS/DAYS] -- The scheduled update interval of the materialized view.
TBLPROPERTIES("enable_auto_refresh"="true") -- Enable the scheduled update feature for the materialized view.
AS <select_statement>;

Examples

Create a materialized view that is updated at an interval of 10 minutes.

Create a materialized view named mf_mv_refresh and query the materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_refresh  
REFRESH EVERY 10 minutes 
TBLPROPERTIES("enable_auto_refresh"="true") 
AS SELECT id,name FROM mf_refresh; 

SELECT * FROM mf_mv_refresh;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
+------------+------------+

Insert data into the table.

INSERT INTO TABLE mf_refresh VALUES (3,'jen'),(4,'cathy');

Query the materialized view after 10 minutes.

SELECT * FROM mf_mv_refresh;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+

Use the `refresh_interval_minutes` parameter of TBLPROPERTIES

When you create a materialized view, specify the scheduled update interval. For example, set the scheduled update interval to N minutes. The next update time point is the previous update time point plus N minutes.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days] -- The lifecycle of 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.
[PARTITIONED BY (col_name [, col_name, ...]) -- The partition key columns 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.
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx")
-- Enable the scheduled update feature.
AS <select_statement>;

Examples

Create a materialized view that is updated at an interval of 20 minutes.

Create a materialized view named mf_mv_refresh2 and query the materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_refresh2 
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="20") 
AS SELECT id,name FROM mf_refresh; 

SELECT * FROM mf_mv_refresh2;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+

Insert data into the table.

INSERT INTO TABLE mf_refresh VALUES (5,'roger'),(6,'david');

Query the materialized view after 20 minutes.

SELECT * FROM mf_mv_refresh2;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

Use the `refresh_cron` parameter of TBLPROPERTIES

You can use a Cron expression to specify the interval or time point for scheduled updates.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days] -- The lifecycle of 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.
[PARTITIONED BY (col_name [, col_name, ...]) -- The partition key columns 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.

TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx")
-- Enable the scheduled update feature.
AS <select_statement>;

The refresh_cron parameter is a string in the Quartz Cron format. For more information, see Cron expression examples.

Examples

Create a materialized view that is updated at an interval of 5 minutes.

Create a materialized view named mf_mv_refresh3 and query the materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_refresh3 
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="* */5 * * * ?  *") 
AS SELECT id,name FROM mf_refresh; 

SELECT * FROM mf_mv_refresh3;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

Insert data into the table.

INSERT INTO TABLE mf_refresh VALUES (7,'ethan'),(8,'cal');

Query the materialized view after 5 minutes.

SELECT * FROM mf_mv_refresh3;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

Create a materialized view that is updated at 20:00 every night.

Create a materialized view named mf_mv_refresh4 and query the materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_refresh4 
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="0 0 20 * * ?  *") 
AS SELECT id,name FROM mf_refresh; 

SELECT * FROM mf_mv_refresh4;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

Insert data into the table.

INSERT INTO TABLE mf_refresh VALUES (9,'annie'),(10,'zoe');

Query the materialized view after 20:00.

SELECT * FROM mf_mv_refresh4;

The following result is returned.

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 9          | annie      |
| 10         | zoe        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

Change the interval for scheduled updates of a materialized view

Execute one of the following statements to change the interval for scheduled updates of a materialized view:

ALTER MATERIALIZED VIEW mf_mv_refresh2 SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx");
ALTER MATERIALIZED VIEW mf_mv_refresh3 SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx");

Disable scheduled update

Execute the following statement to disable the scheduled update feature for a materialized view:

ALTER MATERIALIZED VIEW mf_mv_refresh SET TBLPROPERTIES("enable_auto_refresh"="false");

View the update history of a materialized view

Execute the following statement to view the update history of a materialized view:

DESC EXTENDED mv;

The following result is returned. For more information about the materialized view, see Query information about a materialized view.

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

References

For more information about operations on materialized views, see Materialized view operations.
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.

Usage notes

Enable the scheduled update feature for a materialized view

Use the EVERY parameter

Use the refresh_interval_minutes parameter of TBLPROPERTIES

Use the refresh_cron parameter of TBLPROPERTIES

Change the interval for scheduled updates of a materialized view

Disable scheduled update

View the update history of a materialized view

References

Use the `refresh_interval_minutes` parameter of TBLPROPERTIES

Use the `refresh_cron` parameter of TBLPROPERTIES