Underlying cause: java.lang.ClassNotFoundException : com.mysql.jdbc.Driver Use --verbose for detailed stacktrace.

Question

I was trying to build spark environment on CentOS6.8 on macOS using VMware Fusion.

I have previously installed jdk-10.0.1, hadoop-2.4.1.tar.gz, and MySQL. They worked successfully.

However, when I was trying to format matadata repository,

schematool -dbType mysql -initSchema

the following error happened:

    which: no hbase in (/opt/hive/bin:/usr/local/hive/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/java/latest/bin:/usr/local/hadoop/sbin:/usr/local/hadoop/bin:/usr/local/hive/bin:/root/bin:/usr/java/latest/bin:/usr/local/hadoop/sbin:/usr/local/hadoop/bin:/opt/hive/bin)
     ^HMetastore connection URL:     jdbc:mysql://hbase01:3306/myhive?createDatabaseIfNotExist=true
    Metastore Connection Driver :    com.mysql.jdbc.Driver
    Metastore connection User:       myhive
    org.apache.hadoop.hive.metastore.HiveMetaException: Failed to load driver
    Underlying cause: java.lang.ClassNotFoundException : com.mysql.jdbc.Driver
    Use --verbose for detailed stacktrace.
    *** schemaTool failed ***

I ignored it and entered "$HIVE_HOME/bin/hive", the following error happened:

which: no hbase in (/opt/hive/bin:/usr/local/hive/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/java/latest/bin:/usr/local/hadoop/sbin:/usr/local/hadoop/bin:/usr/local/hive/bin:/root/bin:/usr/java/latest/bin:/usr/local/hadoop/sbin:/usr/local/hadoop/bin:/opt/hive/bin)
Exception in thread "main" java.lang.ClassCastException: java.base/jdk.internal.loader.ClassLoaders$AppClassLoader cannot be cast to java.base/java.net.URLClassLoader
        at org.apache.hadoop.hive.ql.session.SessionState.<init>(SessionState.java:387)
        at org.apache.hadoop.hive.ql.session.SessionState.<init>(SessionState.java:363)
        at org.apache.hadoop.hive.cli.CliSessionState.<init>(CliSessionState.java:60)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:663)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:564)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)

I am sure that I have put the mysql-connector-java into the lib folder of hive. But still there is something wrong.

Any help is appreciated. Thanks a lot!

Answer 1

Underlying cause:

java.lang.ClassNotFoundException : com.mysql.jdbc.Driver

The above error shows that you have to mysql.jdbc.driver jar is missing.

• Download the mysql JDBC Connector to access the MySQL database from Metastore Service.

• Place the connector jar to $HIVE_HOME/lib directory to make it available in classpath.

wget http://www.java2s.com/Code/JarDownload/mysql/mysql-connector-java-commercial-5.1.7-bin.jar.zip
unzip mysql-connector-java-commercial-5.1.7-bin.jar.zip cp
mysql-connector-java-commercial-5.1.7-bin.jar  $HIVE_HOME/lib/

• Create Database and User with proper access rights
• MySQL is installed. Run the below command to access the mysql prompt.

sudo mysql

• Now execute below commands on mysql prompt to configure hive user and metastore schema in MySQL.

Create database hive DEFAULT CHARACTER SET utf8; mysql> grant all PRIVILEGES on . TO 'hive'@'localhost' IDENTIFIED BY 'password_for_hive' WITH GRANT OPTION;

• In above command, in place of localhost you can use IP Address or hostname of the Metastore Server.

• Configure Hive to work with MySQL gedit $HIVE_HOME/conf/hive-site.xml

• Add the following configurations to the end of the line.

 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive</value>
    <description>JDBC connection string used by Hive Metastore</description>
  </property>
 
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>JDBC Driver class</description>
  </property>
 
  <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hive</value>
      <description>Metastore database user name</description>
  </property>
 
  <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>password_hive</value>
      <description>Metastore database password</description>
  </property>
 
  <property>
      <name>hive.metastore.uris</name>
      <value>thrift://localhost:9084</value>
      <description>Thrift server hostname and port</description>
  </property>

• Save the file and close it

• In above configurations, you can use relevant IP Address or host name in place of localhost.

• Change the value of properties of hive-site.xml

  hive.exec.scratchdir /home/path/to/apache-hive-2.3.2-bin/iotmp HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/<username> is created, with ${hive.scratch.dir.permission}. hive.exec.local.scratchdir /home/path/to/apache-hive-2.3.2-bin/iotmp Local scratch space for Hive jobs

  <property>
    <name>hive.downloaded.resources.dir</name>
    <value>/home/path/to/apache-hive-2.3.2-bin/iotmp</value>
    <description>Temporary local directory for added resources in the remote file system.</description>
  </property>
  

• Edit the hive-env.sh file and add the following

export METASTORE_PORT=9084

• Start Hive Metastore Server

• Initialize the Hive Metastore database schema:

HIVE_HOME/bin/schematool -initSchema -dbType $databaseType $ hive --service metastore &

• start hive

hive