Change Hive database location.

[#1]

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!

[#2]

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.

[#3]

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

[#4]

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

[#5]

This article covers your requirement https://community.cloudera.com/t5/Community-Articles/Hive-Changing-Database-Location/ta-p/246699

[#6]

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

[#7]

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