Insert into table in Hive with examples
Contents
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
|
1 2 3 |
INSERT INTO <Table_Name>(column1,column2,..columnN) VALUES (value1,value2,...valueN); |
Insert query with “Table” keyword
|
1 2 3 |
INSERT INTO TABLE <Table_Name>(column1,column2,..columnN) VALUES (value1,value2,...valueN); |
- 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.
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.
|
1 |
hive> Insert Into Customer Values(2398,'james@gmail.com'); |
Method 2 : Insert Into Table <Table_Name>
Here we have used Table keyword in the Insert query that runs successfully in Hive.
|
1 |
hive> Insert Into Table Customer Values(6734,'albert@gmail.com'); |
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.
|
1 2 3 4 5 6 |
hive> Insert into customer values (4563); FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different 'customer': Table insclause-0 has 2 columns, but query has 1 columns. |
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
|
1 2 3 4 5 |
hive> Insert into customer(customer_id) values (4563); Loading data to table test_db.customer Table test_db.customer stats: [numFiles=3, numRows=3, totalSize=75, rawDataSize=48] OK Time taken: 16.34 seconds |
The query inserted the customer id into the customer table. Now we can run the select query to get the results from customer table.
The NULL value has added to email column for the customer id #4563 in the Customer table.
Related Articles: Insert Overwrite Table in Hive
