Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

import with sqoop smalldatetime from sql server to timestamp in Hive

avatar
Expert Contributor

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

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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...

View solution in original post

6 REPLIES 6

avatar
Super Guru

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

avatar

@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

avatar
Expert Contributor

Thanks. But it creates another error :

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

Thanks

avatar
Expert Contributor

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...

avatar
Expert Contributor

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...

avatar
Expert Contributor

Thank you.

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