question Re: Error in using Sqoop in Support Questions

Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:09:27 GMT

Hi,

HDP 2.3.4.0 cluster in 4 nodes has been set up and it is working fine. We are trying to evaluate Atlas using demo in below link:

https://github.com/shivajid/atlas/tree/master/tutorial

We are facing issue while using Sqoop during execution of above demo. Full description of issue is as below:

1) Sqoop and MySql databases are in different hosts.

2) MySql jar is in both the hosts and also in lib folder of sqoop home /usr/hdp/2.3.4.0-3485/sqoop/lib/

3) Have provided below privilege in MySql to avoid privilege issues:

grant all privileges on *.* to 'trucker1'@'%' identified by 'trucker';

flush privileges;

4) Now trying to import data using below command:

sqoop import --connect jdbc:mysql://3.209.124.206:3306/test --username trucker1 --password trucker --table DRIVERS -m 1 --target-dir demo$1 --hive-import --hive-table hortondrivers$1

5) Facing below error when executing above command.

16/02/19 18:20:45 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 16/02/19 18:20:45 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 16/02/19 18:20:45 INFO tool.CodeGenTool: Beginning code generation 16/02/19 18:20:45 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:636) at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52) at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:525) at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:548) at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:191) at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:175) at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:262) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1235) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1060) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476) at org.apache.sqoop.Sqoop.run(Sqoop.java:145) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) 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)

Looking for your help as we need to understand how Atlas work for data governance.

Thanks!

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:09:32 GMT

place the mysql-connector-java.jar into /usr/hdp/sqoop-client/lib @rajdip chaudhuri review the docs https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.2/bk_dataintegration/content/ch_using-sqoop.html

Re: Error in using Sqoop

nsabharwal — Fri, 19 Feb 2016 21:11:00 GMT

@rajdip chaudhuri

See this

Could not load db driver class: com.mysql.jdbc.Driver java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver at org.apache.sqoop.manager.SqlManager.makeConnection

ls -l /usr/share/java/mysql* and http://stackoverflow.com/questions/22741183/sqoop-could-not-load-mysql-driver-exception

[root@phdns02 ~]# cd /usr/hdp/current/sqoop-client/lib/

You have mail in /var/spool/mail/root

[root@phdns02 lib]# ls -l mysql-connector-java.jar

lrwxrwxrwx. 1 root root 40 Feb 14 19:38 mysql-connector-java.jar -> /usr/share/java/mysql-connector-java.jar

[root@phdns02 lib]# pwd

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

[root@phdns02 lib]#

Re: Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:19:14 GMT

Hi @Neeraj Sabharwal & @Artem Ervits thanks for your points but the jar is already at the mentioned path. Attaching the screenshot.

sqoop-lib.jpg

Re: Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:21:19 GMT

Jar is already at the mentioned path. Screenshot has been attached.

sqoop-lib.jpg

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:21:34 GMT

@rajdip chaudhuri

give it right permissions, and you can also specify it explicitly

you can use Sqoop with any other JDBC-compliant database. First, download the appropriate JDBC driver for the type of database you want to import, and install the .jar file in the $SQOOP_HOME/lib directory on your client machine. (This will be /usr/lib/sqoop/lib if you installed from an RPM or Debian package.) Each driver .jar file also has a specific driver class which defines the entry-point to the driver. For example, MySQL’s Connector/J library has a driver class of com.mysql.jdbc.Driver. Refer to your database vendor-specific documentation to determine the main driver class. This class must be provided as an argument to Sqoop with --driver.

Re: Error in using Sqoop

nsabharwal — Fri, 19 Feb 2016 21:23:04 GMT

@rajdip chaudhuri Let's check the versions ...

http://stackoverflow.com/questions/29938694/error-sqoop-sqoop-got-exception-running-sqoop-java-lang-runtimeexception-coul

You have to make sure that we don't have version mismatch.

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:28:50 GMT

