CODEWITHSUNDEEP
azure-databricksazure-data-lakeazure-data-lake-gen2databricks-unity-catalog
Saurabh Mehta
Saurabh Mehta

Reputation: 141

Change location of existing Tables in Unity Catalog

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

Upvotes: 3

Views: 1713

Answers (2)

Cribber
Cribber

Reputation: 2943

You can directly edit the storage location in Databricks SQL:

ALTER TABLE catalog_name.schema_name.table_name
SET LOCATION 'new_storage_location';

-- Example:
-- SET LOCATION 's3://new-bucket/path/to/data_dir'; (or similar ADLS URI)

or you can use the databricks-sql-connector package to run the same query from a Python script.

Upvotes: 0

Dileep Raj Narayan Thumula
Dileep Raj Narayan Thumula

Reputation: 3250

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:

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

Upvotes: 1

Related Questions