question Re: import with sqoop smalldatetime from sql server to timestamp in Hive in Archives of Support Questions (Read Only)

import with sqoop smalldatetime from sql server to timestamp in Hive

nanyim_alain — Thu, 09 Jun 2016 22:38:45 GMT

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

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

rajkumar_singh — Fri, 10 Jun 2016 14:15:26 GMT

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

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

ssubhas — Fri, 10 Jun 2016 16:15:55 GMT

@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

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

nanyim_alain — Fri, 10 Jun 2016 21:38:52 GMT

Thanks. But it creates another error :

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

Thanks

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

ganne — Sat, 11 Jun 2016 00:16:55 GMT

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

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

ganne — Sat, 11 Jun 2016 00:17:26 GMT

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

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

nanyim_alain — Mon, 13 Jun 2016 18:28:23 GMT

Thank you.

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