jdbc导出mysql数据库,保存/导出数据框转换回JDBC / MySQL的

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.

解决方案

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.