title: Spark - connecting to mysql through Zeppelin EMR
body: <p>I am trying to connect to MySQL instance from AWS EMR - Zeppelin notebook. Loaded the mysql connector to this location - <code>/usr/lib/spark/jars/mysql-connector-java-5.0.4-bin.jar</code> . And added this as an artifact in zeppelin interpreter. </p>
  
  <p>Initiating the driver,</p>
  
  <p><code>Class.forName("com.mysql.jdbc.Driver")</code></p>
  
  <p>res77: Class[_] = class com.mysql.jdbc.Driver</p>
  
  <p>Using Scala code as here,</p>
  
  <p>Trial 1,</p>
  
  <pre><code>val jdbcDF = spark.read.format("jdbc").options(
    Map("url" -&gt;  "jdbc:mysql://our-host.readm.co.nz:3306/testdb?user=testuser&amp;password=****",
    "dbtable" -&gt; "testdb.tablename",
    "driver" -&gt; "com.mysql.jdbc.Driver",
    "partitionColumn" -&gt; "id", "lowerBound" -&gt; "1", "upperBound" -&gt; "41514638", "numPartitions" -&gt; "20"
    )).load()
  </code></pre>
  
  <p>Got this error,</p>
  
  <pre><code>com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
  ** BEGIN NESTED EXCEPTION **
  java.net.SocketException
  MESSAGE: java.net.ConnectException: Connection timed out (Connection timed out)
  STACKTRACE:
  java.net.SocketException: java.net.ConnectException: Connection timed out (Connection timed out)
      at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
      at com.mysql.jdbc.MysqlIO.&lt;init&gt;(MysqlIO.java:276)
      at com.mysql.jdbc.Connection.createNewIO(Connection.java:2666)
      at com.mysql.jdbc.Connection.&lt;init&gt;(Connection.java:1531)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:61)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:52)
      at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
      at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
      at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
      at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
      at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
      at &lt;init&gt;(&lt;console&gt;:50)
      at &lt;init&gt;(&lt;console&gt;:55)
      at &lt;init&gt;(&lt;console&gt;:57)
      at &lt;init&gt;(&lt;console&gt;:59)
      at &lt;init&gt;(&lt;console&gt;:61)
      at &lt;init&gt;(&lt;console&gt;:63)
      at &lt;init&gt;(&lt;console&gt;:65)
      at &lt;init&gt;(&lt;console&gt;:67)
      at &lt;init&gt;(&lt;console&gt;:69)
      at &lt;init&gt;(&lt;console&gt;:71)
      at &lt;init&gt;(&lt;console&gt;:73)
      at &lt;init&gt;(&lt;console&gt;:75)
      at &lt;init&gt;(&lt;console&gt;:77)
      at &lt;init&gt;(&lt;console&gt;:79)
      at &lt;init&gt;(&lt;console&gt;:81)
      at &lt;init&gt;(&lt;console&gt;:83)
      at &lt;init&gt;(&lt;console&gt;:85)
      at &lt;init&gt;(&lt;console&gt;:87)
      at &lt;init&gt;(&lt;console&gt;:89)
      at &lt;init&gt;(&lt;console&gt;:91)
      at &lt;init&gt;(&lt;console&gt;:93)
      at .&lt;init&gt;(&lt;console&gt;:97)
      at .&lt;clinit&gt;(&lt;console&gt;)
      at .$print$lzycompute(&lt;console&gt;:7)
      at .$print(&lt;console&gt;:6)
      at $print(&lt;console&gt;)
      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 scala.tools.nsc.interpreter.IMain$ReadEvalPrint.call(IMain.scala:786)
      at scala.tools.nsc.interpreter.IMain$Request.loadAndRun(IMain.scala:1047)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest$$anonfun$loadAndRunReq$1.apply(IMain.scala:638)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest$$anonfun$loadAndRunReq$1.apply(IMain.scala:637)
      at scala.reflect.internal.util.ScalaClassLoader$class.asContext(ScalaClassLoader.scala:31)
      at scala.reflect.internal.util.AbstractFileClassLoader.asContext(AbstractFileClassLoader.scala:19)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest.loadAndRunReq(IMain.scala:637)
      at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:569)
      at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:565)
      at sun.reflect.GeneratedMethodAccessor369.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.zeppelin.spark.Utils.invokeMethod(Utils.java:38)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1000)
      at org.apache.zeppelin.spark.SparkInterpreter.interpretInput(SparkInterpreter.java:1205)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1172)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1165)
      at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
      at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
      at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
      at org.apache.zeppelin.scheduler.FIFOScheduler$1.run(FIFOScheduler.java:139)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  ** END NESTED EXCEPTION **
  Last packet sent to the server was 1 ms ago.
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2741)
    at com.mysql.jdbc.Connection.&lt;init&gt;(Connection.java:1531)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:61)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:52)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    ... 58 elided
  </code></pre>
  
  <p>Then trial 2,</p>
  
  <pre><code>val partitionColumn = "id"
  val dbUrl = "jdbc:mysql://our-host.readm.co.nz:3306"
  
  val lowerBound = 1
  val upperBound = 41514638
  
  val jdbcDF = spark.read.format("jdbc")
      .option("url", dbUrl)
      .option("databaseName", "testdb")
      .option("dbtable", "tablename")
      .option("user", "username")
      .option("password", "*****")
      .option("driver","com.mysql.jdbc.Driver")
      .option("partitionColumn", partitionColumn)
      .option("lowerBound", lowerBound)
      .option("upperBound", upperBound)
      .option("numPartitions", 20)
      .load()
  </code></pre>
  
  <p>Got this error,</p>
  
  <pre><code>java.lang.NullPointerException
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:72)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    ... 58 elided
  </code></pre>
  
  <p>Trial 3, </p>
  
  <pre><code>import java.util.Properties
  
  val table = "tablename"
  val url = "jdbc:mysql://our-host.readm.co.nz"
  val prop = new Properties()
  prop.put("driver","com.mysql.jdbc.Driver")
  prop.put("user","username")
  prop.put("password","****")
  prop.put("databaseName","testdb")
  
  val jdbcDF = spark.read.jdbc(url,
          table,
          prop)
  </code></pre>
  
  <p>Getting this error,</p>
  
  <pre><code>java.lang.NullPointerException
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:72)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:193)
    ... 58 elided
  </code></pre>
  
  <p>Not sure whats wrong here, can any help me? Thanks.</p>
  
  <p>EDIT:- This is what I am getting after trying the answer given by @geo</p>
  
  <pre><code>com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
  ** BEGIN NESTED EXCEPTION **
  java.net.SocketException
  MESSAGE: java.net.ConnectException: Connection timed out (Connection timed out)
  STACKTRACE:
  java.net.SocketException: java.net.ConnectException: Connection timed out (Connection timed out)
      at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
      at com.mysql.jdbc.MysqlIO.&lt;init&gt;(MysqlIO.java:276)
      at com.mysql.jdbc.Connection.createNewIO(Connection.java:2666)
      at com.mysql.jdbc.Connection.&lt;init&gt;(Connection.java:1531)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:61)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:52)
      at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
      at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
      at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
      at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
      at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
      at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
      at &lt;init&gt;(&lt;console&gt;:40)
      at &lt;init&gt;(&lt;console&gt;:45)
      at &lt;init&gt;(&lt;console&gt;:47)
      at &lt;init&gt;(&lt;console&gt;:49)
      at &lt;init&gt;(&lt;console&gt;:51)
      at &lt;init&gt;(&lt;console&gt;:53)
      at &lt;init&gt;(&lt;console&gt;:55)
      at &lt;init&gt;(&lt;console&gt;:57)
      at &lt;init&gt;(&lt;console&gt;:59)
      at &lt;init&gt;(&lt;console&gt;:61)
      at &lt;init&gt;(&lt;console&gt;:63)
      at &lt;init&gt;(&lt;console&gt;:65)
      at &lt;init&gt;(&lt;console&gt;:67)
      at .&lt;init&gt;(&lt;console&gt;:71)
      at .&lt;clinit&gt;(&lt;console&gt;)
      at .$print$lzycompute(&lt;console&gt;:7)
      at .$print(&lt;console&gt;:6)
      at $print(&lt;console&gt;)
      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 scala.tools.nsc.interpreter.IMain$ReadEvalPrint.call(IMain.scala:786)
      at scala.tools.nsc.interpreter.IMain$Request.loadAndRun(IMain.scala:1047)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest$$anonfun$loadAndRunReq$1.apply(IMain.scala:638)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest$$anonfun$loadAndRunReq$1.apply(IMain.scala:637)
      at scala.reflect.internal.util.ScalaClassLoader$class.asContext(ScalaClassLoader.scala:31)
      at scala.reflect.internal.util.AbstractFileClassLoader.asContext(AbstractFileClassLoader.scala:19)
      at scala.tools.nsc.interpreter.IMain$WrappedRequest.loadAndRunReq(IMain.scala:637)
      at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:569)
      at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:565)
      at sun.reflect.GeneratedMethodAccessor340.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.zeppelin.spark.Utils.invokeMethod(Utils.java:38)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1000)
      at org.apache.zeppelin.spark.SparkInterpreter.interpretInput(SparkInterpreter.java:1205)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1172)
      at org.apache.zeppelin.spark.SparkInterpreter.interpret(SparkInterpreter.java:1165)
      at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
      at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
      at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
      at org.apache.zeppelin.scheduler.FIFOScheduler$1.run(FIFOScheduler.java:139)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  ** END NESTED EXCEPTION **
  Last packet sent to the server was 7 ms ago.
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2741)
    at com.mysql.jdbc.Connection.&lt;init&gt;(Connection.java:1531)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:61)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:52)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.&lt;init&gt;(JDBCRelation.scala:114)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    ... 50 elided
  </code></pre>