,

Table Exists in Databricks but Not Visible in Unity Catalog

Posted by

Introduction

If a table exists in Databricks (Hive Metastore) but is not visible in Unity Catalog, it could be due to incorrect metastore configurations, missing permissions, or table format incompatibility.

🚨 Common issues when a table is missing from Unity Catalog:

  • Tables exist in the Hive Metastore (hive_metastore.default), but not in Unity Catalog.
  • SHOW TABLES in Unity Catalog does not list expected tables.
  • Cannot access tables in Unity Catalog even though they exist in Databricks.
  • Tables were created before Unity Catalog was enabled and are still in Hive Metastore.

This guide provides troubleshooting steps and solutions to make tables visible in Unity Catalog.


1. Check If the Table Exists in the Hive Metastore Instead of Unity Catalog

Symptoms:

  • The table is accessible in Databricks but not visible in Unity Catalog.
  • Running SHOW TABLES IN catalog.schema; returns an empty list.
  • Queries fail with “Table not found in Unity Catalog.”

Causes:

  • The table was created in the Hive Metastore (hive_metastore.default), not in Unity Catalog.
  • Existing Databricks tables do not automatically migrate to Unity Catalog.

Fix:

Check if the table exists in Hive Metastore:

SHOW TABLES IN hive_metastore.default;

Manually register the table in Unity Catalog by converting it to a managed table:

CREATE TABLE my_catalog.my_schema.my_table AS SELECT * FROM hive_metastore.default.my_table;

If using Delta format, you can register the table directly:

CREATE TABLE my_catalog.my_schema.my_table USING DELTA LOCATION 's3://bucket/path/';

For external tables, re-register them in Unity Catalog:

CREATE TABLE my_catalog.my_schema.external_table
USING PARQUET LOCATION 's3://bucket/path/';

2. Verify That Your Cluster and SQL Warehouse Are Unity Catalog-Enabled

Symptoms:

  • SHOW TABLES works in Hive Metastore but not in Unity Catalog.
  • Databricks SQL Warehouse does not list Unity Catalog tables.
  • SQL queries in Unity Catalog return “Table not found.”

Causes:

  • The Databricks cluster does not support Unity Catalog.
  • The SQL Warehouse is not configured to use Unity Catalog.
  • The workspace metastore is not properly assigned to the cluster.

Fix:

Check if the cluster supports Unity Catalog:

  • Go to Databricks UI → Clusters
  • Edit the cluster → Advanced Options → Enable Unity Catalog
  • If needed, create a new cluster with Unity Catalog support.

For SQL Warehouses, enable Unity Catalog:

  • Go to Databricks UI → SQL Warehouses
  • Edit the warehouse → Ensure it is set to Unity Catalog mode

Restart the cluster after enabling Unity Catalog.


3. Check Permissions for Unity Catalog Tables

Symptoms:

  • The table exists but is not visible to specific users.
  • Error: “Permission denied: Cannot access table.”
  • SHOW TABLES returns an empty list for certain users.

Causes:

  • Users do not have permission to view or query Unity Catalog tables.
  • Unity Catalog permissions are stricter than Hive Metastore permissions.
  • Missing USE CATALOG, SELECT, or READ privileges.

Fix:

Check which catalogs are accessible:

SHOW CATALOGS;

Grant permissions to access the catalog and schema:

GRANT USE CATALOG ON CATALOG my_catalog TO `user@example.com`;
GRANT USAGE ON SCHEMA my_catalog.my_schema TO `user@example.com`;
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `user@example.com`;

Verify user privileges in Unity Catalog:

SHOW GRANTS ON TABLE my_catalog.my_schema.my_table;

4. Convert Hive Metastore Tables to Unity Catalog

Symptoms:

  • Tables are only available in hive_metastore.default but not in Unity Catalog.
  • Queries in Unity Catalog return “Table does not exist.”

Causes:

  • Hive Metastore tables do not automatically appear in Unity Catalog.
  • Unity Catalog requires tables to be managed within its metastore.

Fix:

Convert a table from Hive Metastore to Unity Catalog:

ALTER TABLE hive_metastore.default.my_table CONVERT TO DELTA;
CREATE TABLE my_catalog.my_schema.my_table USING DELTA LOCATION 'dbfs:/mnt/delta/my_table';

Recreate the table directly in Unity Catalog:

CREATE TABLE my_catalog.my_schema.new_table AS SELECT * FROM hive_metastore.default.my_table;

Verify the migration:

SHOW TABLES IN my_catalog.my_schema;

5. Check If External Tables Are Properly Registered

Symptoms:

  • Unity Catalog does not list external tables.
  • Tables are missing even though they exist in cloud storage (S3, ADLS, GCS).

Causes:

  • External tables need to be explicitly registered in Unity Catalog.
  • Incorrect storage permissions prevent Unity Catalog from accessing the data.

Fix:

Ensure the storage location is correct:

DESCRIBE TABLE hive_metastore.default.my_external_table;

Re-register external tables in Unity Catalog:

CREATE TABLE my_catalog.my_schema.my_external_table
USING DELTA LOCATION 's3://my-bucket/my-table-path/';

If using non-Delta formats, explicitly define the format:

CREATE TABLE my_catalog.my_schema.parquet_table
USING PARQUET LOCATION 's3://my-bucket/parquet-folder/';

Ensure Databricks has storage access permissions:

aws s3 ls s3://my-bucket/
az role assignment create --assignee <service-principal> --role "Storage Blob Data Contributor" --scope /subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<storage-name>

6. Check If Metastore Is Properly Configured

Symptoms:

  • Unity Catalog UI is enabled, but no tables are visible.
  • “No metastore configured for workspace” error appears.

Causes:

  • The workspace metastore is not properly assigned.
  • The metastore is missing required permissions.

Fix:

Check if a Unity Catalog metastore is assigned:

SHOW METASTORES;

Assign a metastore if missing:

databricks unity-catalog metastores assign --workspace-id <workspace-id>

Verify the assignment:

SHOW METASTORES;

7. Verify That Unity Catalog Tables Are Not Hidden by Filters

Symptoms:

  • Some tables do not appear when running SHOW TABLES in Unity Catalog.
  • Certain tables are visible to some users but not others.

Causes:

  • Table visibility is restricted due to schema or object filters.
  • SHOW TABLES only lists objects the user has access to.

Fix:

Ensure the user has SELECT permission on all required tables:

GRANT SELECT ON TABLE my_catalog.my_schema.* TO `user@example.com`;

Run queries without schema filters to check all available tables:

SHOW TABLES FROM my_catalog;

Check if tables were soft-deleted or temporarily unavailable.


Step-by-Step Troubleshooting Guide

1️⃣ Check if the table exists in Hive Metastore

SHOW TABLES IN hive_metastore.default;

2️⃣ Verify that the cluster and SQL Warehouse support Unity Catalog.
3️⃣ Ensure that the user has the correct permissions.
4️⃣ Convert Hive Metastore tables to Unity Catalog format.
5️⃣ Re-register external tables in Unity Catalog.
6️⃣ Check and assign the correct metastore.
7️⃣ Ensure that filters or permission issues are not hiding the tables.


Conclusion

If a table exists in Databricks but is not visible in Unity Catalog, check:
✅ The table’s actual location (Hive Metastore vs. Unity Catalog).
✅ Whether the cluster and SQL Warehouse support Unity Catalog.
✅ If permissions are correctly assigned.
✅ If tables need to be migrated from Hive Metastore.
✅ If external tables need to be re-registered.

By following this guide, you can ensure tables are visible and accessible in Unity Catalog.

guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x