Hive Insert Table

Summary -

In this topic, we described about the Insert Table in detail.

INSERT statement is used to insert the data into the table or view. Single row insertions not supported in HIVE. INSERT can insert more than one row at a time.

Syntax -

INSERT { INTO | OVERWRITE } [TABLE] table_name
 [(column_list)]
 [ PARTITION (col_name [= constant] [, col_name [= constant]...])]
 {[SHUFFLE] | [NOSHUFFLE] select_statement
 | VALUES (value [, value ...]) [, (value [, value ...]) ...]}

INTO and OVERWRITE clauses -

The INSERT INTO syntax appends data to a table and the inserted data is put into one or more new data files. The INSERT OVERWRITE syntax replaces the data in a table and the overwritten data files are deleted immediately.

Note! INSERT will be used to do bulk insert from one table to another table.
Normally INSERT will be used to copy the data from one database to another database for the same table resides in both databases.

Example -

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

Std No	Std Name	Std Class	Std Grade
1	Pawan	10	A
2	Srinivas	10	A
3	Sridhar	10	A
4	Kumar	10	B

The above the data is in the file std_details.txt in the location usr/data/ and got loaded to the table std_details1.

Below example inserts the data from std_details1 in std_db1 to std_details2 in std_db2.

Hive> INSERT OVERWRITE TABLE std_db2.std_details2
 SELECT * FROM std_db1.std_details1;

After successful execution of the above statement, the data will appear in std_details2.