I am trying to export data from excel into a hive table, while doing so, i have a column 'ABC' which has values like '1,2,3'. I used the lateral view explode function but it does not does anything to my data.

Following is my code snippet : CREATE TABLE table_name ( id string, brand string, data_name string, name string, address string, country string, flag string, sample_list array ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
;
LOAD DATA LOCAL INPATH 'location' INTO TABLE table_name ;

output sample:

id brand data_name name address country flag sample_list

19 1 ABC SQL ABC Cornstarch IN 1 ["[1,2,3]"]

then i do: select * from franchise_unsupress LATERAL VIEW explode(SEslist) SEslist as final_SE;

output sample:

id brand data_name name address country flag sample_list

19 1 ABC SQL ABC Cornstarch IN 1 [1,2,3]

I also tried: select * from franchise_unsupress lateral view explode(split(SEslist,',')) SEslist AS final_SE ; but got an error:

FAILED: ClassCastException org.apache.hadoop.hive.serde2.objectinspector.StandardListObjectInspector cannot be cast to org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector

whereas, what i need is:

id brand data_name name address country flag sample_list

19 1 ABC SQL ABC Cornstarch IN 1 1 19 1 ABC SQL ABC Cornstarch IN 1 2 19 1 ABC SQL ABC Cornstarch IN 1 3

Any help will be greatly appreciated! thank you

The problem is that array is recognized in a wrong way and loaded as a single element array ["[1,2,3]"]. It should be [1,2,3] or ["1","2","3"] (if it is array<string>)

When creating table, specify delimiter for collections:

FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','