Hive select data into an array of structs

Question

I am trying to figure out a way in Hive to select data from a flat source and output into an array of named struct(s). Here is a example of what I am looking for...

Sample Data:

house_id,first_name,last_name
1,bob,jones
1,jenny,jones
2,sally,johnson
3,john,smith
3,barb,smith

Desired Output:

1   [{"first_name":"bob","last_name":"jones"},{"first_name":"jenny","last_name":"jones"}]
2   [{"first_name":"sally","last_name":"johnson"}]
3   [{"first_name":"john","last_name":"smith"},{"first_name":"barb","last_name":"smith"}]

I tried collect_list and collect_set but they only allow primitive data types. Any thoughts of how I might go about this in Hive?

o-90 · Accepted Answer · 2015-09-03 21:52:43Z

10

I would use this jar, it is a much better implementation of collect (and takes complex datatypes).

Query:

add jar /path/to/jar/brickhouse-0.7.1.jar;
create temporary function collect as 'brickhouse.udf.collect.CollectUDAF';

select house_id
  , collect(named_struct("first_name", first_name, "last_name", last_name))
from db.table
group by house_id

Output:

1   [{"first_name":"bob","last_name":"jones"}, {"first_name":"jenny","last_name":"jones"}]
2   [{"first_name":"sally","last_name":"johnson"}]
3   [{"first_name":"john","last_name":"smith"},{"first_name":"barb","last_name":"smith"}]

answered Sep 3, 2015 at 21:52

o-90

17.7k10 gold badges44 silver badges65 bronze badges

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

3 Comments

Cymon Over a year ago

Perfect! Works as stated.

samol Over a year ago

Is there a way to do this without having to declare named_struct explicitly? For example: collect(*)

Evg Over a year ago

@samol You can also create struct without giving names to columns, for example struct(1, 'abc') will be structure with col1 and col2 field names.

beterthanlife · Accepted Answer · 2017-06-05 16:25:59Z

4

You can also use a workaround

select collect_list(full_name) full_name_list from (
    select 
        concat_ws(',', 
            concat("first_name:",first_name), 
            concat("last_name:",last_name)
            ) full_name, 
        house_id
    from house) a 
group by house_id

edited Jun 5, 2017 at 16:25

beterthanlife

1,6682 gold badges18 silver badges30 bronze badges

answered Jun 5, 2017 at 15:28

sumit

411 bronze badge

Comments

Niranjan S · Accepted Answer · 2019-11-19 14:53:28Z

0

You can try it using pyspark or scalaspark.. Spark sql allows both primitive and non primitive datatypes. ie., You can do collect_set( named_struct)

answered Nov 19, 2019 at 14:53

Niranjan S

12 bronze badges

Collectives™ on Stack Overflow

Hive select data into an array of structs

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related