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.
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"));
Here the problem is not with the static data, it is about the column having such kind of data.
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.