how to add or remove the partitions of an OSS external table - AnalyticDB

AnalyticDB for MySQL Data Lakehouse Edition (V3.0) allows you to execute the ALTER TABLE statement to add partitions to or remove partitions from an Object Storage Service (OSS) external table.

Prerequisites

A Data Lakehouse Edition (V3.0) cluster is created.
The minor engine version of the cluster is 3.1.8.0 or later.
Note For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.
An OSS external table is created. For more information, see CREATE EXTERNAL TABLE.

Usage notes

The syntax is applicable only to adding partitions to or removing partitions from OSS external tables. This syntax is not applicable to AnalyticDB for MySQL tables or non-partitioned OSS external tables.

Add partitions

You can use the ALTER TABLE ADD PARTITION statement to manually add a partition or a multi-level partition. For information about how to automatically identify an OSS path and add a partition, see MSCK REPAIR TABLE.

Syntax

ALTER TABLE table_name ADD PARTITION (partition_key=value[,...]) LOCATION 'location';

Parameters

Parameter	Description
`table_name`	The name of the external table.
`(partition_key=value[,...])`	The partition that you want to add. To add a multi-level partition, specify multiple partitions. Separate multiple partition names with commas (,).
`location`	The OSS path of the partition. For example, assume that the OSS path of an object is oss://testBucketName/testfolder/p1=20230613/data.csv and the partition to be added is p1='20230613'. In this case, you must specify `LOCATION 'oss://testBucketname/testfolder/'` as this parameter.

Examples

Example 1: Add a partition

ALTER TABLE adb_external_db.partition_table ADD PARTITION (p1='20230613') LOCATION 'oss://testBucketName/testfolder/p1=20230613/';

Example 2: Add a multi-level partition

ALTER TABLE adb_external_db.partition_table ADD PARTITION (p1='20230613',p2='hangzhou') LOCATION 'oss://testBucketName/testfolder/p1=20230613/p2=hangzhou';

Remove partitions

Syntax

ALTER TABLE table_name DROP PARTITION (partition_key=value[,...]);

Parameters

Parameter	Description
`table_name`	The name of the external table.
`(partition_key=value[,...])`	The partition that you want to remove. To remove a multi-level partition, specify multiple partitions. Separate multiple partition names with commas (,).

Examples

Example 1: Remove a partition

ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613');

Example 2: Remove a multi-level partition

ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613',p2='hangzhou');

References

Use external tables to import data from OSS to AnalyticDB for MySQL Data Lakehouse Edition