How to rename a database in azure databricks?

user_11578552 0
Viewed 3785

I am trying to rename a database in azure databricks but I am getting the following error:

no viable alternative at input 'ALTER DATABASE inventory

Below is code:

%sql 
use inventory;
ALTER DATABASE inventory MODIFY NAME = new_inventory;

Please explain what is meant by this error "no viable alternative at input 'ALTER DATABASE inventory" and how can I solve it

azure-databricks databricks apache-spark-sql
2 Answers

It's not possible to rename database on Databricks. If you go to the documentation, then you will see that you can only set DBPROPERTIES.

If you really need to rename database, then you have 2 choices:

  • if you have unmanaged tables (not created via saveAsTable, etc.), then you can produce SQL using SHOW CREATE TABLE, drop your database (be careful anyway), and recreate all tables from saved SQL
  • if you have managed tables, then the solution would be to create new database, and either use CLONE (only for Delta tables), or CREATE TABLE ... AS SELECT for other file types, and after that drop your database
Alex_Ott_186270

Alex Ott's answer, to use Clone, is OK if you do not need to maintain the versioning history of your database when you rename it.

However if you wish to time travel on the database of Delta tables after the renaming, this solution works:

  • Create your new database, specifying its location
  • Move the file system from the old location to the new location
  • For each table on the old database, create a table on the new database, based on the location (my code relies on the standard file structure of {database name}/{table name} being observed). No need to specify schema as it's just taken from the files in place
  • Drop old database

You will then be left with a database with your new name, that has all of the data and all of the history of your old database, i.e. a renamed database of Delta tables.

Pyspark method (on databricks, with "spark" and "dbutils" already defined by default) :

def rename_db(original_db_name, original_db_location, new_db_name, new_db_location):
  spark.sql(f"create database if not exists {new_db_name} location '{new_db_location}'")
  dbutils.fs.mv(original_db_location,new_db_location,True)
  for table in list(map(lambda x: x.tableName, spark.sql(f"SHOW TABLES FROM {original_db_name}").select("tableName").collect())):
    spark.sql(f"create table {new_db_name}.{table} location '{new_db_location}/{table}'")
  spark.sql(f"drop database {original_db_name} cascade")
  return spark.sql(f"SHOW TABLES FROM {new_db_name}")
user_50629920
Related