This topic describes common operations on partitions or columns in a MaxCompute table, such as adding or deleting a partition and adding or changing the name and comment of a column. You can perform these operations based on your business requirements.

For more information about partition and column operations, see Partition and column operations. The following table describes common statements that are used for the operations on partitions and columns in a MaxCompute table.

Operation Description Role Operation platform
Add partitions Adds partitions to an existing partitioned table. Users who have the ALTER permission on tables You can execute the statements that are described in this topic on the following platforms:
Delete partitions Deletes partitions from an existing partitioned table.
Add columns or column comments Adds columns or column comments to an existing non-partitioned table or partitioned table.
Change the name or comment of a column Changes the name or comment of a column in a non-partitioned table or partitioned table.

Add partitions

Adds partitions to an existing partitioned table.

  • Limits
    • A MaxCompute table can have a maximum of 60,000 partitions.
    • To add the values of partition key columns to a table that has multi-level partitions, you must specify all the partitions.
    • This operation can add only the values of partition key columns. The names of partition key columns cannot be added.
  • Syntax
    alter table <table_name> add [if not exists] partition <pt_spec> [partition <pt_spec> partition <pt_spec>...];
  • Parameters
    • table_name: required. The name of the partitioned table to which you want to add partitions.
    • if not exists: optional. If you do not specify the if not exists option and a partition with the same name already exists, this operation fails and an error is returned.
    • pt_spec: required. The partitions that you want to add. 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. The names of partition key columns are not case-sensitive, but their values are case-sensitive.
  • Examples
    • Example 1: Add a partition to the sale_detail table. The partition stores the sales records in the China (Hangzhou) region in December 2013.
      alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
    • Example 2: Add two partitions to the sale_detail table. The partitions store the sales records in the China (Beijing) and China (Shanghai) regions in December 2013.
      alter table sale_detail add if not exists partition (sale_date='201312', region='beijing') partition (sale_date='201312', region='shanghai');
    • Example 3: Add one or more partitions to the sale_detail table and specify only the partition key column sale_date for the partitions. An error is returned because you must specify the two partition key columns sale_date and region for the partitions.
      alter table sale_detail add if not exists partition (sale_date='20111011');

Drop partitions

Drops partitions from an existing partitioned table.

