Configure Hive Metastore on MySQL

We will see how to configure Hive metastore on MySQL.

Create User and Database for Hive Metastore

Create User :-

mysql> CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'hiveuser'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec

Create Database :-

mysql -u hiveuser -ppassword
mysql> create database hivemetastore;
mysql> use hivemetastore;
mysql> exit;

Configure hive-site.xml

Add the details about the Mysql connection and database to the hive-site.xml.

 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost/hivemetastore?createDatabaseIfNotExist=true</value>
    <description>JDBC connect string for a JDBC metastore</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hiveuser</value>
    <description>Username to use against metastore database</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>password</value>
    <description>password to use against metastore database</description>
  </property>

Now the Hive will point to the metastore DB on Mysql.

Add Mysql connector jar

To enable Hive to connect to Mysql DB, add mysql jdbc connector jar in the lib directory under Hive installation directory.

Start Hiveserver2, Connect Through Beeline and Run Hive Queries