Hive Configure Mysql Metastore

Sponsored Links

Ad by Google

In my previous post, we have already seen a step by step guide to set up an environment for Apach Hive here.
And in this post going to show you a step by step guide to change your default meta store from Derby to Mysql database.

What is Meta store in Hive?
The Hive metastore, stores the meta data of Hive tables and partition created in Hive, in a relational database. By default relational database used to keep the meta data of Hive table is Derby database. And support only one active session at a time, which is not suitable for production use.
You can't use more than one session of Hive at at time, if you are using the default(Derby) meta store.

How to configure MySql Metastore for Hive?
Below are the steps given to change your default Derby database to MySql for storing meta data of Hive table, before this you must have Hive installed in your machine, if not you may follow my previous post step-by-step Hive installation guide to install.
Step 1. Install MySql database

sudo apt-get install mysql-server

The above command will take sometime to download and install, before installing it will ask you to set-up password for your MySql with below screen shot, provide the password and it will installed.

Step 2. Download and install MySql Java Connector

sudo apt-get install libmysql-java

Create symbolic link for the downloaded connector or copy connector jar file inside lib folder of Apache Hive installed directory

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

Step 3. Create the initial database schema
using file located in HIVE_HOME/scripts/metastore/upgrade/mysql directory.

subodh@subodh-Inspiron-3520:~$ mysql -u root -p
Enter password: 
mysql> create database metastore;
Query OK, 1 row affected (0.00 sec)

mysql> use metastore;
Database changed
mysql> source $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.14.0.mysql.sql;

Step 4. Create hive-site.xml inside $HIVE_HOME/conf folder

mv hive-default.xml.template hive-site.xml

Now update below properties inside hive-site.xml -

<property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
      <description>Metadata stored in a MySql server</description>
   </property>

   <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
      <description>MySql jdbc driver class</description>
   </property>

   <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
      <description>user name for connecting to mysql server</description>
   </property>

   <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>root</value>
      <description>password for connecting to mysql server</description>
   </property>

Step 5. Verify
Ok, lets verify our configuration-
i. Go to Hive CLI and Create table in Hive

hive> create table login(user_name string,password string);
OK
Time taken: 0.678 seconds

ii. Go to MySql CLI and verify the meta store schema

mysql> use metastore;
mysql> select * from TBLS;

That's it.

Sponsored Links