@rajdip chaudhuri look at my response right above and see this https://community.hortonworks.com/questions/2879/bug-in-sandbox-232-hitting-sqoop-1400.html

Re: Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:33:25 GMT

With HDp 2.3.4.0 sqoop of version 1.4.6.2.3 comes. My sql jar file was downloaded through yum during cluster set up. However, downloading the latest jar file frm https://dev.mysql.com/downloads/file/?id=13520.

Will let you know if this jar file works or not.

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:33:36 GMT

@rajdip chaudhuri please see the link https://community.hortonworks.com/questions/2879/bug-in-sandbox-232-hitting-sqoop-1400.html

Re: Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:41:48 GMT

Thanks @Artem Ervits. I can see the current version is mysql-connector-java-5.1.17-6.el6.noarch. Downloading the latest version from mysql site. Will let you know the results if the latest jar works or not.

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:43:24 GMT

yes but see the note about using --driver and specifying class name. @rajdip chaudhuri

Re: Error in using Sqoop

rajdip_chaudhur — Fri, 19 Feb 2016 21:48:30 GMT

Hi @Artem Ervits facing same issue after using --driver in the sqoop command. Currently downloading the latest jar file. Shall we go ahead and replace the old one with this new one?

Command used:

sqoop import --connect jdbc:mysql://3.209.124.206:3306/test --username trucker1 --password trucker --table DRIVERS -m 1 --driver com.mysql.jdbc.Driver --target-dir demo$1 --hive-import --hive-table hortondrivers$1

Re: Error in using Sqoop

aervits — Fri, 19 Feb 2016 21:58:00 GMT

@rajdip chaudhuri yes try the new jar

Re: Error in using Sqoop

aervits — Tue, 23 Feb 2016 10:39:40 GMT

@rajdip chaudhuri did you get it resolved?

Re: Error in using Sqoop

pminovic — Tue, 23 Feb 2016 11:39:59 GMT

Hi @rajdip chaudhuri, mysql-connector-java.jar in /usr/hdp/sqoop-client/lib is a symlink to /usr/share/java/mysql-connector-java.jar which itself is a symlink to the real jar file with a version in its name. Make sure it points to the right file:

ls -l /usr/share/java/mysql-connector-java.jar

and ispect the destination file. Also, mysql-connecter that can be installed by yum is good for Mysql-5.1, but for 5.5 and 5.6 you need the latest version.

Re: Error in using Sqoop

nsabharwal — Tue, 23 Feb 2016 13:49:14 GMT

@rajdip chaudhuri

Re: Error in using Sqoop

Sreedhar_ch — Tue, 20 Sep 2016 22:13:53 GMT

Just adding my two cents as I faced the same issue today..and able to resolve..After downloading the JAR file while copying this jar file into linux box I used winscp with transfer setting as default instead of using binary ..caused could not load DB drviers after I copied it back with binary it worked fine..please try it..

Re: Error in using Sqoop

kczhen — Fri, 15 Dec 2017 13:53:05 GMT

I'm facing same problem with you, and I tried all following methods, but it doesn't work. So how you solved the problem?@rajdip chaudhuri

Re: Error in using Sqoop

kczhen — Sun, 18 Aug 2019 13:03:14 GMT

Did you solved the problem? I'm facing the same problem with you. And I tried all methods mentioned above, but it didn't work

Here is my exceptions

[root@cos1 ~]# sqoop list-tables --connect jdbc:mysql://192.168.2.190/experiment3 --username scott -P

17/12/15 19:17:36 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 Enter password: 17/12/15 19:17:41

INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 17/12/15 19:17:41

ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver

java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver

at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:856)

at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)

at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)

at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49) at org.apache.sqoop.Sqoop.run(Sqoop.java:143)

at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)

at org.apache.sqoop.Sqoop.main(Sqoop.java:236)

Here is my screenshot

I'm using sqoop-1.4.6, hadoop-2.7.4 and mysql-connecctor-java-5.1.38.jar

@rajdip chaudhuri

@Artem Ervits