This topic describes how to run statements to add or delete partitions and add or modify columns in MaxCompute tables.

Add a partition

Use the following syntax to add a partition:
ALTER TABLE TABLE_NAME ADD [IF NOT EXISTS] PARTITION partition_spec
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...) ;
Note
  • The partition column name must be in lowercase.
  • This statement can only add a partition instead of a partition column.
  • If you do not specify the IF NOT EXISTS option and a partition with the same name already exists, an error is returned.
  • Currently, you can create up to 60,000 partitions in a single table in MaxCompute.
  • To add a partition to a table that has multi-level partitions, you must specify all partition column values.
The following examples show you the correct and incorrect statements for adding a partition to a partitioned table named sale_detail:
alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
-- This statement adds a partition to store the sales records of the Hangzhou region in December 2013.
alter table sale_detail add if not exists partition (sale_date='201312', region='shanghai');
-- This statement adds a partition to store the sales records of the Shanghai region in December 2013.
alter table sale_detail add if not exists partition(sale_date='20111011');
-- This statement specifies only the sale_date partition column. Therefore, an error is returned while adding a partition.
alter table sale_detail add if not exists partition(region='shanghai');
-- This statement specifies only the region partition column. Therefore, an error is returned while adding a partition.

Delete a partition

Use the following syntax to delete a partition:
ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
Note If you do not specify the IF EXISTS option and the target partition does not exist, an error is returned.
For example, the following statement shows how to delete a partition from the sale_detail table.
alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'); 
-- This statement deletes the sales records of the Hangzhou region in December 2013.

Add a column

Use the following syntax to add columns:
ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...) ;
Use the following syntax to add columns and column comments simultaneously:
ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX',col_name2 type2 comment 'XXX');
Note You cannot specify the order of a new column in the table. The new column is added as the last column by default.

Modify a column name

Use the following syntax to modify a column name:
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;
Note
  • The column specified by old_col_name must already exist in the table.
  • The table does not contain a column named new_col_name.

Modify the comment of a column or partition

Use the following syntax to modify the comment of a column or partition:
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;
Note A comment can be up to 1,024 bytes in length.

Modify the name and comment of a column simultaneously

Use the following syntax to modify the name and comment of a column simultaneously:
ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_type COMMENT column_comment;
Note
  • The column specified by old_col_name must already exist in the table.
  • The table does not contain a column named new_col_name.
  • A comment can be up to 1,024 bytes in length.

Modify the last update time of a partition

MaxCompute SQL allows you to run the TOUCH statement to modify the last update time of a partition. This statement modifies the LastDataModifiedTime setting of a partition to the current time.

Use the following syntax to modify the last update time of a partition:
ALTER TABLE table_name TOUCH PARTITION(partition_col='partition_col_value', ...) ;
Note
  • If the table specified by table_name or the partition column specified by partition_col does not exist, an error is returned.
  • If the partition column value specified by partition_col_value does not exist, an error is returned.
  • This statement also modifies the LastDataModifiedTime setting of the table. In this case, MaxCompute considers that the table and partition data has changed, and restarts the lifecycle of the table from the last update time.

Change a partition column value

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

Use the following syntax to change the partition column values in a table:
ALTER TABLE table_name PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...) 
RENAME TO PARTITION (partition_col1 = partition_col_newvalue1, partition_col2 = partiton_col_newvalue2, ...) ;
Note
  • This statement can only change partition column values instead of partition column names.
  • To change one or more partition column values in a table that has multi-level partitions, you must specify the partition column values of partitions at each level.

Merge partitions

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

  • Syntax
    ALTER TABLE <tableName> MERGE [IF EXISTS] PARTITION(<predicate>) [, PARTITION(<predicate2>) ...] OVERWRITE PARTITION(<fullPartitionSpec>) [PURGE];
  • Description
    • If you do not specify the IF EXISTS option and a certain partition does not exist, an error is returned.
    • If you specify the IF EXISTS option but no partition meets the merge condition, no new partition is generated.
    • If the source data is concurrently modified by operations such as INSERT, RENAME, and DROP when you run the preceding statement, an error is returned even if you have specified the IF EXISTS option.
  • Limits
    • You cannot merge partitions of an external table or a shard table. If you merge partitions of a clustered table, the attribute clustering of the table is removed.
    • You can merge up to 4,000 partitions at a time.
  • Examples
    Run the following statement to list the partitions in the tb_test table:
    odps@ jet_zwz>list partitions tb_test;
    
    ds=20181101/hh=00/mm=00
    ds=20181101/hh=00/mm=10
    ds=20181101/hh=10/mm=00
    ds=20181101/hh=10/mm=10
    
    OK
    odps@ jet_zwz>read intpstringstringstring;
    +------------+------------+------------+------------+
    | value      | ds         | hh         | mm         |
    +------------+------------+------------+------------+
    | 1          | 20181101   | 00         | 00         |
    | 1          | 20181101   | 00         | 10         |
    | 1          | 20181101   | 10         | 00         |
    | 1          | 20181101   | 10         | 10         |
    +------------+------------+------------+------------+
    Run the following statement to merge all partitions that meet the hh='00' condition into the ds=20181101/hh=00/mm=00 partition:
    odps@ jet_zwz>alter table intpstringstringstring merge partition(hh='00') overwrite partition(ds='20181101', hh='00', mm='00');
    
    ID = 20190404025755844g80qwa7a
    OK
    Run the following statement to list the partitions of the table after the merging:
    odps@ jet_zwz>list partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00
    ds=20181101/hh=10/mm=00
    ds=20181101/hh=10/mm=10
    
    OK
    Data in two partitions that meet the hh='00' condition is merged into the ds=20181101/hh=00/mm=00 partition.
    odps@ jet_zwz>read intpstringstringstring;
    +------------+------------+------------+------------+
    | value      | ds         | hh         | mm         |
    +------------+------------+------------+------------+
    | 1          | 20181101   | 00         | 00         |
    | 1          | 20181101   | 00         | 00         |
    | 1          | 20181101   | 10         | 00         |
    | 1          | 20181101   | 10         | 10         |
    +------------+------------+------------+------------+
    When you merge partitions, you can specify multiple predicate conditions. For example, you can run the following statement to merge all the remaining partitions to the ds=20181101/hh=00/mm=00 partition:
    odps@ jet_zwz>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;
    
    ID = 20190404034632854g431sqzt2
    OK
    
    odps@ jet_zwz>show partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00
    
    OK