Unable to connect Mysql in Lab through Spark-Submit command

apache-spark

#1

Dear Itversity,

Unable to connect mysql database in the lab through spark submit command.
From the code, I tried to build dataframe directly accessing mysql db.

Help me to resolve the error.

Build.sbt

name := “ClusterConnection”

version := “0.1”

scalaVersion := “2.11.11”

//Spark Library Dependencies

libraryDependencies += “org.apache.spark” %% “spark-core” % “2.1.0”
libraryDependencies += “org.apache.spark” %% “spark-sql” % “2.1.0”
libraryDependencies += “org.apache.spark” %% “spark-hive” % “2.1.0”
libraryDependencies += “com.databricks” %% “spark-avro” % “4.0.0”
libraryDependencies += “mysql” % “mysql-connector-java” % “5.1.6”
libraryDependencies += “com.typesafe” % “config” % “1.3.1”

code

package DatabaseConnection

import java.util.Properties
import java.sql.DriverManager
import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._
import org.apache.spark.sql._

object MysqlConnection {

def main(args: Array[String]): Unit = {
Logger.getLogger(“org”).setLevel(Level.ERROR)
val mas = “yarn-client”
val spark = SparkSession.builder()
.appName(“Mysql database connection”)
.master(mas)
.enableHiveSupport()
.getOrCreate()

val driver= "com.mysql.jdbc.Driver"
Class.forName(driver)
val url = "jdbc:mysql://ms.itversity.com:3306"
val props = new Properties()
props.put("user", "retail_user")
props.put("password", "itversty")

println("connection successfull")

val query = "SELECT order_status, count(1) FROM retail_db.orders GROUP BY order_status;"

val orderDF = spark.read.jdbc(url, s"$query", props)

orderDF.show()

println("Dataframe creation successfull")

}

}

Command

spark-submit --class DatabaseConnection.MysqlConnection --master yarn --deploy-mode client --num-executors 2 clusterconnection_2.11-0.1.jar --conf ‘spark.ui.port=11100’ --jars mysql-connector-java-5.1.6.jar --packages=“mysql:mysql-connector-java:5.1.6” --conf spark.driver.extraClassPath=mysql-connector-java-5.1.6.jar

Error

SPARK_MAJOR_VERSION is set to 2, using Spark2
Exception in thread “main” java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:264)
at DatabaseConnection.MysqlConnection$.main(MysqlConnection.scala:22)
at DatabaseConnection.MysqlConnection.main(MysqlConnection.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:729)
at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:185)
at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:210)
at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:124)
at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)

Regards
Venkatesh


#2

Hi Venkatesh,

Did you get a solution of the above problem? I am struggling with the same issue :frowning: .

Regards,
Ranjit Singh