Sqoop error with Oracle: Could not load db driver class: oracle.jdbc.OracleDriver

[#1]

Hi All,

I am having an issue with sqoop against an Oracle database. HDP2.4. Oracle 11 XE in AWS RDS.

[root@ip-172-31-29-111 lib]# sqoop list-tables --connect jdbc:oracle:thin:@//dbip:dbport/dbname --username myusername -password mypassword
Warning: /usr/hdp/2.4.2.0-258/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/09/02 15:23:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.4.2.0-258
16/09/02 15:23:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/09/02 15:23:54 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
16/09/02 15:23:54 INFO manager.SqlManager: Using default fetchSize of 1000
16/09/02 15:23:54 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:287)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:745)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

The equivalent against a PostgreSQL works fine.

The below is the list of jar files under sqoop library directory.

[root@ip-172-31-29-111 lib]# ls /usr/hdp/current/sqoop-client/lib/
ant-contrib-1.0b3.jar  commons-jexl-2.1.1.jar  jackson-mapper-asl-1.9.13.jar  opencsv-2.3.jar  parquet-generator-1.4.1.jar
ant-eclipse-1.0-jvm1.2.jar  commons-logging-1.1.1.jar  kite-data-core-1.0.0.jar  paranamer-2.3.jar  parquet-hadoop-1.4.1.jar
avro-1.7.5.jar  hsqldb-1.8.0.10.jar  kite-data-hive-1.0.0.jar  parquet-avro-1.4.1.jar  parquet-jackson-1.4.1.jar
avro-mapred-1.7.5-hadoop2.jar
  jackson-annotations-2.3.0.jar  kite-data-mapreduce-1.0.0.jar  
parquet-column-1.4.1.jar  postgresql-9.2-1002.jdbc4.jar
commons-codec-1.4.jar
  jackson-core-2.3.1.jar  kite-hadoop-compatibility-1.0.0.jar  
parquet-common-1.4.1.jar  slf4j-api-1.6.1.jar
commons-compress-1.4.1.jar
  jackson-core-asl-1.9.13.jar  mysql-connector-java.jar  
parquet-encoding-1.4.1.jar  snappy-java-1.0.5.jar
commons-io-1.4.jar  jackson-databind-2.3.1.jar  ojdbc6.jar 

Earlier I have downloaded and copied the oracle jdbc connector jar file from the below Oracle page.

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

Please advise the solution to avoid the error.

Thanks,

