Hive: Insert map data using INSERT statement

 

In this post, I am going to explain how to insert a map type data into a table using ‘INSERT’ statement.

 

We can insert map data into a table using map function.

 

INSERT INTO employee SELECT "1","Krishna",map('Java', '4.3Yrs', 'C', '3Yrs');

 

Step 1: Create an employee table.

CREATE TABLE employee (
    id INT,
    name STRING,
    technology_experience MAP<STRING,STRING>
 )
 STORED AS TEXTFILE;

 

 hive> CREATE TABLE employee (
    >     id INT,
    >     name STRING,
    >     technology_experience MAP<STRING,STRING>
    >  )
    >  STORED AS TEXTFILE;
OK
Time taken: 0.097 seconds
hive> ;
hive> ;
hive> DESCRIBE employee;
OK
id                  	int                 	                    
name                	string              	                    
technology_experience	map<string,string>  	                    
Time taken: 0.042 seconds, Fetched: 3 row(s)

Step 2: Insert data into employee table.

hive> INSERT INTO employee SELECT "1","Krishna",map('Java', '4.3Yrs', 'C', '3Yrs');
Query ID = cloudera_20220419061818_dc711fca-539b-4795-bdfb-bc7bc92208fe
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_0033, Tracking URL = http://quickstart.cloudera:8088/proxy/application_1649172504056_0033/
Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_1649172504056_0033
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-19 06:18:31,108 Stage-1 map = 0%,  reduce = 0%
2022-04-19 06:18:37,452 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.35 sec
MapReduce Total cumulative CPU time: 1 seconds 350 msec
Ended Job = job_1649172504056_0033
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/employee/.hive-staging_hive_2022-04-19_06-18-23_243_5625011913468047916-1/-ext-10000
Loading data to table default.employee
Table default.employee stats: [numFiles=1, numRows=1, totalSize=29, rawDataSize=28]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.35 sec   HDFS Read: 4305 HDFS Write: 101 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 350 msec
OK
Time taken: 15.389 seconds

Query employee table to confirm the data.

hive> SELECT * FROM employee;
OK
1	Krishna	{"Java":"4.3Yrs","C":"3Yrs"}
Time taken: 0.039 seconds, Fetched: 1 row(s)

 

 

 

Previous                                                    Next                                                    Home