How to create a Materialized View in Azure Databricks stored in a Managed Catalog, based on data loaded from a foreign catalog

Question

I am currently facing a problem with creating a materialized view in Catalog_2.Schema_B (Managed_Catalog) based on data I have connected from postgre SQL external source loaded into Catalog1_Schema_A (Foreign_Catalog).

The error I get only says "schema_a.postres.database.azure.com" which maybe suggests it is a connectivity issue to the Foreign catalog. However, I can see that the data is loaded in properly.

I have confirmed that

• I have all the privileges necessary on both Schemas A and B
• I am using Unity Catalog enabled pro SQLwarehouse.
• I can use a select query to directly Select a table from Schema_A
• It seems like I can create a view but not a materialized view.

Answer 1

The error message you provided (schema_a.postres.database.azure.com) is more about a connectivity issue.

Materialized views need to be refreshed to update the data. You can refresh the materialized view using the REFRESH MATERIALIZED VIEW statement.

REFRESH MATERIALIZED VIEW Catalog_2.Schema_B.my_materialized_view;