[SOLVED] There are multiple '. 0' after sqoop exports data from Mysql to hive datetime type

There are multiple '. 0' after sqoop exports data from Mysql to hive datetime type

mysql type is datetime,hive is string or timestamp there are all happen in hive table

mysql

2016-10-13 15:18:10

hive

2016-10-13 15:18:10.0

Solution

Hive default timestamp string representation format is 'yyyy-MM-dd HH:mm:ss.S' and this is why it is being converted to string in this format implicitly, the same happens when the Hive datatype is timestamp and you selecting it, if you have no milliseconds, it is displayed with .0. If you want to suppress .0, store value as STRING in Hive and do explicit conversion to string in the query using date_format function