ALTER TABLE DROP PARTITION - Amazon Athena
SynopsisParametersExamplesNotes
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

ALTER TABLE DROP PARTITION

Drops one or more specified partitions for the named table.

Synopsis

ALTER TABLE table_name DROP [IF EXISTS] PARTITION (partition_spec) [, PARTITION (partition_spec)]

Parameters

[IF EXISTS]

Suppresses the error message if the partition specified does not exist.

PARTITION (partition_spec)

Each partition_spec specifies a column name/value combination in the form partition_col_name = partition_col_value [,...].

Examples

ALTER TABLE orders 
DROP PARTITION (dt = '2014-05-14', country = 'IN');
ALTER TABLE orders 
DROP PARTITION (dt = '2014-05-14', country = 'IN'), PARTITION (dt = '2014-05-15', country = 'IN');

Notes

The ALTER TABLE DROP PARTITION statement does not provide a single syntax for dropping all partitions at once or support filtering criteria to specify a range of partitions to drop.

As a workaround, you can use the Amazon Glue API GetPartitions and BatchDeletePartition actions in scripting. The GetPartitions action supports complex filter expressions like those in a SQL WHERE expression. After you use GetPartitions to create a filtered list of partitions to delete, you can use the BatchDeletePartition action to delete the partitions in batches of 25.

Important

Due to a known issue, when an invalid partition is specified for the ALTER TABLE DROP PARTITION statement, all partitions for the table are dropped in Amazon Glue. For example, the following statement will drop all partitions for the table my_table even though the specified partition does not exist. As a workaround, make sure that you enter the partition information correctly before running the ALTER TABLE DROP PARTITION statement.

ALTER TABLE my_table DROP IF EXISTS PARTITION(zzz='');