Oracle HIVE Metadata to Databricks UC migration

[HarshVardhan1]

Dear Databricks Member,

Could you please recommend the best solution for the following use case?

Use Case:

The Hive Metastore is currently used to manage metadata and is backed by an Oracle database. This metastore stores critical information such as schemas, tables, partitions, and data locations. Databricks is used as the primary platform for data engineering, machine learning, and reporting workloads.

The requirement is to migrate Hive Metastore metadata from the Oracle database to Databricks Unity Catalog, which provides centralized governance, fine-grained access control, and better integration across Databricks workspaces.

✅ Migration Objective

Migrate all metadata (databases, tables, views, and permissions) from:

Hive Metastore (Oracle-backed)

➡️ Databricks Unity Catalog

[Ashwin_DSA]

Hi @HarshVardhan1,

The best approach for your scenario is usually not to migrate the Oracle database itself into Unity Catalog. Instead, the supported pattern is to migrate the Hive Metastore objects into Unity Catalog using Databricks migration tooling and workflows.

I would recommend a hard-migration plan built around UCX, with different migration methods depending on table type. Databricks positions UCX as the automation toolkit for assessment, migration planning, table migration, group migration, and code migration, and specifically recommends UCX for larger or more complex Hive Metastore migrations.

For table migration itself, the rule of thumb is to use SYNC for eligible external-table scenarios because it upgrades metadata into Unity Catalog without copying data and can be rerun to keep source and target aligned during transition, as described in How to Seamlessly Upgrade Your Hive Metastore Objects to the Unity Catalog Metastore Using SYNC

You can also use DEEP CLONE or CTAS for managed tables and unsupported scenarios, because those require data replication rather than metadata-only migration, as described in How to upgrade your Hive tables to Unity Catalog

A sensible sequence would be:

• set up the Unity Catalog foundation first... metastore, catalogs, schemas, storage credentials, and external locations
• run UCX discovery and readiness assessment to inventory tables, permissions, dependencies, and code impacts
• migrate eligible external tables with SYNC, ideally starting with dry run
• migrate managed tables with DEEP CLONE or CTAS into Unity Catalog-supported locations
• use UCX workflows to help with permissions and code migration, then cut workloads over in phases

An important limitation for your specific scenario is the Oracle backing store. If the Hive Metastore is an external HMS hosted in Oracle, I would not recommend Hive Metastore Federation as the main option. This is because the public federation docs for external HMS list supported host database types as MySQL, SQL Server, and Postgres, and supported HMS versions as 0.13, 2.3, and 3.1.. Oracle is not listed there. See the public external federation documentation here: Enable Hive metastore federation for an external Hive metastore.

A few other practical limitations are worth calling out. SYNC does not magically make every Hive table eligible. Databricks migration guidance in How to upgrade your Hive tables to Unity Catalog makes clear that the right method depends on table type, storage location, and file format, and unsupported storage schemes will fail eligibility checks. Also, even when UCX automates much of the migration, code that reads or writes legacy HMS objects still needs to be repointed to the new Unity Catalog objects during final cutover.

Hope this gives you a direction.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.