import with sqoop smalldatetime from sql server to timestamp in Hive

[#1]

Hello,

I have a smalldatetime data in my database server sql data and when I matter with Sqoop , this data is stored in the Hive String format because smalldatetime does not exist in the hive tool. This becomes probmématique for my work. Anyone know if there a way to import through Sqoop , smallldatetime a data type of sql server and store the timestamp format recognized by Hive .

thanks

[#2]

I am not sure about whether is any way available to get the desired output from sql server import, but I dont think it matter much if you import sql server smalldate to hive string. you can modify the hive query to get the expected output e.g unix_timestamp you can convert the string to desired timestamp

to know more about it you can check following documentation.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

[#3]

@alain TSAFACK

You can also make use of --query option during sqoop import to cast the smalldatetime to timestamp:

sqoop import ...other options.... --query "select cast(col1 as datetime) from table_name"

Hope this helps.

Thanks and Regards,

Sindhu

[#4]

Thanks. But it creates another error :

"Hive does not support the SQL type for column date"

Thanks

[#5]

Can you try --map-column-hive option. This will Overridethe default mapping from SQL type to Hive type for configured columns

Refer to the documentation here https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/using_sqoop_to_m...

[#6 · Accepted solution]

Can you try --map-column-hive option. This will Overridethe default mapping from SQL type to Hive type for configured columns

Refer to the documentation here https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/using_sqoop_to_m...

[#7]

Thank you.

By adding the attribute --map-column-hive Date=Timestamp to Sqoop everything works.