topic Re: Renaming the database Name in Databricks in Get Started Discussions

Renaming the database Name in Databricks

Ramakrishnan83 — Tue, 06 Feb 2024 15:27:34 GMT

Team,

Initially our team created the databases with the environment name appended. Ex: cust_dev, cust_qa, cust_prod.

I am looking to standardize the database name as consistent name across environments. I want to rename to "cust".

All of my tables are external tables in this database pointing to a storage in ADLS. which option will help?

1. Does databricks supports rename command ?

2. Create a new database CUST and recreate all the tables in new database . Steps to be followed

a. create database cust

b. drop table <table1> from cust_dev

c. create table cust.<table1>

What is recommended option?

#deltatable

Re: Renaming the database Name in Databricks

Hkesharwani — Tue, 06 Feb 2024 15:59:46 GMT

In Databricks, you cannot directly rename a schema using a specific SQL statement like ALTER SCHEMA or RENAME SCHEMA. However, you can achieve a similar result by following these steps:

Create a new schema with the desired name using the CREATE SCHEMA statement.
Transfer all the (tables, views, functions, etc.) from the old schema to the new schema using ALTER TABLE, ALTER VIEW, or other appropriate statements.
ALTER TABLE old_schema.table_name SET SCHEMA new_schema;
ALTER VIEW old_schema.view_name SET SCHEMA new_schema;
Update any references to the old schema in your code or queries to use the new schema name.
Finally, if you no longer need the old schema, you can drop it using the DROP SCHEMA statement.

Re: Renaming the database Name in Databricks

Ramakrishnan83 — Wed, 07 Feb 2024 03:45:02 GMT

Hi,

I don't see SET Schema as keyword in Alter table statement. https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table.html .

I tried your statement.

alter table ****.table_row_count SET SCHEMA ****.table_row_count

ParseException: no viable alternative at input 'alter table****.table_row_count SET SCHEMA'

Re: Renaming the database Name in Databricks

Hkesharwani — Wed, 07 Feb 2024 06:28:50 GMT

Hi,
Apologies, I just tried the above statement and they were not working. You may try creating the table with CTAS statements.

create table new_schema.table_name as select * from old_schema.table_name

Re: Renaming the database Name in Databricks

roman_tkachuk — Sun, 21 Apr 2024 07:25:03 GMT

It does not work if you need to keep partitioning or table properties.

Solutions:

1. CREATE TABLE .. LIKE ..
2. CREATE TABLE ... CLONE

Re: Renaming the database Name in Databricks

bearded_data — Fri, 21 Jun 2024 22:16:14 GMT

Since you can't simply rename the schema, here are the steps you need to follow in order to "Rename a Schema".

Create new Schema.

CREATE SCHEMA `catalog`.`new_schema_name`;

For every object in the schema create a copy in the new schema. Sample CTAS for a table move shown below, but you will also need to do this with other objects in the schema (functions, views, models, etc.).

CREATE TABLE `catalog`.`new_schema_name`.`table_name` AS SELECT * FROM `catalog`.`old_schema_name`.`table_name`;

Drop the old objects in the old schema. Sample drop for tables shown below, but you will also need to do this
with other objects in the schema (functions, views, models, etc.).

DROP TABLE `catalog`.`old_schema_name`.`table_name`;

Once you've dropped all objects and have a blank schema, drop the schema.

DROP SCHEMA `catalog`.`old_schema_name`;

Re: Renaming the database Name in Databricks

Avvar2022 — Sat, 22 Jun 2024 00:26:32 GMT

You can also use “CASCADE” to drop schema and tables as well. It is recursive.