MaxCompute allows you to drop partitions based on a specified filter condition. If you want to drop multiple partitions that meet a specified filter condition at a time, you can use an expression to specify the filter condition, use the filter condition to match partitions, and drop the partitions at a time.

  • Limits
    • You can specify the information of only one partition key column in a PARTITION (<partition_filtercondition>) clause.
    • If you use an expression to specify PARTITION (<partition_filtercondition>), the function used in the expression must be a built-in scalar function.
  • Usage notes
    • After you drop a partition, the volume of stored data in a MaxCompute project decreases.
    • You can specify a lifecycle for a partitioned table. This way, MaxCompute automatically reclaims partitions whose data is not updated within the time specified by the lifecycle. For more information about the lifecycle, see Lifecycle.
  • Syntax
    • The filter condition is not specified.
      -- Drop one partition at a time. 
      alter table <table_name> drop [if exists] partition <pt_spec>;
      -- Drop multiple partitions at a time. 
      alter table <table_name> drop [if exists] partition <pt_spec>,partition <pt_spec>[,partition <pt_spec>....];
    • The filter condition is specified.
      alter table <table_name> drop [if exists] partition <partition_filtercondition>;
  • Parameters
    • table_name: required. The name of the partitioned table from which you want to drop partitions.
    • if exists: optional. If you do not specify the if exists option and the partition that you want to drop does not exist, an error is returned.
    • pec: required. The partitions that you want to drop. 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. partition_col_value indicates the column value. The names of partition key columns are not case sensitive, but their values are case sensitive.
    • partition_filtercondition: the filter condition. This parameter is required when you specify the filter condition. This parameter is not case-sensitive. The parameter is in the following format:
      partition_filtercondition
          : partition (<partition_col> <relational_operators> <partition_col_value>)
          | partition (scalar(<partition_col>) <relational_operators> <partition_col_value>)
          | partition (<partition_filtercondition1> AND|OR <partition_filtercondition2>)
          | partition (NOT <partition_filtercondition>)
          | partition (<partition_filtercondition1>)[,partition (<partition_filtercondition2>), ...]
      • partition_col: the name of a partition key column.
      • relational_operators: the relational operator. For more information, see Operators.
      • partition_col_value: a value in the partition key column. The value may be a comparison value or regular expression. The data type of this value must be the same as the data type of the partition key column.
      • scalar(): the scalar function. The scalar function generates a scalar based on the input value, processes the values in the column specified by partition_col, and uses relational_operators to compare the processed values with partition_col_value.
      • The filter conditions that are used to drop partitions support the logical operators NOT, AND, and OR. You can use PARTITION (<NOT partition_filtercondition>) to obtain the complementary set of the filter conditions that you specified. You can use PARTITION (<partition_filtercondition1 AND|OR partition_filtercondition2>) to obtain the condition that is used to match the partitions you want to drop.
      • Multiple PARTITION (<partition_filtercondition>) clauses are supported. If these clauses are separated by commas (,), the logical relationship between the clauses is OR. The filter condition is obtained based on the OR logical relationship and used to match the partitions that you want to drop.
  • Examples
    • The filter condition is not specified.
      -- Drop a partition from the sale_detail table. The partition stores the sales record in the China (Hangzhou) region in December 2013. 
      alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'); 
      -- Drop two partitions from the sale_detail table. The partitions store the sales records in the China (Hangzhou) and China (Shanghai) regions in December 2013. 
      alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'),partition(sale_date='201312',region='shanghai');
    • The filter condition is specified.
      -- Create a partitioned table named sale_detail. 
      create table if not exists sale_detail(
      shop_name     STRING,
      customer_id   STRING,
      total_price   DOUBLE)
      partitioned by (sale_date STRING);
      -- Add partitions to the table. 
      alter table sale_detail add if not exists
      partition (sale_date= '201910')
      partition (sale_date= '201911')
      partition (sale_date= '201912')
      partition (sale_date= '202001')
      partition (sale_date= '202002')
      partition (sale_date= '202003')
      partition (sale_date= '202004')
      partition (sale_date= '202005')
      partition (sale_date= '202006')
      partition (sale_date= '202007');
      -- Drop partitions from the table at a time. 
      alter table sale_detail drop if exists partition(sale_date < '201911');
      alter table sale_detail drop if exists partition(sale_date >= '202007');
      alter table sale_detail drop if exists partition(sale_date LIKE '20191%');
      alter table sale_detail drop if exists partition(sale_date IN ('202002','202004','202006'));
      alter table sale_detail drop if exists partition(sale_date BETWEEN '202001' AND '202007');
      alter table sale_detail drop if exists partition(substr(sale_date, 1, 4) = '2020');
      alter table sale_detail drop if exists partition(sale_date < '201912' OR sale_date >= '202006');
      alter table sale_detail drop if exists partition(sale_date > '201912' AND sale_date <= '202004');
      alter table sale_detail drop if exists partition(NOT sale_date > '202004');
      -- Drop partitions by using multiple PARTITION (<partition_filtercondition>) clauses. The logical relationship between these clauses is OR. 
      alter table sale_detail drop if exists partition(sale_date < '201911'), partition(sale_date >= '202007');
      -- Add partitions in other formats. 
      alter table sale_detail add IF NOT EXISTS
      partition (sale_date= '2019-10-05') 
      partition (sale_date= '2019-10-06') 
      partition (sale_date= '2019-10-07');
      -- Use regular expressions to match the partitions that you want to drop and drop these partitions at a time. 
      alter table sale_detail drop if exists partition(sale_date RLIKE '2019-\\d+-\\d+');
      -- Create a table named region_sale_detail table. The table contains multi-level partitions. 
      create table if not exists region_sale_detail(
      shop_name     STRING,
      customer_id   STRING,
      total_price   DOUBLE)
      partitioned by (sale_date STRING , region STRING );
      -- Add partitions to the table. 
      alter table region_sale_detail add IF NOT EXISTS
      partition (sale_date= '201910',region = 'shanghai')
      partition (sale_date= '201911',region = 'shanghai')
      partition (sale_date= '201912',region = 'shanghai')
      partition (sale_date= '202001',region = 'shanghai')
      partition (sale_date= '202002',region = 'shanghai')
      partition (sale_date= '201910',region = 'beijing')
      partition (sale_date= '201911',region = 'beijing')
      partition (sale_date= '201912',region = 'beijing')
      partition (sale_date= '202001',region = 'beijing')
      partition (sale_date= '202002',region = 'beijing');
      -- Execute the following statement to drop multi-level partitions at a time. The logical relationship between the two PARTITION (<partition_filtercondition>) clauses is OR. After the statement is executed, the partitions in which the value of the sale_date column is earlier than 201911 and the partitions in which the value of the region column is beijing are dropped. 
      alter table region_sale_detail drop if exists partition(sale_date < '201911'),partition(region = 'beijing');
      -- Execute the following statement to drop a partition in which the value of the sale_date column is earlier than 201911 and the value of the region column is beijing. 
      alter table region_sale_detail drop if exists partition(sale_date < '201911', region = 'beijing');
      When you drop multi-level partitions at a time, you cannot specify a filter condition that is based on multiple partition key columns in one PARTITION partition_filtercondition clause. Otherwise, the following error is returned: FAILED: ODPS-0130071:[1,82] Semantic analysis exception - invalid column reference region, partition expression must have one and only one column reference.
      -- If you specify the information of multiple partition key columns in a PARTITION (<partition_filtercondition>) clause, an error is returned. Example of incorrect usage: 
      alter table region_sale_detail drop if exists partition(sale_date < '201911' AND region = 'beijing');

