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.

Spoon

Do you want to change hive warehouse directory? – Dendrology

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.

Desist

thanks for trying to help, but what I am looking for is - if I have already created a database, and now I want to move it to a different directory in HDFS. How can that be achieved ? – Spoon

My understanding of HIVE-1537 is that you will have to update the database location and manually move the files on HDFS. You will also have to change the location of each of the existing tables using the above alter. New tables will be put at the new location of the database. – Desist

See my updated answer, the previous one I had was incorrect and would not work. – Desist

Unfortunately the link died. Anyone found an alternative link? – Loincloth

Answer 2

If you are trying to fix the hdfs host that a database is using: As long as you have CLI access to the hive command, you can update all databases at once:

hive --service metatool -updateLocation hdfs://<the-new-address>:8020 hdfs://<the-old>:8020

Chloramphenicol