Home Forums Sqoop Oracle and Sqoop

This topic contains 11 replies, has 5 voices, and was last updated by  Robert Volker 6 months, 2 weeks ago.

  • Creator
    Topic
  • #43062

    I installed HDP 2 thru Ambari, so I believe i have sqoop 1.4.4
    The sqoop is working fine with mysql thats in the cluster, Now I am trying to connect to a Oracle database , thats outside the cluster , in a warehouse, to bring data into HDFS
    This is what I did to test the connection, I brought in the ojdbc6.jar file onto /usr/lib/sqoop/lib and then entered the below statement

    sqoop list-tables –connect jdbc:oracle:thin:@ipaddress:port/db –username name -P — –schema schemaname

    It asked for the pwd,
    13/11/06 12:03:18 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
    Enter password:
    13/11/06 12:03:22 INFO manager.SqlManager: Using default fetchSize of 1000
    13/11/06 12:03:22 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
    java.lang.NullPointerException
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:698)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:240)

    When I tried with the replacing the ipaddress with the host in the tnsnames
    It brought me the below info but no error, but answer too ?

    Is the syntax I am using correct ? Where is the log for sqoop ?

    13/11/06 12:05:01 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
    Enter password:
    13/11/06 12:05:04 INFO manager.SqlManager: Using default fetchSize of 1000
    13/11/06 12:05:05 INFO manager.OracleManager: Time zone has been set to GMT

    Please advice

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Oracle and Sqoop’ is closed to new replies.

  • Author
    Replies
  • #46524

    Robert Volker
    Participant

    Koelli,
    Thank you, I, now, have the ojdbc6 corrrectly loaded and used by sqoop (I write this with much embarassment, was an easy fix).

    I, too, ran into the same “connectivity” problems originally reported by Sivagamasundari Veerabahu.

    Database connectivity tests:
    1. ping and telnet successfully connected to the oracle box and oracle, respectively.
    2. even used a test app to prove ojdbc6 successfully connect via ‘jdbc:oracle:thin:@xxx:<port>:<sid>’

    Reminders:
    1. Oracle JDBC Thin using service name: jdbc:oracle:thin:@//<host>:<port>/<service_name>
    2. Oracle JDBC Thin using SID: jdbc:oracle:thin:@<host>:<port>:<SID>
    3. Double check database credentials!

    NPE is not a very helpful error message, which threw me way off course.

    Again thank you,
    Robert

    Collapse
    #46478

    Koelli Mungee
    Moderator

    Hi Robert,

    Can you verify that the driver file is the correct size in bytes and look at the permissions on the file. Perhaps add the output of the ls -al command to show the file?

    thanks
    koelli

    Collapse
    #44796

    Robert Volker
    Participant

    I hate to piggyback on an issue, but I have the same problem with Oracle and Sqoop and this thread doesn’t seem have been resolved conclusively. I too have tried the recommendations but not the tns access.

    - Connection to Oracle is good and has been proved with: ping, telnet, and a small test java app that uses the ojdbc6 running in the exact context as sqoop (path & lib).

    - I’ve demonstrated that the same jvm is used with sqoop & hdfs, as well as my successful test app can connect to Oracle. Yet sqoop throws an exception.

    - Tried running under different users: root, hdfs. Doesn’t seem like user rights, or have I missed something?
    #!/bin/bash
    export PATH=/usr/jdk64/jdk1.6.0_31/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/hdfs/bin:/usr/lib/sqoop/lib
    echo $PATH
    sqoop list-tables –connect jdbc:oracle:thin@xxx:1521:cdb –username xxx –password xxxx

    /usr/jdk64/jdk1.6.0_31/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/hdfs/bin:/usr/lib/sqoop/lib
    13/11/21 09:47:28 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
    13/11/21 09:47:28 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
    13/11/21 09:47:29 INFO manager.SqlManager: Using default fetchSize of 1000
    13/11/21 09:47:29 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.OracleDriver
    java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.OracleDriver
    at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:279)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:684)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:240)

    I really would like to use sqoop, but I’m kinda stuck.

    Thanks,
    Robert

    Collapse
    #43453

    Robert Molina
    Moderator

    Hi Sivagamasundari ,
    It looks like a connection issue. Make sure you can ping as well as telnet to the oracle database from the node where sqoop is running.

    Regards,
    Robert

    Collapse
    #43420

    Kenny,
    I think I finally got the information you wanted…
    I tried to import instead of list tables and got this error. I have the ojdc6.jar file in sqoop lib dir
    The database i am trying to connect is outside the cluster ,Please advice,

    ERROR manager.SqlManager: Error executing statement: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
    java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:314)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:660)
    at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:683)
    at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:240)
    at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:223)
    at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
    at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
    at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
    at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:502)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
    Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:439)
    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:454)
    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
    … 24 more
    Caused by: java.net.ConnectException: Connection refused

    Collapse
    #43413

    What do you mean by tnsname ?
    Can you please be more specific?

    I tried the host name as in tnsname and it did not work.
    I tried Ip address .. didnt work either.
    Please advice, can you please send the syntax

    Collapse
    #43276

    Kenny Zhang
    Moderator

    Hi Siva,

    Can you try to use the tnsname in your command?
    sqoop list-tables –connect jdbc:oracle:thin:@tnsname –username user -P xxx –verbose

    Please make sure the tnsname is specify in the tnsname.ora file which is accessible.

    Thanks,
    Kenny

    Collapse
    #43174

    Koelli,

    This is all the information I got in verbose..The first para in my previous email… Is there a log somewhere I can look for more info ?

    Collapse
    #43168

    Koelli Mungee
    Moderator

    Hi Siva,

    Do you have the full stack trace from the ip address attempt? I would like to see the “caused by” section to get more information.

    Regards
    Koelli

    Collapse
    #43154

    Thanks a lot for the response, I tried 2 ways,

    First time I gave the ipaddress and –verbose…got the below error
    sqoop list-tables –connect jdbc:oracle:thin:@ipaddress:port/db –username user -P –verbose

    INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
    DEBUG tool.BaseSqoopTool: Enabled debug logging.
    Enter password:
    DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
    DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
    DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@ipaddress
    DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
    INFO manager.SqlManager: Using default fetchSize of 1000
    DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@5cac6a45
    DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@ipaddress:port/db, using username: user
    DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
    ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
    java.lang.NullPointerException
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:698)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:240)

    Second time I used the hostname as in the tnsnames.ora file and got the below error.

    sqoop list-tables –connect jdbc:oracle:thin:@hostasintnsnames:port/db –username user -P –verbose
    INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
    DEBUG tool.BaseSqoopTool: Enabled debug logging.
    Enter password:
    DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
    DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
    DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@hostasintnsnames
    DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
    INFO manager.SqlManager: Using default fetchSize of 1000
    DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@3b75078b
    DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@hostasintnsnames:port/db, using username: user
    DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
    INFO manager.OracleManager: Time zone has been set to GMT

    Just a reminder cluster is completely isolated from the warehouse db, and I trying to connect to the warehouse oracle db from the cluster, after adding the jodbc6.jar in sqoop/lib
    Please advice

    Collapse
    #43104

    Koelli Mungee
    Moderator

    Hi Siva,

    Did you try passing in the –verbose option to see if you get more information? Can you try without passing the schema, just

    sqoop list-tables –connect jdbc:oracle:thin:@ipaddress:port/db –username name -P –verbose

    Thanks
    Koelli

    Collapse
Viewing 11 replies - 1 through 11 (of 11 total)