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 = "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:" WHERE DB_ID = " ";
UPDATE TAB_COL_STATS SET 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:' WHERE DB_NAME = ' '; UPDATE PART_COL_STATS SET DB_NAME = ' ' WHERE DB_NAME = ' ';
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.
Thank you for the steps. My views are not working now. Please tell me how to fix this.
Hi Varun,
Thanks for visiting my blog.
To allow me to help, can you please give me a bit more context on what is broken for VIEW? Do you also want to rename VIEW?
Thanks
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
Hi Varun,
Sorry about the late response, I have updated my article to also cover the VIEW in Hive when renaming database.
Thanks for pointing it out.
Cheers.
Thank you Eric.
Hi Eric,
Can you tell the HMS db location as i was not able to found that in warehouse directory.
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.
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.
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.
hi
this is sumanth
i have one dout how to change database name i tried so many times till i did not get i tried like above one ALTER DATABASE test_db RENAME TO test_db_new; . now i can tried like this ALTER DATABASE sumanth_infra RENAME TO sumanth_bigdata;
can u please tell once i am waiting for u response
thank you.