Avoiding JOINS in Hive

Hello readers,

Allow me to introduce myself briefly. I have been working on Hive since past one a half years and designed data pipeline for saavn.com. At number of times, taking JOINs appear necessary to solve the problem in hand. But sometimes we can translate a 'join' problem into a simpler problem. Let me explain this via an example.  

Problem: For a website's server logs, we get a bunch of unique user ids everyday who visited the website. Problem is to find out the number of new users i.e the user ids who visited the website for the first time.

Lets say there is a table uid_list_per_day, which contains list of all the users who visited website on a given day. 

CREATE EXTERNAL TABLE uid_list_per_day (uid string) PARTITION BY (date string); 

This table is partitioned by date because new data is added everyday.

Lets say we have another table uid_list_till_day which contains a list of all the user ids which visited anytime in the past on a given day. 

CREATE EXTERNAL TABLE uid_list_till_day (uid string) PARTITION BY (date string); 

To store new users, lets have this table : 

CREATE EXTERNAL TABLE uid_list_new_per_day (uid string) PARTITION BY (date string); 

Ok, lets write down what we have before finding out new users for the day d. 

We have

1. uid_list_per_day for day d
2. uid_list_till_day for day d-1 i.e. for one day before d

So, to calculate new users for the day d, we can write a query for simple algorithm : 

"New users for  day d are the users who visited on day d but not visited on of before d-1"  

OR 

"uid_list_new_per_day for day d is uids in table uid_list_per_day for day d but not in table uid_list_till_day for day d-1 "

A query to implement this using JOIN may look like this : 

INSERT OVERWRITE TABLE uid_list_new_per_day PARTITION (date)

SELECT A.uid , A.date

FROM
(

  SELECT uid, date  

  FROM uid_list_per_day 

  WHERE date = '${TODAY}'

)A

LEFT OUTER JOIN 

(

  SELECT uid, 'flag' as flag

  FROM uid_list_per_day 

  WHERE date = '${YESTERDAY}'

)B

ON(A.uid = B.uid )

WHERE flag is null 

;

Solution without a join 

Query : 

INSERT OVERWRITE TABLE uid_list_new_per_day PARTITION (date)

SELECT uid, '${TODAY}' as date

FROM 

(

  SELECT uid , MIN( date) as mindate

  FROM
  (

    SELECT uid, date  

    FROM uid_list_per_day 

    WHERE date = '${TODAY}'

    UNION ALL 

    SELECT uid, date 

    FROM uid_list_per_day 

    WHERE date = '${YESTERDAY}'

  )X

  GROUP BY uid 

)Y

WHERE mindate < '${TODAY}'

;

As you can see in the above query, we took a union of two tables whose alias is X. X contains uids for the day d as well as uids on or before d-1. Now for every uid, we find out the minimum date for which is uid was seen. If this minimum date is less than d, its not a new user.