This article explains how to rename a database in Hive manually without modifying database locations, as the command:
ALTER DATABASE test_db RENAME TO test_db_new;
still does not work due to HIVE-4847 is not fixed yet. For the DB rename to work properly, we need to update three tables in the HMS DB. As I am using MySQL, I will use MySQL query example here, change accordingly based on your DB type: DBS table, this table holds the information about Databases in Hive:
SELECT * FROM DBS WHERE NAME = "";
get the DB_ID value, then:
UPDATE DBS SET NAME = "" WHERE DB_ID = "";
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:
UPDATE TAB_COL_STATS SET DB_NAME = '' WHERE DB_NAME = '';
UPDATE PART_COL_STATS SET DB_NAME = '' WHERE DB_NAME = '';
Update: Thanks to Varun’s comment below, I initially missed the VIEW. Check for any existing VIEWs defined under or access the old database:
mysql> SELECT TBL_ID, TBL_TYPE, VIEW_EXPANDED_TEXT, VIEW_ORIGINAL_TEXT FROM TBLS where DB_ID = 30425;
+--------+---------------+---------------------------------------------+--------------------+
| TBL_ID | TBL_TYPE      | VIEW_EXPANDED_TEXT                          | VIEW_ORIGINAL_TEXT |
+--------+---------------+---------------------------------------------+--------------------+
|  30822 | MANAGED_TABLE | NULL                                        | NULL               |
|  30823 | VIRTUAL_VIEW  | select `test`.`a` from `view_test`.`test`   | select * from test |
+--------+---------------+---------------------------------------------+--------------------+
You will need to update any VIEW’s original text or expanded text so that any references to the old database need to updated. In my test case, I need to update the above VIEW with the following SQL statement:
UPDATE TBLS SET VIEW_EXPANDED_TEXT = 'select `test`.`a` from `view_test_2`.`test`' where TBL_ID = 30823 and TBL_TYPE = 'VIRTUAL_VIEW';
where ‘view_test_2’ is the new DB name and view_test is the old DB name. That should be it to update the DB name.

9 Comments

      1. Varun

        Hi Eric,

        Thank you for the response.

        I was able to rename my database name by following the steps which you have provided. I am able to access all the tables without any issues. There are few views which are not working after updating the database name as they are still looking for the old database name. May be what you told will not support in handling existing views.

        Thanks

    1. Eric Lin

      Hi Nitin,

      Sorry about the late response, I was on holiday in the last few weeks, and now just back to work.

      The HMS DB is configured in hive-site.xml file for HMS, so you can examine this configuration file to determine what’s the backend DB configurations. Or if you use Cloudera Manager, you can go to CM home page > Hive > Configuration > search for “database” and you can see the host, username etc, but password will not be visible, you will need to check with your system admin.

      Hope above helps.

      Thanks again for visiting my site.

  1. Ankur Gadgilwar

    Ok, so here is a scenario. I moved a specific database from one cluster to another with all the related data and tables.
    Now, I want to rename the database. So, my question here is, wouldn’t I also will have to change the name of the directory under /app/hive/warehouse??
    I tried doing so, changed the name of the .db directory to the new database name and guess what, when I use the newly renamed database, use any table and run a select * query on it, it doesn’t return any value.
    Would really appreciate if you could help me resolve this. Thank you.

    1. Eric Lin

      Hi Ankur,

      Firstly thanks for visiting my site and asking questions.

      Have you checked the Hive DB’s metadata information in your backend database? If you run “SELECT * FROM DBS WHERE NAME = ‘{db_name}'”, is the path correct?

      You might also want to check the table’s metadata “SELECT * FROM TBLS WHERE TBL_NAME = ‘{table_name}'” and confirm that the table’s metadata is correct.

      Hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *