Introduction
Registering external tables in Unity Catalog allows Databricks to manage metadata while keeping data stored in cloud storage (S3, ADLS, GCS). However, if external table registration fails, you may encounter issues such as:
🚨 Common errors when registering external tables in Unity Catalog:
- Error: “Insufficient permissions to access external location.”
- Error: “Table creation failed: Unity Catalog is not enabled for this workspace.”
- Error: “External location not found or not authorized.”
- **Table does not appear in
SHOW TABLES
after creation._
This guide covers troubleshooting steps and fixes to successfully register external tables in Unity Catalog.
1. Verify That Unity Catalog Is Enabled for Your Workspace
Symptoms:
- Cannot create external tables using Unity Catalog.
- Error: “Unity Catalog is not enabled for this workspace.”
SHOW CATALOGS;
does not return any Unity Catalog catalogs.
Causes:
- Unity Catalog is not assigned to the workspace.
- The workspace is using the Hive metastore instead of Unity Catalog.
Fix:
✅ Check if Unity Catalog is enabled:
SHOW METASTORES;
✅ If no metastore is found, assign one:
databricks unity-catalog metastores assign --metastore-id <metastore-id> --workspace-id <workspace-id>
✅ Ensure you are using a Unity Catalog-enabled cluster.
- Go to Clusters → Advanced Options → Enable Unity Catalog.
✅ Verify the workspace supports Unity Catalog (Premium or Enterprise required).
2. Ensure External Location Is Registered in Unity Catalog
Symptoms:
- Error: “External location not found or not authorized.”
- Cannot create external tables using cloud storage paths (S3, ADLS, GCS).
Causes:
- The external storage path is not registered as an external location.
- The Databricks workspace lacks access to the cloud storage location.
Fix:
✅ Register the external location in Unity Catalog (Admin required):
CREATE EXTERNAL LOCATION my_external_loc
URL 's3://my-bucket/data/'
WITH (STORAGE CREDENTIAL my_credential);
✅ List available external locations:
SHOW EXTERNAL LOCATIONS;
✅ Ensure storage credentials are properly assigned:
SHOW STORAGE CREDENTIALS;
✅ If missing, create storage credentials for Unity Catalog:
CREATE STORAGE CREDENTIAL my_credential
WITH AWS IAM ROLE 'arn:aws:iam::123456789012:role/unity-catalog-role';
✅ If using Azure ADLS, ensure Managed Identity permissions:
az role assignment create --assignee <service-principal> --role "Storage Blob Data Contributor" --scope <storage-account>
3. Check Permissions for External Location and Table Creation
Symptoms:
- Error: “Insufficient permissions to access external location.”
- Error: “Cannot create table in Unity Catalog: Access Denied.”
- Users cannot see or query external tables.
Causes:
- The user does not have
USAGE
permission on the catalog or schema. - IAM roles (AWS) or Azure RBAC permissions are not correctly assigned.
- The table location is outside the allowed external location.
Fix:
✅ Grant permissions to the user or group:
GRANT USAGE ON CATALOG my_catalog TO `user@example.com`;
GRANT CREATE EXTERNAL TABLE ON SCHEMA my_catalog.my_schema TO `user@example.com`;
✅ Grant access to the external location:
GRANT READ FILES ON EXTERNAL LOCATION my_external_loc TO `user@example.com`;
✅ If using AWS IAM roles, verify storage access policies:
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": ["arn:aws:s3:::my-bucket", "arn:aws:s3:::my-bucket/*"]
}
✅ If using Azure ADLS, ensure Databricks has Storage Blob Data Reader access:
az role assignment create --assignee <databricks-principal> --role "Storage Blob Data Reader" --scope <storage-account>
4. External Table Creation Fails Due to Incorrect Path or Format
Symptoms:
- Error: “Path does not exist: s3://my-bucket/data/”
- Error: “Unsupported file format for external table.”
- Table is created, but querying it fails.
Causes:
- The specified data path does not exist or is incorrectly formatted.
- The file format is not compatible with Unity Catalog external tables.
- The table schema does not match the data format.
Fix:
✅ Verify that the external path exists:
aws s3 ls s3://my-bucket/data/
az storage blob list --container-name my-container --account-name my-storage
✅ Use supported file formats (PARQUET
, DELTA
, CSV
):
CREATE EXTERNAL TABLE my_catalog.my_schema.my_table
USING DELTA
LOCATION 's3://my-bucket/data/';
✅ If using CSV, specify the correct format:
CREATE EXTERNAL TABLE my_catalog.my_schema.my_table
USING CSV
OPTIONS ('header' 'true')
LOCATION 's3://my-bucket/data/';
✅ Ensure schema matches data format:
DESCRIBE TABLE my_catalog.my_schema.my_table;
5. External Table Does Not Appear After Creation
Symptoms:
- Table creation succeeds but does not appear in
SHOW TABLES
results. - Queries return “table not found.”
Causes:
- The table was created in the wrong schema or catalog.
- The table creation failed silently due to permission issues.
Fix:
✅ Check where the table was created:
SHOW TABLES IN my_catalog.my_schema;
✅ Ensure the correct catalog and schema are used:
USE CATALOG my_catalog;
USE SCHEMA my_schema;
✅ Refresh metadata if needed:
REFRESH TABLE my_catalog.my_schema.my_table;
✅ Verify the table path in cloud storage:
aws s3 ls s3://my-bucket/data/
az storage blob list --container-name my-container --account-name my-storage
6. Troubleshooting Step-by-Step
Step 1: Check If Unity Catalog Is Enabled
SHOW METASTORES;
- If empty, assign a Unity Catalog metastore to the workspace.
Step 2: Verify External Location Exists
SHOW EXTERNAL LOCATIONS;
- If missing, register a new external location and assign permissions.
Step 3: Validate User Permissions
SHOW GRANTS ON CATALOG my_catalog;
SHOW GRANTS ON SCHEMA my_catalog.my_schema;
SHOW GRANTS ON EXTERNAL LOCATION my_external_loc;
- If the user lacks permissions, grant access.
Step 4: Ensure Data Path and Format Are Correct
- Check that data exists at the external path and that the file format is supported.
Best Practices for External Tables in Unity Catalog
✅ Ensure External Locations Are Properly Registered
- Use
CREATE EXTERNAL LOCATION
before registering tables.
✅ Grant Fine-Grained Access Control
- Assign least privilege permissions to users and service accounts.
✅ Use Unity Catalog-Enabled Clusters
- Ensure SQL Warehouses and clusters support Unity Catalog.
✅ Store External Tables in a Structured Format
- Prefer Delta or Parquet format for best performance.
Conclusion
If you cannot register external tables in Unity Catalog, check:
✅ Unity Catalog is enabled and a metastore is assigned.
✅ External locations are correctly registered.
✅ Users have the correct permissions for table creation and access.
✅ The data path exists and uses a compatible format.
✅ The table appears in the correct catalog and schema.
By following these steps, you can successfully register and use external tables in Unity Catalog.