Problem

When you try to run ALTER TABLE <catalog>.<schema>.<table-name> DROP PARTITION <partition column=partition value> on a Unity Catalog external table, you encounter an error.

SQL query error : [UC_COMMAND_NOT_SUPPORTED.WITHOUT_RECOMMENDATION] ALTER TABLE (drop partition) are not supported in Unity Catalog.

Cause

Unity Catalog does not store table partition information, so DROP PARTITION is not supported on Unity Catalog tables.

Solution

For Unity Catalog external tables with CSV, JSON, ORC, or Parquet data formats, use partition metadata logging to resolve the issue. 

1. Set the nonDelta.partitionLog.enabled to true for the Apache Spark session while creating the table.
   
   SET spark.databricks.nonDelta.partitionLog.enabled = true;
    
2. Re-create or create the table.
3. Re-run ALTER TABLE <catalog>.<schema>.<table-name> DROP PARTITION <partition column=partition value>.

Example

Set the config for partition metadata logging.

%sql

SET spark.databricks.nonDelta.partitionLog.enabled = true;

Create the table.

%sql

CREATE OR REPLACE TABLE <catalog>.<schema>.<table-name>
USING <format>
PARTITIONED BY (<partition-column-list>)
LOCATION 's3://<bucket-path>/<table-directory>';

Re-run the command.

%sql

ALTER TABLE <catalog>.<schema>.<table-name> DROP PARTITION <partition column=partition value>

For more information, please refer to the Partition discovery for external tables (AWS | Azure | GCP) documentation.