Rename Hive Database

  • Post author:

Recently I received a requirement to rename hive database, however current Apache hive release doesn’t support this feature. In this Apache Hive Jira HIVE-4847, you can see this is a new feature and the patch is available but it is yet to be merged and tagged to a particular release. We can expect this feature in any of the later releases. Once it’s available we can do it as shown below:

ALTER DATABASE test_db RENAME TO test_db_new;

Let’s see how we can change database name without this patch. I am using MySQL database for hive so let’s connect it first:

[admin@host1 ~]$ ps -ef | grep mysql
[admin@host1 ~]$ cd /usr/local/mysql/bin
[admin@host1 ~]$./mysql -u root -p

Now we are into mysql shell. Change DB to hive

mysql> use hive;

Get the DB_ID value of the database we want to rename

mysql>SELECT * FROM DBS WHERE NAME = "";

Update DB name

mysql>UPDATE DBS SET NAME = "" WHERE DB_ID = "";

Update column and partition detail: Table “TAB_COL_STATS” and “PART_COL_STATS” hold information regarding column and partition statistics for each table/partition, we also need to update the DB_NAME in this table, so run the following queries:

mysql>UPDATE TAB_COL_STATS SET DB_NAME = '' WHERE DB_NAME = '';
mysql>UPDATE PART_COL_STATS SET DB_NAME = '' WHERE DB_NAME = '';

You can get existing views detail 

mysql> SELECT TBL_ID, TBL_TYPE, VIEW_EXPANDED_TEXT, VIEW_ORIGINAL_TEXT FROM TBLS where DB_ID = ;
+--------+---------------+---------------------------------------------+----------------------+
| TBL_ID | TBL_TYPE | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT |
+--------+---------------+---------------------------------------------+----------------------+
| 22323 | MANAGED_TABLE | NULL | NULL |
| 98980 | VIRTUAL_VIEW | select `test`.`a` from `old_db_name`.`test` | select * from test |
+--------+---------------+---------------------------------------------+----------------------+

List only view names

mysql>SELECT TBL_NAME FROM TBLS where DB_ID =  AND TBL_TYPE LIKE 'VIRTUAL_VIEW';

You will need to update any VIEW’s original text or expanded text so that any references to the old database need to update. In my test case, we need to update the above VIEW with the following SQL statement

mysql>UPDATE TBLS SET VIEW_EXPANDED_TEXT = 'select `test`.`a` from `new_db_name`.`test`' where TBL_ID = 98980 and TBL_TYPE = 'VIRTUAL_VIEW';

where ‘new_db_name’ is the new DB name and ‘old_db_name’ is the old DB name. So far we have updated following 

  1. Get the DB_ID value
  2. Update DB name
  3. Update column and partition
  4. Update views

It is very important to update table location also to reflect changes in HDFS. Please make a backup of the Metastore database before proceeding with the following steps:

– Back up HMS database
– Shut down the Hive Metastore
– Connect to MySQL run the following statement:

mysql>UPDATE DBS SET DB_LOCATION_URI = "hdfs://nameservice1/user/hive/warehouse/new_db_name.db" WHERE DB_ID = "";
mysql>update SDS set LOCATION = replace(LOCATION, 'hdfs://nameservice1/user/hive/warehouse/old_db_name.db/', 'hdfs://nameservice1/user/hive/warehouse/new_db_name.db/') where LOCATION like 'hdfs://nameservice1/user/hive/warehouse/old_db_name.db/%';

– Exit MySQL
– Start HMS

That’s it your database name has been changed and also it’s location in HDFS URI.

Please check the issue we faced after renaming database:

https://www.devopsbaba.com/rename-hive-database-issue-resolution/

Tags: , , ,

Leave a Reply