CODEWITHSUNDEEP
hdfssqoop
user1646529
user1646529

Reputation: 11

"YYYYMMDD": Invalid identifier error while trying through SQOOP

Please help me out from the below error.It works fine when checked in oracle but fails when trying through SQOOP import.

version : Hadoop 0.20.2-cdh3u4 and Sqoop 1.3.0-cdh3u5

sqoop import $SQOOP_CONNECTION_STRING 
--query 'SELECT st.reference,u.unit,st.reading,st.code,st.read_id,st.avg FROM reading st,tunit `tu,unit u 
WHERE st.reference=tu.reference and st.number IN ('218730','123456') and tu.unit_id = u.unit_id 
and u.enrolled='Y' AND st.reading <= latest_off and st.reading >= To_Date('20120701','yyyymmdd') 
and st.type_id is null and $CONDITIONS' 
--split-by u.unit 
--target-dir /sample/input

Error:

12/10/10 09:33:21 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00904: "YYYYMMDD": invalid identifier

followed by....

12/10/10 09:33:21 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException

Thanks & Regards,

Tamil

Upvotes: 1

Views: 1728

Answers (1)

Jarek Jarcec Cecho
Jarek Jarcec Cecho

Reputation: 1726

I believe that the problem is actually on Bash side (or your command line interpret). Your query contains for example following fragment u.enrolled='Y'. Please notice that you're escaping character constants with single quotes. You seem to be putting entire query into additional single quotes: --query 'YOUR QUERY'. Which results in something like --query '...u.enrolled='Y'...'. However such string is stripped by bash to '...u.enrolled=Y...'. You can verify that by using "echo" to see what exactly will bash do with your string before it will be passed to Sqoop.

jarcec@jarcec-thinkpad ~ % echo '...u.enrolled='Y'...'
...u.enrolled=Y..

.

I would recommend to either escape all single quotes (\') inside your query or choose double quotes for entire query. Please note that the later option will require escaping $ characters with backslash (\$).

Upvotes: 1

Related Questions