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');
- Example 1: Add a partition to the sale_detail table. The partition stores the sales
records of the China (Hangzhou) region in December 2013.
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' );
- Example 1: Add two columns to the sale_detail table.
Change the name and comment of a column
- 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';