Support Questions

Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

import with sqoop smalldatetime from sql server to timestamp in Hive

Rising Star

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

Rising Star

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

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

@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

Rising Star

Thanks. But it creates another error :

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

Thanks

Rising Star

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

Rising Star

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

Rising Star

Thank you.

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.