Any way to move the unity catalog to a new external storage location?

[harvey-c]

Dear Databricks Community

The question is about changing an existing unity catalog to a new storage location. For example: 

With an existing unity catalog (i.e. catalog1)  includeing schemas and volumes. The catalog is based on an external location (i.e.storage1) , I tried to update the external location url from storage1 to storage2 and received the warning:

External Location has dependent catalogs, schemas, managed or external tables/volumes; use force option to update url anyway

Then I use the force command in ALTER EXTERNAL LOCATION, to update the url to storage2 successfully.

However, when I tried to access the schema in the catalog1, the error "the external location storage1 does not exist" occurs. It looks like even I forced the update of the external location, the data objects of the catalog are not updated automatically.

Could you please advise the approach to achieve the goal? I would like to reduce the effort to recreate all objects under the existing catalog again.. 

Thanks.

[Kaniz · Accepted solution]

Hi @harvey-c, Certainly! Updating the external location of an existing Unity Catalog can be tricky, but I’ll guide you through the process.

Here are the steps to achieve your goal without having to recreate all objects under the existing catalog:

Understand External Locations and Storage Credentials:

• External locations and storage credentials allow Unity Catalog to read and write data on your cloud tenant on behalf of users.
• They are used for creating, reading from, and writing to external tables and volumes and assigning managed storage for managed table and volume data at the catalog or schema level.
• A storage credential represents an authentication and authorization mechanism for accessing data stored on your cloud tenant.
• An external location combines a cloud storage path with a storage credential that authorizes access to the cloud storage path.

Update the External Location:

• First, ensure you have the necessary permissions (Azure Databricks account admin, metastore admin, or CREATE STORAGE CREDENTIAL privilege).
• Go to your Unity Catalog and locate the existing external location (in your case, storage1).
• Rename the external location to something temporary (e.g., storage1_temp).
• Create a new external location (storage2) pointing to the desired area (storage2).
• Assign the appropriate storage credential to the new external location.

Update the Catalog Objects:

• You need to update the catalog objects (schemas, tables, volumes) to use the new external location (storage2).
• For each schema, table, or volume using the old location (storage1), alter it to use the new location (storage2).
• You can do this using the ALTER EXTERNAL LOCATION command with the FORCE option, as you did before.
• For example:ALTER EXTERNAL LOCATION catalog1.schema1.table1 SET LOCATION 'storage2/path/to/table1' FORCE;

Verify and Test:

• After updating the external location for all relevant objects, verify that the schema and data are accessible without errors.
• Query the tables and volumes to ensure they are pointing to the correct storage location (storage2).

Remember that external locations can also be used for managed tables and volumes, not just external ones. By following these steps, you can achieve your goal without recreating all objects under the existing catalog. Good luck! 🚀🔍

For more detailed information, refer to the official documentation on managing external locations and storage credentials.

[harvey-c]

Thank you Kaniz

Basically, it is required to update the location for each data objects in the old catalog. 

The external location in the following instruction should be removed as the objects are altered rather than the external location. 

• For example:ALTER EXTERNAL LOCATION catalog1.schema1.table1 SET LOCATION 'storage2/path/to/table1' FORCE;

[Kaniz]

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!