Hive Metastore Configuration After Fresh Installation

For the beginners playing around in Hive, a stoppage arises with the proper configuration. After placing Hive libraries in designated folders and updating necessary environment variables, many times the first eager execution of hive fails with the exception “HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient”. That’s when Hive Metastore needs to be configured, which is pretty simple and straightforward.

There are two ways to configure Hive Metastore. We can use ‘schematool’ or directly source the hive-schema-3.1.0.mysql.sql script provided by Hive into the Metastore database.

Here are the steps for configuring Hive Metastore. We are configuring it for mysql type metastore on ubuntu 16.04.

1. Install MySQL using apt-get as sudo apt-get install mysql-server.
2. Download and copy the mysql jdbc connector jar ‘mysql-connector-java-8.0.13.jar’ into Hive lib directory.
3. Create hive-site.xml file if not exist in Hive conf directory and add the following properties to use the above-installed MySQL database. If the properties are not added/updated, it will use the default Derby database.

<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/metastore_db?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>root</value>
<description>user name for connecting to mysql server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>password</value>
<description>password for connecting to mysql server</description>
</property>
</configuration>

4. Up to this step, our Metastore configurations are complete and we need to initialize those configurations.

The initialization can be done simply by using schematool provided by Hive. To initialize it for MySQL database use the command ‘schematool -initSchema -dbType mysql’ and we are all set to use Hive.

Another way to initialize metastore_db is by sourcing the hive-schema-3.1.0.mysql.sql script. Below are the steps to follow.

1. Login to MySQL using command ‘mysql -u root -p’
2. Create the Metastore database (metastore_db in our case) using command ‘create database metastore_db’
3. Use database metastore_db
4. Source hive-schema-3.1.0.mysql.sql into the MySQL Metastore database using command ‘source $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-3.1.0.mysql.sql’.

That’s it. We are all set to play around with Hive.