Extract timestamp from filename and add it in new column(say,date) by using Pig

[#1]

I have a file with name YYYYMMDD_claims_portal.csv, i need only YYYYMMDD part and store this value in new column(say,date). Earlier we have 3 column like Claim,User,ID. now i need to add one more column date having value as YYYYMMDD as per file. Please help, its bit urgent.

Thanks in advance for any help you guys can provide.

[#2 · Accepted solution]

@Sumee singh

Please try this:

A = LOAD 'YYYYMMDD_claims_portal.csv' using PigStorage(',','-tagFile');

y = FOREACH A GENERATE SUBSTRING($0,0,8),$1..;

DUMP y;

(Input file name comes as the first field in tuple). You can modify after this as you wish.

[#3]

@tsharma

Thanks for your prompt reply..i'll try this approach but by-tagFile we tagged file name with all the column name, here what i want is to create a new column like date and store the file name in it..

Thank you.

[#4]

Ok, do this:-

A = LOAD 'YYYYMMDD_claims_portal.csv' using PigStorage(',','-tagFile') AS (filename:chararray, {other columns as per your schema})

y = FOREACH A GENERATE $1..,SUBSTRING(filename,0,8) AS day;

describe y;

DUMP y;

[#5]

Thanks @tsharma.. This works.. Thank you 🙂