Hive Installation and Configuration with MySQL
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;