ALTER TABLE … PARTITION
November 25, 2024
Applies to: Databricks SQL Databricks Runtime
Adds, drops, renames, or recovers partitions of a table.
Managing partitions is not supported for Delta Lake tables.
Syntax
ALTER TABLE table_name
{ ADD PARTITION clause |
DROP PARTITION clause |
PARTITION SET LOCATION clause |
RENAME PARTITION clause |
RECOVER PARTITIONS clause }
ADD PARTITION
clause
Adds one or more partitions to the table.
Managing partitions is not supported for Delta Lake tables.
Parameters
IF NOT EXISTS
An optional clause directing Databricks to ignore the statement if the partition already exists.
-
A partition to be added. The partition keys must match the partitioning of the table and be associated with values. If the partition already exists an error is raised unless
IF NOT EXISTS
has been specified. LOCATION path
path
must be aSTRING
literal representing an optional location pointing to the partition.If no location is specified the location will be derived from the location of the table and the partition keys.
If there are files present at the location they populate the partition and must be compatible with the
data_source
of the table and its options.
DROP PARTITION
clause
Drops one or more partitions from the table, optionally deleting any files at the partitions’ locations.
Managing partitions is not supported for Delta Lake tables.
Parameters
IF EXISTS
When you specify
IF EXISTS
Databricks will ignore an attempt to drop partitions that do not exists. Otherwise, non existing partitions will cause an error.-
Specifies a partition to be dropped. If the partition is only partially identified a slice of partitions is dropped.
PURGE
If set, the table catalog must remove partition data by skipping the Trash folder even when the catalog has configured one. The option is applicable only for managed tables. It is effective only when:
The file system supports a Trash folder. The catalog has been configured for moving the dropped partition to the Trash folder. There is no Trash folder in AWS S3, so it is not effective.
There is no need to manually delete files after dropping partitions.
RENAME PARTITION
clause
Replaces the keys of a partition.
Managing partitions is not supported for Delta Lake tables.
RECOVER PARTITIONS
clause
This clause does not apply to Delta Lake tables.
Instructs Databricks to scan the table’s location and add any files to the table which have been added directly to the filesystem.
Managing partitions is not supported for Delta Lake tables.
PARTITION SET LOCATION clause
Moves the location of a partition.
Managing partitions is not supported for Delta Lake tables.
Examples
See ALTER TABLE examples.