Try this:
SELECT ID,itemsName,name,loc
FROM Table
LATERAL VIEW explode(items) itemTable AS itemsName;
in explode(items) , there items is your stored table column and Table is your Stored table.
I have a table with 4 columns, one column (items) type is ARRAY and other are string.
ID | items | name | loc
_________________________________________________________________
id1 | ["item1","item2","item3","item4","item5"] | Mike | CT
id2 | ["item3","item7","item4","item9","item8"] | Chris| MN
.
.
Here, I want unnormalized output like
ID | items | name | loc
______________________________________________________
id1 | item1 | Mike | CT
id1 | item2 | Mike | CT
id1 | item3 | Mike | CT
id1 | item4 | Mike | CT
id1 | item5 | Mike | CT
id2 | item3 | Chris | MN
id2 | item7 | Chris | MN
id2 | item4 | Chris | MN
id2 | item9 | Chris | MN
id2 | item8 | Chris | MN
I am not a Hive SQL expert, Please help me out of this.
Try this:
SELECT ID,itemsName,name,loc
FROM Table
LATERAL VIEW explode(items) itemTable AS itemsName;
in explode(items) , there items is your stored table column and Table is your Stored table.
Hi Kishore, Thanks for your reply. can we do the same with multiple array type columns? like ID | items | item_Name | name | loc ____________________________________________________________________ id1 | ["item1","item2","item3","item4","item5"] | ["Ruler","Cap","Pen","brush ","Eraser"] | Mike | CT id2 | ["item3","item7","item4","item9","item8"] | ["Pen","Pencil","brush"," ","Calc"] | Chris| MN
I am not getting your problem, make as another question and define question properly.