Use REPLACE in multicondition RegExp in Pig Latin

user_2857611 0
Viewed 65

I have this set of data:

dump data;

This is a sample output: (this dataset is almost a million rows long).

("0",60,0,1,"Fri")
("1",47,0,1,"Mon")
("1",23,1,0,"Tue")
("1",60,0,0,"Sat")
("1",50,1,1,"Fri")

I want to replace the values: Sat, Fri, Mon to numbers of week, I know how to use REPLACE for change just 1 value at a time, but I have to repeat it multiple times in order to change all days of the week:

data_day_of_week = FOREACH data GENERATE $0,$1,$2,$3,REPLACE($4, 'Mon', '1');

Is there any way to do this in only one statement?

apache-pig hadoop
4 Answers

You could combine the Pig ToDate and ToString functions:

data_day_of_week = FOREACH data GENERATE $0,$1,$2,$3,
    ToString(ToDate($4, 'EEE'), 'e') as day_of_week;

The ToDate functions will convert the chararray day of the week to a Pig date time format. Then ToString will convert this to a format of your choosing.

According to the Java docs a single e or c should give the numeric format for the day of the week, where Monday is 1.

user_149277290

You should use a CASE WHEN THEN statement

data_day_of_week = FOREACH data GENERATE
    CASE
         WHEN $4 == 'Mon' THEN '1'
         WHEN $4 == 'Tue' THEN '2'
         ...
         WHEN $4 == 'Sun' THEN '7'
    END AS day_number;

You should also name your relations so not to use $1, $2 etc. If you name $4 as day_number then when you declare the variable as day_number from the CASE statement it'll "overwrite" your prior data.

user_32447700

You can use JOIN with a reference to a mapping like this:

(Mon,1)
(Tue,2)
(Wed,3)
(Thu,4)
(Fri,5)
(Sat,6)
(Sun,7)

Join statement:

outer_left = join your_data by $4 left outer, day_mapping by day;
user_112188910

saph_top, was the closer one to answer my question, nonetheless after testing it was resolving blank output, I'm going to complement his answer:

'Mon' is not the same as "Mon", therefore when I was using: CASE WHEN $4 == 'Mon' THEN '1' It wasn´t replacing anything, resulting in blank result in: data_day_of_week.

To solve this, I just add " " (double quotes to the condition):

data_day_of_week = FOREACH data GENERATE
CASE
     WHEN $4 == '"Mon"' THEN '1'
     WHEN $4 == '"Tue"' THEN '2'
     ...
     WHEN $4 == '"Sun"' THEN '7'
END AS day_number;

After that in order to rebuild the data I add the following to GENERATE Clause:

data_day_of_week = FOREACH data GENERATE $0,$1,$2,$3,
    CASE
         WHEN $4 == '"Mon"' THEN '1'
         WHEN $4 == '"Tue"' THEN '2'
         ...
         WHEN $4 == '"Sun"' THEN '7'
    END AS day_number;

And the output was complete now: dump data_day_of_week;

("0",60,0,1,5)
("1",47,0,1,1)
("1",23,1,0,2)
("1",60,0,0,6)
("1",50,1,1,5)
user_28576110
Related