MaxCompute partition operations - add, delete, merge, rename, list partitions - MaxCompute

Add, delete, merge, rename, inspect, and truncate partitions in MaxCompute tables.

Operations overview

Operation	Description	Role	Platforms
Add a partition	Add one or more partitions to a partitioned table.	Requires Alter permission.	The following platforms support these commands: Connect to MaxCompute with odpscmd Analyze connections using SQL Connect using odpscmd in Cloud Shell Connect by using DataWorks Develop and submit an SQL script
Modify partition update time	Reset the `LastModifiedTime` of a partition to the current time and recalculate its lifecycle.
Modify a partition value	Rename partition values.
Merge partitions	Combine multiple partitions into a single destination partition.
List all partitions	List every partition in a table.
View partition information	Display metadata for a specific partition.
Delete a partition	Remove one or more partitions from a table.
Truncate a partition	Remove data from partitions without dropping them.

Limitations

Constraint	Limit
Supported data types for partition key columns	TINYINT, SMALLINT, INT, BIGINT, CHAR, VARCHAR, STRING
Maximum partition levels per table	6
Maximum partitions per table	60,000
Maximum partitions queried at a time	10,000
Merge partition on transactional tables	Not supported

Add a partition

Add one or more partitions to an existing partitioned table.

Usage notes

For a multi-level partitioned table, specify values for all partition key columns when adding a partition.
This operation adds partition values only. It does not add partition key columns.

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.
IF NOT EXISTS	No	Suppresses the error if the partition already exists.
pt_spec	Yes	The partition to add, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. `partition_col` is a partition key column and `partition_col_value` is its value. Partition key column names are case-insensitive. Partition values are case-sensitive.

Examples

The following examples use a partitioned table named sale_detail. To create this table, 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);

Add a single partition

Add a partition for the Hangzhou region in December 2025.

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

Add multiple partitions at once

Add two partitions for 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');

Incomplete partition specification (error)

Specifying only part of a multi-level partition key returns an error. Both sale_date and region are required.

ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='20260111');

-- Error returned:
-- FAILED: ODPS-0130071:[1,58] Semantic analysis exception - provided partition spec does not match table partition spec

Add a partition to a Delta Table

-- Create a Delta 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");

Modify properties of a PK Delta Table

-- Update the number of buckets for a partitioned PK Delta Table.
ALTER TABLE delta_table_test_par SET TBLPROPERTIES("write.bucket.num"="64");

-- Update the number of buckets for a non-partitioned PK Delta Table.
-- After the update, historical data is redistributed based on the new bucket count.
ALTER TABLE delta_table_test_nonpar REWRITE TBLPROPERTIES("write.bucket.num"="128");

-- Update the retain property. This property specifies the number of hours
-- for querying historical data states via Time Travel.
ALTER TABLE delta_table_test_par SET TBLPROPERTIES("acid.data.retain.hours"="60");

After adding partitions, verify them with SHOW PARTITIONS. See List all partitions.

Modify partition update time

The touch operation resets a partition's LastModifiedTime to the current time, triggering lifecycle recalculation.

Usage notes

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

Syntax

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

Parameters

Parameter	Required	Description
table_name	Yes	The name of the partitioned table. Returns an error if the table does not exist.
pt_spec	Yes	The partition to update, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. `partition_col` is a partition key column and `partition_col_value` is its value. Returns an error if the partition does not exist.

Examples

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

Modify a partition value

RENAME changes the values of an existing partition.

Usage notes

This operation changes partition values only. It does not rename partition key columns.
For a multi-level partitioned table, specify values for all partition key columns.

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 partitioned table.
pt_spec	Yes	The current partition specification, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. `partition_col` is a partition key column and `partition_col_value` is its value. Returns an error if the partition does not exist.
new_pt_spec	Yes	The new partition specification, in the format `(partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...)`. `partition_col` is a partition key column and `new_partition_col_value` is the new value.

Examples

-- Rename a partition 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

MERGE PARTITION combines multiple partitions into one destination partition. Data moves to the destination and the source partitions are deleted.

Usage notes

