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.

How to extract date from unixtime while importing from sql server to hdfs using sqoop?

Highlighted

How to extract date from unixtime while importing from sql server to hdfs using sqoop?

New Contributor

I am trying to import data from MSSQL to HDFS using free form query import. I would like to extract DATE from unix time in the table. I tried the below command and it gives me a weird output and not even sure why that's the output.

sqoop import --connect "jdbc:sqlserver://xxx.xx.xxx.xx:xxxx;databaseName=Projector" --username USER -P --query 'select convert(varchar,DATEADD(ss,projectorunixtime,'1970-01-01'),112) as dt from TABLENAME where (projectorunixtime='1511963773') AND $CONDITIONS' --fields-terminated-by '\t' --m 1 --target-dir /tmp/dateconverted

The output should be something like :

-----dt-----

20171129

Instead, output looks like

-----dt-----

19530420

The below query gives me the desired result in mssql console and also when I tried it running via Spark sql.

select convert(varchar,DATEADD(ss,projectorunixtime,'1970-01-01'),112)

Could anyone suggest me a way to work this around?


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