Configuarring Mysql Metastore for Hive

By default, Hive stores its metadata in Derby. But we can make it store into mysql so that we can build required statistics onto it. Follow the below steps for the same.

 

Step 1: Install  MySQL

$ sudo apt–get install mysql–server

Step2 : Install Mysql-Java connector

$ sudo apt–get install libmysql–java

Step 3: Create soft link for connector in Hive lib directory  or copy connector jar to lib folder

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

Step 4 :Create the Initial database schema using the hive-schema-0.14.0.mysql.sql file ( or the file corresponding to your installed version of Hive) located in the $HIVE_HOME/scripts/metastore/upgrade/mysql directory.

$ mysql –u root –p

Enter password:

 mysql> CREATE DATABASE metastore;

 mysql> USE metastore;

 mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive–schema–0.14.0.mysql.sql;

Step 5 : You also need a MySQL user account for Hive to use to access the metastore. It is very important to prevent this user account from creating or altering tables in the metastore database schema.

mysql> CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hivepassword';

mysql> GRANT all on *.* to ‘hiveuser’@localhost identified by ‘hivepassword’;

mysql> flush privileges;

Step 6 : Create hive-site.xml ( If not already present) in $HIVE_HOME/conf folder with the below configuration . Replace hiveuser and hivepassword  with the appropriate username and password you created in above step.

<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
<description>metadata is 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>hiveuser</value>
<description>user name for connecting to mysql server</description>
</property><property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hivepassword</value>
<description>password for connecting to mysql server</description>
</property>
</configuration>

Step 7 : Once this is done create a table in hive for verification.  Then go back to mysql and check in the metastore table.