Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Migrate One Hive Schema from Hive Metastore DB (Mysql)

Highlighted

Migrate One Hive Schema from Hive Metastore DB (Mysql)

Contributor

We have many hive schema in Hive DB under MySQL server as Hive Metastore. We want to export/import only ONE hive schema along with all tables, partitions metadata. Please let me kwnow the mysql query to be run on Hive DB.

4 REPLIES 4

Re: Migrate One Hive Schema from Hive Metastore DB (Mysql)

Super Guru

@Sushil Saxena Good question. I would create a shell and/or python script and inside do this

  • USE schema(your schema name here)
  • Run show tables
  • For each table
    • export table to target cluster

The EXPORT command exports the data of a table or partition, along with the metadata, into a specified output location.

Re: Migrate One Hive Schema from Hive Metastore DB (Mysql)

Contributor

I am trying this below but need to add partitions

-----------------------------------------------------------------

mysql -u hive -p -e "select concat( 'show create table ' , DBS.NAME,'.',TBL_NAME,';') from TBLS JOIN DBS ON TBLS.DB_ID = DBS.DB_ID where TBLS.owner='abc'" hive > abc.sql

remove header in abc.sql

hive -f abc.sql > xyz.sql

Open xyz.sql using vim and replace 'old-nameservice' with 'new-nameservice'

hive -f xyz.sql

Now the question is how I need to create a dynamic script to add custom partitions (External Tables). I can't use MSCK REPAIR due to custom partition. I got something:

SELECT concat("ALTER TABLE test_hive10_table ADD PARTITION (year=", year, ", month=", month, "\) LOCATION 'hdfs:///user/alti_aravind/spl_test_hive10_table/", year, "\/", month, "'\;") FROM test_table group by year, month;

But for me I need dynamic script working on multiple table with changing Location.

Re: Migrate One Hive Schema from Hive Metastore DB (Mysql)

Super Guru

@Sushil Saxena I see what you are doing. I would instead fetch each table from the schema and then run export into target hive db.

Re: Migrate One Hive Schema from Hive Metastore DB (Mysql)

Expert Contributor
@Sushil Saxena

What you can try is to get the mysqldump for a schema (for a cluster) and load that into the another mysql server. Once this is done, you would need to configure the Hive to point to the correct host and database. Provided that the HDFS NN is same.