Spark -- 读取mysql的4种方式和各自的应用场景

1. 方式一

以该方式读取，默认只有一个分区，即单线程读取所有数据。该方式主要是表数据量小的本地测试

 /**
   * 单分区读，且是全量读，应用：表数据量小的本地测试
   */
  def read1(spark: SparkSession, url: String, table: String, props: Properties): DataFrame = {
    spark.read.jdbc(url, table, props)
  }

2.方式二

直接spark.read.jdbc有三个重载方法，以该方式读取，需要指定一个上届和下届，和一个分区数以及分区字段。但是这里注意，该分区字段必须是Int/Long的数值型，且该字段最好有索引，不然每个分区都是全表扫，且该方法只能全量读，比如该表有1000条记录，指定了下届是1，上届100，那么还是会读取全量1000的数据。所以该方式可以作为全量读取大表的一个方式，因为该方法会以多分区去读。

 /**
   * 多分区读，但条件列必须是数值列，且limit无法下推mysql，即使给upperBound = 10 也会全量读
   */
  def read2(spark: SparkSession, table: String, column: String, url: String, lower: Long, upper: Long, parts: Int, props: Properties): DataFrame = {
    spark.read.jdbc(url, table, column, lower, upper, parts, props)
  }

3.方式三

直接spark.read.jdbc有三个重载方法，以该方式读取，需要指定一批分区条件这些分区条件会拼装到where后进行读取。这里注意，该条件字段可以是任意字段，但该字段最好有索引，不然每个并发都是全表扫，且该方法可以支持下推limit逻辑，比如该表有1000条记录，指定了根据id过滤，过滤条件是： id >= 1 and id <= 10，那么该方式只会读取10条记录，且可以按指定的分区去读。所以该方式可以作为读取超大表的一个方式，非常建议读取大表直接用该方式读取。

 /**
   * 多分区读，任意条件，limit可下推mysql
   */
  def read3(spark: SparkSession, table: String, column: String, url: String, lower: Long, upper: Long, parts: Int, props: Properties): DataFrame = {
    val step = ((upper - lower) / parts).toInt
    val predicates: Array[String] = 1.to(parts).map(index => {
      val lowerBound = (index - 1) * step + lower
      val upperBound = index * step + lower
      column + " >=" + lowerBound + " and " + column + " < " + upperBound
    }).toArray
    predicates.foreach(println)
    spark.read.jdbc(url, table, predicates, props)
  }

4.方式四

以format jdbc load的方式读取，默认只有一个分区，即单线程读取数据，但该方式由于dbtable参数是以sql的形式读，这里可以读指定字段(上面的三种方式也可以)，也可以加limit关键字，也可以直接写好加工逻辑。即该方式可以下推相关逻辑到mysql，由mysql执行完相关逻辑直接吧结果数据推给spark，由于该方式也是单分区读，所以主要也是表数据量小的本地测试

/**
   * 单分区读，可以limit，应用：不管表大小，只抽取前n条
   */
  def read4(spark: SparkSession, url: String, sql: String): DataFrame = {
    spark.read.format("jdbc")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "")
      .option("password", "")
      .option("url", url)
      .option("dbtable", s"($sql) dbtable").load()
  }

5. 一个完整的测试代码

import org.apache.spark.sql.{DataFrame, SparkSession}

import java.util.Properties

object TestJdbc {

  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder
      .master("local[4]")
      .appName("Test").getOrCreate()
    spark.sparkContext.setLogLevel("WARN")

    val url = "jdbc:mysql://xx:3306/xx"
    val props = new Properties()
    props.put("driver", "com.mysql.cj.jdbc.Driver")
    props.put("user", "xx")
    props.put("password", "xx")
    props.put("url", url)
    props.put("fetchSize", "100")

    read1(spark, url, "table1", props).show()

    read2(spark, "table2", "id", url, 1, 10000, 10, props).show()

    read3(spark, "table3", "id", url, 1, 10000, 10, props).show()

    read4(spark, url, "select * from xxx where yyy = 1 limit 100").show()

    spark.close()
  }

  /**
   * 单分区读，且是全量读，应用：表数据量小的本地测试
   */
  def read1(spark: SparkSession, url: String, table: String, props: Properties): DataFrame = {
    spark.read.jdbc(url, table, props)
  }

  /**
   * 多分区读，但条件列必须是数值列，且limit无法下推mysql，即使给upperBound = 10 也会全量读
   */
  def read2(spark: SparkSession, table: String, column: String, url: String, lower: Long, upper: Long,
            parts: Int, props: Properties): DataFrame = {
    spark.read.jdbc(url, table, column, lower, upper, parts, props)
  }

  /**
   * 多分区读，任意条件，limit可下推mysql
   */
  def read3(spark: SparkSession, table: String, column: String, url: String, lower: Long, upper: Long, parts: Int,
            props: Properties): DataFrame = {
    val step = ((upper - lower) / parts).toInt
    val predicates: Array[String] = 1.to(parts).map(index => {
      val lowerBound = (index - 1) * step + lower
      val upperBound = index * step + lower
      column + " >=" + lowerBound + " and " + column + " < " + upperBound
    }).toArray
    predicates.foreach(println)
    spark.read.jdbc(url, table, predicates, props)
  }

  /**
   * 单分区读，可以limit，应用：不管表大小，只抽取前n条
   */
  def read4(spark: SparkSession, url: String, sql: String): DataFrame = {
    spark.read.format("jdbc")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .option("user", "xx")
      .option("password", "xx")
      .option("url", url)
      .option("dbtable", s"($sql) dbtable").load()
  }

}