CODEWITHSUNDEEP
hadoophive
KMeansK
KMeansK

Reputation: 64

Construct map from result of collect_list in Hive

A sequence of UNION ALLs result in a list of key value pairs which I want to use to build a MAP.

The desired functionality is something like this:

select id1, id2, map(collect_list(col)) as measurements
from
(
    select id1, id2, "height" as col
    union all
    select id1, id2, count(*) as col from table1
    union all
    select id1, id2, "weight" as col
    union all
    select id1, id2, count(*) as col from table2
)

What is the right way to implement this?

The result I hope to get is:

id1  id2  measurements
1    10   {"height": 10, "weight": 20}
2    20   {"height": 10, "weight": 20}

Upvotes: 0

Views: 510

Answers (1)

hlagos
hlagos

Reputation: 7947

you requirement is not very clear but it looks like you need something like

select id1, id2, named_struct("height", height, "weight", weight) from 
(
select t1.id1,t1,id2,height,weight from 
(select id1, id2, count(*) as height from table1 group by id1,id2) t1
join 
(select id1, id2, count(*) as height from table2 group by id1,id2) t2
on t1.d1=t2.d1 and t1.d2=t2.d2
) t;

I didn't run it but it should work.

Upvotes: 1

Related Questions