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

@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

View solution in original post

22 REPLIES 22

avatar
Master Mentor

@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

avatar
Expert Contributor

Hi Neeraj,

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

avatar
Master Mentor

@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

avatar
Master Mentor

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/

avatar
Expert Contributor

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.

avatar
Expert Contributor

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

avatar
Master Mentor
@Satish S

Did it fail?

avatar
Expert Contributor

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

avatar
Master Mentor

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