How to delete or clean Legacy Hive Metastore after successful completion of UC migration

[bhanu_dp]

Say we have completed the migration of tables from Hive Metastore to UC. All the users, jobs and clusters are switched to UC. There is no more activity on Legacy Hive Metastore.

What is the best recommendation on deleting or cleaning the Hive Metastore which are not being anymore.

Thank you.

[Rjdudley]

The ability to do this will be in public preview this quarter, so sometime soon you should be able to disable this.

[Nivethan_Venkat]

Hi @bhanu_dp,

If I understood correctly, all the tables / objects in your HIVE METASTORE is now migrated to UC Catalog. If you have used UCX for migration, you might not see the process / workflow for removing the HIVE METASTORE from your workspace as of now, and this will be coming soon as part of UCX.

UCX Reference: https://docs.databricks.com/aws/en/data-governance/unity-catalog/ucx

But instead if you want to remove as of now, the below approach must be helpful, where under the hive_metastore -> ucx -> tables you can see the tables under your hive as shown in image below:

List down the tables under your ucx schema:

# Read table list from ucx metadata
table_df = spark.sql("SELECT database, name FROM hive_metastore.ucx.tables")

Iterate over the results with the below query to drop them from your hive_metastore.

# Iterate over tables and drop them
for row in table_df.collect():
    schema_name = row["database"]
    table_name = row["name"]
    
    drop_query = f"DROP TABLE IF EXISTS hive_metastore.{schema_name}.{table_name}"
    
    try:
        spark.sql(drop_query)
        print(f"Dropped table: {schema_name}.{table_name}")
    except Exception as e:
        print(f"Failed to drop table {schema_name}.{table_name}: {e}")

PS: Inorder to leverage this approach your workspace you have UCX installed to capture the metadata details. Follow this link to install UCX in your workspace / all for workspaces in your databricks account: https://databrickslabs.github.io/ucx/docs/installation/

Please let us know if you found this helpful / or if more guidance is required.

Best Regards,
Nivethan V