MaxCompute:ALTER MATERIALIZED VIEW

Background information

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 the status of a materialized view.

• Usage notes

  • You can perform only full updates on a materialized. You cannot perform incremental updates on a materialized view.

  • You can trigger scheduled updates in the DataWorks console. For more information about how to trigger scheduling operations in the DataWorks console, see Scheduling configuration.

• 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 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 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. Sample statement:

    -- 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. Sample statement:

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

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

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

Change the lifecycle of an existing materialized view

MaxCompute allows you to change 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	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 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 mv SET LIFECYCLE 10;

Enable or disable the lifecycle feature for an existing materialized view

MaxCompute allows you to enable or disable the lifecycle feature for an existing materialized view.

• 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 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 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. Sample statement:

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

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

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

Drop partitions from an existing materialized view

MaxCompute allows you to drop one or more partitions from an existing materialized view.

• Syntax

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

• Parameters

  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 Projects page.
  mv_name	Yes	The name of the partitioned materialized view from which you want to drop one or more 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 mv DROP PARTITION (ds='20210101');

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

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

Related statements

• CREATE MATERIALIZED VIEW: creates a materialized view that supports clustering or partitioning based on the data for materialized view scenarios.

• DESC TABLE/VIEW: views the information about a materialized view in a MaxCompute project.

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

• DROP MATERIALIZED VIEW: drops an existing materialized view.