apache hive - HiveQL Loading Data - hive tutorial - hadoop hive - hadoop hive - hiveql
What is Data Loading in HiveQL ?
- Data loading is the process of copying and loading data or data sets from a source file, folder or application to a database or similar application.
- It is usually implemented by copying digital data from a source and pasting or loading the data to a data storage or processing utility.
apache hive - learn hive - hive tutorial - process of hiveql loading data - hive example
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Load Data Statement:
- After creating a table in SQL, we can insert data using the Insert statement.
- But in Hive, we can insert data using the LOAD DATA statement.
- While inserting data into Hive, it is better to use LOAD DATA to store bulk records.
- There are two ways to load data: one is from local file system and second is from Hadoop file system.
Syntax :
The syntax for load data is as follows:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- LOCAL is identifier to specify the local path. It is optional.
- OVERWRITE is optional to overwrite the data in the table.
- PARTITION is optional.
Example:
We will insert the following data into the table. It is a text file named sample.txt in /home/user directory.
| 1201 | Aarthi | 45000 | Technical manager |
| 1202 | Boomi | 45000 | Proofreader |
| 1203 | Dharsanya | 40000 | Technical writer |
| 1204 | Harikka | 40000 | Hr Admin |
| 1205 | Mirunalini | 30000 | Op Admin |
The following query loads the given text into the table.
hive> LOAD DATA LOCAL INPATH '/home/user/sample.txt'
OVERWRITE INTO TABLE wikitechy_employee;Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
On successful download, you get to see the following response:
OK
Time taken: 15.905 seconds
hive>
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
JDBC Program:
Given below is the JDBC program to load given data into the table.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class WikitechyLoadData {
private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
public static void main(String[] args) throws SQLException {
// Register driver and create driver instance
Class.forName(driverName);
// get connection
Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
// create statement
Statement stmt = con.createStatement();
// execute statement
stmt.executeQuery("LOAD DATA LOCAL INPATH '/home/user/sample.txt'" + "OVERWRITE INTO TABLE wikitechy_employee;");
System.out.println("Load Data into wikitechy_employee successful");
con.close();
}}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Save the program in a file named WikitechyLoadData.java. Use the following commands to compile and execute this program.
$ javac WikitechyLoadData.java
$ java WikitechyLoadData
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Output:
- Load Data into wikitechy_employee successful