YouTube Data Analysis Part-1

In this article, We will see how to Analyze YouTube Data using Big Data Technology, We will see step by step process execution of the project.

YouTube is an American online video-sharing platform headquartered in San Bruno, California. Three former PayPal employees—Chad Hurley, Steve Chen, and Jawed Karim created the service in February 2005.

Problem Statement: Problem Statement is to

1) Find out the top 5 categories in which the most number of videos are uploaded.

2) Find top 10 rated videos,

3) Find top 10 most viewed videos

4) Find top 10 rated videos in each category

5) Find top 10 most viewed videos in each category

Attribute Information or Dataset Details:

entries per page

Search:

Column Name	Descriptions
video ID	an 11-digit string, which is unique
uploader	a string of the video uploader's username
age	an integer number of days between the date when the video was uploaded and Feb.15, 2007 (YouTube's establishment)
category	a string of the video category chosen by the uploader
length	an integer number of the video length
views	an integer number of the views
rate	a float number of the video rate
ratings	an integer number of the ratings
comments	an integer number of the comments
related IDs	up to 20 strings of the related video IDs

Showing 1 to 10 of 10 entries

Data: Input Format – TAB Separated Values File (tsv file)

Public DATASET available at below website
http://netsg.cs.sfu.ca/youtubedata/0222.zip

Technology Used

Apache Hadoop
Apache Pig
Bigdata
Linux
Shell Script

Processing Logic – Youtube Data Analysis in Hadoop Eco-System.

Apache Pig Script will perform the following

1) Find out the top 5 categories in which the most number of videos are uploaded.
2) Find top 10 rated videos,
3) Find top 10 most viewed videos
4) Find top 10 rated videos in each category
5) Find top 10 most viewed videos in each category

Apache Pig Code (Youtube_data_analysis.pig)

infiles = load '/hdfs/bhavesh/Youtube_POC/Youtube/0222/{0,1,2,3,4}.txt' using PigStorage('\t') as 
(videoid:chararray,uploader:chararray,age:int,category:chararray,length:int,views:int,rate:int,rating:int,comments:int,related_id:chararray);
files = FILTER infiles BY category is not null;
grpn_for_catagories = group files by category;
cnt_for_catagories = foreach grpn_for_catagories generate group, COUNT(files.videoid) as counting;
sorted_for_catagories_desc = order cnt_for_catagories by counting desc;
top5_for_catagories = limit sorted_for_catagories_desc 5;
STORE top5_for_catagories INTO  '/hdfs/bhavesh/Youtube_POC/Top5Catagories' using PigStorage(',');
order_rated_video = order files by rating desc;
top10_rated_video = limit order_rated_video 10;
final_top10_rated_video = foreach top10_rated_video generate $0,$3,$7;
STORE final_top10_rated_video INTO '/hdfs/bhavesh/Youtube_POC/Top10Rated' using PigStorage(',');
order_viewed_video = order files by views desc;
top10_viewed_video = limit order_viewed_video 10;
final_top10_viewed_video = foreach top10_viewed_video generate $0,$3,$5;
STORE final_top10_viewed_video INTO '/hdfs/bhavesh/Youtube_POC/Top10Viewed' using PigStorage(',');
top10_rated_catagories = foreach grpn_for_catagories{
                           sorted = order files by rating desc;
                           top10 = limit sorted 10;
                           generate flatten(top10);
};
top10_rated_by_catagories = foreach top10_rated_catagories generate $0,$3,$7;
STORE top10_rated_by_catagories INTO '/hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories' using PigStorage(',');
top10_viewed_catagories = foreach grpn_for_catagories{
                           sorted = order files by views desc;
                           top10 = limit sorted 10;
                           generate flatten(top10);
};
top10_viewed_by_catagories = foreach top10_viewed_catagories generate $0,$3,$5;
STORE top10_viewed_by_catagories INTO '/hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories' using PigStorage(',');

Pig
x
 
infiles = load '/hdfs/bhavesh/Youtube_POC/Youtube/0222/{0,1,2,3,4}.txt' using PigStorage('\t') as 
(videoid:chararray,uploader:chararray,age:int,category:chararray,length:int,views:int,rate:int,rating:int,comments:int,related_id:chararray);
files = FILTER infiles BY category is not null;
grpn_for_catagories = group files by category;
cnt_for_catagories = foreach grpn_for_catagories generate group, COUNT(files.videoid) as counting;
sorted_for_catagories_desc = order cnt_for_catagories by counting desc;
top5_for_catagories = limit sorted_for_catagories_desc 5;
STORE top5_for_catagories INTO  '/hdfs/bhavesh/Youtube_POC/Top5Catagories' using PigStorage(',');
order_rated_video = order files by rating desc;
top10_rated_video = limit order_rated_video 10;
final_top10_rated_video = foreach top10_rated_video generate $0,$3,$7;
STORE final_top10_rated_video INTO '/hdfs/bhavesh/Youtube_POC/Top10Rated' using PigStorage(',');
order_viewed_video = order files by views desc;
top10_viewed_video = limit order_viewed_video 10;
final_top10_viewed_video = foreach top10_viewed_video generate $0,$3,$5;
STORE final_top10_viewed_video INTO '/hdfs/bhavesh/Youtube_POC/Top10Viewed' using PigStorage(',');
top10_rated_catagories = foreach grpn_for_catagories{
                           sorted = order files by rating desc;
                           top10 = limit sorted 10;
                           generate flatten(top10);
};
top10_rated_by_catagories = foreach top10_rated_catagories generate $0,$3,$7;
STORE top10_rated_by_catagories INTO '/hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories' using PigStorage(',');
top10_viewed_catagories = foreach grpn_for_catagories{
                           sorted = order files by views desc;
                           top10 = limit sorted 10;
                           generate flatten(top10);
};
top10_viewed_by_catagories = foreach top10_viewed_catagories generate $0,$3,$5;
STORE top10_viewed_by_catagories INTO '/hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories' using PigStorage(',');

