This topic describes the operations on partitions or columns in a MaxCompute table. You can perform these operations based on your business requirements.

The following table lists the commands that are used for operations on partitions or 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 run these commands on the following platforms:
Delete partitions Deletes partitions from an existing partitioned table.
Add columns or comments Adds columns or comments to an existing non-partitioned table or partitioned table.
Modify the name of a column Modifies the name of a column in an existing non-partitioned table or partitioned table.
Modify the comment of a column Modifies the comment of a column in an existing non-partitioned table or partitioned table.
Modify the name and comment of a column at the same time Modifies the name and comment of a column in an existing non-partitioned table or partitioned table at the same time.
Change LastDataModifiedTime of a partition Changes LastDataModifiedTime of a partition in a partitioned table.
Change the non-nullable property of a non-partition key column in a table Changes the non-nullable property of a non-partition key column in a table.
Change column values Changes partition column values of a partitioned table.
Merge partitions Merges multiple partitions of a partitioned table into one partition. This operation deletes the dimension information about the merged partitions and transfers data to a specified partition.

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, instead of the names of partition key columns.
  • 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 name of a partition key column, and partition_col_value indicates the value of a partition key column. 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 of 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 of the China (Hangzhou) 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. An error is returned because you must specify the two partition key columns sale_date and region.
      alter table sale_detail add if not exists partition (sale_date='20111011');

Delete partitions

Deletes partitions from an existing partitioned table.

MaxCompute allows you to delete partitions that meet a specified rule condition. If you want to delete one or more partitions that meet a rule condition at a time, you can use an expression to specify the condition, use the condition to match partitions, and delete the partitions at a time.

  • Limits
    • Each PARTITION partition_filtercondition clause can be used for only one partition column.
    • If you use an expression to specify PARTITION partition_filtercondition, only the built-in scalar function can be used for the expression.
  • Syntax
    • The condition is not specified.
      -- Delete one partition at a time.
      alter table <table_name> drop [if exists] partition <pt_spec>;
      -- Delete multiple partitions at a time.
      alter table <table_name> drop [if exists] partition <pt_spec>,partition <pt_spec>[,partition <pt_spec>....];
    • The 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 delete partitions.
    • if exists: optional. If you do not specify the if exists option and the partition that you want to delete does not exist, an error is returned.
    • pt_spec: required. The partitions that you want to delete. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col indicates the name of a partition key column, and partition_col_value indicates the value of a partition key column. The names of partition key columns are not case sensitive, but their values are case sensitive.
    • partition_filtercondition: the partition filter condition. This parameter is required when you specify the filter condition. It is not case-sensitive. Syntax:
      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 the partition key column.
      • relational_operators: the relational operator. For more information, see Operators.
      • partition_col_value: a value in the partition key column, which is a comparison value or regular expression. The data type of this value must be the same as that of the partition key column.
      • scalar(): the scalar function. The scalar function generates a scalar based on the input value, processes the value of partition_col, and uses relational_operators to compare the processed value with partition_col_value.
      • The condition that is used to delete partitions supports the following logical operators: NOT, AND, and OR. You can use PARTITION (NOT partition_filtercondition) to obtain the complementary set of the condition. You can use PARTITION (partition_filtercondition1 AND|OR partition_filtercondition2) to obtain the condition that is used to match the partitions you want to delete.
      • Multiple PARTITION partition_filtercondition clauses are supported. If these clauses are separated by commas (,), OR is used for each clause to obtain the condition that is used to match the partitions that you want to delete.
  • Examples
    • The condition is not specified.
      -- Delete a partition from the sale_detail table. The partition stores the sales record of Hangzhou in December 2013.
      alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'); 
      -- Delete two partitions from the sale_detail table. The partitions store the sales records of Hangzhou and Shanghai in December 2013.
      alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'),partition(sale_date='201312',region='shanghai');
    • The 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 sale_detail 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');
      -- Delete partitions from the sale_detail table.
      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');
      -- Delete 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');
      -- Delete partitions in batches and use regular expressions to match the partitions you want to delete.
      alter table sale_detail drop if exists partition(sale_date RLIKE '2019-\\d+-\\d+');
      -- Create the 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 region_sale_detail 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 delete multi-level partitions at the same 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 deleted.
      alter table region_sale_detail drop if exists partition(sale_date < '201911'),partition(region = 'beijing');
      -- Execute the following statement to delete 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 delete multi-level partitions at the same 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 multiple partition key columns in PARTITION partition_filtercondition, 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 comments

