Hive Installation with MySQL metastore

Hive Installation

  • Download the hive tar ball. ( otherwise do apt-get/ yum install/ install the hive rpm manually)
  • Extract the tar ball in a slave node.
  • Set HIVE_HOME  and PATH in /etc/bash.bashrc file.
  • Logout and login again.
  • Type hive in terminal.
  • Hive will work.
  • This is just a play around installation. Here the database used is derby database. This is the default database, but this will not work for multiuser setup. Multiuser means only one can use hive at a time.

Changing the Hive metastore database from derby to MySql

  • Setup a MySql database.
    • In MySql command line type
CREATE USER 'hive_user'@'%' IDENTIFIED BY 'hive@123';
  • For connecting to a user account in MySql use the command.
C:/Mysql/bin>mysql –uUsername -pPassword
  • Remember the ip address, port, username, password of the mysql setup.
  • Make a firewall inbound rule in the machine where mysql is installed. Otherwise connection to the mysql port will be blocked by the firewall. So jdbc connectivity will not happen.
  • Add mysql-connector-java-5.0.5.jar  file to the lib directory inside hive installation.
  • Create hive-site.xml file in the conf directory of hive installation. Fill that file with all the necessary properties. This file may not be present in the conf directory. So create a new file.
  • Restart Hive.
  • Now multiuser setup will be ready.
  • If you want JDBC Connectivity, thrift server should be started
 hive  --service hiveserver&

*& is used for running that hive service server process in the background.

Sample hive-site.xml file is shown below








<value>jdbc:mysql://<ip address of mysql machine>/hive?createDatabaseIfNotExist=true</value>















About amalgjose
I am an Electrical Engineer by qualification, now I am working as a Software Architect. I am very much interested in Electrical, Electronics, Mechanical and now in Software fields. I like exploring things in these fields. I love travelling, long drives and music.

4 Responses to Hive Installation with MySQL metastore

  1. Aparna says:

    Thanks for the Hive setup steps .Can you please list the steps which needs to be followed on the slave servers ? Is it ok to just copy the Hive folder from master to all the slaves or is there anything which needs to be done differently

  2. amalgjose says:

    If you are using tarballs for installation, you just need to copy the hive to the machines where you want hive. If you are using any databases such as mysql, oracle etc as metastore, you have to give appropriate permission to the database user on each machine, otherwise you will face permission issue.
    Normally we do not install hive on master machine. It is a good practice to install all these client applications in the client machine.

  3. suresh says:

    Can you provide me step by step configuration setup for HIVE and iam planning to have database in seperate serve( mysql). So iam looking for remote database setup.


    • amalgjose says:

      Create a mysql server running in the remote machine.
      Create a database and user.
      Give appropriate permission to the user from the hive host to the mysql database.
      In the hive installation, you can see a schema script.
      Load the schema script to the mysql database.
      Add the username, password and jdbc url of this mysql database to the hive-site.xml ( configuration file of hive).
      Add the mysql-connector-java.jar to the lib folder of hive.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: