topic Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog in Data Governance

Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog

quakenbush — Wed, 30 Nov 2022 12:26:44 GMT

I tried to create a view in hive_metastore.default which would access a table from a different catalog. Is there any chance to do so?

eg.

create view myTest as select * from someCatalog.someSchema.someTable

Re: Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog

Senthil1 — Wed, 30 Nov 2022 12:47:33 GMT

Could you explain your problem and use case in detail?

Better way to model this to create a UNITY catalog on top of Hive metastore, Link Hive metastore to the unity catalog and build a CENTRAL catalog. I have seen many customers linked there existing Hive external metastores to Unity catalog and also there is Azure Purview integration happening with Databricks Unity Catalog.

So, there are options to integrate with existing metastore and governance,lineage tools

Re: Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog

quakenbush — Wed, 30 Nov 2022 12:57:28 GMT

Sure, background of my question is I have legacy software (Oracle Analytics Server and its Client Tools) that can only see hive_metastore and there's no way I know of to change the catalog. I'd like to create different catalogs in Databricks such as dwh_dev, dwh_stage and dwh_prod and reverse these models into the Oracle BI tool.

Hence my idea was to bypass this problem by creating views in any schema of the hive_metastore catalog which would then access the respective table at a different place.

I'm open to any better solution which perhaps fits the Databricks/Unity Catalog world more elegant 🙂

Re: Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog

LandanG — Wed, 30 Nov 2022 14:56:43 GMT

Hi @Roger Bieri ,

Within UC a view can be created from tables and other views in multiple schemas and catalogues.

However, you cannot create a view in UC referencing hive_metastore or vice versa.

Hopefully that answers the question.

Re: Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog

Senthil1 — Wed, 30 Nov 2022 15:01:23 GMT

I will suggest the below links will help you,

For exposing your Hive_metastore multiple catalogs,

1) Create 3 catalogs for each environment under single metastore by using Unity Catalog

2) Now expose the Unity Catalog using DELTA SHARING to your BI app/tool

i) Create a SHARE and tag your tables. views from all the required CATALOGS

ii) Create a RECEPIENT and attach that SHARE with necessary GRANT privileges

Here data won't be replicated or transferred, but it will provide authorized views for all your apps.

Follow this, it works

Share data securely using Delta Sharing - Azure Databricks | Microsoft Learn

Re: Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not

Pranav001 — Thu, 27 Mar 2025 15:52:58 GMT

Hi, so I don't know if you still have this problem (seeing as this post is almost 3 years old now haha) but I came across this when I was trying to solve an issue. What worked for me was creating the unity catalog table in the hive_metastore using an SQL macro and then I pointed the hive view to the hive table instead of the one in unity catalog. I don't know if there is a more elegant way to do this with databricks and dbt but this was a good option for my use case. Hope this comment helps anybody else who might come across this issue in the future!