title: Use p6spy to log Spark JDBC datasource
body: <p>I would like to log the JDBC statements that my Spark job executes with the help of <a href="https://github.com/p6spy/p6spy" rel="nofollow noreferrer">p6spy</a>.</p>
  
  <p>Using p6spy is usually straightforward: one inserts the string <code>:p6spy:</code> into the jdbc url and includes the p6spy driver classes into the classpath of the application. After that, all jdbc actions will be logged into a file.</p>
  
  <p>For example if the original (MySQL) connection string was <pre>jdbc:mysql://172.17.0.2:3306/spark_test</pre> the connection string with logging enabled would be <pre>jdbc:<b>p6spy:</b>mysql://172.17.0.2:3306/spark_test</pre></p>
  
  <p>I use this line to write a dataframe into a MySQL table</p>
  
  <pre class="lang-scala prettyprint-override"><code>df.write.mode(SaveMode.Overwrite).jdbc("jdbc:p6spy:mysql://172.17.0.2:3306/spark_test", "test_table", prop)
  </code></pre>
  
  <p>with <code>prop</code> containg the db user and password.</p>
  
  <p>This line of code fails with the error message</p>
  
  <pre class="lang-none prettyprint-override"><code>Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right syntax to use near '"value" INTEGER NOT NULL)' at line 1
  </code></pre>
  
  <p>Without the <code>:p6spy:</code> part in the connection string everything works as expected.</p>
  
  <hr/>
  
  <h2>My findings so far</h2>
  
  <p>The reason for the error is that Spark tries to execute the statement </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE test_table ("value" INTEGER NOT NULL)
  </code></pre>
  
  <p>which includes <b>"</b> around the column name. The correct character would be <b>`</b> for MySQL.</p>
  
  <p>Spark can handle different SQL dialects. The dialects are implemented in the package <a href="https://github.com/apache/spark/tree/branch-2.4/sql/core/src/main/scala/org/apache/spark/sql/jdbc" rel="nofollow noreferrer">org.apache.spark.sql.jdbc</a>. The dialect to use is chosen depending on the jdbc url of the database. Each dialect object implements the method <code>canHandle(url : String)</code>. The <a href="https://github.com/apache/spark/blob/branch-2.4/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala" rel="nofollow noreferrer">MySQLDialect</a> handles urls starting with <code>jdbc:mysql</code> but not those starting with <code>jdbc:p6spy:mysql</code>. Unfortunately Spark defaults to the <a href="https://github.com/apache/spark/blob/1d1a2079a56de25b0d14dd57f458a0f3830a6e14/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala#L248" rel="nofollow noreferrer">NoopDialect</a> for an unknown url type. This dialect adds the <b>"</b> around the column name.</p>
  
  <hr/>
  
  <h2>Possible solution</h2>
  
  <p>It is possible to register new database dialects by calling <a href="https://github.com/apache/spark/blob/1d1a2079a56de25b0d14dd57f458a0f3830a6e14/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala#L209" rel="nofollow noreferrer">JdbcDialects.registerDialect</a>. Here one could register a new dialect that implements the <code>canHandle</code> method as</p>
  
  <pre class="lang-scala prettyprint-override"><code>override def canHandle(url: String): Boolean = url.startsWith("jdbc:p6spy:mysql")
  </code></pre>
  
  <p>and then delegates all other method calls to the orginal MySQL dialect. </p>
  
  <p>Unfortunately the MySQLDialect object is declared as </p>
  
  <pre class="lang-scala prettyprint-override"><code>private case object MySQLDialect extends JdbcDialect {
    ...
  }
  </code></pre>
  
  <p>so my own implementation of a dialect cannot use MySQLDialect directly. An option would be to copy the code of MySQLDialect into my own dialect object (the code is not long), but I would like to avoid copying code.</p>
  
  <p>Are there any other options?</p>
  
  <p>I am using Spark 2.4.5</p>