Write a custom join query in hive

Question

I have two tables

table J:
P_ID  event   T_ID              URL
8187    6   14690481058450526   maplestage.com
8187    6   14690481058450527   maplestage.com

Table A:
P_ID  event   T_ID              URL
8187    7   14690481058450526   NULL
8187    7   14690481058450526   NULL
8187    7   14690481058450527   NULL

I have the following query to count the event that are 6 and events that are 7:

SELECT sum(if(j.event=6,1,0)) Type1, j.P_ID, j.URL, sum(if(a.event=7,1,0)) Type2  FROM Tabel_J j LEFT outer join Table_A a on a.T_ID = j.T_ID GROUP BY j.P_ID, j.URL;

The result i am getting is this:

Type1   P_ID          URL         Type2
3       8187      maplestage.com    3

The result i want to get is:

Type1   P_ID          URL         Type2
2       8187      maplestage.com    3

Please can someone help me with this.

Thanks

rchang · Accepted Answer · 2015-01-22 19:18:37Z

1

From your data, the significance of the T_ID column isn't immediately obvious to me. Having said that, based on what I think you are after (perhaps I'm wrong), this query might get you there:

SELECT
  SUM(IF(event=6,1,0)) Type1,
  P_ID,
  collect_set(URL)[0] URL,
  SUM(IF(event=7,1,0)) Type2
FROM
  (SELECT * FROM tabel_j UNION ALL SELECT * FROM table_a) everything
GROUP BY P_ID;

edited Jan 22, 2015 at 19:18

answered Jan 22, 2015 at 16:34

rchang

5,2461 gold badge17 silver badges26 bronze badges

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

2 Comments

user175084 Over a year ago

this worked thanks, also will this bale to work without Coalesce??

rchang Over a year ago

@user175084 That's a great point - I had thought the coalesce would be to filter out null values, but it appears that collect_set implicitly does this. I'll edit the answer to remove coalesce.

Collectives™ on Stack Overflow

Write a custom join query in hive

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related