Move/Migrate database from dbfs root (s3) to other mounted s3 bucket

[Jan_A]

Hi,

I have a databricks database that has been created in the dbfs root S3 bucket, containing managed tables. I am looking for a way to move/migrate it to a mounted S3 bucket instead, and keep the database name.

Any good ideas on how this can be done?

Thanks

Jan

[Hubert-Dudek]

• Just copy all data and than alter table location:

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION 'new_location';

• Alternatively create new table in new location and than use INSERT INTO SELECT to move data

[Kaniz_Fatma]

Hi @Jan Ahlbeck​ , Does @Hubert Dudek​ 's reply answer your question?

[Jan_A]

Hi @Kaniz Fatma​ 

Partly... Copied data and alter table works fine, but the database location still points to the root location. So when new tables are created (with no location), they are create in root. So a way to change the database location would be nice 🙂

[User16753724663 · Accepted solution]

Hi @Jan Ahlbeck​ 

we can use below property to set the default location:

"spark.sql.warehouse.dir": "S3 URL/dbfs path"

Please let me know if this helps.

[Kaniz_Fatma]

Hi @Jan Ahlbeck​ , Did @DARSHAN BARGAL​ 's solution work in your case?