ALTER MATERIALIZED VIEW

Command overview

Refresh a materialized view

Insert, overwrite, update, or delete operations on the underlying table or partition invalidate a materialized view, preventing query rewrite. Check the materialized view status and refresh it when invalid. For more information, see Query the status of a materialized view.

• Usage notes

  • Materialized views support only full refresh, not incremental refresh.

  • Use the DataWorks scheduling feature to refresh materialized views on a schedule. For more information, see Scheduling configurations.

• Syntax

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

• Parameters

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project that contains the materialized view. If you omit this parameter, the current MaxCompute project is used. To find the project name, log on to the MaxCompute console, select the appropriate region in the top-left corner, and then navigate to the Projects page.
  mv_name	Yes	The name of the materialized view to refresh.
  ds	No	The name of the partition column of the materialized view.
  max_pt	No	A function that returns the maximum partition value of the specified table_name. The table_name can refer to a table or a materialized view.
  expression	No	An expression that filters the partitions to refresh in a partitioned materialized view.

• Examples

  • Example 1: Refresh a non-partitioned materialized view. Example command:

    -- 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 non-partitioned materialized view. 
    ALTER MATERIALIZED VIEW count_mv rebuild; 

  • Example 2: Refresh a specific partition of a partitioned materialized view. Example command:

    ALTER MATERIALIZED VIEW mv REBUILD PARTITION (ds='20210101');

  • Example 3: Refresh partitions that match a specified condition. Example command:

    ALTER MATERIALIZED VIEW mv REBUILD PARTITION(ds>='20210101', ds<='20210105');

Change the lifecycle of a materialized view

Specify a new lifecycle duration in days for a materialized view.

• Syntax

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

• Parameters

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project that contains the materialized view. If you omit this parameter, the current MaxCompute project is used. To find the project name, log on to the MaxCompute console, select the appropriate region in the top-left corner, and then navigate to the Projects page.
  mv_name	Yes	The name of the materialized view to update.
  days	Yes	The new lifecycle for the materialized view, in days.

• Example

  -- Set the lifecycle of the materialized view to 10 days.
  ALTER MATERIALIZED VIEW mv SET LIFECYCLE 10;

Enable or disable lifecycle

Control whether a materialized view or a specific partition expires automatically.

• Syntax

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

• Parameters

  Parameter	Required	Description
  project_name	No	The name of the MaxCompute project that contains the materialized view. If you omit this parameter, the current MaxCompute project is used. To find the project name, log on to the MaxCompute console, select the appropriate region in the top-left corner, and then navigate to the Projects page.
  mv_name	Yes	The name of the materialized view for which to enable or disable the lifecycle.
  pt_spec	No	The partition for which to enable or disable the lifecycle. If you omit this parameter, the operation applies to the entire materialized view. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition column and partition_col_value is its value.
  enable|disable	Yes	enable enables the lifecycle rule. disable disables the lifecycle rule, preventing the specified partition or materialized view from expiring automatically.

• Examples

  • Example 1: Enable lifecycle management for a materialized view. Example command:

    ALTER MATERIALIZED VIEW mv PARTITION (ds='20210101') enable LIFECYCLE;

  • Example 2: Disable lifecycle management for a materialized view. Example command:

    ALTER MATERIALIZED VIEW mv PARTITION (ds='20210101') disable LIFECYCLE;

Drop materialized view partitions

Remove specific 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	The name of the MaxCompute project that contains the materialized view. If you omit this parameter, the current MaxCompute project is used. To find the project name, log on to the MaxCompute console, select the appropriate region in the top-left corner, and then navigate to the Projects page.
  mv_name	Yes	The name of the partitioned materialized view from which to drop partitions.
  IF EXISTS	No	If you specify IF EXISTS, the command does not report an error if the specified partition does not exist.
  pt_spec	Yes	The partitions to drop. You must specify at least one partition. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition column, and partition_col_value is its value.

• Examples

  • Example 1: Drop a specific partition of a partitioned materialized view. Example command:

    ALTER MATERIALIZED VIEW mv DROP PARTITION (ds='20210101');

  • Example 2: Drop partitions that match a specified condition. Example command:

    ALTER MATERIALIZED VIEW mv DROP PARTITION (ds>='20210101' AND ds<='20210105');

Related commands

• CREATE MATERIALIZED VIEW: Creates a materialized view from a query. This command supports defining partitions and clustering properties.

• DESC TABLE/VIEW: Displays metadata about a materialized view.

• SELECT MATERIALIZED VIEW: Queries the status of a materialized view.

• DROP MATERIALIZED VIEW: Deletes a materialized view.