Change Hive Database location

Question

Is there a way to alter the location that a database points to?

I tried the following ways:

1. alter database <my_db> set DBPROPERTIES('hive.warehouse.dir'='<new_hdfs_loc>');

2. alter database <my_db> set DBPROPERTIES('location'='<new_hdfs_loc>');

3. alter database <my_db> set location '<new_hdfs_loc>';

The first two alter statements just changed the DB properties, however the database still points to the same location; while the third alter statement gave me semantics error.

Any help would be highly appreciated.

Answer 1

After some trial and error, I learned Hive does not support the following two conditions when running an ALTER on a database.

1. Not allowed to alter the directory location or database name.
2. Not allowed to delete or unset the db properties which have been defined before.

However, I found a link to a workaround that involves a direct DB update to the Hive Metastore and simply moving the directory on HDFS. http://gaganonthenet.com/2015/02/23/hive-change-location-for-database-or-schema/

Previous Answer that is Incorrect:

Tables created before the alter will live in the previous location. Only tables created after the alter will be put in the new location. You will have to manually move the directories on HDFS and update the table locations. See https://issues.apache.org/jira/browse/HIVE-1537 and https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/PartitionLocation for details regarding database and table locations.

To alter the location of a table, you can run ALTER TABLE <table> SET LOCATION "/path/to/new/location"; This can also be applied at the partition level.