How to get serial number in Pig Script based on column?

Question

Currently My data is coming in this way but i want my data to show RANK with respect to pid fields changing sequence.My script is this.I have tried rank operator and dense rank operator but still no desired output.

trans_c1 = LOAD '/mypath/data_file.csv' using PigStorage(',') as (date,Product_id);  



    (DATE,Product id)
    (2015-01-13T18:00:40.622+05:30,B00XT)
    (2015-01-13T18:00:40.622+05:30,B00XT)
    (2015-01-13T18:00:40.622+05:30,B00XT)
    (2015-01-13T18:00:40.622+05:30,B00XT)
    (2015-01-13T18:00:40.622+05:30,B00OZ)
    (2015-01-13T18:00:40.622+05:30,B00OZ)
    (2015-01-13T18:00:40.622+05:30,B00OZ)
    (2015-01-13T18:00:40.622+05:30,B00VB)
    (2015-01-13T18:00:40.622+05:30,B00VB)
    (2015-01-13T18:00:40.622+05:30,B00VB)
    (2015-01-13T18:00:40.622+05:30,B00VB)

The final output should look like this where the rank sequence changes with the change in (Product_id) and resets by 1.Is it possible in pig to do that?

    (1,2015-01-13T18:00:40.622+05:30,B00XT)
    (2,2015-01-13T18:00:40.622+05:30,B00XT)
    (3,2015-01-13T18:00:40.622+05:30,B00XT)
    (4,2015-01-13T18:00:40.622+05:30,B00XT)
    (1,2015-01-13T18:00:40.622+05:30,B00OZ)
    (2,2015-01-13T18:00:40.622+05:30,B00OZ)
    (3,2015-01-13T18:00:40.622+05:30,B00OZ)
    (1,2015-01-13T18:00:40.622+05:30,B00VB)
    (2,2015-01-13T18:00:40.622+05:30,B00VB)
    (3,2015-01-13T18:00:40.622+05:30,B00VB)
    (4,2015-01-13T18:00:40.622+05:30,B00VB)

score 1 · Answer 1 · edited May 23 '17 at 11:51

This question can be solved by using piggybank functions Stitch and Over. It can also be solved by using dataFu's Enumerate function.

Script using Piggybank functions:

REGISTER <path to piggybank folder>/piggybank.jar;
DEFINE Stitch org.apache.pig.piggybank.evaluation.Stitch;
DEFINE Over org.apache.pig.piggybank.evaluation.Over('int');
input_data = LOAD 'data_file.csv' USING PigStorage(',') AS (date:chararray, pid:chararray);
group_data = GROUP input_data BY pid;
rank_grouped_data = FOREACH group_data GENERATE FLATTEN(Stitch(input_data, Over(input_data, 'row_number')));
display_data = FOREACH rank_grouped_data GENERATE stitched::result AS rank_number, stitched::date AS date, stitched::pid AS pid;
DUMP display_data;

Script using dataFu's Enumerate function:

REGISTER <path to pig libraries>/datafu-1.2.0.jar;
DEFINE Enumerate datafu.pig.bags.Enumerate('1');
input_data = LOAD 'data_file.csv' USING PigStorage(',') AS (date:chararray, pid:chararray);
group_data = GROUP input_data BY pid;
data = FOREACH group_data GENERATE FLATTEN(Enumerate(input_data));
display_data = FOREACH data GENERATE $2, $0, $1;
DUMP display_data;

DataFu jar file can be downloaded from Maven repository: http://search.maven.org/#search%7Cga%7C1%7Cg%3a%22com.linkedin.datafu%22

Output:

(1,2015-01-13T18:00:40.622+05:30,B00OZ)
(2,2015-01-13T18:00:40.622+05:30,B00OZ)
(3,2015-01-13T18:00:40.622+05:30,B00OZ)
(1,2015-01-13T18:00:40.622+05:30,B00VB)
(2,2015-01-13T18:00:40.622+05:30,B00VB)
(3,2015-01-13T18:00:40.622+05:30,B00VB)
(4,2015-01-13T18:00:40.622+05:30,B00VB)
(1,2015-01-13T18:00:40.622+05:30,B00XT)
(2,2015-01-13T18:00:40.622+05:30,B00XT)
(3,2015-01-13T18:00:40.622+05:30,B00XT)
(4,2015-01-13T18:00:40.622+05:30,B00XT)

Ref:
Implementing row number function in apache pig
Usage of Apache Pig rank function

How to get serial number in Pig Script based on column?

1 Answers1