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.
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.
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
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.
Steven @ DFHZ