Tested on Ubuntu 14.04 LTS, hadoop-2.6.0

Prerequisites: Hadoop should be installed and working.

Here in this tutorial we are going to install apache-hive-2.1.1 and configure it with MySQL.

Step 1: Download Apache Hive

Download any stable version from the official website. Your download page will look like the image given below:

And save the file accordingly.

Step 2: Unzip and Move

The below command will switch the terminal to hduser and we will install the hive in hduser.

    $ su - hduser 

Next you need to explore your root user ‘downloads directory’. So you need to replace the “user” with the “your root user name” in the command given below:

    $ cd /home/user/Downloads

You can take a look at the image given below. In this image, gopal is the root user on my system.

Unzip/open the downloaded compressed file and move it to installation directory:

    $ sudo tar -zxvf apache-hive-2.1.1-bin.tar.gz
    $ sudo mv apache-hive-2.1.1-bin /usr/local/hive

Step 3: Setup Environment: Edit .bashrc file

Open the .bashrc file using following command:

    $ sudo nano ~/.bashrc

Make the following changes in .bashrc file i.e., copy and paste the following hive variables in .bashrc file

    #HIVE VARIABLES START 
    export HIVE_HOME=/usr/local/hive
    export HIVE_CONF_DIR=$HIVE_HOME/conf
    export PATH=$PATH:$HIVE_HOME/bin
    #HIVE VARIABLES END

Use Ctrl+X and press Y to save.

Run the below commands to make above changes in .bashrc:

    $ source ~/.bashrc

The part of .bashrc file, for the above hive variables, will look like the image given below:

Step 4: Setup Environment a Creating directory structure

We have to create directories in HDFS. So, first we need to start the hadoop:

    $ cd /usr/local/hadoop/sbin
    $ start-dfs.sh
    $ start-yarn.sh
    $ jps

The output of jps should be like the image given below, thus this means hadoop services have started now.

Now create the directory structure as follows:

    $ hdfs dfs -mkdir /tmp
    $ hdfs dfs -mkdir /usr
    $ hdfs dfs -mkdir -p /usr/hive/warehouse

Use below command as confirmation to know that required directories are created:

    $ hdfs dfs -ls -R /

Give the required access permission to the above created directories by using the command given below:

    $ hdfs dfs -chmod g+w /tmp
    $ hdfs dfs -chmod g+w /usr/hive/warehouse

Step 5: Install MySQL Server

    $ sudo apt-get install mysql-server
    $ sudo /etc/init.d/mysql start
    $ sudo apt-get install mysql-client
    $ sudo apt-get install libmysql-java
    $ sudo cp /usr/share/java/mysql.jar $HIVE_HOME
    $ cd
    $ sudo wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.38.tar.gz
    $ sudo tar -xzvf mysql-connector-java-5.1.38.tar.gz
    $ sudo cp mysql-connector-java-5.1.38/mysql-connector-java-5.1.38-bin.jar /usr/local/hive/lib/
    $ sudo /usr/bin/mysql_secure_installation

Then series of question will be asked which we need to answer them “yes/no” according to your requirement. At the end, the following message will be displayed saying that MySQL installation is secure and successful.

Step 6: Creating Hive database

    $ cd /usr/local/hive/bin
    
    $ mysql -u root -p
    Enter password:

Enter the same MySQL password that you had given at the time of MySQL installation. If the password is correct, then you will get the MySQL prompt. After that create the database as given in the below commands:

    mysql> CREATE DATABASE metastore;
    mysql> USE metastore;
    mysql> SOURCE /usr/local/hive/scripts/metastore/upgrade/mysql/hive-schema-2.1.0.mysql.sql
    mysql> create user 'hive'@'%' identified by 'password';
    mysql> grant all on *.* to 'hive'@localhost identified by 'password';
    mysql> flush privileges; 
    mysql> exit;    

Step 7: Creating Hive config file:

    $ cd /usr/local/hive/conf
    $ sudo cp hive-default.xml.template hive-site.xml   
    $ sudo nano hive-site.xml

Comment the previous contents between the two configuration tags of hive-site.xml and paste the following between the two configuration tags in hive-site.xml itself:

    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost/metastore</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>password</value>
    </property>

    <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value>
    </property>

    <property>
        <name>hive.stats.autogather</name>
        <value>false</value>
    </property>

    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/usr/hive/warehouse</value>
    </property>

    <property>
        <name>hive.exec.local.scratchdir</name>
        <value>/tmp</value>
    </property>
    
    <property>
        <name>hive.downloaded.resources.dir</name>
        <value>/tmp</value>
    </property>

    <property>
        <name>hive.querylog.location</name>
        <value>/tmp</value>
    </property>

    <property>
        <name>hive.server2.logging.operation.log.location</name>
        <value>/tmp/operation_logs</value>
    </property>

Your hive-site.xml will look like the image given below:

Press Ctrl+X and then Y to save and exit.

Step 8: Launching Hive terminal

    $ cd /usr/local/hive/bin
    $ hive

If everything is set right, you will get the hive prompt like the image given below:

    hive> 

Step 9: Testing

Let’s run some SQL queries for creating and fetching the data using the hive:

    hive> create table demo_table(id int, name string);
    hive> select count(*) from demo_table;

You will get the output like the image given below:

If you want to exit from the hive prompt, then use the following commands:

    hive> exit;