Spark读写MySQL

简介

简单介绍几种Spark读写MYSQL的方式

环境：Spark版本：2.4.0

           MySQL版本：5.7

读取MySQL表

Spark中读取MySQL数据可以有单分区和多分区，一般读取小数据量的表采用简单的单分区模式就可以，对于比较大的单分区抽取需要消耗时间较长的表来说，采用多分区模式读取性能会更好。

单分区单线程读取

此种方式是最简单的读取方式，但只有单线程，仅限于小数据量表，需要谨慎在生产库中使用，指定连接地址和表名即可:

val jdbcUrl = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false"
val jdbcUser = ""
val jdbcPass = ""
val table = ""
val df = spark.read
  .format("jdbc")
  .option("url", jdbcUrl)
  .option("user", jdbcUser)
  .option("password", jdbcPass)
  .option("driver", "com.mysql.jdbc.Driver")
  .option("dbtable", table)
  .load()

多分区并行读取

此种方式对于抽取数据量较大的表有很好的性能提升，但仅限于有连续数值型主键（比如自增id）的数据表：

val jdbcUrl = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false"
val jdbcUser = ""
val jdbcPass = ""
// 可以直接指定表名，也可以写 SELECT 语句（必须要有临时表包裹），比如：
// val table = "(select * from test.test_table where status = 1) tmp"
val table = ""
val partitionNum = 6
val minId = 1
val maxId = 6000000
val df = spark.read
  .format("jdbc")
  .option("url", jdbcUrl)
  .option("user", jdbcUser)
  .option("password", jdbcPass)
  .option("driver", "com.mysql.jdbc.Driver")
  .option("dbtable", table)
  // 以下4个配置项必须同时使用
  // 分区数量，可以理解为读取并行度、线程数
  .option("numPartitions", partitionNum)
  // 分区字段，必须为数字、日期、时间戳字段
  .option("partitionColumn", "id")
  // lowerBound 和 upperBound 仅用于计算每个分区的取数步长，不用于数据过滤
  // 分区字段的最小值
  .option("lowerBound", minId)
  // 分区字段的最大值
  .option("upperBound", maxId)
  .load()

详情见SparkSQL提高read读取数据的并行度_weixin_41851495的博客-CSDN博客

写入数据方式

追加写

df.write
  .format("jdbc")
  .mode(SaveMode.Append)
  .option("driver", "com.mysql.jdbc.Driver")
  .option("url", jdbcUrl)
  .option("dbtable", table)
  .option("user", jdbcUser)
  .option("password", jdbcPass)
  // JDBC批大小，默认 1000，灵活调整该值可以提高写入性能
  .option("batchsize", 10000)
  // 事务级别，默认为 READ_UNCOMMITTED，无事务要求可以填 NONE 以提高性能
  .option("isolationLevel", "NONE")
  .save()

覆盖写

df.write
  .format("jdbc")
  .mode(SaveMode.Overwrite)
  .option("driver", "com.mysql.jdbc.Driver")
  .option("url", jdbcUrl)
  .option("dbtable", table)
  .option("user", jdbcUser)
  .option("password", jdbcPass)
  // JDBC批大小，默认 1000，灵活调整该值可以提高写入性能
  .option("batchsize", 10000)
  // 事务级别，默认为 READ_UNCOMMITTED，无事务要求可以填 NONE 以提高性能
  .option("isolationLevel", "NONE")
  // 需要注意该配置项，Overwrite 模式下，不设置为 true 会删表重建
  .option("truncate", "true")
  .save()

更新写

这里有时需要和业务端对接数据时，比如需要大数据每日计算出一些配置数据，如果没有则按照旧的配置读取。

更新接入比较复杂一些，一般结合 foreachPartition 使用。同时需要目标表创建 UNIQUE KEY，因为需要基于UNIQUE KEY来实现UPSERT。

df.foreachPartition(iter => {
  val conn = ds.getConnection
  val sql =
      """
        |INSERT INTO test_table (uid,a,b,c,d,e)
        |VALUES (?,?,?,?,?,?)
        |ON DUPLICATE KEY
        |UPDATE c = ?, d = ?
        |""".stripMargin
  val ps = conn.prepareStatement(sql)
  iter.foreach(row => {
    val uid = row.getAs[Long]("pid")
    val a = row.getAs[Long]("a")
    val b = row.getAs[String]("b")
    val c = row.getAs[java.math.BigDecimal]("c")
    val d = row.getAs[java.math.BigDecimal]("d")
    val e = row.getAs[Byte]("e")
    ps.setLong(1, uid)
    ps.setLong(2, a)
    ps.setString(3, b)
    ps.setBigDecimal(4, c)
    ps.setBigDecimal(5, d)
    ps.setByte(6, e)
    ps.setBigDecimal(7, c)
    ps.setBigDecimal(8, d)
    ps.executeUpdate()
  })
  DbUtil.close(conn)
})

此处感谢我司导师Sheldon