Hive: Insert multiple records using single insert query

Syntax

INSERT INTO {TABLE_NAME} VALUES
(record1), 
(record2), 
(record3);

Example

INSERT INTO user VALUES
(1, 'Ravi'), 
(2, 'Ram'), 
(3, 'Siva');

Step 1: Let’s create user table.

CREATE TABLE user (id INT, name STRING);

hive> CREATE TABLE user (id INT, name STRING);
OK
Time taken: 0.077 seconds
hive> ;
hive> ;
hive> DESCRIBE user;
OK
id                  	int                 	                    
name                	string              	                    
Time taken: 0.048 seconds, Fetched: 2 row(s)

Step 2: Let’s insert multiple records to user table by executing below command.

INSERT INTO user VALUES
(1, 'Ravi'), 
(2, 'Ram'), 
(3, 'Siva');

hive> INSERT INTO user VALUES
    > (1, 'Ravi'), 
    > (2, 'Ram'), 
    > (3, 'Siva');
Query ID = cloudera_20220414030000_15b86333-6e02-43f9-9a62-05b0f0662ef6
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649172504056_0018, Tracking URL = http://quickstart.cloudera:8088/proxy/application_1649172504056_0018/
Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_1649172504056_0018
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-14 03:00:12,354 Stage-1 map = 0%,  reduce = 0%
2022-04-14 03:00:19,899 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.4 sec
MapReduce Total cumulative CPU time: 1 seconds 400 msec
Ended Job = job_1649172504056_0018
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://quickstart.cloudera:8020/user/hive/warehouse/user/.hive-staging_hive_2022-04-14_03-00-05_320_3281555075190979288-1/-ext-10000
Loading data to table default.user
Table default.user stats: [numFiles=1, numRows=3, totalSize=20, rawDataSize=17]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.4 sec   HDFS Read: 3816 HDFS Write: 88 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 400 msec
OK
Time taken: 16.935 seconds
hive>

 

Select all the records from user table.

hive> SELECT * FROM user;
OK
1	Ravi
2	Ram
3	Siva
Time taken: 0.067 seconds, Fetched: 3 row(s)

 

  

Previous                                                    Next                                                    Home