[#2]

you need ojdbc6.jar or ojdbc7.jar (depending on your oracle version) copied to sqoop/lib directory

[#3]

Thanks vranganathan,

I believe /usr/hdp/current/sqoop-client/lib/, specified above is the lib directory. Is it not?

This is vanilla installation of HDP2.4, and I haven't done any additional sqoop configuration.

Regards,

[#4]

Oracle and other proprietary drivers (like DB2, netezza, etc) need to be downloaded and installed by the user to the sqoop/lib directory by the installer. They are not shipped with the product.

[#5]

As of my original post, I have already uploaded those jdbc jar file in the library directory(

/usr/hdp/current/sqoop-client/lib/)

and sqoop against oracle still doesn't work.

[#6]

Make sure the permissions are right for the ojdbc.jar such that the user as whom you are running has access to the jar

[#7]

Thanks vranganathan,

As you can see in the below, ojdbc6.jar file has the same privilege(644 owner root) as all other preloaded drivers.

I took the exact the same steps for the postgresql driver, and sqoop against postgresql works fine. only oracle doesn't work.

[root@ip-172-31-29-145 lib]# ls -ltr
total 16164
-rw-r--r-- 1 root root 1245776 Apr 25 18:02 snappy-java-1.0.5.jar
-rw-r--r-- 1 root root   60530 Apr 25 18:02 commons-logging-1.1.1.jar
-rw-r--r-- 1 root root  266289 Apr 25 18:02 commons-jexl-2.1.1.jar
-rw-r--r-- 1 root root  770013 Apr 25 18:02 jackson-mapper-asl-1.9.13.jar
-rw-r--r-- 1 root root 1741973 Apr 25 18:02 kite-data-mapreduce-1.0.0.jar
-rw-r--r-- 1 root root  195678 Apr 25 18:02 jackson-core-2.3.1.jar
-rw-r--r-- 1 root root  224135 Apr 25 18:02 ant-contrib-1.0b3.jar
-rw-r--r-- 1 root root   94608 Apr 25 18:02 xz-1.0.jar
-rw-r--r-- 1 root root  241259 Apr 25 18:02 commons-compress-1.4.1.jar
-rw-r--r-- 1 root root   41899 Apr 25 18:03 parquet-avro-1.4.1.jar
-rw-r--r-- 1 root root  902327 Apr 25 18:03 jackson-databind-2.3.1.jar
-rw-r--r-- 1 root root 1015097 Apr 25 18:03 parquet-jackson-1.4.1.jar
-rw-r--r-- 1 root root 2113186 Apr 25 18:03 kite-data-core-1.0.0.jar
-rw-r--r-- 1 root root  108990 Apr 25 18:03 commons-io-1.4.jar
-rw-r--r-- 1 root root 1774917 Apr 25 18:03 kite-data-hive-1.0.0.jar
-rw-r--r-- 1 root root   10378 Apr 25 18:03 parquet-generator-1.4.1.jar
-rw-r--r-- 1 root root  466100 Apr 25 18:03 parquet-format-2.0.0.jar
-rw-r--r-- 1 root root   19816 Apr 25 18:03 opencsv-2.3.jar
-rw-r--r-- 1 root root  706015 Apr 25 18:03 hsqldb-1.8.0.10.jar
-rw-r--r-- 1 root root   36427 Apr 25 18:03 ant-eclipse-1.0-jvm1.2.jar
-rw-r--r-- 1 root root   58131 Apr 25 18:03 commons-codec-1.4.jar
-rw-r--r-- 1 root root  713848 Apr 25 18:03 parquet-column-1.4.1.jar
-rw-r--r-- 1 root root   11359 Apr 25 18:03 parquet-common-1.4.1.jar
-rw-r--r-- 1 root root  400090 Apr 25 18:03 avro-1.7.5.jar
-rw-r--r-- 1 root root  152255 Apr 25 18:03 parquet-hadoop-1.4.1.jar
-rw-r--r-- 1 root root  272869 Apr 25 18:03 parquet-encoding-1.4.1.jar
-rw-r--r-- 1 root root 1740983 Apr 25 18:04 kite-hadoop-compatibility-1.0.0.jar
-rw-r--r-- 1 root root   25477 Apr 25 18:04 slf4j-api-1.6.1.jar
-rw-r--r-- 1 root root   34047 Apr 25 18:04 jackson-annotations-2.3.0.jar
-rw-r--r-- 1 root root  170228 Apr 25 18:04 avro-mapred-1.7.5-hadoop2.jar
-rw-r--r-- 1 root root  229445 Apr 25 18:04 jackson-core-asl-1.9.13.jar
-rw-r--r-- 1 root root   29532 Apr 25 18:04 paranamer-2.3.jar
-rw-r--r-- 1 root root  579785 Sep  2 14:21 postgresql-9.2-1002.jdbc4.jar
-rw-r--r-- 1 root root    2123 Sep  2 14:31 ojdbc6.jar
-rw-r--r-- 1 root root   20570 Sep  2 16:24 optim_items.java
lrwxrwxrwx 1 root root      40 Sep  5 09:50 mysql-connector-java.jar -> /usr/share/java/mysql-connector-java.jar
[root@ip-172-31-29-145 lib]# 

[#8]

@WONHEE CHOI

Place the odbc6.jar in /usr/lib/sqoop/lib and retry. If it does not pick-up the jar file, restart Sqoop server and try again.

If any of the responses to your question addressed the problem, don't forget to vote and accept the answer. If you fix the issue on your own, don't forget to post the answer to your own question. A moderator will review it and accept it.

[#9]

Thanks Constantin,

There is no such directory /usr/lib/sqoop/lib.

I have been restarted the service as well as the entire cluster several times along the way. So it shouldn't be the problem. Postgresql driver, FYI, worked immediately after placing into the directory above without service restart.

[root@ip-172-31-29-145 lib]# cd sqoop
-bash: cd: sqoop: No such file or directory
[root@ip-172-31-29-145 lib]# pwd
/usr/lib
[root@ip-172-31-29-145 lib]# cd sqoop
-bash: cd: sqoop: No such file or directory

Sure, I will certainly vote and accept, if there is any actual benefit in the answers. In the mean time I deeply appreciate everyone involved in the conversation.

[#10]

@WONHEE CHOI

1) Make me a favor and create that directory /usr/lib/sqoop/lib, and place your odbc6.jar there and try.

2) Also, what is your SQOOP_HOME set as an environment variable?

3) Also, sqoop has --driver option where you can set the driver explicitly. Under normal conditions, if you have the SQOOP_HOME environment variable set for your sqoop user and the library is placed to the /lib folder, you shouldn't need this step 3.

Let me know.