cancel
Showing results for 
Search instead for 
Did you mean: 
Community Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

Renaming the database Name in Databricks

Ramakrishnan83
New Contributor II

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

 

4 REPLIES 4

Hkesharwani
New 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.

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  

Kaniz
Community Manager
Community Manager

Hey there! Thanks a bunch for being part of our awesome community! 🎉 

We love having you around and appreciate all your questions. Take a moment to check out the responses – you'll find some great info. Your input is valuable, so pick the best solution for you. And remember, if you ever need more help , we're here for you! 

Keep being awesome! 😊🚀

 

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.