Free form query in Sqoop Import with WHERE clause

[#1]

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.

[#2]

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.

[#3]

@Satish S

Please accept the answer to close the thread 😉

[#4]

@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;

[#5]

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

[#6]

@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:

[#7]

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 🙂

[#8]

@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.

[#9]

@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

[#10]

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

[#11]

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