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

[quakenbush]

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

[Senthil1]

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

[quakenbush]

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 🙂

[LandanG]

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.

[Senthil1]

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