Saving / exporting transformed DataFrame back to JDBC / MySQL

Question

I'm trying to figure out how to use the new DataFrameWriter to write data back to a JDBC database. I can't seem to find any documentation for this, although looking at the source code it seems like it should be possible.

A trivial example of what I'm trying looks like this:

sqlContext.read.format("jdbc").options(Map(
  "url" -> "jdbc:mysql://localhost/foo", "dbtable" -> "foo.bar")
).select("some_column", "another_column")
.write.format("jdbc").options(Map(
  "url" -> "jdbc:mysql://localhost/foo", "dbtable" -> "foo.bar2")
).save("foo.bar2")

This doesn't work — I end up with this error:

java.lang.RuntimeException: org.apache.spark.sql.execution.datasources.jdbc.DefaultSource does not allow create table as select.
    at scala.sys.package$.error(package.scala:27)
    at org.apache.spark.sql.execution.datasources.ResolvedDataSource$.apply(ResolvedDataSource.scala:200)

I'm not sure if I'm doing something wrong (why is it resolving to DefaultSource instead of JDBCRDD for example?) or if writing to an existing MySQL database just isn't possible using Spark's DataFrames API.

Embroidery

It is not the source of the problem but your example is missing load call. – Rallentando

Answer 1

Update

Current Spark version (2.0 or later) supports table creation on write.

The original answer

It is possible to write to an existing table but it looks like at this moment (Spark 1.5.0) creating table using JDBC data source is not supported yet*. You can check SPARK-7646 for reference.

If table already exists you can simply use DataFrameWriter.jdbc method:

val prop: java.util.Properties = ???
df.write.jdbc("jdbc:mysql://localhost/foo", "foo.bar2", prop)

* What is interesting PySpark seems to support table creation using jdbc method.

Rallentando

Thanks, that worked perfectly with the addition of a save mode; i.e. df.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://localhost/foo", "foo.bar2", prop) – Embroidery