Rename Database in Hive

• First of all, there is no rename functionality for database like table in Hive.
• Solutions
  • E.g. rename database rav to rav_dw.
  • mysql -u root -p
  • use hive;
  • select * from DBS where name = "rav";

    • +-------+------+----------------------------------------------+------+------------+------------+ | DB_ID | DESC | DB_LOCATION_URI                              | NAME | OWNER_NAME | OWNER_TYPE | +-------+------+----------------------------------------------+------+------------+------------+ |    46 | NULL | hdfs://hadooptest/apps/hive/warehouse/rav.db | rav  | hdfs       | USER       | +-------+------+----------------------------------------------+------+------------+------------+

  • update DBS set name = "rav_dw", db_location_uri ="hdfs://hadooptest/apps/hive/warehouse/rav_dw.db" where DB_ID = 46;
  • select * from DBS where db_id = 46;

    • +-------+------+-------------------------------------------------+--------+------------+------------+ | DB_ID | DESC | DB_LOCATION_URI                                 | NAME   | OWNER_NAME | OWNER_TYPE | +-------+------+-------------------------------------------------+--------+------------+------------+ |    46 | NULL | hdfs://hadooptest/apps/hive/warehouse/rav_dw.db | rav_dw | hdfs       | USER       | +-------+------+-------------------------------------------------+--------+------------+------------+

  • update SDS a join TBLS b on db_id = 46 and a.sd_id = b.sd_id set location = replace(location, "rav.db", "rav_dw.db");
  • hdfs dfs -mv /apps/hive/warehouse/rav.db /apps/hive/warehouse/rav_dw.db
• Note
  • The location for each partition also need to be updated if there is partitioned table.
  • If there is a table which "BASIC_STATS" is true, like ORC table. Reanalyze the table in hive
    • E.g. analyze table table_name compute statistics;