Changing Default metastore [derby] of Hive to MySQL

Hive comes configured to use Derby as the metastore. , but it can support only one active user at a time and is not certified for production use. The solution to supporting multiple users is to use a standalone database. MySQL is a popular choice for the standalone metastore.

  • Step 1: Install and start MySQL if you have not already done so

MySQL Installation Tutorial for instructions of how to install MySQL.

  • Step 2: Configure the MySQL Service and Connector

Download mysql-connector-java-5.0.5.jar file and copy it to $HIVE_HOME/lib directory.

  • Step 3. Create the Database and User

Create a metastore_db database in MySQL database using root user

$ mysql -u root -p

Enter password:

mysql> CREATE DATABASE metastore_db;

Create a User [hiveuser] in MySQL database using root user

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

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

[here the user hiveuser is same as ConnectionUserName in hive-site.xml file.]

mysql>  flush privileges;
  • Step 4: Install Hive if you have not already done so

Hive Installation Tutorial for instructions of how to install Hive.

  • Step 5: Configure the Metastore Service to Communicate with the MySQL Database

Create hive-site.xml file in $HIVE_HOME/conf directory and add the following configurations:





            <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>


  • That’s all. Now hive will create the schema at the backend. Let us check:

Hive console:

hive> create table hivetesting(id string);

MySql console:

There are 2 ways to access metastore_db

1.      mysql -u root -p
        Enter password:
        mysql> use metastore_db;
        mysql> show tables ;

2.     mysql -u hiveuser -p metastore_db
       Enter password:
       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.

Add a new table and verify that the above query returns updated results.

  • Reference:


Hive Installation On Ubuntu

The hive-0.10.0 installation is done in below versions of Linux, Java and Hadoop respectively.


JAVA 1.7.0_25

HADOOP 1.1.2

I have hduser as a dedicated hadoop system user. I had installed my Hadoop in /home/hduser/hadoop folder. Now I am going to install hive  in /usr/lib/hive folder.

  • Download hive stable version from this link

  • Enter into the directory where the stable version is downloaded. By default it downloads in “Downloads” directory
$ cd ~/Downloads
  • Unzip the tar file.

[go to root user by using command: su ]

# tar xzf hive-0.10.0.tar.gz
  • Create directory
# mkdir /usr/lib/hive
  • move  hive-0.10.0 to hive
 # mv hive-0.10.0 /usr/lib/hive/hive-0.10.0

[Exit from root to hduser by using command: su hduser or exit ]

  • Set the HIVE_HOME path in bashrc file

To open bashrc file use this command

hduser@system_name:~$ gedit ~/.bashrc

            In bashrc file append the below 2 statements

export HIVE_HOME=/usr/lib/hive/hive-0.10.0

export PATH=$PATH:$HIVE_HOME/bin
  •  Type hive in command line and now you can see hive shell.
$ hive

  • Now you can play with Hive 🙂