MaxCompute:Partition operations

Applicability

• Data types: You can set fields of the TINYINT, SMALLINT, INT, BIGINT, CHAR, VARCHAR, and STRING data types as partition key columns.

• Partition levels per table: A table can have a maximum of six partition levels.

• Partitions per table: A table can have a maximum of 60,000 partitions.

• Partitions per query: You can query a maximum of 10,000 partitions at a time.

• You cannot merge partitions of a partitioned transactional table.

Add partitions

Adds partitions to an existing partitioned table.

• Limits

  • 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

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table to which you want to add partitions.
  IF NOT EXISTS	No	If you do not specify IF NOT EXISTS and a partition with the same name already exists, this operation fails and an error is returned.
  pt_spec	Yes	The partition to add. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value. Partition fields are not case-sensitive, but partition values are case-sensitive.

• Examples

  Create a standard partitioned table named sale_detail. For more information about how to create and drop tables, see Create and drop tables.

  CREATE TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  PARTITIONED BY (sale_date STRING, region STRING);

  • Example 1: Add a partition to the sale_detail table to store sales records from the Hangzhou region in December 2025.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='202512', region='hangzhou');

  • Example 2: Add two partitions to the sale_detail table at the same time to store sales records from the Beijing and Shanghai regions in December 2025.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='202512', region='beijing') PARTITION (sale_date='202512', region='shanghai');

  • Example 3: Add a partition to the sale_detail table and specify only the sale_date partition field. An error is returned because you must specify both the sale_date and region partition fields.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='20260111');
    
    -- The following error message is returned:
    FAILED: ODPS-0130071:[1,58] Semantic analysis exception - provided partition spec does not match table partition spec

  • Example 4: Add a partition to a Delta table.

    -- Create a Delta table.
    CREATE TABLE delta_table_test (
      pk BIGINT NOT NULL PRIMARY KEY, 
      val BIGINT NOT NULL) 
      PARTITIONED BY (dd STRING, hh STRING) 
      TBLPROPERTIES ("transactional"="true"); 
                 
    -- Add a partition.           
    ALTER TABLE delta_table_test ADD PARTITION (dd='01', hh='01');

  • Example 5: Change the properties of a Delta table.

    -- Update the number of buckets. This operation is supported only for partitioned tables.
    ALTER TABLE delta_table_test SET tblproperties("write.bucket.num"="64");
    
    -- Update the retain property.
    ALTER TABLE delta_table_test SET tblproperties("acid.data.retain.hours"="60");

Change the value of LastModifiedTime for a partition

MaxCompute SQL provides the touch operation to modify the LastModifiedTime of a partition in a partitioned table. This operation changes the LastModifiedTime to the current time. MaxCompute then considers the data to be updated and recalculates the lifecycle.

• Limits

  For multi-level partitioned tables, you must specify all partitions.

• Syntax

  ALTER TABLE <table_name> touch PARTITION (<pt_spec>);

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table for which you want to change the value of LastModifiedTime. If the table does not exist, an error is returned.
  pt_spec	Yes	The partition whose update time you want to modify. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value. If the specified partition field or partition value does not exist, an error is returned.

• Examples

  -- Modify the LastModifiedTime of the partition where sale_date='202512' and region='shanghai' in the sale_detail table.
  ALTER TABLE sale_detail touch PARTITION (sale_date='202512', region='shanghai');

Modify partition value

MaxCompute SQL supports the rename operation to change the partition values of a partitioned table.

• Limits

  • You can only modify the value of a partition key column, not its name.

  • If a table has multi-level partitions, you must specify all partitions.

• Syntax

  ALTER TABLE <table_name> PARTITION (<pt_spec>) rename TO PARTITION (<new_pt_spec>);

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table in which you want to change a value in a partition key column.
  pt_spec	Yes	The partition whose values you want to modify. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value. If the specified partition field or partition value does not exist, an error is returned.
  new_pt_spec	Yes	The modified partition information. The format is (partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...). partition_col is the partition field, and new_partition_col_value is the new partition value.

• Examples

  -- Modify the partition values of the sale_detail table.
  ALTER TABLE sale_detail PARTITION (sale_date = '201312', region = 'hangzhou') rename TO PARTITION (sale_date = '201310', region = 'beijing');

Merge partitions

MaxCompute SQL provides the merge partition operation to merge partitions of a partitioned table. This operation merges multiple partitions of the same partitioned table into a single partition, deletes the dimension information of the merged partitions, and moves the data to the specified destination partition.

• Limits

  • Foreign tables are not supported, and when partitions of a clustered table are merged, they lose their clustered property.

  • You can merge a maximum of 4,000 partitions at a time.

