Apache Hadoop : Social Media (Twitter) Data Analysis PIG Case Study

Social Media (Twitter) Data Analysis example

This post contains examples of social media analysis using Pig. Individual examples are described in detail below.

The dataset :

• full_text.txt: Contains geo-tagged Twitter data with the following fields:
• Twitter user ID
• Timestamp of the tweet
• Location of the tweet
• Latitude of the tweet
• Longitude of the tweet
• Tweet content
• cities15000.txt: Contains information on cities around the world with the following fields:
  • Record ID
  • City name
  • Country code
  • Latitude of the city
  • Longitude of the city
  • Timezone ID

MostPopularHashtags.pig

This script file will find the top 5 hashtags from full_text.txt. For this example, a hashtag is defined to be any string that starts with '#' and contains numbers, letters or underscores.

	-- Load Data data = LOAD '/hadoopgyaan/user/popularhashtags/full_text.txt' AS (id:chararray, ts:chararray, location:chararray, lat:float, lon:float, tweet:chararray);
	-- Convert all tweets to lowercase to allow for accurate grouping
	lowercase = FOREACH data GENERATE LOWER(tweet) as tweet;
	-- Separate all tweets into individual words
	tweetwords = FOREACH lowercase GENERATE FLATTEN(TOKENIZE(tweet)) as token;
	-- Extract only hashtags from the collection of words
	hashtags = FOREACH tweetwords GENERATE REGEX_EXTRACT(token, '(#)[a-z0-9_](\\w+)',0) as hashtag;
	-- Group identical hashtags together and create an ordered list of aggregrate counts
	grouphashtags = GROUP hashtags BY hashtag;
	counthashtags = FOREACH grouphashtags GENERATE group as hashtag, COUNT(hashtags) as cnt;
	orderhashtags = ORDER counthashtags BY cnt desc;
	limithashtags = LIMIT orderhashtags 5;
	DUMP limithashtags;

MostMobileTweeter.pig

This script file will find the user that tweeted from the greatest number of locations, i.e. the greatest number of distinct latitude and longitude pairs.

	--Load Data data = LOAD '/hadoopgyaan/user/mobiletweeter/full_text.txt' AS (id:chararray, ts:chararray, location:chararray, lat:float, lon:float, tweet:chararray);
	-- Join latitude and longitude coordintates into a tuple
	locations = FOREACH data GENERATE id, TOTUPLE(lat, lon) as loc_tuple:tuple(lat:chararray, lon:chararray);
	-- Find only unique locations for each user
	distinct_locations = DISTINCT(FOREACH locations GENERATE id, loc_tuple);
	-- Create an ordered list of the counts of unique locations for each user, returning the top result
	group_locations = GROUP distinct_locations BY id;
	count_locations = FOREACH group_locations GENERATE group as id, COUNT(distinct_locations) as cnt;
	ordered_counts = ORDER count_locations BY cnt desc;
	limit_counts = LIMIT ordered_counts 1;
	DUMP limit_counts;

Downloads :

Social Media (Twitter) Analysis Dataset

I hope this tutorial will surely help you. If you have any questions or problems please let me know.

Happy Hadooping with Patrick..

Apache Hadoop : Banking Transaction PIG Case Study

Banking Transaction example

we will analyze a banking domain dataset, which contains several files with details of its customers. 

The dataset :

We will refer to the Transaction dataset throughout this analysis. It;s a collection of financial information from a unknown bank. The dataset deals with over 5,300 bank clients with approximately 68,614 transactions. 

Q1. Find how many records are in the data set?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv';

-- Group each record by itself
tGroup = GROUP transactions ALL;

--Count number of groups
tCount = FOREACH tGroup GENERATE COUNT(transactions);


DUMP tCount;

Q2. Show the top 5 customers with largest total sales?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv'
        USING PigStorage(',') AS (Branch_Number:int, Contract_Number:int,
        Customer_Number:int, Invoice_Date:chararray, Invoice_Number:int,
        Product_Number:int, Sales_Amount:double, Employee_Number:int,
        Service_Date:chararray, System_Period:int);

-- Group all entries by Customer_Number
customer = GROUP transactions BY Customer_Number;

-- Add each sale by Customer_Number
sales = FOREACH customer GENERATE group,SUM(transactions.Sales_Amount) AS totalSales;

-- Rank the sum of sales from largest to smallest
rankedSales = RANK sales BY totalSales DESC;

-- Show only top 5 largest numbers
top5 = FILTER rankedSales BY $0 <= 5;

DUMP top5;

Q3. Count customers who made sales in System_Period 200401,200402 and 200403?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv'
        USING PigStorage(',') AS (Branch_Number:int, Contract_Number:int,
        Customer_Number:int, Invoice_Date:chararray, Invoice_Number:int,
        Product_Number:int, Sales_Amount:double, Employee_Number:int,
        Service_Date:chararray, System_Period:int);

-- Selects transactions that contain 200401, 200402, 200403 from System_Period
sysFilter = FILTER transactions BY System_Period == 200401 OR
        System_Period == 200402 OR System_Period == 200403;

DUMP sysFilter;