Foreign tables are not supported.
Merging partitions of a clustered table removes the clustering from the resulting partition.
Maximum of 4,000 partitions can be merged 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.
IF EXISTS	No	Suppresses the error if no partition matches the condition. If no partition qualifies, no new partition is created. Concurrent `INSERT`, `RENAME`, or `DROP` operations during the merge still cause an error.
predicate	Yes	The condition that source partitions must meet.
fullpartitionSpec	Yes	The destination partition specification.
PURGE	No	Clears the session directory. By default, logs within the last three days are cleared. For more information, see Purge.

Examples

Merge partitions by condition

-- View current partitions.
SHOW PARTITIONS sale_detail;

-- Sample result:
-- sale_date=202512/region=beijing
-- sale_date=202512/region=shanghai
-- sale_date=202602/region=beijing

-- Merge all partitions where sale_date='202512' into a single destination partition.
ALTER TABLE sale_detail MERGE PARTITION(sale_date='202512') OVERWRITE PARTITION(sale_date='202601', region='hangzhou');

-- View partitions after the merge.
SHOW PARTITIONS sale_detail;

-- Sample result:
-- sale_date=202601/region=hangzhou
-- sale_date=202602/region=beijing

Merge specific partitions with PURGE

-- Merge two named partitions into a new destination partition.
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 partitions after the merge.
SHOW PARTITIONS sale_detail;

-- Sample result:
-- sale_date=202603/region=shanghai

List all partitions

List every partition in a table. Returns an error if the table does not exist or is not partitioned.

Syntax

SHOW PARTITIONS <table_name>;

Parameters

Parameter	Required	Description
table_name	Yes	The name of the partitioned table.

Examples

-- List all partitions in the sale_detail table.
SHOW PARTITIONS sale_detail;

-- Sample result:
-- sale_date=202603/region=shanghai

View partition information

Display metadata for a specific partition, including its size, creation time, and last modified time.

Syntax

DESC <table_name> PARTITION (<pt_spec>);

Parameters

Parameter	Required	Description
table_name	Yes	The name of the partitioned table.
pt_spec	Yes	The partition to inspect, in the format `partition_col1=col1_value1, partition_col2=col2_value1...`. For a multi-level partitioned table, specify all partition values.

Examples

-- View information for a specific partition.
DESC sale_detail PARTITION (sale_date='202603',region='shanghai');

-- Sample result:
+------------------------------------------------------------------------------------+
| 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

Remove one or more partitions from a table. Specific partitions can be deleted by name, or filter conditions can be used for bulk deletion.

Deleting partitions reduces storage usage. To automate deletion of old partitions, use the Lifecycle feature.

Usage notes

Each partition filter clause can reference only one partition key column.
Functions used in filter expressions must be built-in scalar functions.

Syntax

Delete specific partitions

-- Delete one partition.
ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>;

-- Delete multiple partitions.
ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>,PARTITION <pt_spec>[,PARTITION <pt_spec>....];

Delete partitions by filter condition

ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <partition_filtercondition>;

Parameters

Parameter	Required	Description
table_name	Yes	The name of the partitioned table.
IF EXISTS	No	Suppresses the error if the partition does not exist.
pt_spec	Yes	The partition to delete, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. `partition_col` is a partition key column and `partition_col_value` is its value. Partition key column names are case-insensitive. Partition values are case-sensitive.
partition_filtercondition	No	Required when using filter-based deletion. See Partition filter condition syntax.

Partition filter condition syntax

Both the delete and truncate operations support the same partition filter syntax:

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>), ...]

Element	Description
partition_col	The partition key column name.
relational_operators	A relational operator. For details, see Operators.
partition_col_value	A value or regular expression to compare against the partition key column value. The data type must match the partition key column type.
scalar()	A built-in scalar function. Processes the partition key column value before comparison.
NOT, AND, OR	Logical operators supported within a single filter clause.
Comma-separated clauses	Multiple filter clauses separated by commas are combined with the OR operator.

Examples

Delete specific partitions

-- Delete one partition.
ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202603',region='shanghai');

-- Delete two partitions at once.
ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='202412',region='hangzhou'),PARTITION(sale_date='202412',region='shanghai');

Delete partitions by filter condition (single-level table)

-- Create a single-level partitioned table with sample partitions.
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 by comparison.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201911');
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date >= '202007');

