Install and Configuration of Apache Hive on multi-node Hadoop cluster

6 min readOct 24, 2019

The Apache Hive is a data warehouse system built on top of the Apache Hadoop. Hive can be utilized for easy data summarization, ad-hoc queries, analysis of large datasets stored in various databases or file systems integrated with Hadoop. Ideally, we use Hive to apply structure (tables) on persisted a large amount of unstructured data in HDFS and subsequently query those data for analysis. The objective of this article is to provide step by step procedure in sequence, to install and configure the latest version of Apache Hive (3.1.2) on top of the existing multi-node Hadoop cluster. In a future post, I will detail out how can we use Kibana for data visualization by integrating Elastic Search with Hive. Apache Hadoop -3.2.0 was deployed and running successfully in the cluster. Here is the list of environment and required components.

Existing up and running Hadoop cluster where Hadoop-3.2.0 deployed and configured with 4 DataNodes. Please go through the link if you wish to create a multi-node cluster to deploy Hadoop-3.2.0.
Apache Hive-3.1.2. tarball. It can be downloaded from the Apache mirror.
MySQL. Installed and used Server version: 5.5.62

Step -1:- Untar apache-hive-3.1.2-bin.tar.gz and set up Hive environment

1. Select a healthy DataNode with high hardware resource configuration in the cluster if we wish to install Hive and MySQL together. Here, the used DataNode has 16GB RAM and 1 TB HD for both Hive and MySQL together.

2. Extract the previously downloaded apache-hive-3.1.2-bin.tar.gz from the terminal and rename as a hive.

3. Update the ~/.bashrc file to accommodate the hive environment variables.

4. Please re-login and try below to check environment variable

echo $HIVE_HOME
echo $HIVE_CONF_DIR

5. Copy hive-env.sh.template to hive-env.sh and update with all read write access

6. Update hive-env.sh available inside conf dir with the HADOOP_HOME and HIVE_CONF_DIR

Step-2:- MySQL Database installation for Hive metastore persistence and MySQL java connector.

As said above, both Hive and MySQL database have installed in the same DataNode in the cluster. Here are the steps to install MySQL database, create a schema named as metastore and subsequently update the schema by executing hive-schema-2.3.0.mysql.sql. This SQL script has all the table creation, update etc command that specifically provided by Apache Hive for MySQL database.

1. Download MySQL through the terminal.

2. During installation, it will ask to set database user “root” and its password. Set it and note it down.

3. Download and copy mysql java connector (mysql-connector-java-5.1.28.jar) to lib folder of Hive.

By default, mysql-connector-java-5.1.28.jar will be download under /usr/share/java folder.

4. Start MySQL service. Ideally, MySQL service starts automatically after successful installation.

sudo service mysql start
sudo service mysql status

5. Create metastore database for Hive here by executing following commands from mysql command prompt.

mysql -u root -p<password>
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE /home/<<ubuntu>>/hive/scripts/metastore/upgrade/mysql/hive-schema-3.1.0.mysql.sql
mysql> GRANT all on *.* to ‘root’@localhost;
mysql> flush privileges;

You can create a separate user in MySQL if don’t wish to continue as ‘root’ user.

6. Rename hive-default.xml.template to hive-site.xml available under conf directory.

Step -3:- Update hive-site .xml

In hive-site.xml, we have the option to mention what execution engine to be used by hive when we fire HQL query. Map Reduce has been depreciated now because of performance issue. Based on the stored data volume in HDFS as well as the type of queries, either we can use tez or spark as a default execution engine of Hive to boost performance for query data. Here are properties in hive-site.xml those need to be mentioned with name and value to accommodate with current cluster settings.

1 MySQL Database Connection URL

2. User name to connect with MySQL Database

3. The password to log in with the created user in MySQL

4. Execution engine name which Hive internally use to execute queries. Can use Spark or Tez for better performance.

5. Scratch dir location

Since Hive will run on top HDFS, we need to make sure Hadoop multi-node cluster is started and all daemons are running without any issue. To verify, browse the Hadoop web admin page. Create Hive directory on the HDFS using following commands with subsequent permissions using terminal on the NameNode or MasterNode in the cluster.

hadoop-3.2.0/bin >./hdfs dfs -mkdir /user/ hadoop-3.2.0/bin >./hdfs dfs -mkdir /user/hive hadoop-3.2.0/bin >./hdfs dfs -mkdir /user/hive/warehouse hadoop-3.2.0/bin >./hdfs dfs -mkdir /tmp hadoop-3.2.0/bin >./hdfs dfs -chmod -R a+rwx /user/hive/warehouse hadoop-3.2.0/bin >./hdfs dfs -chmod g+w /tmp

Execute the hive command inside bin directory of Hive using terminal of the DataNode where Hive installed and configured. We should see the “hive>” prompt for successful installation.

There are multiple options available to connect with Hive to execute HQL queries, data loading etc. Hive CLI can be used by default but Hive should be installed on the same machine or the DataNode in the cluster. It connects directly to the Hive Driver. Hive CLI won’t be used in real-time/ production environment. since it’s depreciated from Hive 2.0 onwards. HiveServer2 (HS2) is a service that enables clients to execute queries against the Hive. HS2 supports multi-client concurrency and authentication. We don’t need any separate configuration for HiverServer2 . If we can access Hive CLI from the terminal without any issue, HiverServer2 service can be started by executing following command in a separate terminal.

/hive/bin$ ./hive -service hiveserver2

And access the web ui of HiverServer2 from browser at default port 10002.

Beeline is another thin client CLI to execute queries via HiveServer2 which support concurrent client connection and authentication. Beeline can be leveraged by multiple user from multiple node in the cluster to execute queries. We can used Hive Web Interface (HWI) as a client to communicate with existing Hive deployment besides CLI. Please go through the link if you want to use HWI.

Ref:-

Written by
Gautam Goswami

Originally published at https://onlineguwahati.com on October 24, 2019.

Install and Configuration of Apache Hive on multi-node Hadoop cluster

Written by Gautam Goswami