Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Convert date from different format to one particular format

avatar

How to insert data into hive table in a particular date format (DD/MM/YY) from the below hive table

1904287Christopher Rodriguez

Jan 11, 2003

96391595Thomas Stewart617/1969
2236067John Nelson08/22/54
1 ACCEPTED SOLUTION

avatar
Master Guru

Apologies for grammar and typos... writing this from my phone.

If your date format within a given data set is inconsistent the i would write a UDF to handle this. Inside the UDF you would have to detect the type of date you are working with using regex for example. This is done very nicely witb NiFi if you want yo hit the wasy button. If the format is consistent within a dataset yet different amount others then simply write a hive or pig script for each dataset and then parse out the date with the format you expect for that specific data set.

View solution in original post

9 REPLIES 9

avatar
Master Guru

Apologies for grammar and typos... writing this from my phone.

If your date format within a given data set is inconsistent the i would write a UDF to handle this. Inside the UDF you would have to detect the type of date you are working with using regex for example. This is done very nicely witb NiFi if you want yo hit the wasy button. If the format is consistent within a dataset yet different amount others then simply write a hive or pig script for each dataset and then parse out the date with the format you expect for that specific data set.

avatar

Hey Sunile thanks for your reply could you help me with date udf have less idea about that if you could write here or you can email me niraj.tandel89@gmail.com

avatar
Master Guru

@Neeraj Tandel I would start by looking at the apache site and looking for UDF samples. Tons of information on the web on how to build simple UDF. I would start there and build out your UDF.

avatar

Thank you Sunil I really appreciate your efforts. I am kind of newbie in this stuff I have searched alot. Thanks once again for your time.

avatar
Master Guru

avatar

@Sunile Manjee Can you help me write date udf

avatar
Super Collaborator

We would have to create the column as a date field instead of a string field, to store dates. That way everything would be stored as a date datatype. So once you have that determined, your ingest data can use a UDF like @Sunile Manjee suggested. If you have data being stored as the date into hive, you could use any of the hive functions to represent the data in any way you prefer.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

search for Date Functions

avatar

Hey Satish Thanks for your Answer i dont know if this is right to say but could you provide an example (may be some coding stuffs) on how to proceed ?

avatar
New Contributor

This is a kind of a late response to this thread but you can use a coalesce function:


coalesce(from_unixtime(unix_timestamp(dob ,"MMM dd, yyyy"), "dd/MM/yy"),from_unixtime(unix_timestamp(dob ,"MM/dd/yyyy"), "dd/MM/yy"), from_unixtime(unix_timestamp(dob ,"MM/dd/yy"), "dd/MM/yy)) as new_dob