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 · Accepted solution]

@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

[#3]

Hi Neeraj,

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

[#4]

@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

[#5]

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/

[#6]

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.

[#7]

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

[#8]

@Satish S

Did it fail?

[#9]

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

[#10]

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