spark写mysql优化简书_spark 通过 jdbc 写入 clickhouse 需要注意的点

最近在用 spark 通过 jdbc 写入 clickhouse 的时候，遇到一些坑，这里分享下，造福人民群众。

一个 WARN

WARN JdbcUtils: Requested isolation level 1, but transactions are unsupported

这是因为 clickhouse 不支持事务造成的，解决方案，jdbc 加入 isolationLevel 等于 NONE 的选项，isolationLevel 详解

The transaction isolation level, which applies to current connection. It can be one of NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE, corresponding to standard transaction isolation levels defined by JDBC's Connection object, with default of READ_UNCOMMITTED. This option applies only to writing. Please refer the documentation in java.sql.Connection.

一个报错

merges are processing significantly slower than inserts

这是因为 spark 多个 partition 同时并发写引发的错误，解决方案 jdbc 加入 numPartitions 等于 1 的选项控制并发数，numPartitions 详解

The maximum number of partitions that can be used for parallelism in table reading and writing. This also determines the maximum number of concurrent JDBC connections. If the number of partitions to write exceeds this limit, we decrease it to this limit by calling coalesce(numPartitions) before writing.

完整 scala 代码

spark.createDataFrame(data)

.write

.mode(SaveMode.Append)

.option("batchsize", "50000")

.option("isolationLevel", "NONE") // 设置事务

.option("numPartitions", "1") // 设置并发

.jdbc(dbUrl,

"table",

dbProp)

更多 spark jdbc 选项，参考 spark 官方文档 更多架构、PHP、GO相关踩坑实践技巧请关注我的公众号