Issues Creating External Tables in Hive Metastore vs. Unity Catalog

jv_v — Fri, 06 Sep 2024 12:52:23 GMT

Hi Databricks Community,

I’m encountering some challenges when working with external tables in Databricks. I’m using both Unity Catalog and Hive Meta store and have noticed differences in how external tables are handled.

Unity Catalog: I can successfully create a Delta table using the following query, specifying an external location:

CREATE TABLE IF NOT EXISTS 'UC-catalog.schema.table'
USING DELTA
LOCATION 'Path'.

2.Hive Metastore: When I attempt a similar approach using the Hive Metastore, I run into problems. The table gets created, but I am unable to see the data. Here is the query I used:

CREATE TABLE IF NOT EXISTS 'hivemetastore.schema.table'
USING DELTA
LOCATION 'Path'.

Despite the table being created, the data isn’t visible. And facing below error

Questions:

Can external tables be created in Hive Metastore from external locations like Azure Blob or AWS S3 buckets? If so, what is the correct approach?
What could be causing the data visibility issue in Hive Metastore? I’ve checked the path and permissions but am still encountering problems.

Any guidance or best practices for handling external tables in Hive Metastore would be greatly appreciated!

Thanks in advance for your help!

Re: Issues Creating External Tables in Hive Metastore vs. Unity Catalog

Imalkov — Fri, 27 Sep 2024 12:57:17 GMT

hi @jv_v ,

that's possible to put a path to external storage i.e.:

documentation

CREATE OR REPLACE TABLE hive_metastore.schema.table
  LOCATION 's3://depts/finance/sec_filings'

Can you share which issue do you have cause it's not clear from your question

topic Re: Issues Creating External Tables in Hive Metastore vs. Unity Catalog in Data Governance

Issues Creating External Tables in Hive Metastore vs. Unity Catalog

Re: Issues Creating External Tables in Hive Metastore vs. Unity Catalog