How to import a mysqldump in Hive to recreate the database (HDP 2.6) ?

[#1]

Hello,

I have a mysqldump that is created every morning and I want to recreate the DB in in Hive (HDP 2.6) ?

The size of the mysqldump file is small less than 1Go.

What are the best scenarios for this task ?

Thank you in advance for your answers.

Arnault

[#2 · Accepted solution]

@Arnault Droz

If the mysqldump is for different version other than Hive 2.1.1000 in HDP 2.6. Then do the following:

1. Stop Hive services from Ambari.

2. Create new database under MySQL as say hive2:

mysql> create database hive2;
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on hive2.* to 'hive'@'%' identified by 'hive';
Query OK, 0 rows affected (0.00 sec)

3. Restore database as:

mysql -u hive -phive hive2 < dumpfilename.sql

4. Update database connection string for mysql under Ambari -> Hive configs.

5. Save configurations and try restarting. Since there is different in VERSION, this would fail.

6. Run Hive metatool command to upgrade the schema as below:

hive@ssnode260 bin]$ /usr/hdp/2.6.0.3-8/hive2/bin/schematool -upgradeSchema -dbType mysql

7. Restart Hive services from Ambari.

If the Hive metadata version is same as Hive 2.1.1000 in HDP 2.6, then follow steps 1 through 5.

Hope this helps.!!

[#3]

Thank you for your answer but I think I was not clear enough with my explanation. The MySQL dump come from MariaDB. And I want to ingest the data from MariaDB but I can't access directly MariaDB but only a mysqldump of this database. I want to recreate the MariaDB in HDP 2.6 in some way.

My question is the same what are the best scenarios to do this workflow ?

Thank you a lot again.

Arnault