Over a million developers have joined DZone.

How to Configure MySQL Metastore for Hive?

This is a step by step guide on How to Configure MySQL Metastore for Hive in place of Derby Metastore (Default).

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

Hive by default comes with Derby as its metastore storage, which is suited only for testing purposes and in most of the production scenarios it is recommended to use MySQL as a metastore. This is a step by step guide on How to Configure MySQL Metastore for Hive in place of Derby Metastore (Default).

Assumptions : Basic knowledge of Unix is assumed and also It’s assumed that Hadoop and Hive configurations are in place.Hive with default metastore Derby is properly configured and tested out.

  1. Install  MySQL -
$ sudo apt-get install mysql-server

Note:  You will be prompted to set a password for root.

2. Install the MySQL Java Connector –

$ sudo apt-get install libmysql-java

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

Note :- HIVE_HOME points to installed hive  folder.

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;

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;

Note : –  hiveuser is the ConnectionUserName in hive-site.xml ( As explained next)

6. Create hive-site.xml ( If not already present) in $HIVE_HOME/conf folder with the configuration below –

      <description>metadata is stored in a MySQL server</description>
      <description>MySQL JDBC driver class</description>
      <description>user name for connecting to mysql server</description>
      <description>password for connecting to mysql server</description>

7. We are all set now. Start the hive console.

Note :- If you are seeing any error related to Driver “com.mysql.jdbc.Driver” not found, Please make sure you have copied the mysql-java connector jar properly to Hive lib folder or created a soft link for the same.

If you have reached this far, without any errors , you can continue reading else , Go back to step 1 and see what you missed – :)

Now we’ll test through hive console and mysql console if our Hive Metastore using MySQL is configured properly.

Hive console:

Let’s create a table in Hive.

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

Hive MySQL MetaStore Configuration

And see if we can see it’s metadata stored in MySQL.

MySql console:

mysql -u root -p

Enter password:                                                             
mysql> use metastore;
                           mysql> show tables ;

You can query the metastore schema in your MySQL database. Something like:

mysql> select * from TBLS;

On your MySQL database you will see the names of your Hive tables.

MySQL Metastore Configuration for Hive

That’s It !! You are all set to use MySQL as a metastore in Hive.

Happy Learning !!

References :

[1]  Cloudera MySQL Metastore Guide.


Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

java,sql,mysql,tips and tricks,hive

Published at DZone with permission of Saurabh Chhajed, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}