Hive: Insert struct data using INSERT statement

Step 1: Create employee table.

CREATE TABLE employee (
    id INT,
    name STRING,
    other_info STRUCT<gender:STRING,age:INT>
 )
 STORED AS TEXTFILE;

 

hive> CREATE TABLE employee (
    >     id INT,
    >     name STRING,
    >     other_info STRUCT<gender:STRING,age:INT>
    >  )
    >  STORED AS TEXTFILE;
OK
Time taken: 0.051 seconds
hive> ;
hive> ;
hive> DESC employee;
OK
id                  	int                 	                    
name                	string              	                    
other_info          	struct<gender:string,age:int>	                    
Time taken: 0.042 seconds, Fetched: 3 row(s)

 

Step 2: Insert data into employee table by executing below statement.

hive> INSERT INTO employee SELECT "1","Krishna",NAMED_STRUCT('gender', 'Male', 'age', 32);
Query ID = cloudera_20220419062424_01fbea53-8448-400a-b8cc-a1d23dc19c6f
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_0034, Tracking URL = http://quickstart.cloudera:8088/proxy/application_1649172504056_0034/
Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_1649172504056_0034
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-19 06:24:29,839 Stage-1 map = 0%,  reduce = 0%
2022-04-19 06:24:36,229 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.53 sec
MapReduce Total cumulative CPU time: 1 seconds 530 msec
Ended Job = job_1649172504056_0034
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-24-22_769_2507653904500688823-1/-ext-10000
Loading data to table default.employee
Table default.employee stats: [numFiles=1, numRows=1, totalSize=18, rawDataSize=17]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.53 sec   HDFS Read: 4198 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 530 msec
OK
Time taken: 15.839 seconds

 

Query employee table.

 

Query employee table to confirm the data.

 

hive> SELECT * FROM employee;
OK
1	Krishna	{"gender":"Male","age":32}
Time taken: 0.036 seconds, Fetched: 1 row(s)

 

Previous                                                    Next                                                    Home