• Syntax

  ALTER TABLE <table_name> MERGE [IF EXISTS] PARTITION (<predicate>) [, PARTITION(<predicate2>) ...] overwrite PARTITION (<fullpartitionSpec>) [purge];

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table whose partitions you want to merge.
  IF EXISTS	No	If you do not specify IF EXISTS and the partition does not exist, the operation fails and an error is returned. If you specify IF EXISTS but no partitions meet the merge condition, no new partition is generated. If the source data is concurrently modified by an operation such as insert, rename, or drop during the merge, an error is returned even if you specify IF EXISTS.
  predicate	Yes	The condition that is used to match the partitions that you want to merge.
  fullpartitionSpec	Yes	Target partition details.
  purge	No	Optional keyword. If you configure this parameter, the session directory is cleared. By default, logs that are generated in the last three days are deleted. For more information, see Purge.

• Examples

  • Example 1: Merge partitions that meet a specified condition into a destination partition.

    -- View the partitions of the table.
    SHOW PARTITIONS sale_detail;
    
    -- Sample result:
    sale_date=202512/region=beijing
    sale_date=202512/region=shanghai
    sale_date=202602/region=beijin
    
    -- Merge all partitions that meet the condition sale_date='202512' into the partition where sale_date='202601' and region='hangzhou'.
    ALTER TABLE sale_detail MERGE PARTITION(sale_date='202512') overwrite PARTITION(sale_date='202601', region='hangzhou');
    
    -- View the partitions after the merge.
    SHOW PARTITIONS sale_detail;
    
    -- Sample result:
    sale_date=202601/region=hangzhou
    sale_date=202602/region=beijing                   

  • Example 2: Merge multiple specified partitions into a destination partition.

    -- Merge multiple specified partitions.
    ALTER TABLE sale_detail MERGE IF EXISTS 
      PARTITION(sale_date='202601', region='hangzhou'), 
      PARTITION(sale_date='202602', region='beijing') 
      overwrite PARTITION(sale_date='202603', region='shanghai') purge;
    
    -- View the partitions of the table.
    SHOW PARTITIONS sale_detail;
    
    -- Sample result:
    sale_date=202603/region=shanghai

List partitions

Lists all the partitions of a table. If the table does not exist or the table is a non-partitioned table, an error is returned.

• Syntax

  SHOW PARTITIONS <table_name>; 

• Parameters

  table_name: required. The name of the partitioned table whose partition information you want to view.

• Examples

  -- List all partitions in the sale_detail table.
  SHOW PARTITIONS sale_detail;
  
  -- Sample result:
  sale_date=202603/region=shanghai

View partition information

Displays the partition information about a partitioned table.

• Syntax

  DESC <table_name> PARTITION (<pt_spec>);

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table whose partition information you want to view.
  pt_spec	Yes	The partition that you want to view. The format is partition_col1=col1_value1, partition_col2=col2_value1.... For a table that has multi-level partitions, you must specify all partition values.

• Examples

  -- Query the partition information of the sale_detail partitioned table.
  DESC sale_detail PARTITION (sale_date='202603',region='shanghai');
  -- The following result is returned:
  +------------------------------------------------------------------------------------+
  | PartitionSize: 0                                                                   |
  +------------------------------------------------------------------------------------+
  | CreateTime:               2026-01-13 11:35:49                                      |
  | LastDDLTime:              2026-01-13 11:35:49                                      |
  | LastModifiedTime:         2026-01-13 11:35:49                                      |
  +------------------------------------------------------------------------------------+
  
  OK

Drop partitions

Drops partitions from an existing partitioned table.

MaxCompute lets you drop partitions based on a filter condition. If you want to drop multiple partitions that meet a rule at the same time, you can use an expression to specify a filter condition, match the partitions, and then drop them in a batch.

• Limits

  • You can specify the information of only one partition key column in a PARTITION (<partition_filtercondition>) clause.

  • Any function used in an expression must be a built-in scalar function.

• Precautions

  • After you drop a partition, the volume of stored data in a MaxCompute project decreases.

  • You can use the lifecycle feature of MaxCompute to automatically reclaim old partitions.

• 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

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table from which you want to drop partitions.
  IF EXISTS	No	If you do not specify IF EXISTS and the partition does not exist, an error is returned.
  pt_spec	Yes	The partition to drop. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value. Partition fields are not case-sensitive, but partition values are case-sensitive.
  partition_filtercondition	No	The filter condition. This parameter is required when you specify the filter condition. It is not case-sensitive. 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>), ...] Description: partition_col: the name of the partition key column. relational_operators: the relational operator. For more information, see Operator. partition_col_value: 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(): a 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 specified by relational_operators to compare the processed values with the value specified by partition_col_value. The filter conditions 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> AND|OR <partition_filtercondition2>) to obtain the condition that is used to match partitions. 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 partitions.

