Creating Hive table using TEXTFILE format and importing data

We will see how to create a table in Hive using TEXTFILE format and how to import data into the table.

TEXTFILE

Textfile format stores data as plain text files. Textfile format enables rapid development due to its simplicity but other file formats like ORC are much better when it comes to data size, compression, performance etc.

Create table

CREATE TABLE Employee(
ID BIGINT,
NAME STRING, 
AGE INT,
SALARY BIGINT 
)
COMMENT 'This is Employee table stored as textfile'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

Load data into the table

Input File :-

On our HDFS, we have a file ‘/home/hadoop/data/employee.csv‘ containing the following data. We will load this data in our Employee table :-

1,rajesh,28,30000
2,rahul,22,20000
3,mahesh,25,10000

Load Data :-

#To overwrite the data in the table use -
LOAD DATA INPATH '/home/hadoop/data/employee.csv' OVERWRITE INTO TABLE Employee;

#To append the data in the table use - 
LOAD DATA INPATH '/home/hadoop/data/employee.csv' INTO TABLE Employee;

After loading of data is successful, the file ‘/home/hadoop/data/employee.csv‘ will get deleted.

Verify :-

Lets see if the data is successfully loaded in the Employee table.

select * from employee;
+--------------+----------------+---------------+------------------+--+
| employee.id  | employee.name  | employee.age  | employee.salary  |
+--------------+----------------+---------------+------------------+--+
| 1            | rajesh         | 28            | 30000            |
| 2            | rahul          | 22            | 20000            |
| 3            | mahesh         | 25            | 10000            |
+--------------+----------------+---------------+------------------+--+