Sqoop, SQLServer, DateTime, and Fun!

Ok, so writing a simple query like the following:

SELECT a.* FROM table a WHERE a.timestamp >= '20101125'

Should be pretty straightforward for importing data into Hive through Sqoop, but alas there are some hurdles.

I'll just get straight to the solution, I know people hate to read, so here is the final command line example:

sqoop import --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --connect 'jdbc:sqlserver://localhost;user=xxxxxx;password=xxxxxxx;database=MyDB' --query "select a.* from tableX a where (a.timestamp >= '2010-11-25 00:00:00') AND \$CONDITIONS" --target-dir /data/feedlog --split-by 'MyIDColumn' --fields-terminated-by '\t'

Basically, enclose the entire --query in double-quotes, and then escape the $ on the $CONDITIONS value with a \ as well so that is escapes the initial bash evaluation.

Happy hunting!