create nested data from from un-nested data in hive

Question

Is there any way that I can create nested data in hive from un-nested data

Example: source table

id zip_code
123 12345
123 23456
123 56789
234 12345
234 99999

to look like this

id zipcode
123 12345,23456,56789
234 12345,99999

Do note that the number of zipcode's for an id can be varying

Possible duplicate of Grouping hive rows in an array of this rows — o-90
– o-90, Commented Jul 8, 2016 at 22:19

leftjoin · Accepted Answer · 2016-07-09 09:34:34Z

1

select id
     , concat_ws(',',collect_set(zip_code)) as zipcode 
  from your_table 
 group by id

collect_set() will remove duplicates in zip_code collection. If you need duplicates, use collect_list instead

answered Jul 9, 2016 at 9:34

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

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

1 Answer 1