Sqoop: Could not load mysql driver exception

Question

I Installed Sqoop in my local machine. Following are the config information.

Bash.bashrc:

export HADOOP_HOME=/home/hduser/hadoop
export HBASE_HOME=/home/hduser/hbase
export HIVE_HOME=/home/hduser/hive
export HCAT_HOME=/home/hduser/hive/hcatalog
export SQOOP_HOME=/home/hduser/sqoop

export PATH=$PATH:$HIVE_HOME/bin
export PATH=$PATH:$HADOOP_HOME/bin
export PATH=$PATH:$HBASE_HOME/bin
export PATH=$PATH:$SQOOP_HOME/bin
export PATH=$PATH:$HCAT_HOME/bin

Hadoop:

Version: Hadoop 1.0.3

Hive:

Version: hive 0.11.0 

Mysql Connector driver

version: mysql-connector-java-5.1.29

"The driver is copied to the lib folder of sqoop"

Sqoop :

version: sqoop 1.4.4

After making all the installation I create a table in mysql named practice_1, But when I run the load command to load data from mysql to hdfs the command throws an exception:

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

Coud anyone please guide me what can be the possible problem.

Gamma

Can you please provide the sqoop command which you are running. ? – Fingerboard

Here you go : the Scoop command I provided is ::: /home/hduser/sqoop/bin/sqoop import -connect 'jdbc:mysql://localhost:3306/test' --username root --password root -–table ‘practice_1’ --m 1; – Gamma

Answer 1

You need database driver in 'SQOOP' classpath check this It has wonderful explanation about the 'SQOOP'

SQOOP has other options like

Ex: --driver com.microsoft.jdbc.sqlserver.SQLServerDriver -libjars=".*jar"

from here

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.

You may be interested in understanding the difference between connector and driver here is the article

Motherland

Answer 2

Another solution which avoids using a shared library is adding the driver jar to the classpath of sqoop by using HADOOP_CLASSPATH. I haven't got the -libjars option to work. This solution works also on a secure cluster using kerberos.

HADOOP_CLASSPATH=/use.case/lib/postgresql-9.2-1003-jdbc4.jar
sqoop export --connect jdbc:postgresql://db:5432/user \
  --driver org.postgresql.Driver \
  --connection-manager org.apache.sqoop.manager.GenericJdbcManager \
  --username user \
  -P \
  --export-dir /user/hive/warehouse/db1/table1 \
  --table table2

This one works at least with sqoop 1.4.3-cdh4.4.0

Searching

+1 I would argue that this is much cleaner than copying your jdbc jar to any lib directory and may well be the only option if you don't have write access to those lib directories. – Trout

Answer 3

You need to add the MySql connector to /usr/lib/sqoop/lib. MySQL JDBC Driver by default is not present in Sqoop distribution in order to ensure that the default distribution is fully Apache license compliant. Hope this helps...!!!

Joshia

Answer 4

copy the 'mysql-connector-java-5.1.41-bin.jar' into sqoop/lib folder and execute sqoop import statements

Multiply

Answer 5

If you have copied mysql driver to the sqoop lib folder. It will work for sure. Make sure you sqoop command is correct

/home/hduser/sqoop/bin/sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password root -–table practice_1 -m 1

Regeneracy

Where can i find the mysql driver in hadoop?? I'm using ubuntu 16.04 – Goldengoldenberg

Answer 6

It's a Oozie ShareLib problem. The script below works for my:

At Shell

sudo -u hdfs hadoop fs -chown cloudera:cloudera /user/oozie/share/lib/lib_20170719053712/sqoop
hdfs dfs -put /var/lib/sqoop/mysql-connector-java.jar /user/oozie/share/lib/lib_20170719053712/sqoop
sudo -u hdfs hadoop fs -chown oozie:oozie /user/oozie/share/lib/lib_20170719053712/sqoop

oozie admin -oozie http://localhost:11000/oozie -sharelibupdate
oozie admin -oozie http://localhost:11000/oozie -shareliblist sqoop

At Hue Sqoop Client

sqoop list-tables --connect jdbc:mysql://localhost/retail_db --username root --password cloudera

More detail at:

https://blog.cloudera.com/blog/2014/05/how-to-use-the-sharelib-in-apache-oozie-cdh-5/

Samualsamuel

Answer 7

You need to grant priveleges to the tables as below:

grant all privileges on marksheet.* to 'root'@'192.168.168.1' identified by 'root123';

flush privileges;

Here is sample command that I have successfully executed:

sqoop import --verbose --fields-terminated-by ',' --connect jdbc:mysql://192.168.168.1/test --username root --password root123 --table student --hive-import --create-hive-table --hive-home /home/training/hive --warehouse-dir /user/hive/warehouse --fields-terminated-by ',' --hive-table studentmysql

Dualpurpose