Clever Geek Handbook

How to consume array values ​​in strings! using Hive SQL

I have a table with 4 columns, one column (s) is ARRAY and the others are row.

ID   |    items                                  | name  |  loc  
_________________________________________________________________

id1  | ["item1","item2","item3","item4","item5"] | Mike | CT
id2  | ["item3","item7","item4","item9","item8"] | Chris| MN
.
.

Here I want to get 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 on this.

+3


source to share


1 answer


Try the following:

 SELECT ID,itemsName,name,loc
 FROM Table
 LATERAL VIEW explode(items) itemTable AS itemsName;


in explosion (elements), there elements is your stored table column and table is your Stored Table.

+4


source






More articles:


All Articles