Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.1-incubating
    • Fix Version/s: 1.4.2
    • Component/s: connectors
    • Labels:
      None

      Description

      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:

      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
      
      
      1. SQOOP-468.patch
        12 kB
        Cheolsoo Park

        Activity

        Hide
        Hudson added a comment -

        Integrated in Sqoop-ant-jdk-1.6 #106 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6/106/)
        SQOOP-468. Oracle free form queries fail.

        (Cheolsoo Park via Jarek Jarcec Cecho) (Revision 1309268)

        Result = SUCCESS
        jarcec :
        Files :

        • /sqoop/trunk/src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java
        • /sqoop/trunk/src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java
        • /sqoop/trunk/src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java
        • /sqoop/trunk/src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java
        Show
        Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6 #106 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6/106/ ) SQOOP-468 . Oracle free form queries fail. (Cheolsoo Park via Jarek Jarcec Cecho) (Revision 1309268) Result = SUCCESS jarcec : Files : /sqoop/trunk/src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java /sqoop/trunk/src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java /sqoop/trunk/src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java /sqoop/trunk/src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java
        Hide
        Jarek Jarcec Cecho added a comment -

        Patch committed, thank you Cheolsoo!

        Show
        Jarek Jarcec Cecho added a comment - Patch committed, thank you Cheolsoo!
        Hide
        jiraposter@reviews.apache.org added a comment -

        On 2012-04-03 19:34:16, Jarek Cecho wrote:

        > Hi Cheolsoo,

        > your changes looks good to me.

        >

        > Jarcec

        Please attach your patch to the JIRA and I'll commit it.

        • Jarek

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4621/#review6661
        -----------------------------------------------------------

        On 2012-04-03 06:47:20, Cheolsoo Park wrote:

        -----------------------------------------------------------

        This is an automatically generated e-mail. To reply, visit:

        https://reviews.apache.org/r/4621/

        -----------------------------------------------------------

        (Updated 2012-04-03 06:47:20)

        Review request for Sqoop and Jarek Cecho.

        Summary

        -------

        Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb.

        https://issues.apache.org/jira/browse/SQOOP-468

        The changes include:

        1) Use alias 't1' instead of table name in the qualified names.

        2) Add 3 unit tests for Hsqldb, MySQL, and Oracle.

        This addresses bug SQOOP-468.

        https://issues.apache.org/jira/browse/SQOOP-468

        Diffs

        -----

        /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION

        /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION

        /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION

        /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555

        Diff: https://reviews.apache.org/r/4621/diff

        Testing

        -------

        1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs.

        2) ant test, ant test -Dthirdparty=true, and ant checkstyle

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - On 2012-04-03 19:34:16, Jarek Cecho wrote: > Hi Cheolsoo, > your changes looks good to me. > > Jarcec Please attach your patch to the JIRA and I'll commit it. Jarek ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/#review6661 ----------------------------------------------------------- On 2012-04-03 06:47:20, Cheolsoo Park wrote: ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/ ----------------------------------------------------------- (Updated 2012-04-03 06:47:20) Review request for Sqoop and Jarek Cecho. Summary ------- Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb. https://issues.apache.org/jira/browse/SQOOP-468 The changes include: 1) Use alias 't1' instead of table name in the qualified names. 2) Add 3 unit tests for Hsqldb, MySQL, and Oracle. This addresses bug SQOOP-468 . https://issues.apache.org/jira/browse/SQOOP-468 Diffs ----- /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555 Diff: https://reviews.apache.org/r/4621/diff Testing ------- 1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs. 2) ant test, ant test -Dthirdparty=true, and ant checkstyle Thanks, Cheolsoo
        Hide
        jiraposter@reviews.apache.org added a comment -

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4621/#review6661
        -----------------------------------------------------------

        Ship it!

        Hi Cheolsoo,
        your changes looks good to me.

        Jarcec

        • Jarek

        On 2012-04-03 06:47:20, Cheolsoo Park wrote:

        -----------------------------------------------------------

        This is an automatically generated e-mail. To reply, visit:

        https://reviews.apache.org/r/4621/

        -----------------------------------------------------------

        (Updated 2012-04-03 06:47:20)

        Review request for Sqoop and Jarek Cecho.

        Summary

        -------

        Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb.

        https://issues.apache.org/jira/browse/SQOOP-468

        The changes include:

        1) Use alias 't1' instead of table name in the qualified names.

        2) Add 3 unit tests for Hsqldb, MySQL, and Oracle.

        This addresses bug SQOOP-468.

        https://issues.apache.org/jira/browse/SQOOP-468

        Diffs

        -----

        /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION

        /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION

        /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION

        /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555

        Diff: https://reviews.apache.org/r/4621/diff

        Testing

        -------

        1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs.

        2) ant test, ant test -Dthirdparty=true, and ant checkstyle

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/#review6661 ----------------------------------------------------------- Ship it! Hi Cheolsoo, your changes looks good to me. Jarcec Jarek On 2012-04-03 06:47:20, Cheolsoo Park wrote: ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/ ----------------------------------------------------------- (Updated 2012-04-03 06:47:20) Review request for Sqoop and Jarek Cecho. Summary ------- Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb. https://issues.apache.org/jira/browse/SQOOP-468 The changes include: 1) Use alias 't1' instead of table name in the qualified names. 2) Add 3 unit tests for Hsqldb, MySQL, and Oracle. This addresses bug SQOOP-468 . https://issues.apache.org/jira/browse/SQOOP-468 Diffs ----- /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555 Diff: https://reviews.apache.org/r/4621/diff Testing ------- 1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs. 2) ant test, ant test -Dthirdparty=true, and ant checkstyle Thanks, Cheolsoo
        Hide
        jiraposter@reviews.apache.org added a comment -

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4621/
        -----------------------------------------------------------

        (Updated 2012-04-03 06:47:20.080415)

        Review request for Sqoop and Jarek Cecho.

        Changes
        -------

        Factor out code that constructs the boundary query into a separate function.

        Summary
        -------

        Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb.

        https://issues.apache.org/jira/browse/SQOOP-468

        The changes include:
        1) Use alias 't1' instead of table name in the qualified names.
        2) Add 3 unit tests for Hsqldb, MySQL, and Oracle.

        This addresses bug SQOOP-468.
        https://issues.apache.org/jira/browse/SQOOP-468

        Diffs (updated)


        /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION
        /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION
        /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION
        /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555

        Diff: https://reviews.apache.org/r/4621/diff

        Testing
        -------

        1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs.
        2) ant test, ant test -Dthirdparty=true, and ant checkstyle

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/ ----------------------------------------------------------- (Updated 2012-04-03 06:47:20.080415) Review request for Sqoop and Jarek Cecho. Changes ------- Factor out code that constructs the boundary query into a separate function. Summary ------- Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb. https://issues.apache.org/jira/browse/SQOOP-468 The changes include: 1) Use alias 't1' instead of table name in the qualified names. 2) Add 3 unit tests for Hsqldb, MySQL, and Oracle. This addresses bug SQOOP-468 . https://issues.apache.org/jira/browse/SQOOP-468 Diffs (updated) /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555 Diff: https://reviews.apache.org/r/4621/diff Testing ------- 1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs. 2) ant test, ant test -Dthirdparty=true, and ant checkstyle Thanks, Cheolsoo
        Hide
        Cheolsoo Park added a comment -

        It turns out that this is an issue not only for Oracle but also for MySQL and Hsqldb. So I made a change to SqlManager rather than OracleManager and added 3 new unit tests against Oracle, MySQL, and Hsqldb.

        Show
        Cheolsoo Park added a comment - It turns out that this is an issue not only for Oracle but also for MySQL and Hsqldb. So I made a change to SqlManager rather than OracleManager and added 3 new unit tests against Oracle, MySQL, and Hsqldb.
        Hide
        jiraposter@reviews.apache.org added a comment -

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4621/
        -----------------------------------------------------------

        Review request for Sqoop and Jarek Cecho.

        Summary
        -------

        Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb.

        https://issues.apache.org/jira/browse/SQOOP-468

        The changes include:
        1) Use alias 't1' instead of table name in the qualified names.
        2) Add 3 unit tests for Hsqldb, MySQL, and Oracle.

        This addresses bug SQOOP-468.
        https://issues.apache.org/jira/browse/SQOOP-468

        Diffs


        /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION
        /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION
        /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555
        /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION

        Diff: https://reviews.apache.org/r/4621/diff

        Testing
        -------

        1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs.
        2) ant test, ant test -Dthirdparty=true, and ant checkstyle

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4621/ ----------------------------------------------------------- Review request for Sqoop and Jarek Cecho. Summary ------- Sqoop free-form query import does not work with Oracle, MySQL, and Hsqldb. https://issues.apache.org/jira/browse/SQOOP-468 The changes include: 1) Use alias 't1' instead of table name in the qualified names. 2) Add 3 unit tests for Hsqldb, MySQL, and Oracle. This addresses bug SQOOP-468 . https://issues.apache.org/jira/browse/SQOOP-468 Diffs /src/test/com/cloudera/sqoop/manager/MySQLFreeFormQueryTest.java PRE-CREATION /src/test/com/cloudera/sqoop/manager/OracleFreeFormQueryTest.java PRE-CREATION /src/java/org/apache/sqoop/mapreduce/DataDrivenImportJob.java 1308555 /src/test/com/cloudera/sqoop/TestFreeFormQueryImport.java PRE-CREATION Diff: https://reviews.apache.org/r/4621/diff Testing ------- 1) The new tests verify that import with a simple free-form query that includes join works with 3 different DBs. 2) ant test, ant test -Dthirdparty=true, and ant checkstyle Thanks, Cheolsoo
        Hide
        Cheolsoo Park added a comment -

        Hi Jarek,

        Thank you very much for your suggestion. Indeed, that sounds like a better solution. Looking at the query that Sqoop executes, I also noticed that it already has a name 't1' for the intermediate output even though it is not being used now. Let me see if I can make use of it.

        Show
        Cheolsoo Park added a comment - Hi Jarek, Thank you very much for your suggestion. Indeed, that sounds like a better solution. Looking at the query that Sqoop executes, I also noticed that it already has a name 't1' for the intermediate output even though it is not being used now. Let me see if I can make use of it.
        Hide
        Jarek Jarcec Cecho added a comment -

        Hi Cheolsoo,
        thank you very much for looking at this issue. I have another idea that I would like to mention - I would suggest to name the intermediate output and use that name. Something like:

        SELECT MIN(FREEFORMQUERY.ID), MAX(FREEFORMQUERY.ID) FROM (SELECT TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND  (1 = 1) ) FREEFORMQUERY;
        
        Show
        Jarek Jarcec Cecho added a comment - Hi Cheolsoo, thank you very much for looking at this issue. I have another idea that I would like to mention - I would suggest to name the intermediate output and use that name. Something like: SELECT MIN(FREEFORMQUERY.ID), MAX(FREEFORMQUERY.ID) FROM (SELECT TESTTABLE.ID,TESTTABLE2.FNAME FROM TESTTABLE JOIN TESTTABLE2 ON (TESTTABLE.ID = TESTTABLE2.ID ) WHERE TESTTABLE.ID < 3 AND (1 = 1) ) FREEFORMQUERY;
        Hide
        Cheolsoo Park added a comment -

        The exception is thrown while executing the bounding value query:

        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) );
        

        If I execute this query in Oracle SQL, it gives the following error:

        ORA-00904: "TESTTABLE"."ID": invalid identifier.
        

        But if I drop the "TESTTABLE" prefix from the column names in the select clause of the outer query as follows:

        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) );
        

        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.

        Show
        Cheolsoo Park added a comment - The exception is thrown while executing the bounding value query: 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) ); If I execute this query in Oracle SQL, it gives the following error: ORA-00904: "TESTTABLE" . "ID" : invalid identifier. But if I drop the "TESTTABLE" prefix from the column names in the select clause of the outer query as follows: 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) ); 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.

          People

          • Assignee:
            Cheolsoo Park
            Reporter:
            Prashant Gokhale
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development