Created 02-12-2016 04:12 PM
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.
Created 02-12-2016 04:18 PM
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
Created 02-12-2016 04:18 PM
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
Created 02-12-2016 04:19 PM
Hi Neeraj,
I don't want to get all columns from my table.
Created 02-12-2016 04:36 PM
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
Created 02-12-2016 04:55 PM
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/
Created 02-12-2016 05:07 PM
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.
Created 02-12-2016 05:20 PM
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
Created 02-12-2016 05:25 PM
Did it fail?
Created 02-12-2016 05:29 PM
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
Created 02-12-2016 05:33 PM
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/
Created 02-12-2016 05:37 PM
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.
Created 02-12-2016 05:40 PM
Please accept the answer to close the thread 😉
Created 02-12-2016 05:32 PM
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;
Created 02-12-2016 04:45 PM
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
Created 02-12-2016 05:35 PM
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:
Created 02-07-2017 06:54 AM
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 🙂
Created 02-12-2016 04:57 PM
@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.
Created 02-12-2016 05:00 PM
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
Created 05-23-2016 03:39 PM
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
Created 08-10-2016 06:33 AM
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
Created 06-08-2016 06:54 AM
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¿??