CODEWITHSUNDEEP
hadoopmapreduceapache-pighdfs
ravi theja
ravi theja

Reputation: 43

Loading datetime format files using PIG

I have a dataset in the following way.

ravi,savings,avinash,2,char,33,F,22,44,12,13,33,44,22,11,10,22,2006-01-23
avinash,current,sandeep,3,char,44,M,33,11,10,12,33,22,39,12,23,19,2001-02-12
supreeth,savings,prabhash,4,char,55,F,22,12,23,12,44,56,7,88,34,23,1995-03-11
lavi,current,nirmesh,5,char,33,M,11,10,33,34,56,78,54,23,445,66,1999-06-15
Venkat,savings,bunny,6,char,11,F,99,12,34,55,33,23,45,66,23,23,2016-05-18

the last column(example:2006-01-23) is date. I am trying to load the above data with following command using PIG. Following is the code which i am using to load the file.

file = LOAD 'FI_USER_CREDS_TBL_T.txt' 
       USING PigStorage(',') AS (USER_ID:chararray,
                                 ROLE_ID:chararray,
                                 USER_PW:chararray,
                                 NUM_PWD_HISTORY:int,
                                 PWD_HISTORY:chararray,                                     
                                 PWD_LAST_MOD_TIME:int,
                                 NUM_PWD_ATTEMPTS:int,
                                 NEW_USER_FLG:chararray,
                                 LOGIN_TIME_LOW:int,
                                 LOGIN_TIME_HIGH:int,
                                 DISABLED_FROM_DATE:int,
                                 DISABLED_UPTO_DATE:int,
                                 PW_EXPY_DATE:int,
                                 ACCT_EXPY_DATE:int,
                                 ACCT_INACTIVE_DAYS:int,                                     
                                 LAST_ACCESS_TIME:int,
                                 TS_CNT:int,
                                 DTL__CAPXTIMESTAMP:int,
                                 ETL_INSERT_DATE:datetime);

But it is not reading the date column instead it is giving following output after using dump file command.

(ravi,savings,avinash,2,char,33,,22,44,12,13,33,44,22,11,10,22,,)
(avinash,current,sandeep,3,char,44,,33,11,10,12,33,22,39,12,23,19,,)
(supreeth,savings,prabhash,4,char,55,,22,12,23,12,44,56,7,88,34,23,,)
(lavi,current,nirmesh,5,char,33,,11,10,33,34,56,78,54,23,445,66,,)
(Venkat,savings,bunny,6,char,11,,99,12,34,55,33,23,45,66,23,23,,)

how can i read the date column.

Kindly help me in this regard.

Thank-you.

Upvotes: 0

Views: 478

Answers (2)

Amrutha K
Amrutha K

Reputation: 204

Load data as chararray.

file = LOAD 'FI_USER_CREDS_TBL_T.txt' USING PigStorage(',') AS (USER_ID:chararray,ROLE_ID:chararray,USER_PW:chararray,NUM_PWD_HISTORY:int,PWD_HISTORY:chararray,PWD_LAST_MOD_TIME:int,NUM_PWD_ATTEMPTS:int,NEW_USER_FLG:chararray,LOGIN_TIME_LOW:int,LOGIN_TIME_HIGH:int,DISABLED_FROM_DATE:int,DISABLED_UPTO_DATE:int,PW_EXPY_DATE:int,ACCT_EXPY_DATE:int,ACCT_INACTIVE_DAYS:int,LAST_ACCESS_TIME:int,TS_CNT:int,DTL__CAPXTIMESTAMP:int,ETL_INSERT_DATE:chararray);

--ToDate builtIn function is used to convert to datetime, format need to be specified

file2 = FOREACH file GENERATE USER_ID,ROLE_ID,USER_PW,NUM_PWD_HISTORY,PWD_HISTORY,PWD_LAST_MOD_TIME,NUM_PWD_ATTEMPTS,NEW_USER_FLG,LOGIN_TIME_LOW,LOGIN_TIME_HIGH,DISABLED_FROM_DATE,DISABLED_UPTO_DATE,PW_EXPY_DATE,ACCT_EXPY_DATE,ACCT_INACTIVE_DAYS,LAST_ACCESS_TIME,TS_CNT,DTL__CAPXTIMESTAMP, ToDate(ETL_INSERT_DATE, 'yyyy-MM-dd') AS ETL_INSERT_DATE;

describe file2; dump file2;

Upvotes: 0

Jack
Jack

Reputation: 520

Load date as chararray and then convert into date format

like:

file2 = FOREACH file GENERATE ToDate(date, 'dd/MM/yyyy') AS date,....

Try this link for reference, http://pig.apache.org/docs/r0.11.0/api/org/apache/pig/builtin/ToDate.html OR http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Upvotes: 1

Related Questions