• Examples

  • The filter condition is not specified.

    -- Drop a partition from the sale_detail table. This drops the sales records from the Shanghai region in March 2026.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202603',region='shanghai'); 
    -- Drop two partitions from the sale_detail table at the same time. This drops the sales records from the Hangzhou and Shanghai regions in December 2024.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202412',region='hangzhou'),PARTITION(sale_date='202412',region='shanghai');

  • The filter condition is specified.

    • Partitioned table

      -- Create a partitioned table.
      CREATE TABLE IF NOT EXISTS sale_detail_del(
      shop_name     STRING,
      customer_id   STRING,
      total_price   DOUBLE)
      partitioned BY (sale_date STRING);
      
      -- Add partitions.
      ALTER TABLE sale_detail_del 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 in a batch.
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201911');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date >= '202007');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date LIKE '20191%');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date IN ('202002','202004','202006'));
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date BETWEEN '202001' AND '202007');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(substr(sale_date, 1, 4) = '2020');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201912' OR sale_date >= '202006');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date > '201912' AND sale_date <= '202004');
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(NOT sale_date > '202004');
      
      -- Multiple partition filter expressions are supported. The relationship between the expressions is OR.
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201911'), PARTITION(sale_date >= '202007');
      
      -- Add partitions in other formats.
      ALTER TABLE sale_detail_del ADD IF NOT EXISTS
      PARTITION (sale_date= '2019-10-05') 
      PARTITION (sale_date= '2019-10-06') 
      PARTITION (sale_date= '2019-10-07');
      
      -- Drop partitions in a batch using a regular expression to match partitions.
      ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date RLIKE '2019-\\d+-\\d+');

    • Multi-level partitioned table

      -- Create a multi-level partitioned table.
      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.
      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');
      
      -- Run the following statement to drop multi-level partitions in a batch. The two matching conditions have an OR relationship. All partitions where sale_date is less than '201911' or region is 'beijing' are dropped.
      ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'),PARTITION(region = 'beijing');
      
      -- To drop partitions where sale_date is less than '201911' and region is 'beijing', use the following method.
      ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911', region = 'beijing');

      When you drop multi-level partitions in a batch, you cannot create a combined condition based on multiple partition key columns in a single partition filter clause. The following statement returns the error FAILED: ODPS-0130071:[1,82] Semantic analysis exception - invalid column reference region, partition expression must have one and only one column reference.

      -- A partition filter clause can access only one partition key column. The following statement returns an error.
      ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911' AND region = 'beijing');

Clear data from a partition

Clears the data from the specified partition of a partitioned table.

MaxCompute lets you clear the data in partitions that meet the specified filter condition. If you want to drop one or more partitions that meet a filter condition at a time, you can use an expression to specify the condition, use the condition to match partitions, and then clear data from the matched partitions.

• Syntax

  • The filter condition is not specified.

    TRUNCATE TABLE <table_name> PARTITION <pt_spec>[, PARTITION <pt_spec>....];

  • The filter condition is specified.

    TRUNCATE TABLE <table_name> PARTITION <partition_filtercondition>;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table of which you want to clear partition data.
  pt_spec	Yes	The partition from which you want to clear data. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is the partition field, and partition_col_value is the partition value. Partition fields are not case-sensitive, but partition values are case-sensitive.
  partition_filtercondition	No	The filter condition. This parameter is required when you specify the filter condition. It is not case-sensitive. 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>), ...] The descriptions are as follows: partition_col: the name of the partition key column. relational_operators: the relational operator. For more information, see Operator. partition_col_value: 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(): a 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 specified by relational_operators to compare the processed values with the value specified by partition_col_value. The filter conditions 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> AND|OR <partition_filtercondition2>) to obtain the condition that is used to match partitions. 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 partitions.

• Examples

  • The filter condition is not specified.

    -- Clear data from a partition in the sale_detail table. This clears the sales records from the Hangzhou region in January 2026.
    TRUNCATE TABLE sale_detail PARTITION(sale_date='202601',region='hangzhou');
    
    -- Clear data from two partitions in the sale_detail table at the same time. This clears the sales records from the Hangzhou and Shanghai regions in December 2025.
    TRUNCATE TABLE sale_detail PARTITION(sale_date='202512',region='hangzhou'),  PARTITION(sale_date='202512',region='shanghai');

  • The filter condition is specified.

    -- Clear data from multiple partitions in the sale_detail table. This clears the sales records from the Hangzhou region for which the sale_date value starts with '2025'.
    TRUNCATE TABLE sale_detail PARTITION(sale_date LIKE '2025%' AND region='hangzhou');

  • Clear the data from a non-partitioned Delta table.

    -- Clear a non-partitioned table. The table must be a non-partitioned table. Otherwise, an error is reported.
    TRUNCATE TABLE non_par_table; 

References

For more information about table operations, see the following topics:

• Create and drop tables

• Modify and view tables

• Column operation

• View tables and views

• CLONE TABLE