Queries Upgradation from HMS to UC

[DjtheDE]

I am currently doing Queries upgradation from HMS to Unity catalog. I would like to know and understand a few best practices to update the queries and also use a 3-level namespace for the existing query structure. Please guide me!

[Kaniz]

Hi @DjtheDE, When upgrading your queries from Hive Metastore (HMS) to Unity Catalog, there are best practices you can follow to ensure a smooth transition and effective data governance.

Let’s dive into some recommendations:

1. Understand Data Governance Building Blocks:

   • Unity Catalog is a fine-grained governance solution for data and AI on the Databricks Platform.
   • It simplifies security and governance by providing a central place to administer and audit data access.
   • Key building blocks include:
     • Metastore: The top-level container of objects in Unity Catalog. It manages data assets (tables, views, volumes) and permissions.
     • Catalog: The highest level in the data hierarchy, representing a logical grouping of schemas. Catalogs are the primary unit of data isolation.
     • Schema: A logical grouping of tables/views within a catalog.
     • Table/View/Volume: Data assets managed within catalogs.
     • Metastore Admins: Manage all objects in the metastore.
     • Data Isolation: Begins at the catalog level¹.

2. Upgrade Tables and Views:

   • Use the Upgrade Wizard to migrate metadata from HMS to Unity Catalog.
   • Click “Create Query for Upgrade” and run the generated SQL statements.
   • Each table’s metadata will be copied to Unity Catalog, marked as upgraded.
   • Define fine-grained access control using the Permissions tab for each new table².

3. Plan Your Data Isolation Model:

   • Consider creating separate catalogs for different data access requirements.
   • For example, have a catalog for production data and another for development data.
   • Organize catalogs based on organizational units or software development lifecycle scopes¹.

4. Configure Unity Catalog Metastore:

   • Set up metastores at the account level. Assign them to multiple Azure Databricks workspaces in the same region.
   • Metastores provide regional isolation but are not intended as units of data isolation.
   • Configure external locations and storage credentials¹.

5. Leverage Cluster Configurations:

   • Unity Catalog integrates with Databricks clusters. Utilize cluster configurations for efficient data access and management.

6. Audit Logs and Security:

   • Unity Catalog provides audit logs for tracking data access and changes.
   • Set up fine-grained permissions to control who can access and modify data.

7. Share Data through Delta Sharing:

   • Delta Sharing allows secure data sharing with users outside your organization.
   • It uses Unity Catalog to manage and audit sharing behavior.

8. Use DBFS with Unity Catalog Clusters:

   • Leverage Databricks File System (DBFS) for efficient data storage and retrieval.

Remember that Unity Catalog and Delta Sharing together form a robust solution for data governance. By following these best practices, you’ll enhance security, simplify administration, and ensure effe...¹³.