MaxCompute: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 parameter 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 column name. partition_col_value indicates the column value. 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 in 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 in 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');

Drop partitions

Drops partitions from an existing partitioned table.

MaxCompute allows you to drop partitions based on a specified filter condition. If you want to drop multiple partitions that meet a specified filter condition at a time, you can use an expression to specify the filter condition, use the filter condition to match partitions, and drop the partitions at a time.

• Limits
  • You can specify the information of only one partition key column in a PARTITION (<partition_filtercondition>) clause.
  • If you use an expression to specify PARTITION (<partition_filtercondition>), the function used in the expression must be a built-in scalar function.
• Precautions
  • After you drop a partition from a table in your MaxCompute project, the volume of stored data in your MaxCompute project decreases.
  • You can specify a lifecycle for a partitioned table. This way, MaxCompute automatically reclaims partitions whose data is not updated within the time specified by the lifecycle. For more information about the lifecycle, see Lifecycle.
• Syntax
  • The filter condition is not specified.

    -- Drop one partition at a time. 
    alter table <table_name> drop [if exists] partition <pt_spec>;
    -- Drop multiple partitions at a time. 
    alter table <table_name> drop [if exists] partition <pt_spec>,partition <pt_spec>[,partition <pt_spec>....];

  • The filter 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 drop partitions.
  • if exists: optional. If you do not specify the if exists parameter and the partition that you want to drop does not exist, an error is returned.
  • pt_spec: required. The partitions that you want to drop. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col indicates the column name, and partition_col_value indicates the column value. The names of partition key columns are not case sensitive, but their values are case sensitive.
  • partition_filtercondition: the filter condition. This parameter is required when you specify the filter condition. This parameter is not case-sensitive. The parameter is in the following format:

    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 a partition key column.
    • relational_operators: the relational operator. For more information, see Operator.
    • partition_col_value: a value in the partition key column. The value may be a comparison value or regular expression. The data type of this value must be the same as the data type of the partition key column.
    • scalar(): the scalar function. The scalar function generates a scalar based on the input value, processes the values in the column specified by partition_col, and uses relational_operators to compare the processed values with partition_col_value.
    • The filter conditions that are used to drop partitions support the logical operators NOT, AND, and OR. You can use PARTITION (<NOT partition_filtercondition>) to obtain the complementary set of the filter conditions that you specified. You can use PARTITION (<partition_filtercondition1 ANDOR partition_filtercondition2>) to obtain the condition that is used to match the partitions you want to drop.
    • Multiple PARTITION (<partition_filtercondition>) clauses are supported. If these clauses are separated by commas (,), the logical relationship between the clauses is OR. The filter condition is obtained based on the OR logical relationship and used to match the partitions that you want to drop.
• Examples
  • The filter condition is not specified.

    -- Drop a partition from the sale_detail table. The partition stores the sales record in the China (Hangzhou) region in December 2013. 
    alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'); 
    -- Drop two partitions from the sale_detail table. The partitions store the sales records in the China (Hangzhou) and China (Shanghai) regions in December 2013. 
    alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'),partition(sale_date='201312',region='shanghai');

  • The filter 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 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');
    -- Drop partitions from the table at a time. 
    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');
    -- Drop 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');
    -- Use regular expressions to match the partitions that you want to drop and drop these partitions at a time. 
    alter table sale_detail drop if exists partition(sale_date RLIKE '2019-\\d+-\\d+');
    -- Create a table named region_sale_detail. 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 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 drop multi-level partitions at a 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 dropped. 
    alter table region_sale_detail drop if exists partition(sale_date < '201911'),partition(region = 'beijing');
    -- Execute the following statement to drop 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 drop multi-level partitions at a 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 the information of multiple partition key columns in a PARTITION (<partition_filtercondition>) clause, 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 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 position 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' );

  • Example 3: Add a column of a complex data type to the sale_detail table.

    alter table sale_detail add columns (region struct<province:string, area:string>);

Change the name and comment of a column