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 stores 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.
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 previous downloaded apache-hive-3.1.2-bin.tar.gz from the terminal and rename as hive.
3. Update the ~/.bashrc file to accommodate the hive environment variables.
4. Please re login and try below to check environment variable
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 has installed in the same DataNode in the cluster. Here are the steps to install MySQl database , create 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 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 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 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 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. Password to login with 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
Step-4:- Work on HDFS.
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
Step-5:- Access Hive CLI
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 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 Hive. HS2 supports multi-client concurrency and authentication. We don’t need any separate configuration for HiverServer2 . If we can access Hive CLI from terminal without any issue , HiverServer2 service can be started by executing following command in a separate terminal .
/hive/bin$ ./hive –service hiveserver2
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.
Can be contacted for real time POC development and hands-on technical training. Also to develop/support any Hadoop related project. Email:- [email protected], [email protected]. Gautam is a consultant as well as Educator. Prior to that, he worked as Sr. Technical Architect in multiple technologies and business domain across many countries. Currently, he is specializing in Big Data processing and analysis, Data lake creation, architecture etc. using HDFS. Besides, involved in HDFS maintenance and loading of multiple types of data from different sources, Design and development of real time use case development on client/customer demands to demonstrate how data can be leveraged for business transformation, profitability etc. He is passionate about sharing knowledge through blogs, training, seminars, presentations etc. on various Big Data related technologies, methodologies, real time projects with their architecture /design, multiple procedure of huge volume data ingestion, basic data lake creation etc.