how to expend array values in rows!! using Hive SQL

Question

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.

Solution

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.

Comments

[Amar]

look at "explode" function in hive : cwiki.apache.org/confluence/display/Hive/…

[user2416693]

Select explode(items) is working for one field only, how to fetch other columns with explode?

[user2416693]

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

[Kishore]

I am not getting your problem, make as another question and define question properly.