将转换后的 DataFrame 保存/导出回 JDBC/MySQL [英] Saving / exporting transformed DataFrame back to JDBC / MySQL

问题描述

我正在尝试弄清楚如何使用新的 DataFrameWriter 将数据写回 JDBC 数据库.我似乎找不到任何相关文档，尽管查看源代码似乎应该是可能的.

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)

我不确定我是否做错了什么(例如，为什么它解析为 DefaultSource 而不是 JDBCRDD?)或者是否无法使用 Spark 的 DataFrames API 写入现有的 MySQL 数据库.

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.

推荐答案

更新

当前 Spark 版本(2.0 或更高版本)支持写入时创建表.

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

原答案

可以写入现有表，但目前(Spark 1.5.0)似乎尚不支持使用 JDBC 数据源创建表*.您可以查看 SPARK-7646 以供参考.

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.

如果表已经存在，你可以简单地使用 DataFrameWriter.jdbc 方法:

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)

* 有趣的是 PySpark 似乎支持使用 jdbc 方法创建表.

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

这篇关于将转换后的 DataFrame 保存/导出回 JDBC/MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！