Partition and column operations

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

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