Creating a persistent view that references both Unity Catalog and Hive Metastore objects is not supported in Unity Catalog
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-30-2022 04:26 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-30-2022 04:47 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-30-2022 04:57 AM
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 ๐
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-30-2022 06:56 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
โ11-30-2022 07:01 AM
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