Insert into table in Hive with examples

Insert into query in Hive

Hive support INSERT INTO syntax starting in version 0.8. We can write the insert query like other traditional database(Oracle/Teradata) to add the records into the Hive table.

Syntax of Insert into query in Hive

Hive provides two syntax for Insert into query like below. Here we are using Hive version 1.2 and it is supporting both syntax of insert query.

Insert query without “Table” keyword

Insert query with “Table” keyword

  • column1,column2..columnN – It is required only if you are going to insert values only for few columns. otherwise it is optional parameter.
  • value1,value2,..valueN – Mention the values that you needs to insert into hive table.

Example for Insert Into Query in Hive

Lets create the Customer table in Hive to insert the records into it.

Create table in Hive
Create table in Hive

The customer table has created successfully in test_db. Now we can run the insert query to add the records into it.

Method 1 : Insert Into <Table_Name>

In this Insert query, We used traditional Insert query like Insert Into <Table_Name> Values to add the records into Hive table.

Example for insert into query in hive
Example for insert into query in hive

Method 2 : Insert Into Table <Table_Name>

Here we have used Table keyword in the Insert query that runs successfully in Hive.

Insert Into Table in Hive
Insert Into Table in Hive

Insert Selective columns in Hive

If we insert the values only for few columns, we need to specify the column name in the insert query. Otherwise Hive will throw the error message as below.

As we are inserting values only for customer_id column, lets specify the column name in the insert query and rerun the same in Hive

The query inserted the customer id into the customer table. Now we can run the select query to get the results from customer table.

Select query in Hive
Select query in Hive

The NULL value has added to email column for the customer id #4563 in the Customer table.

Related Articles: Insert Overwrite Table in Hive