How to migrate Hive Metastore from Old cluster to new cluster

[#1]

1. Determine the version of the source hive database (from old cluster):

mysql> select * from VERSION; +--------+----------------+----------------------------+ | VER_ID | SCHEMA_VERSION | VERSION_COMMENT | +--------+----------------+----------------------------+ | 1 | 1.2.0 | Hive release version 1.2.0 | +--------+----------------+----------------------------+

2. On the old clusters metastore Mysql Database, take a dump of the hive database mysqldump -u root hive > /data/hive_20161122.sql

3. Do a find and replace in the dump file for any host name from the old cluster and change them to the new cluster (i.e. namenode address). Be cautious if the target cluster is HA enabled, when replacing hostname/hdfs url.

4. On the new cluster, stop the Hive Metastore service

5. Within MySQL, perform drop/create of the new hive database. mysql> drop database hive; create database hive;

7. Run the schematool to initialize the schema to the exact version of the hive schema on the old cluster. /usr/hdp/current/hive-metastore/bin/schematool -dbType mysql -initSchemaTo 1.2.0 -userName hive -passWord '******' -verbose

8. Import the hive database from the old cluster: mysql -u root hive < /tmp/hive_20161122.sql

9. Upgrade the schema to the latest schema version: /usr/hdp/current/hive-metastore/bin/schematool -upgradeSchema -dbType mysql -userName hive -passWord '******' -verbose

10. Start the Hive Metastore.