Adds columns or 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 add to the table.
    • type: required. The data type of the column that you add to the table.
    • col_comment: optional. The comment of the column that you 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' );

Modify the name of a column

Modifies the name of a column in an existing non-partitioned table or partitioned table.

  • Syntax
    alter table <table_name> change column <old_col_name> rename to <new_col_name>;
  • Parameters
    • table_name: required. The name of the table in which you want to modify the name of a column.
    • old_col_name: required. The name of the column you want to modify. 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.
  • Example
    -- Rename the customer_name column as customer in the sale_detail table.
    alter table sale_detail change column customer_name rename to customer;

Modify the comment of a column

Modifies the comment of a column in an existing non-partitioned table or partitioned table.

  • Syntax
    alter table <table_name> change column <col_name> comment '<col_comment>';
  • Parameters
    • table_name: required. The name of the table in which you want to modify the column comment
    • col_name: required. The name of the column whose comment you want to modify. The column specified by col_name must already exist in the table.
    • col_comment: required. The new comment of the column. The comment must be a valid string that is a maximum of 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
  • Example
    -- Modify the comment of the customer column in the sale_detail table.
    alter table sale_detail change column customer comment 'customer';

Change the name and comment of a column

Changes the name and 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.
  • Example
    -- Change the name of the customer_name column in the sale_detail table to customer_newname and the column comment to customer.
    alter table sale_detail change column customer_name customer_newname STRING comment 'customer';

Change the non-nullable property of a non-partition key column in a table

Changes the non-nullable property of a non-partition key column in a table. If the value of a non-partition key column in a table must not be null, MaxCompute allows you to set the column value to null by executing this statement.

You can execute the DESC EXTENDED table_name; statement to view the value of the Nullable property. This property determines whether the value of a non-partition key column can be null. If the value of the Nullable property is true, the column value can be null. If the value of the Nullable property is false, the column value must not be null.

  • Limits

    After you change the value of the Nullable property to true, you cannot restore the property setting. Proceed with caution.

  • Syntax
    alter table <table_name> change column <old_col_name> null;
  • Parameters
    • table_name: required. The name of the table in which you want to change the value of the Nullable property.
    • old_col_name: required. The name of the non-partition key column whose Nullable property you want to change. The column specified by old_col_name must already exist in the table.
  • Example
    -- Create a partitioned table. The value of the id column must not be null.
    create table null_test(id int not null, name string) partitioned by (ds string);
    -- Change the value of the id column to null.
    alter table null_test change column id null;

Change LastDataModifiedTime of a partition

