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/

[#11]

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.

[#12]

@Satish S

Please accept the answer to close the thread 😉

[#13]

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

[#14]

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

[#15]

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

[#16]

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 🙂

[#17]

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

[#18]

@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

[#19]

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

[#20]

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

[#21]

hi:

i have receiving this error with the $CONDITIONS

sqoop import -D oraoop.disabled=true \
--connect jdbc:oracle:thin:@HOSTNAME:2521/CIP_BATCH \
--username=U029550 \
--password=Mayo2016 \
--query "SELECT ID_INTERNO_PE,MI_FECHA_FIN_MES,COD_NRBE_EN,COD_LINEA,ID_GRP_PD,MI_NUM_TOT_AC_ACT,MI_NUM_AC_SUS,MI_SDO_AC_P,MI_NUM_AC_P,MI_DIA_AC_P,MI_INT_DEV_ACR_D,MI_INT_DEV_DEU_D,MI_COMIS_APL_D,MI_TOT_MOV_D,MI_TOT_MOV_H,MI_TOT_IMP_MOV_D,MI_TOT_IMP_MOV_ FROM RDWC01.MI_CLTE_ECO_GEN WHERE $CONDITIONS AND COD_RL_PERS_AC = 01 AND COD_LINEA in ('01','03','04','05') AND COD_NRBE_EN = '3159' AND TRUNC(MI_FECHA_FIN_MES) >=TO_DATE('2010-01-01', 'YYYY-MM-DD')" \
--split-by MI_NUM_TOT_AC_ACT \
--fetch-size=50000 \
--direct \
--target-dir=/RSI/datalake/desercion/2016/1 --verbose \

16/06/08 08:56:18 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Query [SELECT ID_INTERNO_PE,MI_FECHA_FIN_MES,COD_NRBE_EN,COD_LINEA,ID_GRP_PD,MI_NUM_TOT_AC_ACT,MI_NUM_AC_SUS,MI_SDO_AC_P,MI_NUM_AC_P,MI_DIA_AC_P,MI_INT_DEV_ACR_D,MI_INT_DEV_DEU_D,MI_COMIS_APL_D,MI_TOT_MOV_D,MI_TOT_MOV_H,MI_TOT_IMP_MOV_D,MI_TOT_IMP_MOV_ FROM RDWC01.MI_CLTE_ECO_GEN WHERE '' AND COD_RL_PERS_AC = 01 AND COD_LINEA in ('01','03','04','05') AND COD_NRBE_EN = '3159' AND TRUNC(MI_FECHA_FIN_MES) >=TO_DATE('2010-01-01', 'YYYY-MM-DD')] must contain '$CONDITIONS' in WHERE clause.
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:300)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

Please why the $CONDITIONS doesnt work¿??