Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Change Hive database location.

avatar
Explorer

Hello.

I have several Hive databases that were created by default in Hive warehouse.

I need to change the location those databases and don't lost data, in addition I don't have direct access to Hive metastore. Maybe there is some good advice how to do it simple? So far, I haven't found any good solution to this issue.

Thanks!

6 REPLIES 6

avatar
Master Collaborator

Is this a cluster with sentry and sentry hdfs synchronisation enabled?

 

If it is just hive without sentry or sentry hdfs synchronisation enabled and you want to switch from default /user/hive/warehouse to a new path then following steps would do

 

Step1: Stop hive
Step2: Take backup of your metastore database
Step3: Change the CM > Hive > Configuration > hive.metastore.warehouse.dir setting to '<new path>'
- Deploy client configuration
Step4: Move the current directory
i.e hdfs dfs -mv /user/hive/warehouse <new_path>

Step5:
Update HMS DB tables.
Login to backend hms db and update the following tables in metastore database.
update SDS set location = (select replace(location, '/user/hive/warehouse', '<new_path>') ) where location like '%/user/hive/warehouse%';
update DBS set DB_LOCATION_URI = (select replace(DB_LOCATION_URI, '/user/hive/warehouse', '<new_path>') ) where DB_LOCATION_URI like '%/user/hive/warehouse%';
update SKEWED_COL_VALUE_LOC_MAP set LOCATION = (select replace(LOCATION, '/user/hive/warehouse', '<new path>') ) where LOCATION like '%/user/hive/warehouse%';
update SERDE_PARAMS set PARAM_VALUE = (select replace(PARAM_VALUE, '/user/hive/warehouse', '<new path>') ) where PARAM_VALUE like '%/user/hive/warehouse%';
update TABLE_PARAMS set PARAM_VALUE = (select replace(PARAM_VALUE, '/user/hive/warehouse', '<new path>') ) where PARAM_VALUE like '%/user/hive/warehouse%';

Step6:
- Start hive

 

 Make sure to test this steps on your test clusters first before proceeding towards production cluster.

 

 

avatar
Master Collaborator

Sorry It seems I misread your request. The steps I quoted above is for changing the whole default location of hive warehouse to a new path. If you are looking for changing the database location of existing database alone inside warehouse and dont wants to move the whole warehouse to new location, the above steps aren't right

avatar
Explorer

Thanks!
May be there is some way to resolve my issue?

avatar
Master Collaborator

avatar
Explorer

Hello.
I don't have access to metastore, maybe there is some other way to change database location?
Thanks.

avatar
Super Guru

Have you considered creating a copy of the table as external (in new location you want) and then INSERT into NewTable SELECT * FROM OldTable?