Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Change Hive database location.

Highlighted

Change Hive database location.

New Contributor

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
Highlighted

Re: Change Hive database location.

Expert Contributor

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.

 

 

Highlighted

Re: Change Hive database location.

Expert Contributor

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

Highlighted

Re: Change Hive database location.

New Contributor

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

Highlighted

Re: Change Hive database location.

Expert Contributor
Highlighted

Re: Change Hive database location.

New Contributor

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

Re: Change Hive database location.

Master Collaborator

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

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven @ DFHZ

Don't have an account?
Coming from Hortonworks? Activate your account here