question Re: Convert date from different format to one particular format in Support Questions

Convert date from different format to one particular format

niraj_tandel89 — Sun, 24 Jul 2016 02:24:32 GMT

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

1904287	Christopher Rodriguez	Jan 11, 2003
96391595	Thomas Stewart	617/1969
2236067	John Nelson	08/22/54

Re: Convert date from different format to one particular format

sunile_manjee — Sun, 24 Jul 2016 06:55:09 GMT

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.

Re: Convert date from different format to one particular format

niraj_tandel89 — Sun, 24 Jul 2016 07:01:59 GMT

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

Re: Convert date from different format to one particular format

sbomma — Tue, 26 Jul 2016 02:32:50 GMT

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

Re: Convert date from different format to one particular format

sunile_manjee — Tue, 26 Jul 2016 02:41:21 GMT

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

Re: Convert date from different format to one particular format

niraj_tandel89 — Tue, 26 Jul 2016 02:47:31 GMT

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 ?

Re: Convert date from different format to one particular format

niraj_tandel89 — Tue, 26 Jul 2016 02:52:47 GMT

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.

Re: Convert date from different format to one particular format

sunile_manjee — Tue, 26 Jul 2016 03:22:18 GMT

@Neeraj Tandel here is good example

http://www.hadooptpoint.com/how-to-write-hive-udf-example-in-java/

Re: Convert date from different format to one particular format

niraj_tandel89 — Tue, 26 Jul 2016 05:37:16 GMT

@Sunile Manjee Can you help me write date udf

Re: Convert date from different format to one particular format

abhi_kevin — Tue, 23 Jul 2019 20:12:26 GMT

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