Spark（三）-- SparkSQL扩展（数据读写） -- JDBC(四)

7.6 JDBC

导读

1. 通过 SQL 操作 MySQL 的表

2. 将数据写入 MySQL 的表中

（1）准备 MySQL 环境

在使用 SparkSQL 访问 MySQL 之前, 要对 MySQL 进行一些操作, 例如说创建用户, 表和库等

• Step 1: 连接 MySQL 数据库

  在 MySQL 所在的主机上执行如下命令

  mysql -u root -p

• Step 2: 创建 Spark 使用的用户

  登进 MySQL 后, 需要先创建用户

  CREATE USER 'spark'@'%' IDENTIFIED BY 'Spark123!';
  GRANT ALL ON spark_test.* TO 'spark'@'%';

• Step 3: 创建库和表

  CREATE DATABASE spark_test;
  
  USE spark_test;
  
  CREATE TABLE IF NOT EXISTS `student`(
  `id` INT AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `age` INT NOT NULL,
  `gpa` FLOAT,
  PRIMARY KEY ( `id` )
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）使用 SparkSQL 向 MySQL 中写入数据

其实在使用 SparkSQL 访问 MySQL 是通过 JDBC, 那么其实所有支持 JDBC 的数据库理论上都可以通过这种方式进行访问

在使用 JDBC 访问关系型数据的时候, 其实也是使用 DataFrameReader, 对 DataFrameReader 提供一些配置, 就可以使用 Spark 访问 JDBC, 有如下几个配置可用

属性	含义
url	要连接的 JDBC URL
dbtable	要访问的表, 可以使用任何 SQL 语句中 from 子句支持的语法
fetchsize	数据抓取的大小(单位行), 适用于读的情况
batchsize	数据传输的大小(单位行), 适用于写的情况
isolationLevel	事务隔离级别, 是一个枚举, 取值 NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE, 默认为 READ_UNCOMMITTED

读取数据集, 处理过后存往 MySQL 中的代码如下

  def main(args: Array[String]): Unit = {
    //1.创建SparkSession对象
    val spark = SparkSession.builder()
      .master("local[6]")
      .appName("mysql write")
      .getOrCreate()

    //2.读取数据创建DataFrame
    //2.1 拷贝文件
    //2.2 读取
    val schema = StructType(
      List(
        StructField("name",StringType),
        StructField("age",StringType),
        StructField("gpa",FloatType)
      )
    )

    val df = spark.read
      .schema(schema)
      .option("delimiter","\t")
      .csv("spark/dataset/studenttab10k")

    //3.处理数据
    val resultDF = df.where("age < 30")

    //4.落地数据
    resultDF.write
      .format("jdbc")
      .option("url","jdbc:mysql://node03:3306/spark_test")
      .option("dbtable","student")
      .option("user","root")
      .option("password","123456")
      .mode(SaveMode.Overwrite)
      .save()

  }

（3）运行程序

如果是在本地运行, 需要导入 Maven 依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

如果使用 Spark submit 或者 Spark shell 来运行任务, 需要通过 --jars 参数提交 MySQL 的 Jar 包, 或者指定 --packages 从 Maven 库中读取

bin/spark-shell --packages  mysql:mysql-connector-java:5.1.47 --repositories http://maven.aliyun.com/nexus/content/groups/public/

（4）从 MySQL 中读取数据

读取 MySQL 的方式也非常的简单, 只是使用 SparkSQL 的 DataFrameReader 加上参数配置即可访问

spark.read.format("jdbc")
  .option("url", "jdbc:mysql://node01:3306/spark_test")
  .option("dbtable", "student")
  .option("user", "spark")
  .option("password", "Spark123!")
  .load()
  .show()

默认情况下读取 MySQL 表时, 从 MySQL 表中读取的数据放入了一个分区, 拉取后可以使用 DataFrame 重分区来保证并行计算和内存占用不会太高, 但是如果感觉 MySQL 中数据过多的时候, 读取时可能就会产生 OOM, 所以在数据量比较大的场景, 就需要在读取的时候就将其分发到不同的 RDD 分区

属性	含义
partitionColumn	指定按照哪一列进行分区, 只能设置类型为数字的列, 一般指定为 ID
lowerBound, upperBound	确定步长的参数, lowerBound - upperBound 之间的数据均分给每一个分区, 小于 lowerBound 的数据分给第一个分区, 大于 upperBound 的数据分给最后一个分区
numPartitions	分区数量

spark.read.format("jdbc")
  .option("url", "jdbc:mysql://node01:3306/spark_test")
  .option("dbtable", "student")
  .option("user", "spark")
  .option("password", "Spark123!")
  .option("partitionColumn", "age")
  .option("lowerBound", 1)
  .option("upperBound", 60)
  .option("numPartitions", 10)
  .load()
  .show()

有时候可能要使用非数字列来作为分区依据, Spark 也提供了针对任意类型的列作为分区依据的方法

val predicates = Array(
  "age < 20",
  "age >= 20, age < 30",
  "age >= 30"
)

val connectionProperties = new Properties()
connectionProperties.setProperty("user", "spark")
connectionProperties.setProperty("password", "Spark123!")

spark.read
  .jdbc(
    url = "jdbc:mysql://node01:3306/spark_test",
    table = "student",
    predicates = predicates,
    connectionProperties = connectionProperties
  )
  .show()

SparkSQL 中并没有直接提供按照 SQL 进行筛选读取数据的 API 和参数, 但是可以通过 dbtable 来曲线救国, dbtable 指定目标表的名称, 但是因为 dbtable 中可以编写 SQL, 所以使用子查询即可做到

spark.read.format("jdbc")
  .option("url", "jdbc:mysql://node01:3306/spark_test")
  .option("dbtable", "(select name, age from student where age > 10 and age < 20) as stu")
  .option("user", "spark")
  .option("password", "Spark123!")
  .option("partitionColumn", "age")
  .option("lowerBound", 1)
  .option("upperBound", 60)
  .option("numPartitions", 10)
  .load()
  .show()