Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

unable to convert string field to timestamp

Highlighted

unable to convert string field to timestamp

New Contributor

Hello Team

 

I have a requirement to pull data in csv format and load to hive

 

In csv we have 10 columns amoung which 2 columns are with date format

 

format are MM-dd-yyyy HH;MM AM/PM anf MM-dd-YYYY respectively 

i need to cast these columns from string to timestamp format in hive.

 

i tried to tweek the sql either i get null values for few records or below error

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.hive.serde2.io.TimestampWritable

1 REPLY 1
Highlighted

Re: unable to convert string field to timestamp

New Contributor

sql i am using

 

select

cast(from_unixtime(unix_timestamp(date_with_am,'MM-dd-yyyy HH:mm a'), 'yyyy-dd-MM HH:mm:ss')) date_with_am,

Employee_Name as employee_name,

Email as email,

cast(from_unixtime(unix_timestamp(term_date,'MM-dd-yyyy'), 'yyyy-dd-MM HH:mm:ss'))  as term_date, 

from table;

Don't have an account?
Coming from Hortonworks? Activate your account here