CODEWITHSUNDEEP
hadoopsplithive
CJR
CJR

Reputation: 200

Hadoop/Hive - Split a single row into multiple rows

I'm trying to find a way to split a row in Hive into multiple rows based on a delimited column. For instance taking a result set:

ID1  Subs
1     1, 2
2     2, 3

And returning:

ID1  Subs
1     1
1     2
2     2
2     3

I've found some road signs at http://osdir.com/ml/hive-user-hadoop-apache/2009-09/msg00092.html, however I wasn't able enough detail to point me in the direction of a solution, and I don't know how I would set up the transform function to return an object that would split the rows.

Upvotes: 10

Views: 29562

Answers (2)

Narendra Reddy Kalli
Narendra Reddy Kalli

Reputation: 1

SELECT ID1, new_Subs_clmn
FROM tableName lateral view explode(split(Subs,',')) Subs AS new_Sub_clmn;

I was initially confused with the names used, sharing the above query thinking it would be of help.

Upvotes: 0

Guiyanakuang
Guiyanakuang

Reputation: 451

Try this wording

SELECT ID1, Sub
FROM tableName lateral view explode(split(Subs,',')) Subs AS Sub

Upvotes: 28

Related Questions