topic Any way to move the unity catalog to a new external storage location? in Administration & Architecture

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

harvey-c — Sun, 03 Dec 2023 06:16:13 GMT

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.

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

harvey-c — Mon, 04 Dec 2023 01:12:07 GMT

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;

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

NandiniN — Thu, 07 Nov 2024 23:17:34 GMT

https://docs.databricks.com/ja/sql/language-manual/sql-ref-syntax-ddl-alter-location.html