02-06-2024 07:27 AM
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
02-06-2024 07:58 AM - edited 02-06-2024 07:59 AM
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:
02-06-2024 07:45 PM
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.
02-06-2024 10:28 PM
Hi,
Apologies, I just tried the above statement and they were not working. You may try creating the table with CTAS statements.
04-21-2024 12:25 AM
It does not work if you need to keep partitioning or table properties.
Solutions:
1. CREATE TABLE .. LIKE ..
2. CREATE TABLE ... CLONE
06-21-2024 03:12 PM - edited 06-21-2024 03:16 PM
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`;
06-21-2024 05:26 PM
You can also use “CASCADE” to drop schema and tables as well. It is recursive.
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