Q4. Show top 3 employees (using employee number)who have processed highest average sales?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv'
        USING PigStorage(',') AS (Branch_Number:int, Contract_Number:int,
        Customer_Number:int,Invoice_Date:chararray, Invoice_Number:int,
        Product_Number:int, Sales_Amount:double, Employee_Number:int,
        Service_Date:chararray, System_Period:int);

-- Group each record by Employee_number
employeeGroup = GROUP transactions BY Employee_Number;

-- Average sales by employee number
employeeSales = FOREACH employeeGroup GENERATE group,
        AVG(transactions.Sales_Amount) AS avgSales;

-- Rank average sales
rankedSales = RANK employeeSales BY avgSales DESC;

-- Show top three from rankedSales
top3 = FILTER rankedSales BY $0<=3;

DUMP top3;

Q5. Show how many transactions were made during system periods 20040?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv'
        USING PigStorage(',') AS (Branch_Number:int, Contract_Number:int,
        Customer_Number:int,Invoice_Date:chararray, Invoice_Number:int,
        Product_Number:int, Sales_Amount:double, Employee_Number:int,
        Service_Date:chararray, System_Period:chararray);

-- Filter transactions by column System_Period starting with 20040
sysFilter = FILTER transactions BY STARTSWITH(System_Period, '20040');

-- Group all of sysFilter
sysGroup = GROUP sysFilter ALL;

-- Count entries after being filtered
sysCount = FOREACH sysGroup GENERATE COUNT(sysFilter);

DUMP sysCount;

Q6. Display each unique Sales_Amount by Product_Number?

-- Load data from Transactions.csv
transactions = LOAD '/home/cloudera/datasets/hadoopgyaan/Transactions.csv'
        USING PigStorage(',') AS (Branch_Number:int, Contract_Number:int,
        Customer_Number:int,Invoice_Date:chararray, Invoice_Number:int,
        Product_Number:int, Sales_Amount:double, Employee_Number:int,
        Service_Date:chararray, System_Period:int);

-- Creates an alias for columns Product_Number and Sales_Amount
prices = FOREACH transactions GENERATE $5 as col0, $6 as col1;

-- Groups groups columns
priceGroup = GROUP prices BY (col0,col1);

-- Displays each unique Sales_Amount for each Product_Number
priceFilter = FOREACH priceGroup {
        sort = ORDER prices BY col0 DESC;
        topRec = LIMIT prices 1;
        GENERATE FLATTEN(topRec);
        };

DUMP priceFilter;

Downloads:

1.Sample Input file (Transaction.csv)

I hope this tutorial will surely help you. If you have any questions or problems please let me know.

Happy Hadooping with Patrick..

Apache Hadoop : MovieLens HIVE and PIG Case Study

The MovieLens example

We will use MovieLens dataset for analysis with Pig. The data is available from here. This dataset has been collected by GroupLens Research Project. 

The data set:

The datasets contain movie ratings made by movie goers.It contains three text files: ratings.dat, users.dat and movies.dat.For the sake of completeness, data in the three files is briefly described here.

ratings.dat–>userid::movieid:rating::timestamp

- UserIDs range between 1 and 6040 

- MovieIDs range between 1 and 3952

- Ratings are made on a 5-star scale (whole-star ratings only)

- Timestamp is represented in seconds since the epoch as returned by time(2)

- Each user has at least 20 ratings

users.dat–>userid::gender::age::occupation::zipcode

- Gender is denoted by a "M" for male and "F" for female

- Age is chosen from the following ranges:

*  1:  "Under 18"

* 18:  "18-24"

* 25:  "25-34"

* 35:  "35-44"

* 45:  "45-49"

* 50:  "50-55"

* 56:  "56+"

- Occupation is chosen from the following choices:

*  0:  "other" or not specified

*  1:  "academic/educator"

*  2:  "artist"

*  3:  "clerical/admin"

*  4:  "college/grad student"

*  5:  "customer service"

*  6:  "doctor/health care"

*  7:  "executive/managerial"

*  8:  "farmer"

*  9:  "homemaker"

* 10:  "K-12 student"

* 11:  "lawyer"

* 12:  "programmer"

* 13:  "retired"

* 14:  "sales/marketing"

* 15:  "scientist"

* 16:  "self-employed"

* 17:  "technician/engineer"

* 18:  "tradesman/craftsman"

* 19:  "unemployed"

* 20:  "writer"

movies.dat–>movieID::title::genres

- Genres are pipe-separated and are selected from the following genres:

* Action

* Adventure

* Animation

* Children's

* Comedy

* Crime

* Documentary

* Drama

* Fantasy

* Film-Noir

* Horror

* Musical

* Mystery

* Romance

* Sci-Fi

* Thriller

* War

* Western

Apache Hadoop : Chicago Crime HIVE and PIG Case Study

The Chicago Crime example

Crime Data with HIVE and PIG Using the Chicago Crime data. Here I will answer a few simple questions to illustrate the use of some common big data tools.

The data set :

The data set contains a little over 90 plus records, perhaps not really on the scale of big data, however the tools and code used in this document (HIVE and PIG) will be unchanged if we were to handle this data set with tens of millions of records.

