Reply
New Contributor
Posts: 2
Registered: ‎10-08-2014

Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Hi,

we have the context as , A table containing the date column with STRING as data type in the format  2014-09-24 19:21:38:532+0000. Tableau  is taking a lot of time while doing operations on this date column. Impala does not support DATE data type to convert it to date though hive supports, so we changed to TIMESTAMP format but Impala returns the following error.

 

Error converting column: 10 TO TIMESTAMP (Data is: 2014-10-09 11:46:48:424+0000). Not sure if that is because the data is following the format as HH:MM:SEC:sssss. Any ideas on this how can we make this data better read through impala with out much performance issue.

 

 

Regards

Sree

Cloudera Employee
Posts: 307
Registered: ‎10-16-2013

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

I'd suggest having a look at the builtin timestamp functions. The following should help in your situation, but the conversion may come with a performance hit - you'll need to try it:

 

select from_unixtime(unix_timestamp("2014-09-24 19:21:38:532+0000", "yyyy-MM-dd HH:mm:ss:SSS"));

 

Explorer
Posts: 44
Registered: ‎07-24-2014

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Here the problem is not with the static data, it is about the column having such kind of data.

Highlighted
Cloudera Employee
Posts: 307
Registered: ‎10-16-2013

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

I understand. Change my example to:

 

select from_unixtime(unix_timestamp(my_custom_string_column, "yyyy-MM-dd HH:mm:ss:SSS")) from mytable;

 

The second parameter describes the date format to be parsed.

Announcements