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]

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¿??

[#3]

@Roberto Sancho

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

So, please try:

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

[#4]

Hi @Roberto Sancho 

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

This query works fine 🙂