Shell Script will perform the following

Purpose of this shell script is to perform clean-up (delete existing output files) and execute the Pig Script and Hive Commands to store the resultant in Hive Tables and Store result in file (CSV format).

Shell Script Code (Youtube_data_analysis.sh)

sudo rm -rf /var/lib/hive/metastore/metastore_db/*.lck
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top5Catagories.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Rated.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10RatedByCatagories.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Viewed.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10ViewedByCatagories.csv
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top5Catagories
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10Rated
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10Viewed
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories
pig /home/bhavesh/Youtube_POC/Youtube/0222/Youtube_data_analysis.pig
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top5Catagories/part-r-00000               /home/mrinmoy/Downloads/POC/YoutubePOC/Top5Catagories.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10Rated/part-r-00000                   /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Rated.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories/part-r-00000       /home/mrinmoy/Downloads/POC/YoutubePOC/Top10RatedByCatagories.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10Viewed/part-r-00000                  /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Viewed.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories/part-r-00000      /home/mrinmoy/Downloads/POC/YoutubePOC/Top10ViewedByCatagories.csv
hive -e 'drop table if exists Top5CatagoriesTable';
hive -e 'drop table if exists Top10RatedTable';
hive -e 'drop table if exists Top10RatedByCatagoriesTable';
hive -e 'drop table if exists Top10ViewedTable';
hive -e 'drop table if exists Top10ViewedByCatagoriesTable';
hive -e "create external table Top5CatagoriesTable(Top5Catagory string, VideoCount int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top5Catagories'";
hive -e "create external table Top10RatedTable(Videoid string,Catagory string,Rating int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10RatedTable'";
hive -e "create external table Top10RatedByCatagoriesTable(Videoid string,Catagory string,Rating int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10RatedByCatagoriesTable'";
hive -e "create external table Top10ViewedTable(Videoid string,Catagory string,Viewed_count int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10ViewedTable'";
hive -e "create external table Top10ViewedByCatagoriesTable(Videoid string,Catagory string,Viewed_count int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10ViewedByCatagoriesTable'";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top5Catagories/part-r-00000' overwrite into table Top5CatagoriesTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10Rated/part-r-00000' overwrite into table Top10RatedTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories/part-r-00000' overwrite into table Top10RatedByCatagoriesTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10Viewed/part-r-00000' overwrite into table Top10ViewedTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories/part-r-00000' overwrite into table Top10ViewedByCatagoriesTable";

Shell
 
sudo rm -rf /var/lib/hive/metastore/metastore_db/*.lck
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top5Catagories.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Rated.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10RatedByCatagories.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Viewed.csv
rm /home/mrinmoy/Downloads/POC/YoutubePOC/Top10ViewedByCatagories.csv
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top5Catagories
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10Rated
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10Viewed
hadoop fs -rmr /hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories
pig /home/bhavesh/Youtube_POC/Youtube/0222/Youtube_data_analysis.pig
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top5Catagories/part-r-00000               /home/mrinmoy/Downloads/POC/YoutubePOC/Top5Catagories.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10Rated/part-r-00000                   /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Rated.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories/part-r-00000       /home/mrinmoy/Downloads/POC/YoutubePOC/Top10RatedByCatagories.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10Viewed/part-r-00000                  /home/mrinmoy/Downloads/POC/YoutubePOC/Top10Viewed.csv
hadoop fs -get /hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories/part-r-00000      /home/mrinmoy/Downloads/POC/YoutubePOC/Top10ViewedByCatagories.csv
hive -e 'drop table if exists Top5CatagoriesTable';
hive -e 'drop table if exists Top10RatedTable';
hive -e 'drop table if exists Top10RatedByCatagoriesTable';
hive -e 'drop table if exists Top10ViewedTable';
hive -e 'drop table if exists Top10ViewedByCatagoriesTable';
hive -e "create external table Top5CatagoriesTable(Top5Catagory string, VideoCount int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top5Catagories'";
hive -e "create external table Top10RatedTable(Videoid string,Catagory string,Rating int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10RatedTable'";
hive -e "create external table Top10RatedByCatagoriesTable(Videoid string,Catagory string,Rating int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10RatedByCatagoriesTable'";
hive -e "create external table Top10ViewedTable(Videoid string,Catagory string,Viewed_count int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10ViewedTable'";
hive -e "create external table Top10ViewedByCatagoriesTable(Videoid string,Catagory string,Viewed_count int) row format delimited fields terminated by',' lines terminated by '\n' stored as textfile location '/hdfs/bhavesh/Youtube_POC/hive/Top10ViewedByCatagoriesTable'";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top5Catagories/part-r-00000' overwrite into table Top5CatagoriesTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10Rated/part-r-00000' overwrite into table Top10RatedTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10RatedByCatagories/part-r-00000' overwrite into table Top10RatedByCatagoriesTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10Viewed/part-r-00000' overwrite into table Top10ViewedTable";
hive -e "load data inpath '/hdfs/bhavesh/Youtube_POC/Top10ViewedByCatagories/part-r-00000' overwrite into table Top10ViewedByCatagoriesTable";