MaxCompute:Partition operations

Scope

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

• Partition levels for a single table: A table can have a maximum of six levels of partitions.

• Number of partitions in a single table: A table can have a maximum of 60,000 partitions.

• Maximum number of partitions to query at a time: You can query a maximum of 10,000 partitions at a time.

• The merge partition operation is not supported for transactional partitioned tables.

Add a partition

You can add a new partition to an existing partitioned table.

• Limits

  • For a multi-level partitioned table, you must specify values for all partition key columns when you add a partition.

  • You can add only partition values. You cannot add partition fields.

• Command format

  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 a partition.
  IF NOT EXISTS	No	If you do not specify IF NOT EXISTS and a partition with the same name already exists, the statement 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 a partition field, and partition_col_value is a partition value. Partition fields are case-insensitive. Partition values are case-sensitive.

• Examples

  You can create a partitioned table named sale_detail. For more information about how to create and delete tables, see Create and delete 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 of 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 simultaneously to store sales records of 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. This operation returns an error. 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 Lake table

    -- Create a Delta Lake table.
    CREATE TABLE delta_table_test_par (
      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_par ADD PARTITION (dd='01', hh='01');
    
    CREATE TABLE delta_table_test_nonpar (
      pk BIGINT NOT NULL PRIMARY KEY, 
      val BIGINT NOT NULL) 
      TBLPROPERTIES ("transactional"="true");

  • Example 5: Modify the properties of a PK Delta Lake table

    -- Update the number of buckets for a partitioned Primary Key (PK) Delta Lake table.
    ALTER TABLE delta_table_test_par SET tblproperties("write.bucket.num"="64");
    
    -- Update the number of buckets for a non-partitioned PK Delta Lake table. After the update, historical data is redistributed based on the new number of buckets.
    ALTER TABLE delta_table_test_nonpar REWRITE tblproperties("write.bucket.num"="128");
    
    -- Update the retain property. This property specifies the time range in hours within which you can query the historical state of data using Time Travel.
    ALTER TABLE delta_table_test_par SET tblproperties("acid.data.retain.hours"="60");

Update the last modified time of a partition

MaxCompute SQL provides the touch operation to update 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 changed and recalculates its lifecycle.

• Limits

  For a multi-level partitioned table, you must specify values for all partition key columns.

• Command format

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

• Parameters

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

• Example

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

Modify a partition value

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

• Limits

  • You cannot modify the names of partition key columns. You can only modify the values that correspond to the partition key columns.

  • For a multi-level partitioned table, you must specify values for all partition key columns.

• Command format

  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 whose partition value you want to modify.
  pt_spec	Yes	The information about the partition whose value you want to modify. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. If the specified partition field or partition value does not exist, an error is returned.
  new_pt_spec	Yes	The information about the partition after the modification. The format is (partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...). partition_col is a partition field, and new_partition_col_value is the new partition value.

• Example

  -- Modify the partition value in 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 table into a single partition. The data is moved to the destination partition, and the source partitions are deleted.

• Limits

  • Foreign tables are not supported. If you merge partitions of a clustered table, the resulting partition is no longer clustered.

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

• Command format

  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 statement fails and an error is returned. If you specify IF EXISTS but no partition meets the merge condition, no new partition is created. If the source data is concurrently modified by operations such as insert, rename, or drop during the execution, an error is returned even if you specify IF EXISTS.
  predicate	Yes	The condition that the partitions to be merged must meet.
  fullpartitionSpec	Yes	The information about the destination partition.
  purge	No	Optional keyword. If you specify this keyword, the session directory is cleared. By default, logs within the last three days are cleared. For more information, see Purge.

• Examples

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

    -- View the partitions of the partitioned 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 sale_date='201512' condition into the sale_date='202601', region='hangzhou' partition.
    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 partitioned table.
    SHOW PARTITIONS sale_detail;
    
    -- Sample result:
    sale_date=202603/region=shanghai

List all partitions

You can list all partitions in a table. If the table does not exist or is not a partitioned table, this operation returns an error.

• Command format

  SHOW PARTITIONS <table_name>; 

• Parameters

  table_name: Required. The name of the partitioned table whose partitions you want to view.

• Example

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

View partition information

You can view the information about a specific partition in a partitioned table.

• Command format

  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 information about the partition that you want to view. The format is partition_col1=col1_value1, partition_col2=col2_value1.... For a multi-level partitioned table, you must specify all partition values.

• Example

  -- Query the information of a partition in 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

Delete a partition

You can delete a partition from an existing partitioned table.

MaxCompute lets you delete partitions based on filter conditions. To delete multiple partitions that meet specific rules at once, you can specify an expression as the filter condition to match and delete the partitions in a batch operation.

• Limits

  • Each partition filter clause can access only one partition key column.

  • The functions used in the expression must be built-in scalar functions.

• Notes

  • After you delete a partition, the storage usage of your MaxCompute project decreases.

  • You can use the Lifecycle feature of MaxCompute to automatically delete old partitions.

• Command format

  • Without a filter condition

    -- Delete one partition at a time.
    ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>;
    -- Delete multiple partitions at a time.
    ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>,PARTITION <pt_spec>[,PARTITION <pt_spec>....];

  • Filter conditions

    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 delete 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 delete. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. Partition fields are case-insensitive. Partition values are case-sensitive.
  partition_filtercondition	No	Required when you specify a filter condition. The partition filter condition. This parameter is case-insensitive. The format is as follows: 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 following provides the details. partition_col: The partition name. relational_operators: A relational operator. For more information, see Operators. partition_col_value: A value or regular expression to be compared with the partition key column value. The data type of the value must be the same as the data type of the partition key column. scalar(): A scalar function. A scalar function generates a scalar value based on an input value. The value of the partition key column (partition_col) is processed and then compared with partition_col_value based on the specified relational_operators. Partition filter conditions support the logical operators NOT, AND, and OR. You can use a NOT filter condition clause to obtain the complement of a filtering rule. You can also use multiple filter condition clauses with the AND or OR operator to form an overall partition matching rule. You can specify multiple partition filter clauses. If multiple partition filter clauses are separated by commas (,), the clauses are combined using the OR logical operator to form an overall partition matching rule.

• Examples

  • Without a filter condition

    -- Delete a partition that contains the sales records of the Shanghai region in March 2026 from the sale_detail table.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202603',region='shanghai'); 
    -- Delete two partitions that contain the sales records of the Hangzhou and Shanghai regions in December 2024 from the sale_detail table.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202412',region='hangzhou'),PARTITION(sale_date='202412',region='shanghai');

  • Specify filter criteria

    • 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');
      
      -- Delete partitions in batches.
      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 expressions are combined using the OR logical operator.
      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');
      
      -- Delete partitions in batches 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 delete partitions from the multi-level partitioned table in batches. The two matching conditions are combined using the OR logical operator. All partitions where sale_date is less than 201911 or region is beijing are deleted.
      ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'),PARTITION(region = 'beijing');
      
      -- To delete 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 delete partitions from a multi-level partitioned table in a batch operation, you cannot create a composite condition based on multiple partition key columns in a partition filter clause to match partitions. The following statement returns an error message: 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

You can clear the data from a specified partition in a partitioned table.

MaxCompute lets you clear data from partitions based on filter conditions. To clear data from one or more partitions that meet specific rules at once, you can specify an expression as the filter condition to match the partitions and clear their data in a batch operation.

• Command format

  • Without a filter condition

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

  • Specify the filter criteria

    TRUNCATE TABLE <table_name> PARTITION <partition_filtercondition>;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the partitioned table from 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 a partition field, and partition_col_value is a partition value. Partition fields are case-insensitive. Partition values are case-sensitive.
  partition_filtercondition	No	Required when you specify a filter condition. The partition filter condition. This parameter is case-insensitive. The format is as follows: 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>), ...] Details are as follows: partition_col: The partition name. relational_operators: A relational operator. For more information, see Operators. partition_col_value: A value or regular expression to be compared with the partition key column value. The data type of the value must be the same as the data type of the partition key column. scalar(): A scalar function. A scalar function generates a scalar value based on an input value. The value of the partition key column (partition_col) is processed and then compared with partition_col_value based on the specified relational_operators. Partition filter conditions support the logical operators NOT, AND, and OR. You can use a NOT filter condition clause to obtain the complement of a filtering rule. You can also use multiple filter condition clauses with the AND or OR operator to form an overall partition matching rule. You can specify multiple partition filter clauses. If multiple partition filter clauses are separated by commas (,), the clauses are combined using the OR logical operator to form an overall partition matching rule.

• Examples

  • Without a filter condition

    -- Clear a partition that contains the sales records of the Hangzhou region in January 2026 from the sale_detail table.
    TRUNCATE TABLE sale_detail PARTITION(sale_date='202601',region='hangzhou');
    
    -- Clear two partitions that contain the sales records of the Hangzhou and Shanghai regions in December 2025 from the sale_detail table.
    TRUNCATE TABLE sale_detail PARTITION(sale_date='202512',region='hangzhou'),  PARTITION(sale_date='202512',region='shanghai');

  • Specify filter criteria

    -- Clear multiple partitions that contain the sales records of the Hangzhou region where sale_date starts with 2025 from the sale_detail table.
    TRUNCATE TABLE sale_detail PARTITION(sale_date LIKE '2025%' AND region='hangzhou');

  • Clear a non-partitioned Delta Lake table

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

References

For more information about table operation commands, see the following topics:

• Create and delete tables

• Modify and view tables

• Column operations

• View tables and views

• CLONE TABLE