Maintainable processing of json data

In saavn.com, we convert our webserver logs into json format. Lets call these as unprocessed_json_logs. These unprocessed_json_logs need some cleanup before they can be actually used for other data processing. We will perform the cleanup and call the result as processed_json_logs which are also in json format.

Sample row of unprocessed_json_logs looks like :
{'uid': 'user1', 'event' : 'android:click_homepage', 'country': 'IN', k1:v1, k2:v2, k3:v3 .... }

Sample row of processed_json_logs looks like :
{'uid': 'user1', 'event' : 'click_homepage','platform':'android', 'country': 'IN', k1:v1, k2:v2, k3:v3 .... }

Point to notice here is that in addition to fields of you interest, there are some dont-care key-value pairs that should simply be passed though. They should remain same in unprocessed_json_logs and processed_json_logs. 

A Bad solution using Json-serde

CREATE TABLE unprocessed_json_logs (uid string, event string, country string, k1 string, k2 string, k3 string,  ..., kn string) LOCATION 's3://mycompany/unprocessed_json_logs';

CREATE TABLE processed_json_logs (uid string, event string, platform string , country string, k1 string, k2 string, k3 string,  ..., kn string) LOCATION 's3://mycompany/processed_json_logs';

INSERT OVERWRITE TABLE processed_json_logs
SELECT uid, split(event, '\:')[1] as event , split(event, '\:')[0] as platform country, k1, k2, k3 ... ,kn
FROM unprocessed_json_logs

This solution is bad because eveytime a new key-pair is added to unprocessed_json_logs lets say Kn+1:Vn+1, you will have to update your hive script. This sounds like a maintenance nightmare -doesn't it?

A better solution using Hive Streaming

CREATE TABLE unprocessed_json_logs (jsonstr string) LOCATION 's3://mycompany/unprocessed_json_logs';

CREATE TABLE processed_json_logs (jsonstr string) LOCATION 's3://mycompany/processed_json_logs';

INSERT OVERWRITE TABLE processed_json_logs
SELECT TRANSFORM (jsonstr)
USING 'cleanup.php'
FROM unprocessed_json_logs

Idea here is to read the whole json in one string variable jsonstr. This jsonstr is passed as it is to a reducer cleanup.php. Now all the cleanup takes place inside this reducer. In the reducer you can decode the json, make changes to the fields of your interest while leaving dont-care key-value pars as it is and then print it to stdout. As you must notice, hive query and reducer both do not make any mention of dont care key-value pairs. So when new key-value pairs show up in your logs, you will not have to change your code.

Simulating 'NOT IN' caluse in Hive

Hello,

While designing data pipeline for saavn.com, I faced the need of SQL's 'NOT IN' clause. But Hive does not support 'NOT IN' clause. I came up with a simple workaround for that using a JOIN.


Lets say I have a table A which contains a list of user ids who bought apples and a table B which contains a list of user ids who bought bananas. Problem is to find a list of users who bought apples but not bananas.

Let's write down the schema of the two tables before we proceed :

CREATE TABLE A (uids string);

CREATE TABLE B (uids string); 

If we were to do this in SQL, we would do :
SELECT uid FROM A WHERE uid NOT IN (SELECT uid FROM B) X ;

But since, 'NOT IN' operator is not available in Hive, here is a simple workaround:

SELECT
FROM
(
  SELECT uid
  FROM A
) X
LEFT OUTER JOIN
(
  SELECT uid , 'flag' as  flag
  FROM B
) Y
ON (A.uid )

WHERE flag is null ;

Its easy to notice that flag will be non-null for those who buys both apple and oranges. Similarly, flag will be null for those who buys apples but not oranges.

Conclusion : We can simulate 'NOT IN' operator in Hive by using a JOIN and a dummy flag.

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.