Materialized views cache precomputed query results, but source table changes can make them stale. Scheduled refresh keeps a materialized view in sync with its source tables by running automatic refresh tasks at a fixed interval or Cron schedule.
How it works
A scheduled refresh is a background task that MaxCompute submits on your configured schedule. Completion time depends on cluster resources and task size.
Refresh scope
Partitioned materialized views -- If data changes in a partition of the partitioned source table that corresponds to the partition key columns, only the associated partition of the materialized view is refreshed. If data in other tables associated with the materialized view changes, a full refresh runs.
Non-partitioned materialized views -- MaxCompute checks whether source table data has changed since the last refresh. If no changes occurred, the refresh is skipped.
Overlap handling
If a refresh is still running when the next scheduled time arrives, MaxCompute cancels the pending refresh and triggers a new one at the following scheduled time.
Example with a 20-minute interval starting at 10:00:00:
| Scheduled time | Outcome |
|---|---|
10:00:00 | Refresh starts, completes at 10:30:00 |
10:20:00 | Canceled (previous refresh still in progress) |
10:40:00 | Refresh starts as scheduled |
MaxCompute may detect schedule configuration changes with a delay of up to 30 minutes. A scheduled refresh task might be submitted with this delay after you enable or modify the schedule.
Scheduling methods
Three methods control when a materialized view refreshes. All three require "enable_auto_refresh"="true" in TBLPROPERTIES.
| Method | Syntax | Interval unit | Best for |
|---|---|---|---|
EVERY clause (Recommended) | REFRESH EVERY N MINUTES/HOURS/DAYS | Minutes, hours, or days | Fixed intervals with readable syntax |
refresh_interval_minutes | TBLPROPERTIES("refresh_interval_minutes"="N") | Minutes only | Minute-level control through table properties |
refresh_cron | TBLPROPERTIES("refresh_cron"="expression") | Quartz Cron expression | Time-of-day schedules or advanced Cron patterns |
Enable scheduled refresh
Prerequisites
A MaxCompute project with sufficient compute resources
A source table with data
The following source table is used in all examples:
CREATE TABLE mf_refresh (id BIGINT, name STRING);
INSERT INTO TABLE mf_refresh VALUES (1,'kyle'),(2,'tom');Full syntax
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days]
[(col_name [COMMENT col_comment], ...)]
[DISABLE REWRITE]
[PARTITIONED BY (col_name [, col_name, ...])]
[CLUSTERED BY|RANGE CLUSTERED BY (col_name [, col_name, ...])
[SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])]
INTO number_of_buckets BUCKETS]
[REFRESH EVERY N MINUTES/HOURS/DAYS]
TBLPROPERTIES("enable_auto_refresh"="true"[, "refresh_interval_minutes"="N"][, "refresh_cron"="expression"])
AS <select_statement>;| Parameter | Description |
|---|---|
LIFECYCLE days | Number of days before the materialized view expires |
DISABLE REWRITE | Disables automatic query rewrite based on the materialized view |
PARTITIONED BY | Partition key columns for partitioned materialized views |
CLUSTERED BY / RANGE CLUSTERED BY | Shuffle and sort attributes. Requires INTO number_of_buckets BUCKETS |
REFRESH EVERY N MINUTES/HOURS/DAYS | Fixed refresh interval |
"enable_auto_refresh"="true" | Enables scheduled refresh. Required for all three methods |
"refresh_interval_minutes"="N" | Refresh interval in minutes |
"refresh_cron"="expression" | Quartz Cron expression for time-based scheduling. The expression uses 6 or 7 fields (seconds, minutes, hours, day-of-month, month, day-of-week, and optionally year). Standard 5-field Unix cron is not supported. For more information, see Cron expression examples |
Method 1: EVERY clause (Recommended)
Specify the refresh interval directly in the CREATE MATERIALIZED VIEW statement. The next refresh runs at the previous refresh time plus the specified interval.
Create a materialized view that refreshes every 10 minutes.
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;Query the materialized view to verify the initial data. Output:
SELECT * FROM mf_mv_refresh;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | +------------+------------+Insert new rows into the source table.
INSERT INTO TABLE mf_refresh VALUES (3,'jen'),(4,'cathy');Query the materialized view after 10 minutes. The new rows appear after the scheduled refresh completes. Output:
SELECT * FROM mf_mv_refresh;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 3 | jen | | 4 | cathy | +------------+------------+
Method 2: refresh_interval_minutes
Set the refresh interval in minutes through TBLPROPERTIES. The next refresh runs at the previous refresh time plus the specified number of minutes.
Create a materialized view that refreshes every 20 minutes.
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;Query the materialized view to verify the initial data. Output:
SELECT * FROM mf_mv_refresh2;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 3 | jen | | 4 | cathy | +------------+------------+Insert new rows into the source table.
INSERT INTO TABLE mf_refresh VALUES (5,'roger'),(6,'david');Query the materialized view after 20 minutes. The new rows appear after the scheduled refresh completes. Output:
SELECT * FROM mf_mv_refresh2;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 3 | jen | | 4 | cathy | | 5 | roger | | 6 | david | +------------+------------+
Method 3: refresh_cron
Use a Quartz Cron expression to define interval-based or time-of-day schedules. The refresh_cron value is a string in the Quartz Cron format.
Example: Refresh every 5 minutes
Create a materialized view that refreshes every 5 minutes.
CREATE MATERIALIZED VIEW IF NOT EXISTS mf_mv_refresh3 TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="* */5 * * * ? *") AS SELECT id, name FROM mf_refresh;Query the materialized view to verify the initial data. Output:
SELECT * FROM mf_mv_refresh3;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 3 | jen | | 4 | cathy | | 5 | roger | | 6 | david | +------------+------------+Insert new rows into the source table.
INSERT INTO TABLE mf_refresh VALUES (7,'ethan'),(8,'cal');Query the materialized view after 5 minutes. The new rows appear after the scheduled refresh completes. Output:
SELECT * FROM mf_mv_refresh3;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 7 | ethan | | 8 | cal | | 3 | jen | | 4 | cathy | | 5 | roger | | 6 | david | +------------+------------+
Example: Refresh daily at 20:00
Create a materialized view that refreshes at 20:00 every day.
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;Query the materialized view to verify the initial data. Output:
SELECT * FROM mf_mv_refresh4;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 7 | ethan | | 8 | cal | | 3 | jen | | 4 | cathy | | 5 | roger | | 6 | david | +------------+------------+Insert new rows into the source table.
INSERT INTO TABLE mf_refresh VALUES (9,'annie'),(10,'zoe');Query the materialized view after 20:00. The new rows appear after the scheduled refresh completes. Output:
SELECT * FROM mf_mv_refresh4;+------------+------------+ | id | name | +------------+------------+ | 1 | kyle | | 2 | tom | | 9 | annie | | 10 | zoe | | 7 | ethan | | 8 | cal | | 3 | jen | | 4 | cathy | | 5 | roger | | 6 | david | +------------+------------+
Modify the refresh schedule
Change the refresh interval or switch to a different scheduling method with ALTER 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 refresh
Set enable_auto_refresh to false:
ALTER MATERIALIZED VIEW mf_mv_refresh SET TBLPROPERTIES("enable_auto_refresh"="false");View refresh history
Run DESC EXTENDED to view refresh history and configuration details:
DESC EXTENDED mv;The output includes the AutoRefresh History section with the instance ID, status, start time, and end time for each refresh run:
+------------------------------------------------------------------------------------+
| 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 |
+------------------------------------------------------------------------------------+In MaxCompute, TERMINATED means that the task execution finished. This status covers both successful and failed runs. For more details about materialized view metadata, see Query information about a materialized view.