question Re: Extract timestamp from filename and add it in new column(say,date) by using Pig in Archives of Support Questions (Read Only)

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

Sume77 — Wed, 20 Sep 2017 21:39:49 GMT

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.

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

tsharma — Wed, 20 Sep 2017 21:59:26 GMT

@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.

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

Sume77 — Thu, 21 Sep 2017 08:43:20 GMT

@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.

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

tsharma — Thu, 21 Sep 2017 13:59:35 GMT

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;

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

Sume77 — Sun, 24 Sep 2017 11:12:21 GMT

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