CODEWITHSUNDEEP
substringapache-pig
JAY3
JAY3

Reputation: 35

Generate a count of a substring (Apache Pig)

I am trying to count a specific part of a substring. My A is correct, but I am having trouble with B to work right. I included the lab's comments to help explain certain bits of the code.

data = LOAD '/dualcore/orders' AS (order_id:int,
         cust_id:int,
         order_dtm:chararray);

 /*
  * Include only records where the 'order_dtm' field matches
  * the regular expression pattern:
  *
  *   ^       = beginning of string
  *   2013    = literal value '2013'
  *   0[2345] = 0 followed by 2, 3, 4, or 5
  *   -       = a literal character '-'
  *   \\d{2}  = exactly two digits
  *   \\s     = a single whitespace character
  *   .*      = any number of any characters
  *   $       = end of string
  *
  * If you are not familiar with regular expressions and would
  * like to know more about them, see the Regular Expression 
  * Reference at the end of the Exercise Manual.
  */
 recent = FILTER data by order_dtm matches '^2013-0[2345]-\\d{2}\\s.*$';

 -- TODO (A): Create a new relation with just the order's year and month
 A = FOREACH data GENERATE SUBSTRING(order_dtm,0,7);

 -- TODO (B): Count the number of orders in each month
 B = FOREACH data GENERATE COUNT_STAR(A);

 -- TODO (C): Display the count by month to the screen.
 DUMP C;'

Upvotes: 1

Views: 878

Answers (1)

Sivasakthi Jayaraman
Sivasakthi Jayaraman

Reputation: 4724

You can solve this problem in two ways.

Option1: Using SUBSTRING as you mentioned

input

1       100     2013-02-15 test
2       100     2013-04-20 test1
1       101     2013-02-14 test2
1       101     2014-02-27 test3

PigScript:

data = LOAD 'input' AS (order_id:int,cust_id:int,order_dtm:chararray);
recent = FILTER data by order_dtm matches '^2013-0[2345]-\\d{2}\\s.*$';
A = FOREACH recent GENERATE order_id,cust_id,SUBSTRING(order_dtm,0,4) AS year,SUBSTRING(order_dtm,5,7) AS month;
B = GROUP A BY month;
C = FOREACH B GENERATE group AS month,FLATTEN(A.year) AS year,COUNT(A) AS cnt;
DUMP C;

Output:

(02,2013,2)
(02,2013,2)
(04,2013,1)

Option2: Using REGEX function

data = LOAD 'input' AS(order_id:int,cust_id:int,order_dtm:chararray);
A = FOREACH data GENERATE order_id,cust_id,FLATTEN(REGEX_EXTRACT_ALL(order_dtm,'^(2013)-(0[2345])-\\d{2}\\s.*$')) AS (year,month);
B = FILTER A BY month IS NOT NULL;
C = GROUP B BY month;
D = FOREACH C GENERATE group AS month,FLATTEN(B.year) AS year,COUNT(B) AS cnt;
DUMP D;

Output:

(02,2013,2)
(02,2013,2)
(04,2013,1)

In both the cases i have included year also in the final output, in case if you don't want then remove FLATTEN(year) from the script.

Upvotes: 2

Related Questions