Change Hive Metastore From Derby to MySQL

Change Hive metastore from derby to MySQL

Machine : UBUNTU-14.04 |  Hive :  HIve 1.2.1

To change Hive Metastore from Derby to MySQL we need to follow these 8 simple steps,

Step-1 :

First we need to install Mysql and its dependencies on system.

Command-1 : sudo apt-get install mysql-server

Note: Click Next > Next and set the password for MySQL.

Command-2 :  sudo apt-get install libmysql-java

Step-2 :

Create soft-link for connector in Hive lib directory.

Command-1 : ln -s /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar

Step-3 :

Access your MySQL and create one new database metastore for hive,

Command : $ mysql -u root -p

Enter password:

mysql> CREATE DATABASE metastore;

Step-4 :

Then we need one MySQL account for Hive to access the metastore. It is very important to prevent this user account from any type of change in schema.

Command :

mysql> CREATE USER ‘hive’@’%’ IDENTIFIED BY ‘password’;

mysql> GRANT all on *.* to ‘hive’@localhost identified by ‘password’;

mysql>  flush privileges;

Step-5 :

Now we need to configure Hive to access MySQL metastore, for this we need to update hive-site.xml file(If file does not exist then create a new one)

Note: While updating please take all properties-tag only.

Step-6 :

Now we need to run the Hive schematool to initialization MySQL metastore.

For this we need to go to $HIVE_HOME>bin> folder

Command-1 : schematool -initSchema -dbType mysql

Note : When you have found that your metastore is corrupted, then we need to update metastore.

• Before you run hive for the first time, run

Command : schematool -initSchema -dbType mysql

• If you already ran hive and then tried to initSchema and if it’s failing:

Command : mv metastore_db metastore_db.tmp

You find your metasore_db file at $HIVE_HOME location.

• Re run

Step-7 :

Start your Hive and access your tables.

Step-8 :

To validate it ,

Connect and open your hive

hive>

Then create a table in it and insert one record.

hive> create table saurzcode(id int, name string);

hive> insert into saurzcode(1, “Helical”);

Later access your MySQL and open metastore database

mysql -u root -p

Enter password:

mysql> use metastore;

And see your table as a record in TBLS table of metastore database.

mysql> show tables ;

mysql> select * from TBLS;

Hurray, Completed !!!!

Thanks for visiting , Have a great day.

PUSHPRAJ KUMAR 

Free Open Source BI Platform

• AI-Powered Analytics
• Embedded Analytics
• Single Sign-On (SSO)
• Multi-Tenancy
• Paginated Canned Reporting
• Interactive Dashboard
• White Labeling

Download Free