Add columns or column comments

Adds columns or column comments to an existing non-partitioned table or partitioned table.

  • Syntax
    alter table <table_name> add columns (<col_name1> <type1> comment ['<col_comment>'][, <col_name2> <type2> comment '<col_comment>'...]);
  • Parameters
    • table_name: required. The name of the table to which you want to add columns. You cannot specify the placement of a new column in the table. By default, the new column is added as the last column.
    • col_name: required. The name of the column that you want to add to the table.
    • type: required. The data type of the column that you want to add to the table.
    • col_comment: optional. The comment of the column that you want to add to the table.
  • Examples
    • Example 1: Add two columns to the sale_detail table.
      alter table sale_detail add columns (customer_name STRING, education BIGINT);
    • Example 2: Add two columns and their comments to the sale_detail table.
      alter table sale_detail add columns (customer_name STRING comment 'Customer', education BIGINT comment 'Education' );

Change the name and comment of a column

Changes the name or comment of a column in a non-partitioned table or partitioned table.
  • Syntax
    alter table <table_name> change column <old_col_name> <new_col_name> <column_type> comment '<col_comment>';
  • Parameters
    • table_name: required. The name of the table in which you want to change the name and comment of a column.
    • old_col_name: required. The original name of the column whose name and comment need to be changed. The column specified by old_col_name must already exist in the table.
    • new_col_name: required. The new name of the column. The table does not contain a column named new_col_name.
    • column_type: required. The data type of the column.
    • col_comment: optional. The new comment of the column. The maximum size of a comment is 1,024 bytes.
  • Examples
    -- Change the name of the customer_name column in the sale_detail table to customer_newname and the comment of the column to customer. 
    alter table sale_detail change column customer_name customer_newname STRING comment 'customer';