Modifies an existing materialized view. Supported operations:
Refresh a materialized view — rebuild the view data after the underlying table is modified
Change the lifecycle — update the number of days before the view expires
Enable or disable the lifecycle — control whether lifecycle management applies to the view or a specific partition
Drop partitions — remove one or more partitions from a partitioned materialized view
Refresh a materialized view
A materialized view becomes invalid after an INSERT, OVERWRITE, UPDATE, or DELETE operation on its underlying table or partition. An invalid materialized view cannot be used for query rewrite. Check the view status first, then run ALTER MATERIALIZED VIEW ... REBUILD to refresh it. For details on checking status, see Query the status of a materialized view.
Only full refreshes are supported. Incremental refreshes are not supported. To automate refreshes on a schedule, configure a scheduled refresh in the DataWorks console.
Syntax
ALTER MATERIALIZED VIEW [<project_name>.]<mv_name>
REBUILD [PARTITION (<ds>=max_pt(<table_name>), <expression1>...)];Parameters
| Parameter | Required | Description |
|---|---|---|
project_name | No | Name of the MaxCompute project. Defaults to the current project. To find the project name, log on to the MaxCompute console, select a region in the top navigation bar, and view the name on the Projects page. |
mv_name | Yes | Name of the materialized view to refresh. |
ds | No | Name of the partition column in the materialized view. |
max_pt | No | Maximum partition value of the table or materialized view specified by table_name. |
expression | No | Expression that specifies which partitions to refresh. Required for partitioned materialized views. |
Examples
All three examples use ALTER MATERIALIZED VIEW ... REBUILD to trigger a full refresh.
Example 1: Refresh 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;
-- Refresh the materialized view.
ALTER MATERIALIZED VIEW count_mv REBUILD;Example 2: Refresh a specific partition
ALTER MATERIALIZED VIEW mv REBUILD PARTITION (ds='20210101');Only the partition where ds='20210101' is refreshed. Other partitions are unaffected.
Example 3: Refresh partitions matching a condition
ALTER MATERIALIZED VIEW mv REBUILD PARTITION (ds>='20210101', ds<='20210105');All partitions where ds falls within the specified range are refreshed.
Change the lifecycle
Updates the lifecycle of an existing materialized view.
Syntax
ALTER MATERIALIZED VIEW [<project_name>.]<mv_name> SET LIFECYCLE <days>;Parameters
| Parameter | Required | Description |
|---|---|---|
project_name | No | Name of the MaxCompute project. Defaults to the current project. |
mv_name | Yes | Name of the materialized view to update. |
days | Yes | New lifecycle value, in days. |
Example
-- Set the lifecycle to 10 days.
ALTER MATERIALIZED VIEW mv SET LIFECYCLE 10;Enable or disable the lifecycle
Enables or disables lifecycle management for a materialized view or a specific partition.
Syntax
ALTER MATERIALIZED VIEW [<project_name>.]<mv_name> [<pt_spec>] ENABLE|DISABLE LIFECYCLE;Parameters
| Parameter | Required | Description |
|---|---|---|
project_name | No | Name of the MaxCompute project. Defaults to the current project. |
mv_name | Yes | Name of the materialized view. |
pt_spec | No | Partition to target. Format: (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition column name; partition_col_value is the partition value. If omitted, the operation applies to the entire materialized view. |
ENABLE|DISABLE | Yes | ENABLE activates lifecycle management for the view or partition. DISABLE deactivates it; lifecycle management is not required for the materialized view or partition. |
Examples
Example 1: Enable lifecycle for a partition
ALTER MATERIALIZED VIEW mv PARTITION (ds='20210101') ENABLE LIFECYCLE;Lifecycle management is activated for the ds='20210101' partition.
Example 2: Disable lifecycle for a partition
ALTER MATERIALIZED VIEW mv PARTITION (ds='20210101') DISABLE LIFECYCLE;The ds='20210101' partition is excluded from lifecycle management and will not expire automatically.
Drop partitions
Removes one or more partitions from a partitioned materialized view.
Syntax
ALTER MATERIALIZED VIEW [<project_name>.]<mv_name>
DROP [IF EXISTS] PARTITION <pt_spec> [PARTITION <pt_spec>, PARTITION <pt_spec>...];Parameters
| Parameter | Required | Description |
|---|---|---|
project_name | No | Name of the MaxCompute project. Defaults to the current project. |
mv_name | Yes | Name of the partitioned materialized view. |
IF EXISTS | No | If omitted and the materialized view does not exist, an error is returned. |
pt_spec | Yes | Partition to drop. At least one partition must be specified. Format: (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). |
Examples
Example 1: Drop a specific partition
ALTER MATERIALIZED VIEW mv DROP PARTITION (ds='20210101');The ds='20210101' partition is removed from the materialized view.
Example 2: Drop partitions matching a condition
ALTER MATERIALIZED VIEW mv DROP PARTITION (ds>='20210101' AND ds<='20210105');All partitions where ds falls within the specified range are removed.
What's next
CREATE MATERIALIZED VIEW — create a materialized view with clustering or partitioning
DESC TABLE/VIEW — view metadata for a materialized view
SELECT MATERIALIZED VIEW — query the status of a materialized view
DROP MATERIALIZED VIEW — delete an existing materialized view