ClickStream analysis using Pig and Hive

Many of the e-commerce sites had been making a quite an impact on the overall economy for quite some time in many of the countries. Recently it had picked up quite good in India also. Flipkart is one of the top portal in India. Some time back I bought a HP 430 Notebook from Flipkart.

All the e-commerce portals store the user activities on their site as clickstream activity and later they analyze it to identify what the user has browsed and show the appropriate recommendations when the user visits the site again.

In this blog entry, we will see how to analyze the clickstream and the user data together using Pig and Hive. The challenge is to find the top 3 URLs visited by users whose age is less than 16 years.

To get a quick overview of Pig and Hive, Hadoop - The Definitive Guide is the best resource, but to deep dive Programming Hive and Programming Pig are the best bets. Some time back I compared Pig and Hive in a blog entry.

Data from the external systems can be pushed into HDFS using Sqoop, Flume and in many other ways. For now, lets assume that the user data and the clickstream data is already there is HDFS as shown below. For sake of simplicity only a few columns have been included, but can be made much more complex.

/user/training/user/part-m-00000

1,Praveen,20,India,M
2,Prajval,5,India,M
3,Prathibha,15,India,F

/user/training/clickstream.txt

1,www.bbc.com
1,www.abc.com
1,www.gmail.com
2,www.cnn.com
2,www.eenadu.net
2,www.stackoverflow.com
2,www.businessweek.com
3,www.eenadu.net
3,www.stackoverflow.com
3,www.businessweek.com

Let's create the 'user' table in Hive and map the user data in HDFS to the table

CREATE TABLE user (
   user_id INT,
   name STRING,
   age INT,
   country STRING,
   gender STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA INPATH '/user/training/user/part-m-00000' OVERWRITE INTO TABLE user;

Similarly, let's create the 'clickstream' table in Hive and map the user data in HDFS to the table

CREATE TABLE clickstream (
   userid INT,
   url STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA INPATH '/user/training/clickstream.txt' OVERWRITE INTO TABLE clickstream;

Here is the HiveQL query to get the top 3 URLs visited by user whose age is less than 16. The query looks very similar to SQL, which makes it easy to get started with Hive. Hive automatically creates a plan for the below query and submits it to the Hadoop cluster. SQL interfaces are being added to the Big Data frameworks to make it easier for those who are familiar with SQL to get started with the different Big Data frameworks easily. Here is an interesting article from GigaOM on the same.

SELECT url,count(url) c FROM user u JOIN clickstream c ON (u.user_id=c.userid) where u.age<16 group by url order by c DESC LIMIT 3;

Here is the PigLatin code for the same.

Users1 = load '/user/training/user/part-m-00000' using PigStorage(',') as (user_id, name, age:int, country, gender);
Fltrd = filter Users1 by age <= 16;
Pages  = load '/user/training/clickstream.txt' using PigStorage(',') as (userid, url);
Jnd = join Fltrd by user_id, Pages by userid;
Grpd = group Jnd by url;
Smmd = foreach Grpd generate group, COUNT(Jnd) as clicks;
Srtd = order Smmd by clicks desc;
Top3 = limit Srtd 3;
dump Top3;

Here is the output after running the HiveQL query.

MapReduce Jobs Launched:
Job 0: Map: 2  Reduce: 1   Cumulative CPU: 2.4 sec   HDFS Read: 686 HDFS Write: 238 SUCCESS
Job 1: Map: 1  Reduce: 1   Cumulative CPU: 1.35 sec   HDFS Read: 694 HDFS Write: 238 SUCCESS
Job 2: Map: 1  Reduce: 1   Cumulative CPU: 1.32 sec   HDFS Read: 694 HDFS Write: 64 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 70 msec
OK
www.businessweek.com    2
www.eenadu.net    2
www.stackoverflow.com    2
Time taken: 105.076 seconds

Here is the output after running the PigLatin script.

2013-03-04 21:06:57,654 [main] INFO  org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher - Success!
2013-03-04 21:06:57,656 [main] INFO  org.apache.pig.data.SchemaTupleBackend - Key [pig.schematuple] was not set... will not generate code.
2013-03-04 21:06:57,667 [main] INFO  org.apache.hadoop.mapreduce.lib.input.FileInputFormat - Total input paths to process : 1
2013-03-04 21:06:57,667 [main] INFO  org.apache.pig.backend.hadoop.executionengine.util.MapRedUtil - Total input paths to process : 1
(www.eenadu.net,2)
(www.businessweek.com,2)
(www.stackoverflow.com,2) 

Just a quick comparison Hive is converting the query into 3 MR jobs and took around 105 seconds for completion while Pig is converting the PigLatin script into 5 MR jobs and is taking around 210 seconds for completion.

Didn't spend much time  to look at why Pig is twice as slow when compared to Hive. Could have used partitions in Hive to make the Hive query run faster. If anyone is really interested in making the earlier mentioned Pig and Hive queries faster, please send me an email at praveensripati@gmail.com and I will post a follow on blog post with the consolidated suggestions after trying them out.

Also, if anyone is interested in any POC (Proof Of Concept) ideas around Big Data / Hadoop send me an email at praveensripati@gmail.com. I will attribute it to them :)

Happy hadooping !!!!