Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-1959

sqoop failed to import data from oracle whether specify the driver option or not

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 1.4.5
    • Fix Version/s: None
    • Component/s: connectors/oracle
    • Labels:
      None
    • Environment:

      CentOS release 5.5
      sqoop : 1.4.5
      hive: 0.11.0
      hadoop: 1.2.1
      jdk: 1.7.0_71
      oracle: 10.0.1.0
      oracle jdbc driver: ojdbc6.jar

      Description

      The ojdbc6.jar works fine through the PLSQL client. While trying to import data from oracle, the sqoop import didn't work whether specifying the -driver option or not.

      (1) use the default driver (oracle.jdbc.OracleDriver ),

      a) can't get tables.
      sqoop list-tables --connect jdbc:oracle:thin:@IP:HOST:DB01 --username XX --password XX

      14/12/26 05:04:32 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
      14/12/26 05:04:32 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
      14/12/26 05:04:32 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
      14/12/26 05:04:32 INFO manager.SqlManager: Using default fetchSize of 1000
      14/12/26 05:04:33 INFO manager.OracleManager: Time zone has been set to GMT
      

      b)
      ERROR tool.ImportTool: Imported Failed: There is no column found in the target table TABLE01. Please ensure that your table name is correct.

      sqoop import --driver oracle.jdbc.driver.OracleDriver --hive-import --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX --verbose -m 1 --table TABLE01

      ......
      14/12/26 05:36:55 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@b15b5a
      14/12/26 05:36:55 INFO tool.CodeGenTool: Beginning code generation
      14/12/26 05:36:55 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM TABLE01 t WHERE 1=0
      14/12/26 05:36:55 DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM TABLE01 t WHERE 1=0
      14/12/26 05:36:56 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@IP:HOST:SID, using username: XX
      14/12/26 05:36:56 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
      14/12/26 05:36:56 INFO manager.OracleManager: Time zone has been set to GMT
      14/12/26 05:36:56 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
      14/12/26 05:36:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM TABLE01 t WHERE 1=0
      {color:red}
      14/12/26 05:36:56 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@IP:HOST:SID/XX
      14/12/26 05:36:56 ERROR tool.ImportTool: Imported Failed: There is no column found in the target table TABLE01. Please ensure that your table name is correct.
      {color}
      

      (2) specify the "--driver " option: --driver oracle.jdbc.driver.OracleDriver

      a) correctly list the tables of DB01
      sqoop list-tables --driver oracle.jdbc.driver.OracleDriver --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX

      14/12/26 05:32:13 INFO manager.SqlManager: Using default fetchSize of 1000
      WWV_FLOW_DUAL100
      WWV_FLOW_LOV_TEMP
      WWV_FLOW_TEMP_TABLE
      TABLE01
      ......
      

      b) same as issues https://issues.apache.org/jira/browse/SQOOP-457, ORA-00933: SQL command not properly ended
      sqoop import --driver oracle.jdbc.driver.OracleDriver --hive-import --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX --verbose -m 1 --table TABLE01

      14/12/26 05:33:49 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM TABLE01 AS t WHERE 1=0
      14/12/26 05:33:49 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
      java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
      	......
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              coodoing daniel
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 72h
                72h
                Remaining:
                Remaining Estimate - 72h
                72h
                Logged:
                Time Spent - Not Specified
                Not Specified