Updates a 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 check the status of a materialized view, see GRANT LABEL.

  • 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 on a materialized view 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(<expression1>, <expressio2>...)];
  • Parameters
    • project_name: optional. 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. Log on to the MaxCompute console. In the top navigation bar, select a region and view the name of the MaxCompute project on the Project management tab.
    • mv_name: required. The name of the materialized view that you want to update.
    • expression: optional. 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: 
      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
    • project_name: optional. 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. Log on to the MaxCompute console. In the top navigation bar, select a region and view the name of the MaxCompute project on the Project management tab.
    • mv_name: required. The name of the materialized view whose lifecycle you want to update.
    • days: required. 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
    • project_name: optional. 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. Log on to the MaxCompute console. In the top navigation bar, select a region and view the name of the MaxCompute project on the Project management tab.
    • mv_name: required. The name of the materialized view for which you want to enable or disable the lifecycle feature.
    • pt_spec: optional. The partition information of the materialized view for which you want to enable or disable the lifecycle feature. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col indicates the column name, and partition_col_value indicates the column value.
    • enable|disable: required. 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 exists] partition <pt_spec> [partition <pt_spec>, partition <pt_spec>....];
  • Parameters
    • project_name: optional. 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. Log on to the MaxCompute console. In the top navigation bar, select a region and view the name of the MaxCompute project on the Project management tab.
    • mv_name: required. The name of the partitioned materialized view from which you want to drop one or more partitions.
    • if exists: optional. If you do not specify if exists and the materialized view from which you want to drop one or more partitions does not exist, an error is returned.
    • pt_spec: The partitions that you want to drop. You must specify at least one partition. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col indicates the column name, and partition_col_value indicates the column 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', ds<='20210105');

Related statements