Spark SQL百万级数据批量读写入MySQL

Spark SQL还包括一个可以使用JDBC从其他数据库读取数据的数据源。与使用JdbcRDD相比，应优先使用此功能。这是因为结果作为DataFrame返回，它们可以在Spark SQL中轻松处理或与其他数据源连接。JDBC数据源也更易于使用Java或Python，因为它不需要用户提供ClassTag。

可以使用Data Sources API将远程数据库中的表加载为DataFrame或Spark SQL临时视图。用户可以在数据源选项中指定JDBC连接属性。 user和password通常作为用于登录数据源的连接属性。除连接属性外，Spark还支持以下不区分大小写的选项：

属性名称	解释
url	要连接的JDBC URL
dbtable	读取或写入的JDBC表
query	指定查询语句
driver	用于连接到该URL的JDBC驱动类名
partitionColumn, lowerBound, upperBound	如果指定了这些选项，则必须全部指定。另外， numPartitions必须指定
numPartitions	表读写中可用于并行处理的最大分区数。这也确定了并发JDBC连接的最大数量。如果要写入的分区数超过此限制，我们可以通过coalesce(numPartitions)在写入之前进行调用将其降低到此限制
queryTimeout	默认为0，查询超时时间
fetchsize	JDBC的获取大小，它确定每次要获取多少行。这可以帮助提高JDBC驱动程序的性能
batchsize	默认为1000，JDBC批处理大小，这可以帮助提高JDBC驱动程序的性能。
isolationLevel	事务隔离级别，适用于当前连接。它可以是一个NONE，READ_COMMITTED，READ_UNCOMMITTED，REPEATABLE_READ，或SERIALIZABLE，对应于由JDBC的连接对象定义，缺省值为标准事务隔离级别READ_UNCOMMITTED。此选项仅适用于写作。
sessionInitStatement	在向远程数据库打开每个数据库会话之后，在开始读取数据之前，此选项将执行自定义SQL语句，使用它来实现会话初始化代码。
truncate	这是与JDBC writer相关的选项。当SaveMode.Overwrite启用时，就会清空目标表的内容，而不是删除和重建其现有的表。默认为false
pushDownPredicate	用于启用或禁用谓词下推到JDBC数据源的选项。默认值为true，在这种情况下，Spark将尽可能将过滤器下推到JDBC数据源。

值得注意的是，Spark不指定分区的话，默认会使用一个分区读取数据，这样在数据量特别大的情况下，会出现OOM。在读取数据之后，调用DataFrameDF.rdd.partitions.size方法可以查看分区数。

1.Spark SQL批量写入MySQL
代码示例如下：

object BatchInsertMySQL {
  case class Person(name: String, age: Int)
  def main(args: Array[String]): Unit = {

    // 创建sparkSession对象
    val conf = new SparkConf()
      .setAppName("BatchInsertMySQL")
    val spark: SparkSession =  SparkSession.builder()
      .config(conf)
      .getOrCreate()
    import spark.implicits._
    // MySQL连接参数
    val url = JDBCUtils.url
    val user = JDBCUtils.user
    val pwd = JDBCUtils.password

    // 创建Properties对象，设置连接mysql的用户名和密码
    val properties: Properties = new Properties()
    properties.setProperty("user", user) // 用户名
    properties.setProperty("password", pwd) // 密码
    properties.setProperty("driver", "com.mysql.jdbc.Driver")
    properties.setProperty("numPartitions","10")
    // 读取mysql中的表数据
    val testDF: DataFrame = spark.read.jdbc(url, "test", properties)
     println("testDF的分区数：  " + testDF.rdd.partitions.size)
   testDF.createOrReplaceTempView("test")
   testDF.persist(StorageLevel.MEMORY_AND_DISK)
   testDF.printSchema()
    val result =
      s"""-- SQL代码
               """.stripMargin

    val resultBatch = spark.sql(result).as[Person]
    println("resultBatch的分区数： " + resultBatch.rdd.partitions.size)
    // 批量写入MySQL
    // 此处最好对处理的结果进行一次重分区
    // 由于数据量特别大，会造成每个分区数据特别多
    resultBatch.repartition(500).foreachPartition(record => {

      val list = new ListBuffer[Person]
      record.foreach(person => {
        val name = Person.name
        val age = Person.age
        list.append(Person(name,age))
      })
      upsertDateMatch(list) //执行批量插入数据
    })
    // 批量插入MySQL的方法
    def upsertPerson(list: ListBuffer[Person]): Unit = {
      var connect: Connection = null
      var pstmt: PreparedStatement = null
      try {
        connect = JDBCUtils.getConnection()
        // 禁用自动提交
        connect.setAutoCommit(false)

        val sql = "REPLACE INTO `person`(name, age)" +
          " VALUES(?, ?)"
        pstmt = connect.prepareStatement(sql)
        var batchIndex = 0
        for (person <- list) {
          pstmt.setString(1, person.name)
          pstmt.setString(2, person.age)
          // 加入批次
          pstmt.addBatch()
          batchIndex +=1
          // 控制提交的数量,
          // MySQL的批量写入尽量限制提交批次的数据量，否则会把MySQL写挂！！！
          if(batchIndex % 1000 == 0 && batchIndex !=0){
            pstmt.executeBatch()
            pstmt.clearBatch()
          }
        }
        // 提交批次
        pstmt.executeBatch()
        connect.commit()
      } catch {
        case e: Exception =>
          e.printStackTrace()
      } finally {
        JDBCUtils.closeConnection(connect, pstmt)
      }
    }
    spark.close()
  }
}

JDBC连接工具类：

object JDBCUtils {
  val user = "root"
  val password = "root"
  val url = "jdbc:mysql://localhost:3306/mydb"
  Class.forName("com.mysql.jdbc.Driver")
  // 获取连接
  def getConnection() = {
    DriverManager.getConnection(url,user,password)
  }
// 释放连接
  def closeConnection(connection: Connection, pstmt: PreparedStatement): Unit = {
    try {
      if (pstmt != null) {
        pstmt.close()
      }
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      if (connection != null) {
        connection.close()
      }
    }
  }
}

Spark写入大量数据到MySQL时，在写入之前尽量对写入的DF进行重分区处理，避免分区内数据过多。在写入时，要注意使用foreachPartition来进行写入，这样可以为每一个分区获取一个连接，在分区内部设定批次提交，提交的批次不易过大，以免将数据库写挂。