,

Cannot Use Materialized Views with Unity Catalog

Posted by

Introduction

Materialized Views (MVs) in Databricks SQL allow precomputed query results to be stored and automatically refreshed. However, when using Unity Catalog, you may encounter issues preventing the creation or usage of Materialized Views.

🚨 Common issues when using Materialized Views with Unity Catalog:

  • Cannot create a Materialized View in Unity Catalog.
  • Error: “Materialized Views are not supported in Unity Catalog.”
  • Refresh of Materialized View fails with permission errors.
  • Existing Materialized Views stop working after enabling Unity Catalog.

This guide explains why Materialized Views may not work in Unity Catalog, troubleshooting steps, and best practices for handling this limitation.


1. Check If Materialized Views Are Supported in Unity Catalog

Symptoms:

  • Error: “Materialized Views are not supported in Unity Catalog.”
  • Cannot create a Materialized View in Unity Catalog using SQL.

Causes:

  • Unity Catalog does not currently support Materialized Views.
  • Materialized Views are only supported in Hive Metastore (legacy Databricks workspaces).

Fix:

Use Delta Live Tables (DLT) as an alternative to Materialized Views.

  • DLT pipelines provide similar functionality for maintaining fresh query results.
  • Create a DLT pipeline instead of a Materialized View:
CREATE LIVE TABLE my_dlt_table AS SELECT * FROM my_catalog.my_schema.source_table;

Use standard Delta tables with scheduled updates if MVs are unavailable:

CREATE TABLE my_catalog.my_schema.cached_table AS 
SELECT * FROM my_catalog.my_schema.source_table;
  • Then, schedule a refresh using Databricks Jobs.

2. Cannot Refresh Materialized Views in Unity Catalog

Symptoms:

  • Materialized View refresh fails in Unity Catalog.
  • Permission errors when running ALTER MATERIALIZED VIEW ... REFRESH.

Causes:

  • Unity Catalog does not support automated refresh of Materialized Views.
  • The underlying tables have restricted access in Unity Catalog.

Fix:

Manually refresh Materialized Views using a SQL job:

INSERT OVERWRITE my_catalog.my_schema.mv_table 
SELECT * FROM my_catalog.my_schema.source_table;

Ensure correct permissions for Materialized View refresh:

GRANT SELECT, INSERT, DELETE ON TABLE my_catalog.my_schema.mv_table TO `user@example.com`;

Consider using Delta Live Tables (DLT) or Materialized Views outside Unity Catalog.


3. Cannot Query Existing Materialized Views After Migrating to Unity Catalog

Symptoms:

  • Queries on Materialized Views return errors after enabling Unity Catalog.
  • Materialized Views appear missing from SHOW TABLES.

Causes:

  • Materialized Views are not automatically migrated to Unity Catalog.
  • Databricks SQL does not recognize Materialized Views under Unity Catalog.

Fix:

Recreate Materialized Views as Delta tables in Unity Catalog:

CREATE TABLE my_catalog.my_schema.mv_table AS 
SELECT * FROM my_catalog.my_schema.source_table;

Manually export and recreate the Materialized View data:

databricks fs cp dbfs:/mnt/hive_metastore/mv_table.parquet dbfs:/mnt/unity_catalog/mv_table.parquet

Check if legacy Materialized Views still exist in Hive Metastore:

SHOW TABLES IN hive_metastore.default;

4. Workarounds for Materialized Views in Unity Catalog

Since Materialized Views are not fully supported in Unity Catalog, use these alternatives:

1. Delta Live Tables (DLT) as a Replacement for Materialized Views

  • DLT automatically refreshes query results, like a Materialized View.
  • Example:
CREATE LIVE TABLE my_dlt_table AS 
SELECT * FROM my_catalog.my_schema.source_table;

2. Scheduled Query Jobs for Data Caching

  • Store precomputed query results in a Delta table and refresh it periodically.
  • Example:
CREATE OR REPLACE TABLE my_catalog.my_schema.cached_table AS 
SELECT * FROM my_catalog.my_schema.source_table;
  • Use Databricks Jobs to refresh the table at scheduled intervals.

3. Use Databricks SQL Warehouses for Query Caching

  • Enable Query Caching in SQL Warehouses to improve performance.
  • Reduce load on source tables by caching results in Databricks SQL.

5. Step-by-Step Troubleshooting Guide

Step 1: Check If Materialized Views Are Supported

SHOW CREATE TABLE my_catalog.my_schema.mv_table;
  • If Unity Catalog is enabled, Materialized Views will not be supported.

Step 2: Verify If the Materialized View Exists in Hive Metastore

SHOW TABLES IN hive_metastore.default;
  • If it exists in Hive Metastore but not in Unity Catalog, you need to migrate it.

Step 3: Convert Materialized Views to Delta Tables

CREATE TABLE my_catalog.my_schema.cached_table AS 
SELECT * FROM my_catalog.my_schema.source_table;
  • Use a scheduled job to refresh the table periodically.

Step 4: Use Delta Live Tables for Automatic Refreshing

CREATE LIVE TABLE my_dlt_table AS 
SELECT * FROM my_catalog.my_schema.source_table;
  • This automates updates like a Materialized View.

6. Best Practices for Using Unity Catalog Without Materialized Views

Use Delta Live Tables for Real-Time Updates

  • DLT automatically refreshes query results.
  • Works like Materialized Views but fully supports Unity Catalog.

Schedule Data Refresh Jobs

  • If DLT is not an option, create a scheduled job to update a Delta table.

Enable Query Caching in Databricks SQL

  • Reduces compute costs by caching query results in SQL Warehouses.

Use Materialized Views Only in Hive Metastore Workspaces

  • If Materialized Views are required, keep them in a legacy Hive Metastore instead of Unity Catalog.

7. Conclusion

💡 Unity Catalog does not currently support Materialized Views. To work around this:
✅ Use Delta Live Tables (DLT) for automated refresh.
Create Delta tables with scheduled refresh jobs instead of MVs.
Enable Query Caching in Databricks SQL Warehouses.
✅ If necessary, keep Materialized Views in Hive Metastore instead of Unity Catalog.

By using these alternatives, you can achieve the same functionality as Materialized Views in Unity Catalog without running into compatibility issues.

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