question Re: Free form query in Sqoop Import with WHERE clause in Support Questions

Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 00:12:49 GMT

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.

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 00:18:56 GMT

@Satish S

See this tutorial http://www.tutorialspoint.com/sqoop/sqoop_import.htm

 sqoop import \
--connect jdbc:mysql://localhost/userdb \--username root \
--table emp_add \
--m 1 \
--where“city =’sec-bad’” \
--target-dir /wherequery

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 00:19:59 GMT

Hi Neeraj,

I don't want to get all columns from my table.

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 00:36:25 GMT

@Satish S

Your query must include the token $CONDITIONS which each Sqoop process will replace with a unique condition expression. You must also select a splitting column with --split-by.

For example:

$ sqoop import \
  --query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' \
  --split-by a.id --target-dir /user/foo/joinresults

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 00:45:32 GMT

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

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 00:55:41 GMT

Try this @Satish S and Are you using CDH cluster?

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/

Re: Free form query in Sqoop Import with WHERE clause

aervits — Sat, 13 Feb 2016 00:57:25 GMT

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

Re: Free form query in Sqoop Import with WHERE clause

divakarreddy_a — Sat, 13 Feb 2016 01:00:17 GMT

@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

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 01:07:18 GMT

Yes @Neeraj Sabharwal & @Artem Ervits.

I am able to execute it using below 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/ff

but, still my requirement is not met. I am trying to execute below query using sqoop,

select emp_no,salary from salaries where salary >8000

Please let me know on the same. Thanks.

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 01:20:43 GMT

no luck, I am using it as,

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

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 01:25:11 GMT

@Satish S

Did it fail?

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 01:29:04 GMT

Yes and error is ,

16/02/12 09:28:51 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 16/02/12 09:28:51 INFO tool.CodeGenTool: Beginning code generation 16/02/12 09:28:51 INFO manager.SqlManager: Executing SQL statement: select emp_no, salary from salaries where salary>8000 (1 = 0)

16/02/12 09:28:51 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 '(1 = 0)' at line 7 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 '(1 = 0)' at line 7

Re: Free form query in Sqoop Import with WHERE clause

divakarreddy_a — Sat, 13 Feb 2016 01:32:23 GMT

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

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 01:33:14 GMT

@Satish S

Final try...I will try to reproduce if it does not work.

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

Re: Free form query in Sqoop Import with WHERE clause

divakarreddy_a — Sat, 13 Feb 2016 01:35:47 GMT

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

Re: Free form query in Sqoop Import with WHERE clause

SatishS — Sat, 13 Feb 2016 01:37:58 GMT

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.

Re: Free form query in Sqoop Import with WHERE clause

nsabharwal — Sat, 13 Feb 2016 01:40:40 GMT

@Satish S

Please accept the answer to close the thread 😉

Re: Free form query in Sqoop Import with WHERE clause

hdpcdkarthi — Mon, 23 May 2016 22:39:59 GMT

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

Re: Free form query in Sqoop Import with WHERE clause

tono — Wed, 08 Jun 2016 15:36:36 GMT

@Roberto Sancho

When you use double quotes for enclosing character, you have to escape '$' such as '\$'.

So, please try:

--query "SELECT (....) WHERE \$CONDITIONS AND (...)"

Re: Free form query in Sqoop Import with WHERE clause

masayyed — Wed, 10 Aug 2016 13:33:13 GMT

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