Different ways to insert data into Hive table

There are several different variations and ways when it comes to inserting or loading data into Hive tables. This post will cover 3 broad ways to insert or load data into Hive tables.

Create Table As Select (CTAS)

A table named newtable will be created with the same structure as oldtable and all records from oldtable will also be copied to the newtable. 

create table newtable as select * from oldtable;

Load data

Load the data that is available under the local directory – /home/hirw/localdirectory into the table sales.

Note that overwrite is optional and will overwrite the data in the sales table.

load data local inpath '/home/hirw/localdirectory' 
overwrite into table sales;

Below script will load the data that is available in HDFS directory – /user/hirw/hdfsdirectory into the table sales2.

Note that overwrite is optional and will overwrite the data in the sales2 table.

load data inpath '/user/hirw/hdfsdirectory' 
overwrite into table sales2;

Insert data

Below is a simple insert statement to insert a row into table Sales. Sales table have 3 columns – id, item and quantity.

insert into sales
values(100, 'Shirt', 3);

Inserting multiple records in to sales tables.

insert into sales
values(100, 'Shirt', 3)
values(200, 'Pants', 2)
values(300, 'Skirt', 4);

Below is a simple insert into a table using select from another table.

insert into table sales2
select id, item, quantity from sales;