CODEWITHSUNDEEP
hadoopgroup-byapache-pig
Pedro_Rodgers
Pedro_Rodgers

Reputation: 15

Apache PIG - Count(*) Group by ID and Creation of a Ranking

​Hi experts,

I've this dataset:

Field_A Field_B    DATE
John     1       01-01-2016
John     1       05-01-2016
Cate     1       05-01-2016
Cate     4       01-01-2016
Cate     6       05-01-2016
Perdi    4       01-01-2016

And I'm trying to calculate the Count(*) of each Field_A and create a rank based on Field A and Date. Basically I want to return this:

Field_A Count   Rank    Field_B
John      2      1        1
John      2      2        1
Cate      3      3        1
Cate      3      4        4
Cate      3      3        6
Perdi     1      5        4

For that I'm trying with this code:

DATA  = load '...'
AS
          (Field_A:Int, 
          FIELD_B:Int,
          DATE:CHARARRAY);
A = rank DATA BY Field_A;
B = GROUP A BY $0;
C = foreach B {
     CNT = COUNT(A.Field_A);
     generate $0, CNT;
}
D = join A by $0, C by $0;
E = rank D BY DATE,Field_A DENSE;
F = foreach E generate $0 AS RANK,Field_A,CNT;
DUMP F;

But I'm getting the following error:

<file script.pig, line 35, column 69> Invalid field projection. Projected field [CNT] does not exist in schema;

How can I solve this?

Many thanks!

Upvotes: 1

Views: 1671

Answers (2)

Ravinder Karra
Ravinder Karra

Reputation: 307

Changed Field_A to CHARARRAY and used '\t' file, I am not impressed with below solution to many statements , but it works, 

A = LOAD '/user/root/datex.txt' USING PigStorage('\t') AS (Field_A:CHARARRAY, FIELD_B:Int,DATE:CHARARRAY);
B = FOREACH A GENERATE  Field_A, FIELD_B, ToDate(DATE,'MM-dd-yyyy') as Datex;
D = GROUP B by Field_A;
E = FOREACH D GENERATE group , COUNT(B.Field_A) ;
F = join E by $0, B by Field_A;
G = FOREACH F GENERATE $0,$1,$3,$4
H = rank G by $0, $3 ;
Last = FOREACH H GENERATE $1 as FIELD_A, $2 as CNT, $0 as Rank , $3 AS FIELD_B;
DUMP

Upvotes: 0

Alexey
Alexey

Reputation: 2478

C = foreach B {
     generate group as Field_A, COUNT(A) as CNT;
}

Upvotes: 1

Related Questions