spark增量抽取MySQL中的数据存入hive动态分区表（2）

老版本：spark增量抽取MySQL中的数据存入hive动态分区表（1）

因为之前那个版本写的比较捞，所以有了这篇文章。

阅读前提： 懂动态分区以及SQL左连接，不会可以看这两篇文章，Hive —— 动态分区表，SQL之全外连接，左外连接，右外连接，内连接。

本文用到的sql数据源下载地址：nba_clean_data.sql

ok! let is go!

说明： 我在 hive 创建了一个 sql 对应的数据表，用于模拟增量抽取，其中存放了 2333 条数据；此案例是通过spark提交jar包到集群运行。

增量抽取的步骤如下：

1. 读取Mysql 中的数据源；
2. 使用spark sql编写代码进行增量抽取；
3. 结果验证。

实现需求：读取 mysql 中 dw.nba_data 表，根据字段 id 抽取增量数据到 hive 中 对应的表 dw.nba_data 中，根据字段 firstTime 中的年份添加动态分区，firstTime 原值如：2022-08-09。

代码如下：

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

object increment {
  def main(args: Array[String]): Unit = {

    val spark: SparkSession = SparkSession
      .builder()
      .appName("increment")
      .master("local[*]")
      .enableHiveSupport()
      .getOrCreate()

    
    spark.sql("set hive.exec.dynamic.partition=true")

    spark.sql("set hive.exec.dynamic.partition.mode=nonstrict")

    /******************* Begin *******************/
    // 1.读取 Mysql 中的数据源；
    spark.read
      .format("jdbc")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("url", "jdbc:mysql://127.0.0.1:3306/dw")
      .option("dbtable", "nba_data")
      .option("user", "root")
      .option("password", "123123")
      .load()
      .createOrReplaceTempView("data")

  // 2.使用 spark sql 编写代码进行增量抽取；
    spark.sql("""
        |insert into table dw.nba_data partition(year)
        |select t1.*,regexp_extract(t1.firstTime,"([0-9]{4})") year 
        |from 
        |   data t1 
        |left join 
        |   dw.nba_data t2 
        |on
        |   t1.id = t2.id 
        |where t2.id is null
        |""".stripMargin)


    /******************* End *******************/


    spark.stop()

  }
}
 

核心抽取代码解释：

    spark.sql("""
        |insert into table dw.nba_data partition(year)
        |select t1.*,regexp_extract(t1.firstTime,"([0-9]{4})") year 
        |from 
        |   data t1 
        |left join 
        |   dw.nba_data t2 
        |on
        |   t1.id = t2.id 
        |where t2.id is null
        |""".stripMargin)

我这里是通过左连接与 where 查询过滤进行抽取，原理很简单。首先，t1 表为 mysql 中的数据表，t2 表为 hive 中的数据表，根据字段id对数据进行增量抽取。利用左连接的特性：收集左表的全部数据，以及右表符合连接条件的数据，也就是t1.id = t2.id，其余不相等的会自动填充null值，然后我们通过where查询过滤出所有为null值的数据，这样就找出了所有新id的数据啦。

注意，select t1.*,regexp_extract(t1.firstTime,"([0-9]{4})") year这一部分的写法哦！


结果验证：直接上 hive，执行 select count(distinct(id)) from dw.nba_data;，查看数量是否变多。