Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

import with sqoop smalldatetime from sql server to timestamp in Hive

Solved Go to solution
Highlighted

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

Accepted Solutions
Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

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
Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

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

Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

@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

Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

Rising Star

Thanks. But it creates another error :

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

Thanks

Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

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

Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

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

Highlighted

Re: import with sqoop smalldatetime from sql server to timestamp in Hive

Rising Star

Thank you.

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

Don't have an account?
Coming from Hortonworks? Activate your account here