cancel
Showing results for 
Search instead for 
Did you mean: 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results for 
Search instead for 
Did you mean: 

Renaming the database Name in Databricks

Ramakrishnan83
New Contributor III

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

 

6 REPLIES 6

Hkesharwani
Contributor II

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:

  1. Create a new schema with the desired name using the CREATE SCHEMA statement.
  2. 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;
  3. Update any references to the old schema in your code or queries to use the new schema name.
  4. Finally, if you no longer need the old schema, you can drop it using the DROP SCHEMA statement.

Harshit Kesharwani
Data engineer at Rsystema

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'

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  

Harshit Kesharwani
Data engineer at Rsystema

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

Solutions:

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

 

bearded_data
New Contributor III

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`;​

 

 

Avvar2022
Contributor

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group