Search code examples
oracle-databasehivesqoop

How to fix ORA-00933: SQL command not properly ended using sqoop

I am trying to import data from Oracle into hive using SQOOP. I have read the threads with the same error, it doesn't seem to solve my problem.

This is the command I am using

sqoop import --connect jdbc:oracle:thin:@connection --query "SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022' $CONDITIONS"; --username stg_uat -P  --target-dir  \user\hive\warehouse--hive-import --hive-database dwhpd --create-hive-table --hive-table actb_hist -m 1

I keep getting this error

Caused by: Error : 933, Position : 80, Sql = SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022'  (1 = 0) , OriginalSql = SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022'  (1 = 0) , Error Msg = ORA-00933: SQL command not properly ended

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
        ... 31 more
2022-08-02 09:23:33,739 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1677)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

I want to fetch data from that table between a specified date range. What could be wrong with the query?

Solution

  • You guys are on right track but the statement need a change. Need to put an AND before $CONDITIONS. sqoop uses this to validate the sql against metadata. You can use this sqoop-

    sqoop import --connect jdbc:oracle:thin:@connection --query "SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022' AND $CONDITIONS"; --username stg_uat -P  --target-dir  \user\hive\warehouse--hive-import --hive-database dwhpd --create-hive-table --hive-table actb_hist -m 1