How-To : Configure MySQL Metastore for Hive ?

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 –

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

2. Install the MySQL Java Connector –

3. Create soft link for connector in Hive lib directory  or copy connector jar to lib folder  –

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.

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.

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 –

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 MySQL MetaStore Configuration

 

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

MySql console:

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

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.

  • Pingback: How to Configure MySQL metastore for Hive ? | SaurzCode()

  • Pingback: How To : Use HCatalog with Pig - SaurzCode()

  • hareeshA

    Good Tutorial
    I have question: In cluster environment where mysql installed (NameNode or any other nodes)?

    Thanks
    Hareesh

    • Rajasree

      Hi, Your website is very useful to connect the mysql with hive. but one problem occurs which is the last step
      “mysql> select * from TBLS;”
      while executing this command, it produces the error as “ERROR 1146 (42S02): Table ‘meta.tbls’ doesn’t exist”
      how to rectify the error?

  • ASeth

    Thanks Saurabh.

    While using MYSQL 5.7.18, we need to change jdbc URL to jdbc:mysql://localhost/metastore?useSSL=false to supress SSL warning and if we see error while creating a table
    Error: Error while processing statement: FAILED: Error in acquiring locks: Locks on the underlying objects cannot b

    we should set hive.support.concurrency=false in hive-site.xml file.