This topic describes the operations on the partitions or columns in a MaxCompute table. You can perform thse operations based on your business requirements. This topic describes common operations on partitions or columns, such as add or delete a partition and add or change the name and comment of a column.

For more information about partition and column operations, see Partition and column operations. The following table describes common statements that are used for partition and column operations.

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 described in this topic 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.
Change the name and comment of a column Changes the name and 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, 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.

  • Syntax
    alter table <table_name> drop [if exists] partition <pt_spec>;
  • 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.
  • Example
    -- Delete the sales records of the China (Hangzhou) region in December 2013 from the sale_detail table.
    alter table sale_detail drop if exists partition (sale_date='201312',region='hangzhou'); 

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' );

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';