How can I change the schema name and table name of a table in Databricks with Unity Catalog enabled?

1.6k views Asked by At

I have a table, located at my_catalog.my_schema.my_table in Databricks (version 13.3 LTS) with Unity Catalog enabled.

How can I rename this table to my_catalog.my_new_schema.my_new_table without doing a full table scan and a full copy? I want to do this as a metadata change without really going over the full data, because the table is huge.

I've tried the following:

ALTER TABLE my_catalog.my_schema.my_table RENAME TO my_catalog.my_new_schema.my_new_table

but this results in the following error:

[CANNOT_RENAME_ACROSS_SCHEMA] Renaming a table across schemas is not allowed.

2

There are 2 answers

7
partlov On BEST ANSWER

It is not possible to use renaming in order to move table from one catalog (or schema) to another. That would cause moving all the files from one location to another. If you want to do that you will need to do deep clone of source table and once clone is done remove old table.

Renaming in same catalog is possible with:

USE my_catalog.my_schema;
ALTER TABLE my_table RENAME TO my_new_table;
1
Robert Mosley On

Is this an external table or a managed table? If it's external, then you can drop the table in one schema and create it (specifying the location) in the other schema. No scans or rewrites necessary. The data's location will not change, just the reference.

If this is a managed table, I don't believe it's possible to move between schemas/catalogs without rewriting the data.