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

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?

CodePudding user response：

You guys are on right track but the statement need a change. Need to put and 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

CodePudding user response：

I don't know tool(s) you use, but - error message says

Caused by: Error : 933, Position : 80,

Position 80 is:

SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022'  (1 = 0) 
                                                                               --------
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
         10        20        30        40        50        60        70        80

So, what is exactly this (1 = 0)? Can you remove it?

Apart from that, if trn_dt column's datatype is date, then don't compare it to strings; use date literal or to_date function with appropriate format model.

Moreover, the way you put it, between would do (as it includes both boundaries):

select * from actb_history
where trn_dt between date '2021-09-01' and date '2022-08-01'