-- Delete by pattern matching.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date LIKE '20191%');

-- Delete by value list.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date IN ('202002','202004','202006'));

-- Delete by range.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date BETWEEN '202001' AND '202007');

-- Delete using a scalar function.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(substr(sale_date, 1, 4) = '2020');

-- Delete with OR condition.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201912' OR sale_date >= '202006');

-- Delete with AND condition.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date > '201912' AND sale_date <= '202004');

-- Delete with NOT condition.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(NOT sale_date > '202004');

-- Multiple filter clauses separated by commas are combined with OR.
ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date < '201911'), PARTITION(sale_date >= '202007');

-- Delete by regular expression.
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');

ALTER TABLE sale_detail_del DROP IF EXISTS PARTITION(sale_date RLIKE '2019-\\d+-\\d+');

Delete partitions from a multi-level partitioned table

-- Create a multi-level partitioned table with sample 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.
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');

-- Comma-separated filter clauses are combined with OR.
-- This deletes all partitions where sale_date < '201911' OR region = 'beijing'.
ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'),PARTITION(region = 'beijing');

-- To combine conditions with AND, place both conditions in a single clause.
-- This deletes only partitions where sale_date < '201911' AND region = 'beijing'.
ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911', region = 'beijing');

Each filter clause can reference only one partition key column. Using AND to combine multiple partition key columns in a single filter clause returns an error.

-- The following statement fails because it references two partition key columns
-- (sale_date and region) with AND in a single filter clause.
ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911' AND region = 'beijing');

-- Error returned:
-- FAILED: ODPS-0130071:[1,82] Semantic analysis exception - invalid column reference region,
-- partition expression must have one and only one column reference

Truncate a partition

Remove all data from specified partitions without dropping the partitions themselves.

Truncate by exact partition name or by filter condition.

Syntax

Truncate specific partitions

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

Truncate partitions by filter condition

TRUNCATE TABLE <table_name> PARTITION <partition_filtercondition>;

Parameters

Parameter	Required	Description
table_name	Yes	The name of the partitioned table.
pt_spec	Yes	The partition to truncate, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. `partition_col` is a partition key column and `partition_col_value` is its value. Partition key column names are case-insensitive. Partition values are case-sensitive.
partition_filtercondition	No	Required when using filter-based truncation. The filter condition is case-insensitive. Syntax: `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>), ...]` Parameters: partition_col: The partition column name. relational_operators: A relational operator. For more information, see Operators. partition_col_value: The comparison value or regular expression for the partition column. The data type must match the partition column. scalar(): A scalar function that transforms the partition column value before comparing it against partition_col_value using the specified relational operator. The filter condition supports the following logical operators: NOT: Returns the complement of the filter condition. AND / OR: Combines multiple filter conditions into an overall partition matching rule. Comma (,): Separates multiple PARTITION clauses. Comma-separated clauses are evaluated with OR logic.

Examples

Truncate specific partitions

-- Truncate one partition.
TRUNCATE TABLE sale_detail PARTITION(sale_date='202601',region='hangzhou');

-- Truncate two partitions at once.
TRUNCATE TABLE sale_detail PARTITION(sale_date='202512',region='hangzhou'),  PARTITION(sale_date='202512',region='shanghai');

Truncate partitions by filter condition

-- Truncate all partitions where sale_date starts with '2025' and region is 'hangzhou'.
TRUNCATE TABLE sale_detail PARTITION(sale_date LIKE '2025%' AND region='hangzhou');

Truncate a non-partitioned Delta Table

-- For non-partitioned tables only. Using this on a partitioned table returns an error.
TRUNCATE TABLE non_par_table;

Operations overview

Limitations

Add a partition

Usage notes

Syntax

Parameters

Examples

Modify partition update time

Usage notes

Syntax

Parameters

Examples

Modify a partition value

Usage notes

Syntax

Parameters

Examples

Merge partitions

Usage notes

Syntax

Parameters

Examples

List all partitions

Syntax

Parameters

Examples

View partition information

Syntax

Parameters

Examples

Delete a partition

Usage notes

Syntax

Parameters

Partition filter condition syntax

Examples

Truncate a partition

Syntax

Parameters

Examples

References