Hadoop/Hive - Split a single row into multiple rows

Question

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.

Answer 1

Try this wording

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