Problem
When attempting to create an external table with the following SQL query, you encounter an error indicating there is an issue with the specified path when trying to create the table.
%sql
CREATE OR REPLACE TABLE <schema-name>.<table-name>
(
<column_1> <data-type>,
<column_2> <data-type>,
…
)
USING delta
LOCATION 's3://<bucket-name>/<path>/'
Note
The error message's LOCATION may vary based on the cloud provider you are using.
For Azure:
abfss://<container>@<storageaccount>.dfs.core.windows.net/<path>/
For GCP:
gs://<bucket-name>/<path>/
The error message displayed is:
Unsupported path operation PATH_CREATE_TABLE on volume.Cause
The paths used for the external location and the external volume overlap.
For context, the conflict arises because external volumes and external tables serve different purposes. External volumes are used to manage and organize data storage, while external tables are used to query data stored in external locations. Creating volumes with the same path as the external location prevents you from adding additional Unity Catalog (UC) entities, such as tables or volumes, under that external location.
Solution
Create the table in a different location that does not overlap with the external volume.
- Delete the existing external volume that has the same path as the external location.
- Create a new external volume with a different path that does not overlap with the external location.
- Create the external table using the following query:
%sql
CREATE OR REPLACE TABLE <schema-name>.<table-name>
(
<column_1> <data-type>,
<column_2> <data-type>,
…
)
USING delta
LOCATION 's3://<bucket-name>/<new-path>/'
To avoid similar issues in the future, Databricks recommends using unique paths for external volumes and external locations. For more information, please refer to the How do paths work for data managed by Unity Catalog? (AWS | Azure | GCP) documentation.