Spark: java.sql.SQLException: No suitable driver

Question

I am facing below error while loading data from sql to spark for processing.
Could you please help me with the error.

val df = spark.read.format("jdbc").option("url", "jdbc:mysql://sqoopdb.edu.cloudlab.com/z_dinesh").option("dbtable", "t").option("user", "labuser").option("
password", "edureka").load()
java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getDriver(DriverManager.java:315)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
at scala.Option.getOrElse(Option.scala:121)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:83)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:34)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:32)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:330)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:152)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:125)
... 48 elided

Answer 1

The missing driver is the JDBC one and you have to add it to the SparkSQL configuration. You can do it through your Properties object with this line:

prop.setProperty("driver", "com.mysql.jdbc.Driver") 

Or, in your case, the complete statement should be as below,

val df = spark.read.format("jdbc").option("url", "jdbc:mysql://sqoopdb.edu.cloudlab.com/z_dinesh").option("driver", "com.mysql.jdbc.Driver").option("dbtable", "t").option("user", "labuser").option("password", "edureka").load() 

Also, make sure that the MySQL JDBC connection jar is added to the program.