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.

look at "explode" function in hive : cwiki.apache.org/confluence/display/Hive/… — Amar
– Amar, Commented Nov 20, 2014 at 12:45
Select explode(items) is working for one field only, how to fetch other columns with explode? — user2416693
– user2416693, Commented Nov 20, 2014 at 13:07

Jonathan Hall · Accepted Answer · 2016-01-05 11:38:38Z

10

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.

edited Jan 5, 2016 at 11:38

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

answered Nov 20, 2014 at 13:06

Kishore

5,9215 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2416693 Over a year ago

Kishore Over a year ago

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

groenhen · Accepted Answer · 2020-02-01 09:26:54Z

0

We can use the posexplode() function to achieve the scenario you mentioned, that is with multiple array columns.

Something like this will work out:

SELECT ID,i1.item,i2.itemName,name,loc
 FROM Table
 LATERAL VIEW posexplode(items) i1 AS item,item_1
 LATERAL VIEW posexplode(item_Name) i2 AS itemName,itemName_1
 WHERE item=itemName

edited Feb 1, 2020 at 9:26

groenhen

3,02725 gold badges51 silver badges69 bronze badges

answered Feb 1, 2020 at 9:03

Asif Ramthan K T

1

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related