{"id":104801,"date":"2021-09-20T11:00:00","date_gmt":"2021-09-20T08:00:00","guid":{"rendered":"https:\/\/examples.javacodegeeks.com\/?p=104801"},"modified":"2021-09-17T14:06:52","modified_gmt":"2021-09-17T11:06:52","slug":"apache-hadoop-etl-tutorial","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/apache-hadoop-etl-tutorial\/","title":{"rendered":"Apache Hadoop ETL Tutorial"},"content":{"rendered":"

1. Introduction<\/h2>\n

This is an in-depth article related to the Apache Hadoop ETL Tool – Hive. Hive is part of the Hadoop Ecosystem. It is used in Big Data solutions with Hadoop. It was developed by Facebook. Hadoop is an Apache Opensource project now. Hive is used as ETL (Extraction-Transformation-Load) tool in the Hadoop system for the execution of queries and handling big data. <\/p>\n

2. Apache Hadoop ETL<\/h2>\n

2.1 Prerequisites<\/h3>\n

Java 7 or 8 is required on the Linux, windows, or Mac operating system. Maven 3.6.1 is required. Apache Hadoop 2.9.1 and Hive 3.1.2 are used in this example.<\/p>\n

2.2 Download<\/h3>\n

You can download Java 8 can be downloaded from the Oracle website<\/a>.\u00a0Apache Maven 3.6.1 can be downloaded from the\u00a0Apache site<\/a>. Apache Hadoop 2.9.1 can be downloaded from\u00a0Hadoop Website<\/a>. You can download Apache Hive 3.1.2 from the\u00a0Hive website<\/a>.<\/p>\n

2.3 Setup<\/h3>\n

You can set the environment variables for JAVA_HOME and PATH. They can be set as shown below:<\/p>\n

Setup<\/em><\/span><\/p>\n

JAVA_HOME=\"\/desktop\/jdk1.8.0_73\"\nexport JAVA_HOME\nPATH=$JAVA_HOME\/bin:$PATH\nexport PATH\n<\/pre>\n
The environment variables for maven are set as below:<\/p>\n
Maven Environment<\/em><\/span><\/p>\n
JAVA_HOME=\u201d\/jboss\/jdk1.8.0_73\u2033\nexport M2_HOME=\/users\/bhagvan.kommadi\/Desktop\/apache-maven-3.6.1\nexport M2=$M2_HOME\/bin\nexport PATH=$M2:$PATH\n<\/pre>\n
2.4 How to download and install Hadoop and ETL tool<\/h3>\n
 After downloading the zip files of Hadoop and hive, they can be extracted to different folders.  The libraries in the libs folder are set in the CLASSPATH variable.<\/p>\n
2.5 Apache Hive<\/h3>\n
Apache Hive is part of the Hadoop Ecosystem. It is used in Big Data solutions with Hadoop. It was developed by Facebook. Hadoop is an Apache Opensource project now. Apache Hive has features for SQL Access of data, handling multiple data formats, file access from Apache HDFS ad Apache HBase, executing query through Apache Tez, Apache Spark or Map Reduce, HPL-SQL language support, and query retrieval using Hive LLAP (Low Latency Analytical Processin<\/strong>g), Apache YARN & Apache Slider. Hive has a command-line tool and JDBC driver for data operations.<\/p>\n
Apache Hive has HCatalog and WebHCat components. HCatalog is used for storing data into Hadoop and provides data processing capabilities using Pig and Map Reduce. WebHCat is used to execute Hadoop MapReduce, Pig, and Hive jobs. Hive can be used for managing metadata operations using REST API. Hive can handle JDBC data types for handling data transformations.<\/p>\n
Apache Hive is used for the execution of queries on Hadoop- as Map-Reduce jobs. Customizations can be added as scripts. Hive can be executed for storing data into the database.  Data can have primitives, and collections. This tool has CLI (Command Line Interface) which is used for the execution of DDL-based queries. Hive query language has support for CONCAT, SUBSTR, ROUND, SUM, COUNT, MAX, and other operations. This query language has support for GROUP BY and SORT BY clauses.<\/p>\n
2.6 Apache Hadoop ETL Example<\/h3>\n
You need to configure HADOOP_HOME<\/code> as below:<\/p>\n
Setup<\/em><\/span><\/p>\n
export HADOOP_HOME=\/users\/bhagvan.kommadi\/desktop\/hadoop-2.9.1\/\n<\/pre>\n
You need to configure $HADOOP_HOME\/etc\/hadoop\/core-site.xml<\/code> as below:<\/p>\n
Core Site XML file<\/em><\/span><\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<?xml-stylesheet type=\"text\/xsl\" href=\"configuration.xsl\"?>\n\n<configuration>\n\n<property>\n <name>fs.defaultFS<\/name>\n <value>hdfs:\/\/apples-MacBook-Air.local:8020<\/value>\n<\/property>\n\n<\/configuration>\n\n<\/pre>\n
You need to start running Hadoop by using the command below:
 
<\/div> <\/div><\/p>\n
Hadoop Execution<\/em><\/span><\/p>\n
cd hadoop-2.9.1\/\ncd sbin\n.\/start-dfs.sh\n<\/pre>\n
The output of the commands is shown below:<\/p>\n
Hadoop Execution<\/em><\/span><\/p>\n
apples-MacBook-Air:sbin bhagvan.kommadi$ .\/start-dfs.sh\n20\/09\/14 20:26:23 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform\u2026 using builtin-java classes where applicable\nStarting namenodes on [apples-MacBook-Air.local]\napples-MacBook-Air.local: Warning: Permanently added the ECDSA host key for IP address 'fe80::4e9:963f:5cc3:a000%en0' to the list of known hosts.\nPassword:\napples-MacBook-Air.local: starting namenode, logging to \/Users\/bhagvan.kommadi\/desktop\/hadoop-2.9.1\/logs\/hadoop-bhagvan.kommadi-namenode-apples-MacBook-Air.local.out\nPassword:\nlocalhost: starting datanode, logging to \/Users\/bhagvan.kommadi\/desktop\/hadoop-2.9.1\/logs\/hadoop-bhagvan.kommadi-datanode-apples-MacBook-Air.local.out\nStarting secondary namenodes [0.0.0.0]\nPassword:\n0.0.0.0: starting secondarynamenode, logging to \/Users\/bhagvan.kommadi\/desktop\/hadoop-2.9.1\/logs\/hadoop-bhagvan.kommadi-secondarynamenode-apples-MacBook-Air.local.out\n20\/09\/14 20:27:07 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform\u2026 using builtin-java classes where applicable\n<\/pre>\n
Mysql is used as the database for Hive Metastore. You need to configure $HIVE_HOME\/conf\/hive-site.xml<\/code> as below:<\/p>\n
Hive Site \u2013 Hive Configuration<\/em><\/span><\/p>\n
<configuration>\n \n      <property>\n \n        <name>hive.metastore.warehouse.dir<\/name>\n \n        <value>\/users\/bhagvan.kommadi\/hive\/warehouse<\/value>\n \n      <\/property>\n \n    <property>\n \n      <name>javax.jdo.option.ConnectionURL<\/name>\n \n      <value>jdbc:mysql:\/\/localhost:3306\/hivedb?createDatabaseIfNotExist=true&useSSL=false<\/value>\n \n    <\/property>\n \n    <property>\n \n      <name>javax.jdo.option.ConnectionDriverName<\/name>\n \n      <value>com.mysql.jdbc.Driver<\/value>\n \n    <\/property>\n \n    <property>\n \n      <name>javax.jdo.option.ConnectionUserName<\/name>\n \n      <value>newuser<\/value>\n \n    <\/property>\n \n    <property>\n \n      <name>javax.jdo.option.ConnectionPassword<\/name>\n \n      <value>newuser<\/value>\n \n    <\/property>\n \n<\/configuration>\n\n\n<\/pre>\n
You need to start running Hive (HiveServer2) by using the command below:<\/p>\n
Hive Execution<\/em><\/span><\/p>\n
export HIVE_HOME=\/users\/bhagvan.kommadi\/desktop\/apache-hive-3.1.2-bin\/\n$HIVE_HOME\/bin\/hiveserver2\n<\/pre>\n
The output of the commands is shown below:<\/p>\n
Hive Execution<\/em><\/span><\/p>\n
apples-MacBook-Air:hive bhagvan.kommadi$ $HIVE_HOME\/bin\/hiveserver2\n2020-09-14 23:56:26: Starting HiveServer2\nSLF4J: Class path contains multiple SLF4J bindings.\nSLF4J: Found binding in [jar:file:\/Users\/bhagvan.kommadi\/Desktop\/apache-hive-3.1.2-bin\/lib\/log4j-slf4j-impl-2.10.0.jar!\/org\/slf4j\/impl\/StaticLoggerBinder.class]\nSLF4J: Found binding in [jar:file:\/Users\/bhagvan.kommadi\/Desktop\/hadoop-2.9.1\/share\/hadoop\/common\/lib\/slf4j-log4j12-1.7.25.jar!\/org\/slf4j\/impl\/StaticLoggerBinder.class]\nSLF4J: See http:\/\/www.slf4j.org\/codes.html#multiple_bindings for an explanation.\nSLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]\nHive Session ID = 28c5134a-d9f7-4ac2-9313-a04386f57ac9\nHive Session ID = 9c2982fa-965d-43e3-9f45-660e899a8958\nHive Session ID = 3000b392-aa68-4db1-ae3f-5b55c0fda19d\nHive Session ID = da06d930-091f-4097-b8b0-cd463e14dc2d\nHive Session ID = be1d5b5a-7f1a-4608-a08e-68f5515a2d90\nHive Session ID = 42f8afa1-3399-490e-8101-3f28d8d30072\nHive Session ID = 17b1f2aa-2c6d-40ff-849b-4c82fd1e38e0\nHive Session ID = d4e82376-f0ee-42e1-b27c-70dd8ce6efdc\nHive Session ID = 1e20ac56-21cc-45ef-9976-48078c6e3a12\nHive Session ID = 5821afdf-696f-46d1-acfe-15f1cf078e4e\nHive Session ID = f67cf1ba-937b-46a3-92b7-9c9efd145ae2\nHive Session ID = 9d8e3c3e-e216-4907-b0ba-08f23ffc8fd4\nHive Session ID = 316e0807-9c55-4bb5-a8da-360396581870\nHive Session ID = cef4c8de-9da8-4617-a053-9e28b40e8d6b\nHive Session ID = 596b7b81-47d1-4b09-9816-e88576c5529c\nHive Session ID = 7b1fe697-77e7-4c19-ac19-b0e0bf942480\nHive Session ID = 3aa7813d-f6a8-4238-a0b4-334106946266\nHive Session ID = e6631200-ee2b-487a-af8f-5d25f2a5e193\n<\/pre>\n
To configure the JDBC connection to Apache Hive, you can use the following code:<\/p>\n
Hadoop ETL Example<\/em><\/span><\/p>\n
import java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class HadoopETLExample {\n\tprivate static String driverClass = \"org.apache.hive.jdbc.HiveDriver\";\n\n\tpublic static void main(String args[]) throws SQLException {\n\t\ttry {\n\t\t\tClass.forName(driverClass);\n\t\t} catch (ClassNotFoundException exception) {\n\n\t\t\texception.printStackTrace();\n\t\t\tSystem.exit(1);\n\t\t}\n\t\tConnection connection = DriverManager.getConnection(\"jdbc:hive2:\/\/\", \"\", \"\");\n\t\tStatement statement = connection.createStatement();\n\n\t\tString table = \"EMPLOYEE\";\n\t\ttry {\n\t\t\tstatement.executeQuery(\"DROP TABLE \" + table);\n\t\t} catch (Exception exception) {\n\t\t\texception.printStackTrace();\n\t\t}\n\n\t\ttry {\n\t\t\tstatement.executeQuery(\"CREATE TABLE \" + table + \" (ID INT, NAME STRING, ADDR STRING)\");\n\t\t} catch (Exception exception) {\n\t\t\texception.printStackTrace();\n\t\t}\n\n\t\tString sql = \"SHOW TABLES '\" + table + \"'\";\n\t\tSystem.out.println(\"Executing Show table: \" + sql);\n\t\tResultSet result = statement.executeQuery(sql);\n\t\tif (result.next()) {\n\t\t\tSystem.out.println(\"Table created is :\" + result.getString(1));\n\t\t}\n\n\t\tsql = \"INSERT INTO EMPLOYEE (ID,NAME,ADDR) VALUES (1, 'John', '4 WestDrive SJC' )\";\n\t\tSystem.out.println(\"Inserting table into employee: \" + sql);\n\n\t\ttry {\n\t\t\tstatement.executeUpdate(sql);\n\t\t} catch (Exception exception) {\n\t\t\texception.printStackTrace();\n\t\t}\n\n\t\tsql = \"SELECT * FROM \" + table;\n\t\tresult = statement.executeQuery(sql);\n\t\tSystem.out.println(\"Running: \" + sql);\n\t\tresult = statement.executeQuery(sql);\n\t\twhile (result.next()) {\n\t\t\tSystem.out.println(\"Id=\" + result.getString(1));\n\t\t\tSystem.out.println(\"Name=\" + result.getString(2));\n\t\t\tSystem.out.println(\"Address=\" + result.getString(3));\n\t\t}\n\t\tresult.close();\n\n\t\tstatement.close();\n\n\t\tconnection.close();\n\n\t}\n}\n<\/pre>\n
In the eclipse, a Java project is configured with dependencies from<\/p>\n