Avoiding Self Join in Hive

Question

I am using Hives built in collect_set function. The table looks like this:

 cookie, events, keywords,pages 
 1234      1      'dress'  10
 1234      1      'dress'  10
 1235      2      'shoes'  14
 1234      5      'socks'  22

using collect_set I can get the following structure

   select cookie, collect_set(events) as ev, collect_set(keywords) as kwords, 
   collect_set(pages)
    from table1 
    group by cookie

What I need to do is search the the collected arrays, multiple times, an example would be something like:

 select cookie 
 ,array_contains(collect_set(events),2) as has_2
 ,array_contains(collect_set(keywords),1) as has_4
  from table1
  group by cookie) A

As I understand, I am unable to project a field more than 1 time and end up having to do something like

 select a.cookie,a.has_2,b.has_4 from ( 
 select cookie 
 ,array_contains(collect_set(events),2) as has_2 
 from table1 group by cookie ) A
 inner join 
 select cookie 
 ,array_contains(collect_set(events),4) as has_4
 from table1 group by cookie) B 
on A.cookie = B. cookie

final result looks like:

 cookie, has_2, has_4 
 1234     F      F 
 1235     T      T

Is there any way to do this without the self join? Currently I would have to self join something like 30 times to get the format I need.

Thanks

libjack · Accepted Answer · 2014-04-04 15:58:47Z

2

select S.cookie, array_contains(S.events_set,2), array_contains(S.events_set,4) 
from
(select cookie, collect_set(events) as events_set
 from table1 group by cookie ) S

answered Apr 4, 2014 at 15:58

libjack

6,4532 gold badges32 silver badges36 bronze badges

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

Comments

Carter Shanklin · Accepted Answer · 2014-04-04 14:38:50Z

0

You should introduce a GROUP BY to your SQL.

e.g.

select
    cookie,
    array_contains(collect_set(events),2) as has_2,
    array_contains(collect_set(keywords),1) as has_4
 from
    table1
 group by
    cookie;

answered Apr 4, 2014 at 14:38

Carter Shanklin

3,04923 silver badges18 bronze badges

1 Comment

user2726995 Over a year ago

- added groupby to example

Collectives™ on Stack Overflow

Avoiding Self Join in Hive

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related