Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Free form query in Sqoop Import with WHERE clause

avatar
Expert Contributor

Hi,

I am new to sqoop and trying to write few simple sqoop scripts. I am trying to query a table with where clause condition. When I give the where clause condition it's failing. Here is my simple script:

sqoop import \

--connect jdbc:mysql://localhost/test \

--username sat \

-P \

--query 'select emp_no,salary from salaries where salary >8000' \

--target-dir /user/ \

--m 1

I went through sqoop documentation and it was mentioned that I should use $CONDITIONS , but here my question is, if I use this parameter then , where I can specify my where clause condition. Please help me on the same.

Thanks,

Satish.

1 ACCEPTED SOLUTION

avatar
Master Mentor
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
22 REPLIES 22

avatar
Expert Contributor

yes, it worked. Here is the final script:

sqoop import --connect jdbc:mysql://localhost/test --username sat -P --query 'select emp_no, salary from salaries where $CONDITIONS and salary>800' --split-by emp_no --target-dir /user/cloudera/tmp/ff

Thanks much.

avatar
Master Mentor
@Satish S

Please accept the answer to close the thread 😉

avatar

@Satish S

please try this after giving valid password

sqoop import \

--connect jdbc:mysql://localhost/test \

--username sat \

--password XXXXX \

--query "select emp_no, salary from salaries where $CONDITIONS" \

--fields-terminated-by '|' \

--target-dir /user/cloudera/tmp/ \

--m 1;

avatar
Expert Contributor

This is my script:

sqoop import --connect jdbc:mysql://localhost/test --username sat -P --query 'select emp_no, \ salary \ from \ salaries \ where \ $CONDITIONS' --split-by emp_no --target-dir /user/cloudera/tmp/

Error:

16/02/12 08:42:22 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\
salary \
from \
salaries \
where \ 
 (1 = 0)' at line 2
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\
salary \
from \
salaries \
where \ 
 (1 = 0)' at line 2

avatar

@Satish S

see the error message you have to include SqlManager in your conenction string.

16/02/12 09:28:51 ERROR manager.SqlManager: Error executing statement:

avatar
New Contributor

sqoop import --connect jdbc:mysql://localhost/test --username sat -P --query 'select emp_no, salary from salaries where $CONDITIONS' --split-by emp_no --target-dir /user/cloudera/tmp/

there is no need of \ that you have add into your mysql query

and if you want use query in double quotes --query "select emp_no, salary from salaries where \$CONDITIONS"

add backslash before $CONDITIONS like above 🙂

avatar
Master Mentor

@Satish S your issue is all the \ in your query, you use \ outside of quotes, inside the quotes it is interpreted as SQL. That's why your MySQL is complaining. Remove all that and do use '$CONDITIONS', make sure to read the note about wrapping $CONDITIONS with single quotes or double because it does make a difference.

avatar

@Satish S

I'm using below command frequently ..

sqoop import \ --connect jdbc:teradata://<DB Schema Nmae>/DATABASE=app_hadoop,CHARSET=UTF8 \

--driver "com.teradata.jdbc.TeraDriver" \

--username XXXXX \

--password XXXXX \

--query "select a*,b* from abc where CAST(ts as DATE) >='2012-00-01' and CAST(ts as DATE) < '2016-02-01' AND \$CONDITIONS" \

--null-string '\\N' \

--null-non-string '\\N' \

--hive-delims-replacement '\0D' \

--fields-terminated-by '\001' \

--target-dir /db/prep/data \

--split-by created_on \

--m 4 ;

Here my Source DB is Teradata

avatar
New Contributor

Based on the Neeraj Sabharwal reply , $Condition with where condition is working fine as expected . But that condition should be int comparision .

Example

Where id is integer field

sqoop import --connect jdbc:mysql://localhost/test --username sat --query 'select id,name from employee where $CONDITIONS and id = 323437 ' --split-by id --target-dir taget-dir-name

this is working fine as expected .

but

sqoop import --connect jdbc:mysql://localhost/test --username sat --query " select id,name from employee where $CONDITIONS and name = 'sathish' " --split-by id --target-dir taget-dir-name

name = 'sathish' is not working .

Could you please let us know how to handle string in where condition

avatar
Explorer

sqoop import --connect jdbc:mysql://localhost/test --username sat --query " select id,name from employee where \$CONDITIONS and name = 'sathish' " --split-by id --target-dir taget-dir-name

This query works fine. When you are using double-quote to specify sql statement then you have to use \$CONDITIONS instead of only $CONDITIONS