Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change Hive Database location

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.

like image 464
Harman Avatar asked Jun 01 '15 16:06

Harman


2 Answers

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.

like image 162
brandon.bell Avatar answered Sep 19 '22 15:09

brandon.bell


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
like image 35
TheNumenorean Avatar answered Sep 20 '22 15:09

TheNumenorean