ALTER TABLE … PARTITION

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.

Syntax

ADD [IF NOT EXISTS] { PARTITION clause [ LOCATION path ] } [...]

Parameters

• IF NOT EXISTS

  An optional clause directing Databricks to ignore the statement if the partition already exists.

• PARTITION clause

  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 a STRING 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.

Syntax

DROP [ IF EXISTS ] PARTITION clause [, ...] [PURGE]

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.

• PARTITION clause

  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.

Syntax

from_partition_clause RENAME TO to_partition_clause

Parameters

• from_partition_clause

  The definition of the partition to be renamed.

• to_partition_clause

  The new definition for this partition. A partition with the same keys must not already exist.

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.

Syntax

RECOVER PARTITIONS

Parameters

None

PARTITION SET LOCATION clause

Moves the location of a partition.

Syntax

PARTITION clause SET LOCATION path

Parameters

• PARTITION clause

  Identifies the partition for which the location will to be changed.

• LOCATION path

  path must be a STRING literal. Specifies the new location for the partition.

  Files in the original location will not be moved to the new location.

Examples

See ALTER TABLE examples.

Related articles

• ALTER TABLE