Can I generate nested bags using nested FOREACH statements in Pig Latin?

Question

Let's say I have a data set of restaurant reviews:

User,City,Restaurant,Rating
Jim,New York,Mecurials,3
Jim,New York,Whapme,4.5
Jim,London,Pint Size,2
Lisa,London,Pint Size,4
Lisa,London,Rabbit Whole,3.5

And I want to produce a list by user and city of average review. I.e. output:

User,City,AverageRating
Jim,New York,3.75
Jim,London,2
Lisa,London,3.75

I could write a Pig script as follows:

Data = LOAD 'data.txt' USING PigStorage(',') AS (
    user:chararray, city:chararray, restaurant:charray, rating:float
);

PerUserCity = GROUP Data BY (user, city);

ResultSet = FOREACH PerUserCity {
    GENERATE group.user, group.city, AVG(Data.rating);
}

However I'm curious whether I can first group the higher level group (the users) and then sub group the next level (the cities) later: i.e.

PerUser = GROUP Data BY user;

Intermediate = FOREACH PerUser {
    B = GROUP Data BY city;
    GENERATE group AS user, B;
}

I get:

Error during parsing.
Invalid alias: GROUP in {
  group: chararray,
  Data: {
    user: chararray,
    city: chararray,
    restaurant: chararray,
    rating: float
  }
}

Has anyone tried this with success? Is it simply not possible to GROUP within a FOREACH?

My goal is to do something like:

ResultSet = FOREACH PerUser {
    FOREACH City {
        GENERATE user, city, AVG(City.rating)
    }
}

score 9 · Answer 1 · answered Feb 11 '11 at 18:17

9

Currently the allowed operations are DISTINCT, FILTER, LIMIT, and ORDER BY inside a FOREACH.

For now grouping directly by (user, city) is the good way to do as you said.

answered Feb 11 '11 at 18:17

Romain

7,022
3
30
30

supyo · Answer 2 · 2013-01-16T02:09:43.497

2

Release notes for Pig version 0.10 suggest that nested FOREACH operations are now supported.

edited Jan 16 '13 at 02:09

answered Dec 27 '12 at 15:45

supyo

3,017
2
20
35

Thank you. Why are two GENERATEs required in the inner block? – PP. Jan 15 '13 at 15:07
Retracting my suggestion. Release notes suggest this can be done, but I can't get it working. – supyo Jan 16 '13 at 02:08

score 1 · Answer 3 · edited May 18 '14 at 19:10

1

Try this:

Records = load 'data_rating.txt' using PigStorage(',') as (user:chararray, city:chararray, restaurant:chararray, rating:float);
grpRecs = group Records By (user,city);
avgRating_Byuser_perCity = foreach grpRecs generate AVG(Records.rating) as average; 
Result = foreach avgRating_Byuser_perCity generate flatten(group), average;

edited May 18 '14 at 19:10

Artjom B.

61,146
24
125
222

answered May 18 '14 at 18:37

Raja Gopala Chary G

21
2

2

You should add a description what this code accomplishes and how it does this. – Artjom B. May 18 '14 at 19:08
This is wrong... It should be Records = load 'data_rating.txt' using PigStorage(',') as (user:chararray, city:chararray, restaurant:chararray, rating:float); grpRecs = group Records By (user,city); avgRating_Byuser_perCity = foreach grpRecs generate flatten(group), AVG(Records.rating) as average; Result = dump avgRating_Byuser_perCity ; – Kumar Sep 09 '15 at 12:55

score 0 · Answer 4 · answered Apr 29 '14 at 07:20

awdata = load 'data' using PigStorage(',') as (user:chararray , city:chararray , restaurant:chararray , rating:float);
data = filter rawdata by user != 'User';

groupbyusercity = group data by (user,city);

--describe groupbyusercity;
--groupbyusercity: {group: (user: chararray,city: chararray),data: {(user: chararray,city: chararray,restaurant: chararray,rating: float)}}

average = foreach groupbyusercity {
    generate group.user,group.city,AVG(data.rating);
}

dump average;

score 0 · Answer 5 · answered May 21 '14 at 11:07

Grouping by two keys and then flattening the structure leads to the same result:

Loading Data like you did

Data = LOAD 'data.txt' USING PigStorage(',') AS (
    user:chararray, city:chararray, restaurant:charray, rating:float);

Group by user and city

 ByUserByCity = GROUP Data BY (user, city);

Add Rating average of the groups (you can add more, like COUNT(Data) as count_res) Then flatten the group structure to the original one.

ByUserByCityAvg = FOREACH ByUserByCity GENERATE
FLATTEN(group) AS (user, city),
AVG(Data.rating) as user_city_avg;

Results in:

Jim,London,2.0
Jim,New York,3.75
Lisa,London,3.75
User,City,

I guess, this does not answer the question – Kumar Sep 09 '15 at 12:54 — Kumar, Sep 09 '15 at 12:54

Can I generate nested bags using nested FOREACH statements in Pig Latin?

5 Answers5