Change location of existing Tables in Unity Catalog

Question

I want to know how can we change location of existing (Managed/External) Tables in Unity Catalog from Storage Account/Blob to Another. We want to have a functionality to make it flexible to store data either at root storage/external location/change in between

Answer 1

The below steps will help you move your metastore to a different Storage Account within Unity Catalog.

Step 1 : Create a new storage account for yourUnity Catalog. The Stoarge Account must be ADLS Gen2 account in the same region as your Databricks workspaces.

Step 2 : Create a container in the newly created storage account and note the ADLSv2 URI.

Step 3 : Create a service principal(SPN) and generate a client secret for the service principal.

Step 4 : The new service principal needs the Storage blob data contributor role in the storage account. Grant the Storage blob data contributor.

Step 5 : Log in to the Azure databricks account console and click on Data.

Step 6 : Click metastore name and then click Edit.

Step 7 : Now Update the ADLS Gen 2 path with the new storage container URI.

Step 8 : Click Save to update the metastore with the new storage account.

Reference:

• https://docs.databricks.com/data-governance/unity-catalog/create-metastore.html
• https://docs.databricks.com/data-governance/unity-catalog/get-started.html
• Effective Migration to Unity Catalog: A Guide

The below are the Upgrade steps

• The following steps will be performed by the Metastore Administrator.
• Create a Unity Catalog-enabled cluster (DBR 11.1 or later, “Single-User” Security Mode).
• Create CREDENTIAL via the Data Explorer UI in DBSQL documentation.
• Create EXTERNAL LOCATION, either via the Data Explorer UI in DBSQL or with the below command documentation.
• Grant Permission to CREATE TABLES, READ FILES, and WRITE FILES to the user/principal who will run the Databricks job. It is recommended that a principal will run the job rather than a user.

Create a Catalog and Database for the target table.

%sql
CREATE CATALOG uc_batch
CREATE SCHEMA  uc_batch.upgraded _sqldf

Grant access to the user performing the upgrade as well as to the user/principal running the streaming job.

%sql
CREATE CATALOG uc_batch
CREATE SCHEMA  uc_batch.upgraded _sqldf

%sql
GRANT USAGE ON CATALOG uc_batch; TO 'data_eng'
Grant USAGE.CREATE ON SCHEMA uc_batch.upgraded TO 'data_eng'

The following steps can be performed by a developer. The developer has to be granted CREATE TABLES, READ FILES, and WRITE FILES rights on the external location. The users must be given access to a UC schema or permission to create one.

To move the existing managed table to the external table, perform the following steps:

• Copy data from managed tables to datalake by creating a dataframe and writing the output of the datalake location using a mount point.
• Create an external table in Unity Catalog metastore using External Location to point to the data in datalake.

To move the existing managed table to the Unity Catalog managed table, perform the following steps:

Deep clone the old target table to a newly created table in the Unity Catalog.

%sql
CREATE TABLE IF NOT EXISTS uc_batch.upgraded.salary_adjusted DEEP CLONE hive_metastore.default.salary_adjusted_sqldf

• Docs: uc-metastore-admin-quickstart • Docs: hive-metastore • Docs: index