Spark——Spark读写MySQL及问题汇总

Spark主要是以JDBC驱动的方式读写MySQL的。在提交Spark作业时别忘记了打包驱动包mysql-connector-java-5.1.47.jar(用合适自己项目的版本)。

Spark连接MySQL所需参数

1. 参数配置方式

1. 通过 java.util.Properties

val url = jdbc:mysql://hostname:3306/db_name?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
val table = "table_name"

val props = new Properties()
props.put("driver", "com.mysql.jdbc.Driver")
props.put("user", "root")
props.put("password", "123456")

spark.read.jdbc(url: String, table: String, predicates: Array[String], props: Properties)

2. 通过 scala.collection.Map

   val options = Map(
      "url" -> url, // jdbc url
      "driver" -> "com.mysql.jdbc.Driver", //驱动
      "user" -> userName, //用户名
      "password" -> password, //密码
      "pushDownPredicate" -> "true",
      "dbtable" -> "(select * from table_name where id != 10000) as subq", //MySQL表名
      "partitionColumn" -> "id", //被用来分区的整型列
      "lowerBound" -> "1", //要读取的整型列的下界(包含下界)
      "UpperBound" -> "300000", //要读取的整型列的上界(不包含上界)
      "numPartitions" -> "5" //分区数
  )
  
spark.read.format("jdbc").options(options).load()

2. 可选配置参数

可选配置参数参考Spark官方网站:
https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html

Spark读MySQL

Spark提供了两种(并行)读MySQL的方式：

1. 基于整型列并行读取
2. 基于范围并行读取

1. 基于整型列设置并行度

def readByIntegralColumn(spark: SparkSession): Unit = {
    val options = Map(
        "url" -> "jdbc:mysql://host:3306/dbName", // jdbc的url
        "dbtable" -> "tableName", //MySQL表名
        "user" -> "userName", //用户名
        "password" -> "passwd", //密码
        "driver" -> "com.mysql.jdbc.Driver", //驱动
        "partitionColumn" -> "id", //被用来分区的整型列
        "lowerBound" -> "1", //要读取的整型列的下界(包含下界)
        "UpperBound" -> "400000", //要读取的整型列的上界(不包含上界)
        "numPartitions" -> "10" //分区数
    )

    spark.read
        .format("jdbc")
        .options(options)
        .load()
        .write
        .saveAsTable("dbName.tableName")
}

上面代码中，要读取MySQL中id值从1到400000的行，并划分10个分区，每个分区平均读取40000条记录。

2. 基于范围设置并行度

同样先上代码，对着代码再做详细的解释：

def readByRange(spark: SparkSession): Unit = {
	//用户名和密码
    val prop = new Properties()
    prop.put("user", "userName")
    prop.put("password", "passwd")

    val url = "jdbc:mysql://host:3306//dbName" //url
    val table = "tableName" //表名

    //predicates参数就相当于在where表达式中的范围, 有几个范围就有几个分区进行并行读取
    val predicates = Array[String](
        "created_at < '2018-08-01 00:00:00'",
        "created_at >= '2018-08-01 00:00:00' && created_at < '2018-10-01 00:00:00'",
        "created_at >= '2018-10-01 00:00:00'")

    spark.read
        .jdbc(url, table, predicates, prop)
        .write.saveAsTable("dbName.tableName")
}

上面代码中，基于MySQL中记录的创建时间来划分分区，predicates中设置的范围区间数就是分区数。当然，也可以是使用其他任何可以进行区间查询的列来设置分区数。

注意：不要在集群上并行创建太多分区，否则可能会给MySQL产生很大的访问压力，甚至可能会导致数据库系统崩溃

Spark写MySQL

Spark写MySQL比较简单，直接看代码：

def writeMySQL(spark: SparkSession): Unit = {
    val host = "hostname" //MySQL服务器地址
    val port = "3306" //端口号
    val userName = "userName" //用户名
    val password = "password" //访问密码
    val dbName = "dbName" //库名
    val jdbcUrl = s"jdbc:mysql://${host}:${port}/${dbName }" //jdbc url

    import java.util.Properties
    val prop = new Properties()
    prop.put("user", userName)
    prop.put("password", password)

    spark.read.table("db.test")
            .coalesce(10) //调节写入并行度(增加并行度要用repartition(n))
            .write
            .mode("append") //追加写入()
            .jdbc(jdbcUrl, "test", prop)
}

写入的时候需要注意并行度，以免给MySQL带来太大写入压力。

Spark读写MySQL - 问题汇总

1. Spark写MySQL覆盖表结构问题

问题

你在MySQL中创建了一个表user，现在你要通过Spark将DataFrame中的数据写入到表user中，代码如下：

df
    .coalesce(2)
    .write
    .mode("overwrite")
    .jdbc("url", "db.user", props)

原因分析

因为每次都是要覆盖之前表中的所有数据，所以写入mode类型为overwrite。而overwrite模式的底层执行机制是，先把之前的表user删掉(drop table db.user)，然后再根据要写入的DataFrame的schema及字段类型创建新的表，这就造成你原来的建表语句就失效了(比如，之前指定的主键、字段类型等都被覆盖了)。

造成这个问题的原因就是，DataFrame在写MySQL的时候，有一个选项"truncate", 默认值是false, false情况下覆盖写(overwrite)是执行"drop table"，而true情况下覆盖写才会执行"truncate table"。

解决方法

将选项"truncate"设置为true即可，代码如下：

df
    .coalesce(2)
    .write
    .option("truncate", "true")
    .mode("overwrite")
    .jdbc("url", "db.user", props)

2. Spark读MySQL写入Hive时，boolean(true、false)类型自动转成了0和1

问题

当Spark读MySQL中的tinyint(1)类型写入到Hive之后，会将原来MySQL中的tinyint(1)类型（也就是0/1）自动转为true/false进行存储。

原因分析

MySQL没有内建的Boolean类型，Boolean类型只能通过tinyint(1)来存储（0为false，1为true）。而Spark在读MySQL的时候，会将此类型转成true/false进行存储，这个转换操作是由 tinyInt1isBit=true/false 这个配置来指定的，默认为true。如果想要以原始的0/1进行存储，就要将此配置设置为false不进行转换，即tinyInt1isBit=false。

解决方法

在jdbc url中配置参数tinyInt1isBit=false，如下所示：

url=jdbc:mysql://hostname:3306/dbname?tinyInt1isBit=false