MaxCompute SQL allows you to execute the TOUCH statement to change LastDataModifiedTime of a partition in a partitioned table. This operation changes LastDataModifiedTime to the current time. In this case, MaxCompute considers that data is updated, and the new lifecycle of the table or partition starts from the time specified by LastDataModifiedTime.

  • Limits

    If a table contains multi-level partitions, you must specify all partitions when you change LastDataModifiedTime of a partition in the table.

  • Syntax
    alter table <table_name> touch partition (<pt_spec>);
  • Parameters
    • table_name: required. The name of the partitioned table whose LastDataModifiedTime you want to modify. If the table does not exist, an error is returned.
    • pt_spec: required. The partition whose LastDataModifiedTime you want to modify. The parameter value is in the format of (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col indicates the column name, and partition_col_value indicates the column value. If the specified column name or column value does not exist, an error is returned.
  • Example
    -- Change LastDataModifiedTime of the partition with sale_date='201312', region='shanghai' in the sale_detail table.
    alter table sale_detail touch partition (sale_date='201312', region='shanghai');

Change column values

MaxCompute SQL allows you to execute the RENAME statement to change column values of a partition in a partitioned table.

  • Limits
    • This operation can change only column values instead of column names.
    • If a table contains multi-level partitions, you must specify all partitions when you change column values in partitions of this table.
  • Syntax
    alter table <table_name> partition (<pt_spec>) rename to partition (<new_pt_spec>);
  • Parameters
    • table_name: required. The name of the table in which you want to change column values in a partition.
    • pt_spec: required. The partition in which you want to change column values. The parameter value is in the format of (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col indicates the column name, and partition_col_value indicates the column value. If the specified column name or column value does not exist, an error is returned.
    • new_pt_spec: required. The new partition information. The parameter value is in the format of (partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...). partition_col indicates the column name, and new_partition_col_value indicates the new column value.
  • Example
    -- Change column values in a partition of the sale_detail table.
    alter table sale_detail partition (sale_date = '201312', region = 'hangzhou') rename to partition (sale_date = '201310', region = 'beijing');

Merge partitions

MaxCompute SQL allows you to execute the MERGE PARTITION statement to merge multiple partitions of a table into one partition. This operation deletes the dimension information about the merged partitions and transfers data to a specified partition.

  • Limits
    • Partitions in external tables cannot be merged. After partitions in a clustered table are merged into one partition, the partition does not have the clustering attribute.
    • You can merge a maximum of 4,000 partitions at a time.
  • Syntax
    alter table <table_name> merge [if exists] partition (<predicate>) [, partition(<predicate2>) ...] overwrite partition (<fullpartitionSpec>) [purge];
  • Parameters
    • table_name: required. The name of the partitioned table whose partitions you want to merge.
    • IF EXISTS: optional. If IF EXISTS is not specified and the partition does not exist, an error is returned when you merge partitions. If IF EXISTS is specified but no partitions meet the merge condition, a new partition cannot be generated after merging. If you merge partitions and modify source data by using a statement at the same time, an error is returned even if you specify IF EXISTS. The statement may be INSERT, RENAME, or DROP.
    • predicate: required. The condition that is used to match the partitions that you want to merge.
    • fullpartitionSpec: required. The partition that is generated after merging.
  • Example
    • Example 1: Merge the partitions that meet a specified condition into the destination partition.
      -- View the partitions that you want to merge in the partitioned table.
      show partitions intpstringstringstring;
      
      ds=20181101/hh=00/mm=00
      ds=20181101/hh=00/mm=10
      ds=20181101/hh=10/mm=00
      ds=20181101/hh=10/mm=10
      
      -- Merge all partitions that meet the hh='00' condition into the ds='20181101', hh='00', mm='00' partition.
      alter table intpstringstringstring merge partition(hh='00') overwrite partition(ds='20181101', hh='00', mm='00');
      
      -- View the destination partition after merging.
      show partitions intpstringstringstring;
      
      ds=20181101/hh=00/mm=00
      ds=20181101/hh=10/mm=00
      ds=20181101/hh=10/mm=10                    
    • Example 2: Merge multiple partitions that you specified into the destination partition.
      -- Merge multiple partitions that you specified.
      alter table intpstringstringstring merge if exists partition(ds='20181101', hh='00', mm='00'), partition(ds='20181101', hh='10', mm='00'),  partition(ds='20181101', hh='10', mm='10') overwrite partition(ds='20181101', hh='00', mm='00') purge;
      -- View the destination partition in the partitioned table.
      show partitions intpstringstringstring;
      
      ds=20181101/hh=00/mm=00