Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Mass Ingestion incremental load fails with the exception "java.io.IOException: java.sql.SQLSyntaxErrorException: ORA-00904: "id": invalid identifier"
Problem Description
When running mass ingestion incremental load, the job fails with the following exception against an Oracle relational source:

2018-09-25 14:05:06.694 <LdtmWorkflowTask-pool-5-thread-6> INFO: Maximal id query for free form incremental import: SELECT MAX("id") FROM "TEST_OPPORTUNITY2" WHERE TEST_OPPORTUNITY2.ID < 0
2018-09-25 14:05:06.699 <LdtmWorkflowTask-pool-5-thread-6> INFO: Encountered IOException running import job: java.io.IOException: java.sql.SQLSyntaxErrorException: ORA-00904: "id": invalid identifier

at org.apache.sqoop.tool.ImportTool.initIncrementalConstraints(ImportTool.java:316)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:498)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil$SqoopT.run(SqoopExecUtil.java:236)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil.runInfaSqoop(SqoopExecUtil.java:625)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil.invoke(SqoopExecUtil.java:591)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil.access$1(SqoopExecUtil.java:483)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil$GetSqoopHandler.run(SqoopExecUtil.java:466)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil$GetSqoopHandler.run(SqoopExecUtil.java:1)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil.run(SqoopExecUtil.java:788)
at com.informatica.platform.dtm.executor.hadoop.shellcmd.SqoopExecUtil.main(SqoopExecUtil.java:416)
at sun.reflect.GeneratedMethodAccessor276.invoke(Unknown Source)
Cause
The query being fired by Mass Ingestion Service for the incremental load is created as follows:

SELECT MAX("id") FROM "TEST_OPPORTUNITY2" WHERE TEST_OPPORTUNITY2.ID < 0

Here, the key column provided ("id") in the Mapping spec-->Incremental Load Options is in lower case as shown below:

mass_ingestion_incremental_column_1.png

Thus, if the Oracle database is case-sensitive, the query would be invalid because of the lower case key column.
Solution
​Ensure that the key column provided in the Incremental Load Options is in uppercase if the database is case-sensitive (Example: "ID" in the above example).
More Information
Applies To
Product: Data Engineering Quality(Big Data Quality); Data Engineering Integration(Big Data Management)
Problem Type: Configuration; Connectivity
User Type: Developer; Administrator
Project Phase: Implement
Product Version:
Database:
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:11/19/2019 4:43 AMID:602805
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)