Hi @Bala Vignesh N V. I know this is an old question, but I have encountered this recently. This answer may help someone else as well... The issue you had is most likely caused by specifying " COLLECTION ITEMS TERMINATED BY ',' ". When the table is defined like this (with COLLECTION ITEMS TERMINATED BY comma): -- Create a Dummy table to use in the insert query - like an Oracle DUAL table
create table dummy_TBL (col1 int) ;
insert into dummy_TBL (col1) values(1) ;
create table data_TBL (id int, name string, address struct<city:string,State:string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ','
STORED AS TEXTFILE;
insert into table data_TBL
select 1,
'Bala',
named_struct('city','Tampa','State','FL')
from dummy_TBL limit 1; The address.state value is NULL: +--------------+----------------+--------------------------------+--+
| data_tbl.id | data_tbl.name | data_tbl.address |
+--------------+----------------+--------------------------------+--+
| 1 | Bala | {"city":"Tampa","state":null} |
+--------------+----------------+--------------------------------+--+ But when you define the table like this (without COLLECTION ITEMS TERMINATED BY comma): create table data_TBL (id int, name string, address struct<city:string,State:string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
insert into table data_TBL
select 1,
'Bala',
named_struct('city','Tampa','State','FL')
from dummy_TBL limit 1; The address.state value is correct: +--------------+----------------+--------------------------------+--+
| data_tbl.id | data_tbl.name | data_tbl.address |
+--------------+----------------+--------------------------------+--+
| 1 | Bala | {"city":"Tampa","state":"FL"} |
+--------------+----------------+--------------------------------+--+
I hope this helps.
... View more
