Hive Installation With MySQL Database Metastore

Hive Installation With MySQL Database Metastore


Hive versions 1.2 onward require Java 1.7 or newer. Hive versions 0.14 to 1.1 work with Java 1.6 as well.

Hadoop 2.x (preferred), 1.x (not supported by Hive 2.0.0 onward). Hive versions up to 0.13 also supported Hadoop 0.20.x, 0.23.x.

Hive Installation Steps

Step 1 - Installing MySQL Server. Open a terminal (CTRL + ALT + T) and type the following sudo command.

$ sudo apt-get install mysql-server

During mysql-server installation it will ask password to enter for root, give it as 'root' or something else. In my case i have given it as root to remember easily.

Step 2 - Installing MySQL Java Connector. This will install libraries (mysql-connector-java.jar) in /usr/share/java/ folder using that we can connect MySQL using Java.

$ sudo apt-get install libmysql-java

Step 3 - Enter into MySQL command line inteface(CLI). Open a terminal (CTRL + ALT + T) and type the following command.

$ mysql -u root -p

Enter password

Enter password :****

Step 4 - Creating new user

mysql> CREATE USER 'hduser'@'%' IDENTIFIED BY 'hduser';

Step 5 - Grant all privileges to new user

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

Step 6 - Flush privileges

mysql> flush privileges;

Step 7 - Creating hive directory. Open a new terminal(CTRL + ALT + T) and enter the following command.

$ sudo mkdir /usr/local/hive

Step 8 - Change the ownership and permissions of the directory /usr/local/hive. Here 'hduser' is an Ubuntu username.

$ sudo chown -R hduser /usr/local/hive
$ sudo chmod -R 755 /usr/local/hive

Step 9 - Switch User, is used by a computer user to execute commands with the privileges of another user account.

$ su hduser

Step 10 - Change the directory to /home/hduser/Desktop , In my case the downloaded apache-hive-2.1.0-bin.tar.gz file is in /home/hduser/Desktop folder. For you it might be in /downloads folder check it.

$ cd /home/hduser/Desktop/

Step 11 - Untar the apache-hive-2.1.0-bin.tar.gz file.

$ tar xzf apache-hive-2.1.0-bin.tar.gz

Step 12 - Move the contents of apache-hive-2.1.0-bin folder to /usr/local/hive

$ mv apache-hive-2.1.0-bin/* /usr/local/hive

Step 13 - Edit $HOME/.bashrc file by adding the pig path.

$ sudo gedit $HOME/.bashrc

$HOME/.bashrc file. Add the following lines

export HIVE_HOME=/usr/local/hive

Step 14 - Reload your changed $HOME/.bashrc settings

$ source $HOME/.bashrc

Step 15 - Change the directory to /usr/local/hive/conf

$ cd $HIVE_HOME/conf

Step 16 - Copy the default to

$ cp

Step 17 - Edit file.

$ gedit

Step 18 - Add the below lines to file. Save and Close.

export HADOOP_HOME=/usr/local/hadoop

Step 19 - Copy the default hive-default.xml.template to hive-site.xml

$ cp hive-default.xml.template hive-site.xml

Step 20 - Edit hive-site.xml file.

$ gedit hive-site.xml

Step 21 - Add or update below properties in hive-site.xml file.

metadata is stored in a MySQL server

MySQL JDBC driver class

user name for connecting to mysql server

password for connecting to mysql server

location of default database for the warehouse 

Step 22 - Remove below property in hive-site.xml file. Save and Close.

    Username to use against metastore database


Step 23 - Copy mysql-connector-java-5.1.28.jar from /usr/share/java/ to $HIVE_HOME/lib/ folder.

$ cp /usr/share/java/mysql-connector-java-5.1.28.jar $HIVE_HOME/lib/

Step 24 - Change the directory to /usr/local/hadoop/sbin

$ cd /usr/local/hadoop/sbin

Step 25 - Start all hadoop daemons.


Step 26 - You must use below HDFS commands to create /tmp and /user/hive/warehouse (aka hive.metastore.warehouse.dir) and set them chmod g+w before you can create a table in Hive.

$ hdfs dfs -mkdir /tmp

$ hdfs dfs -chmod 777 /tmp 

$ hdfs dfs -mkdir /user/hive/warehouse

$ hdfs dfs -chmod g+w /tmp

$ hdfs dfs -chmod g+w /user/hive/warehouse

Step 27 - Change the directory to /usr/local/hive/bin

$ cd $HIVE_HOME/bin

Step 28 - We need to run the schematool command below as an initialization step. For example, we can use "mysql" as db type.

$ schematool -initSchema -dbType mysql

Step 29 - To use the Hive command line interface (CLI) from the shell.

$ hive

Step 30 - To list all the tables those are present in mysql database.

$ show tables;

Step 31 - Enter into MySQL command line inteface(CLI). Open a terminal (CTRL + ALT + T) and type the following command.

$ mysql -u hduser -p 

Enter password

Enter password: hduser

Step 32 - Use matastore database.

mysql> use metastore;

Step 33 - To list all the tables those are present in mysql metastore database.

select * from TBLS;

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *