You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sqoop.apache.org by "Cheolsoo Park (Commented) (JIRA)" <ji...@apache.org> on 2012/03/30 03:35:26 UTC
[jira] [Commented] (SQOOP-468) Oracle free form queries fail.
[ https://issues.apache.org/jira/browse/SQOOP-468?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13241999#comment-13241999 ]
Cheolsoo Park commented on SQOOP-468:
-------------------------------------
The exception is thrown while executing the bounding value query:
{code}
SELECT MIN(TESTTABLE.ID), MAX(TESTTABLE.ID) FROM (SELECT TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND (1 = 1) );
{code}
If I execute this query in Oracle SQL, it gives the following error:
{code}
ORA-00904: "TESTTABLE"."ID": invalid identifier.
{code}
But if I drop the "TESTTABLE" prefix from the column names in the select clause of the outer query as follows:
{code}
SELECT MIN(ID), MAX(ID) FROM (SELECT TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND (1 = 1) );
{code}
it works fine. So it seems that "TESTTABLE" is not recognized in the outer query while it is in the inner query.
The possible fix is probably to strip the table name of column names when constructing the bounding value queries for the free-form query import for Oracle Db. Please let me know if anyone thinks otherwise.
> Oracle free form queries fail.
> ------------------------------
>
> Key: SQOOP-468
> URL: https://issues.apache.org/jira/browse/SQOOP-468
> Project: Sqoop
> Issue Type: Bug
> Components: connectors
> Affects Versions: 1.4.1-incubating
> Reporter: Prashant Gokhale
> Assignee: Cheolsoo Park
>
> Sqoop query I am running:
> sqoop import $SQOOP_CONNECTION_STRING --query 'select TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND \$CONDITIONS' --split-by TESTTABLE.ID --target-dir $OUTPUT/query
> If I just run this query (select TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3;) in Oracle, it runs fine, but sqoop throws an error:
> {noformat}
> 12/03/23 12:41:42 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(TESTTABLE.ID), MAX(TESTTABLE.ID) FROM (select TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND (1 = 1) ) t1
> 12/03/23 12:41:42 INFO mapreduce.JobSubmitter: Cleaning up the staging area /user/prashant/.staging/job_1332458964138_0060
> 12/03/23 12:41:42 ERROR security.UserGroupInformation: PriviledgedActionException as:prashant (auth:SIMPLE) cause:java.io.IOException: java.sql.SQLSyntaxErrorException: ORA-00904: "TESTTABLE"."ID": invalid identifier
> 12/03/23 12:41:42 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: java.sql.SQLSyntaxErrorException: ORA-00904: "TESTTABLE"."ID": invalid identifier
> at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:167)
> at org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:451)
> at org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:468)
> at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:360)
> at org.apache.hadoop.mapreduce.Job$11.run(Job.java:1221)
> at org.apache.hadoop.mapreduce.Job$11.run(Job.java:1218)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1177)
> at org.apache.hadoop.mapreduce.Job.submit(Job.java:1218)
> at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1239)
> at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:119)
> at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:179)
> at org.apache.sqoop.manager.SqlManager.importQuery(SqlManager.java:458)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:382)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:453)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:69)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
> at com.cloudera.sqoop.Sqoop.main(Sqoop.java:57)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TESTTABLE"."ID": invalid identifier
> {noformat}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira