Storing Date and Time In PIG

Question

I am trying to store a txt file that has two columns date and time respectively. Something like this: 1999-01-01 12:08:56

Now I want to perform some Date operations using PIG, but i want to store date and time like this 1999-01-01T12:08:56 ( I checked this link): http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

What I want to know is that what kind of format can I use in which my date and time are in one column, so that I can feed it to PIG, and then how to load that date into pig. I know we change it into datetime, but its showing errors. Can somebody kindly tell me how to load Date&Time data together. An example would be of great help.

I do not know anything about Apache PIG. But if your question is simply how to take a string representing a date and a string representing a time-of-day, combine the two into a string to be parsed as a date-time value (object), possibly adjust that value to another time zone such as UTC, and then serialize that value to a string in a different format to represent the combined date-time value… well, that has been covered in at least a thousand questions and answers on StackOverflow. And I just gave you the key words you need to search for, along with 'joda' and 'java.time'. — Basil Bourque

Sivasakthi Jayaraman Sivasakthi Jayaraman · Accepted Answer · 2014-09-24T03:31:44

Please let me know if this works for you.

input.txt  
1999-01-01 12:08:56  
1999-01-02 12:08:57  
1999-01-03 12:08:58  
1999-01-04 12:08:59  

PigScript:  
A = LOAD 'input.txt' using PigStorage(' ') as(date:chararray,time:chararray);  
B = FOREACH A GENERATE CONCAT(date,'T',time) as myDateString;  
C = FOREACH B GENERATE ToDate(myDateString);  
dump C;  

Output:  
(1999-01-01T12:08:56.000+05:30)  
(1999-01-02T12:08:57.000+05:30)  
(1999-01-03T12:08:58.000+05:30)  
(1999-01-04T12:08:59.000+05:30)  

Now the myDateString is in date object, you can process this data using all the build in date functions.

Incase if you want to store the output as in this format 
(1999-01-01T12:08:56)  
(1999-01-02T12:08:57)  
(1999-01-03T12:08:58)  
(1999-01-04T12:08:59)

you can use REGEX_EXTRACT to parse the each data till "."  something like this  

D = FOREACH C GENERATE ToString($0) as temp;
E = FOREACH D GENERATE REGEX_EXTRACT(temp, '(.*)\\.(.*)', 1);
dump E;

Output:
(1999-01-01T12:08:56)  
(1999-01-02T12:08:57)  
(1999-01-03T12:08:58)  
(1999-01-04T12:08:59)

Storing Date and Time In PIG

1 Answers