spark jdbc操作

1.全量读表

下面代码是全量读表，相当于select * from dbtest.test

import org.apache.spark.sql.SparkSession
object SQLDataSourceExample {

  def main(args: Array[String]) {
    val spark = SparkSession
      .builder
      .appName("jdbc test")
      .master("local[*]")
      .getOrCreate()

    import spark.implicits._

   
    val jdbcDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://172.168.10.10:3306/dbtest?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true")
      .option("dbtable", "dbtest.test")
      .option("user", "root")
      .option("password", "123456")
      .load()

    jdbcDF.show()
  
    spark.stop()
  }
}

option中的参数，自行看官网解释（下图是官网上的部分参数截图），

这里要注意,dbtable和query，

2.按条件读表

如果要按条件读表，按官网的解释可以用query参数，官网也说dbtable和query参数不能同时使用，但是我测试发现只用query会报错，说没有dbtable参数。
requirement failed: Option ‘dbtable’ is required

可以用dbtable参数查询，注意写法，要加一个表的别名。

  val jdbcDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://172.17.1.80:3306?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true")
      .option("dbtable", "(select id from dbtest.test ) tmp")
      .option("user", "root")
      .option("password", "123456")
      .load()

3.全量和增量写表

下面是把df2这个dataframe写入到mysql中，df2的schema的字段名要和mysql中的字段名一致。

         df2.write
        .format("jdbc")
        .mode(SaveMode.Overwrite)
        .option("url", this.url + "&rewriteBatchedStatements=true")
        .option("dbtable", this.database + "." + this.table)
        .option("truncate",true)
        .option("batchSize", bSize)
        .option("user", this.user)
        .option("password", this.password)
        .save()

SaveMode有四个模式，默认是ErrorIfExists

SaveMode
ErrorIfExists	如果数据库中已经存在该表，则会直接报异常，导致数据不能存入数据库
Append	如果数据库中表已经存在，则追加在该表中；若该表不存在，则会先创建表，再插入数据
Overwrite	先将已有的表及其数据全都删除，再重新创建该表，最后插入新的数据；
Ignore	若表不存在，则创建表，并存入数据；在表存在的情况下，直接跳过数据的存储，不会报错

注意Overwrite 是先删表再建表，如果不想删表，而是要清空表的数据再插入新的数据需要设置.option(“truncate”,true)

4.修改dataframe

下面的处理是把df中为空的字段用null替换，形成一个新的df2，
我替换的原因是把df插入到我的mysql中时datetime字段会报错
Data truncation: Incorrect datetime value: ‘’
因为我的mysql的datetime字段不允许插入空值，但可以插入null
注意不是所有MySQL的datatime都不允许插入空值，因为mysql的版本和设置不同

def changeRow(r: Row) = {
    val l = r.length
    var z: Array[String] = new Array[String](l)
    for (i <- 0 until l) {
      if (r(i) != null) {
        if (r(i).toString().length() == 0) {
          z(i) = null

        } else {
          z(i) = r(i).toString()
        }
      }
    }
    z.toSeq
  }
 val rows = df.rdd.map(r => changeRow(r)).map(s => Row.fromSeq(s))
 val df2 = spark.createDataFrame(rows, df.schema)