Brief Exercises with Pig Latin

With Hortonworks Sandbox 2.6 we’ll have a brief exercise using Pig to take a look at movie dataset.

So a movie dataset can be downloaded from http://files.grouplens.org/datasets/movielens/ml-100k.zip. It includes After extracting the zip file, we’ll need u.data and u.item two files.

u.data – There are 100,000 ratings by 943 users on 1682 movies. This is a comma separated list of user id | item id | rating | timestamp. (The time stamps are unix seconds since 1/1/1970 UTC)

Sample data from file u.data: 196,242,3,881250949 # user #196 rated movie #242 with score 3 at time of 881250949 186,302,3,891717742 22,377,1,878887116

u.item – Information about the items (movies); this is a tab separated list of “movie id

movie title

release date

…” The fields after “release date” are not related to our task so we ignore them for now.

Sample data from file u.item: 1|Toy Story (1995)|01-Jan-1995|… 2|GoldenEye (1995)|01-Jan-1995|… 3|Four Rooms (1995)|01-Jan-1995|…

So the question we’re going to attack is “what are the top 20 best rated latest movies?”

Following are Pig Latin script to do so:

ratings = LOAD '/user/maria_dev/ml-100k/u.data' 
          USING PigStorage(',') 
          AS (userID:int, movieID:int, rating:int, ratingTime:int);

metadata = LOAD '/user/maria_dev/ml-100k/u.item' 
           USING PigStorage('|')
           AS (movieID:int, movieTitle:chararray, releaseDate:chararray,
               videoRelease:chararray, imdbLink:chararray);
               
nameLookup = FOREACH metadata GENERATE movieID, movieTitle, 
                 ToUnixTime(ToDate(releaseDate, 'dd-MMM-yyyy')) AS releaseTime;
                 
ratingByMovie = GROUP ratings BY movieID;

avgRatings = FOREACH ratingByMovie GENERATE group as movieID, 
                                           AVG(ratings.rating) AS avgRating;

fiveStarMovies = FILTER avgRatings BY avgRating > 4.5;

fiveStarMoviesWithData = JOIN fiveStarMovies BY movieID, nameLookup BY movieID;

oldestFiveStarMovies = ORDER fiveStarMoviesWithData BY nameLookup::releaseTime DESC;

first20 = LIMIT oldestFiveStarMovies 20;

DUMP first20;

Run this script in Pig View in Ambari we’ll get the latest top 20 movie list that are best rated. The latest movie in this small dataset is dated to April 1998. good

Next, based on this script, we can also find what movies are most rated by using COUNT(ratings.rating).
It could be interesting to find most rated movie with lowest ratings.

We can do that with following Pig Latin script:

ratings = LOAD '/user/maria_dev/ml-100k/u.data' 
          USING PigStorage(',') 
          AS (userID:int, movieID:int, rating:int, ratingTime:int);

metadata = LOAD '/user/maria_dev/ml-100k/u.item' 
           USING PigStorage('|')
           AS (movieID:int, movieTitle:chararray, releaseDate:chararray,
               videoRelease:chararray, imdbLink:chararray);
               
nameLookup = FOREACH metadata GENERATE movieID, movieTitle, 
                 ToUnixTime(ToDate(releaseDate, 'dd-MMM-yyyy')) AS releaseTime;
                 
ratingByMovie = GROUP ratings BY movieID;

countRatings = FOREACH ratingByMovie GENERATE group as movieID, 
                       AVG(ratings.rating) AS avgRating, COUNT(ratings.rating) AS numRatings;

lowestRatingMovies = FILTER countRatings BY avgRating < 2.0;

lowestRatingMoviesWithData = JOIN lowestRatingMovies BY movieID, nameLookup BY movieID;

mostRatedLowestRatingMovies = FOREACH lowestRatingMoviesWithData GENERATE nameLookup::movieTitle AS movieName,
                                      lowestRatingMovies::avgRating AS avgRating, lowestRatingMovies::numRatings AS numRatings;

mostRatedLowestRatingMoviesSorted = ORDER mostRatedLowestRatingMovies BY numRatings DESC;

first20 = LIMIT mostRatedLowestRatingMoviesSorted 20;

DUMP first20;

Run above script in Ambari: bad

This is an exercise in Udemy course “the-ultimate-hands-on-hadoop-tame-your-big-data”.

Written on January 31, 2018