HDFS String data to timestamp in hive table

Question

Hi I have a data in HDFS as a string '2015-03-26T00:00:00+00:00' ..if i want to load this data into Hive table (column as timestamp).i am not able to load and i am getting null values.

if i specify column as string i am getting the data into hive table but if i specify column as timestamp i am not able to load the data and i am getting all NULL values in that column.

Eg: HDFS - '2015-03-26T00:00:00+00:00'
hive table- create table t1(my_date string) i can get output as - '2015-03-26T00:00:00+00:00'

if i specify create table t1(my_date as timestamp)--i can see all null values

Can any one help me on this

Answer 1

Timestamps in text files have to use the format yyyy-mm-dd hh:mm:ss[.f...]. If they are in another format declare them as the appropriate type (INT, FLOAT, STRING, etc.) and use a UDF to convert them to timestamps.

Go through below link:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps

Answer 2

You have to use a staging table. In the staging table load as String and in the final table use UDF as below to convert the string value to Timestamp

from_unixtime(unix_timestamp(column_name, 'dd-MM-yyyy HH:mm'))