Questions to Answer: 

1. The most frequently occurring primary type (i.e. theft, narcotics etc..) 
2. Districts with the most reported incidents 
3. Blocks with the most reported incidents 
4. Blocks with the most reported incidents, grouped by primary type 
5. A look at the date and time when the highest number of incidents where reported 
6. Arrests by primary type 
7. Arrests by district 
8. A look at the date and time when the highest number of arrests took place.

In each instance we will restrict the reporting in this document to 10 lines of data, simply to preserve space.

The intention at a high level is to use historical data to assist law enforcement in answering, WHAT has been taking place (primary type i.e. narcotics, motor theft etc.), WHERE has it been taking place (district, block etc.), WHEN has it been taking place (month, day, hour). With this information law enforcement could operate in a more effective and efficient manner. In addition when combining this data with additional variables from other data sets/sources, law enforcement could possibly develop predictive models, further improving the effectiveness and efficiency of its operations.

1. The most frequently occurring primary type (i.e. theft, narcotics etc..)?

HIVE QUERY:

SELECT primarytype,

COUNT(*) AS cnt FROM crime GROUP BY primarytype

ORDER BY cnt DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

(its path in which you have store your Chicago crime “csv” file. Path could be change as per your requirement)

crime_grp_type = GROUP crime BY primarytype;

crime_grp_type_cntd = FOREACH crime_grp_type GENERATE COUNT(crime) AS cnt;

srtd = ORDER crime_grp_type_cntd BY cnt;

DUMP srtd; 

RESULT:

2. Districts with the most reported incidents?

HIVE QUERY:

SELECT district,

COUNT(*) AS cntdistrict FROM crime GROUP BY district

ORDER BY cntdistrict DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_grp_dist = GROUP crime BY district;

crime_grp_dist_cntd = FOREACH crime_grp_dist GENERATE COUNT(crime) AS cnt;

 srtd = ORDER crime_grp_dist_cntd BY cnt;

DUMP srtd;

RESULT:

3. Blocks with the most reported incidents?

HIVE QUERY:

SELECT block,

COUNT(*) AS cntblock FROM crime

GROUP BY block

ORDER BY cntblock DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_grp_block = GROUP crime BY block;

 crime_grp_block_cntd = FOREACH crime_grp_block GENERATE COUNT(crime) AS cnt;

 srtd = ORDER crime_grp_block_cntd BY cnt;

 DUMP srtd; 

RESULT: 

4. Blocks with the most reported incidents, grouped by primary type?

 HIVE QUERY:

SELECT block,

primarytype, COUNT(*) AS cntblocktype FROM crime GROUP BY block,

primarytype ORDER BY cntblocktype DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_cogrp_block_type = COGROUP crime BY (block, primarytype);

crime_ cogrp_block_type _cntd = FOREACH crime_ cogrp_block_type GENERATE COUNT(crime) AS cnt;

srtd = ORDER crime_ cogrp_block_type _cntd BY cnt;

DUMP srtd;

RESULT:

5. A look at the date and time when the highest number of incidents where reported?

HIVE QUERY:

SELECT date,

COUNT(*) AS cnt FROM crime

GROUP BY date

ORDER BY cnt DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_grp_date = GROUP crime BY date;

crime_grp_date_cntd = FOREACH crime_grp_date GENERATE COUNT(crime) AS cnt;

srtd = ORDER crime_grp_date_cntd BY cnt;

DUMP srtd;

RESULT:

6. Arrests by primary type?

HIVE QUERY:

SELECT primarytype,

COUNT(*) AS cnt FROM crime WHERE arrest = True

GROUP BY primarytype

ORDER BY cnt DESC

PIG SCRIPT:

crime = LOAD ''/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_filter = FILTER crime BY ( UPPER (arrest) matches '.*TRUE.*' );

crime_grp_type = GROUP crime_filter BY primarytype;

crime_grp_type_cntd = FOREACH crime_grp_type GENERATE COUNT(crime_filter) AS cnt;

srtd = ORDER crime_grp_type_cntd BY cnt;

DUMP srtd; 

RESULT:

7. Arrests by district?

HIVE QUERY:

SELECT district,

COUNT(*) AS cntdistrictarrest FROM crime WHERE arrest = True

GROUP BY district

ORDER BY cntdistrictarrest DESC

PIG SCRIPT:

crime = LOAD '/home/cloudera/Downloads/ chicago _Crimes_2014.csv’

crime_filter = FILTER crime BY ( UPPER (arrest) matches '.*TRUE.*' );

crime_grp_dist = GROUP crime_filter BY district;

crime_grp_dist_cntd = FOREACH crime_grp_dist GENERATE COUNT(crime_filter) AS cnt;

srtd = ORDER crime_grp_dist_cntd BY cnt;

DUMP srtd; 

RESULT:

8. A look at the date and time when the highest number of arrests took place?

HIVE QUERY:

SELECT date,

COUNT(*) AS cnt_arrest FROM crime WHERE arrest = True

GROUP BY date

ORDER BY cnt_arrest DESC