SparkSql优化-读取MySql慢优化-写入MySql慢优化-表关联注意

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

SparkSqk读mysql计算写入mysql的踩坑和优化记录

---

一、SparkSql读取mysql慢优化

spark执行的时候非常慢首先观察的是spark的UI界面找到哪个地方慢在刚开始的时候就卡着不动，多半是spark读取mysql过慢

SELECT * FROM information_schema.processlist WHERE command='Query' ORDER BY time desc;

查看mysql的进程，在spark读取的表的库里面执行，找到spark读取的表观察读取sql，和执行时间，是否锁表，是否使用索引
他这里是一个链接在读取数据，可以增加读取mysql的链接数

    val order: DataFrame = spark.read
      .format("jdbc")
      .option("url", ConnInfoUtils.getAddress("MG", "dw-product",1,"1"))
      .option("dbtable", "dim_variant_order_day")
      .option("user", ConnInfoUtils.getUserPassword("user"))
      .option("password", ConnInfoUtils.getUserPassword("password"))
      .option("driver", ConnInfoUtils.getUserPassword("driver"))
      .option("numPartitions", 50)
      .option("partitionColumn", "id")
      .option("lowerBound", "1")
      .option("upperBound", "9000000")
      .load()
    order.createOrReplaceTempView("dim_variant_order_day")

参数介绍
dbtable： 表名
url/user/password/driver ：数据库链接信息
numPartitions ：预期的分区数
lowerBound：分区列的最小值
upperBound:分区列的最大值
partitionColumn:用于分区的列，必须是数字类型和时间类型
partitionColumn这里数值类型自增的最好
如果使用时间分区报错解决不了可以使用一下方式

         //时间分片
      val ymdFormat = new SimpleDateFormat("yyyy-MM-dd")
      val day1: Calendar = Calendar.getInstance()
      val start: Date = ymdFormat.parse("2020-01-01")
      //开始时间 结束时间 切分天数 分区字段
      val strings2: util.List[String] = DateSplitUtils.splitByDay(start,day1.getTime, 30,"createDate")
      val str3: String = strings2.toArray().mkString(",")
      val predicates: Array[String] = str3.split(",")
/*
 val predicates = Array[String]("createDate < '2019-01-01'",
      "createDate>='2019-01-01' and createDate<'2020-01-01'",
      "createDate>='2020-01-01' and createDate<'2020-02-01'",
      "createDate>='2020-02-01' and createDate<'2020-03-01'",
      "createDate>='2020-03-01' and createDate<'2020-04-01'",
      "createDate>='2020-04-01'"
*/
      //在链接里面多加入Array[String]的参数
      val associated: DataFrame = spark.read.jdbc(ConnInfoUtils.getAddress("MG", "dsp", 1, "1"), "db_accproduct", predicates, ConnInfoUtils.getProperties())
      associated.createOrReplaceTempView("db_accproduct")

在链接里面加入 Array[String] predicates参数，对表里面的时间进行切分在读取的时候会按照自定义的时间段去取区数据

在读取的时候一定要多关注mysql的链接信息和spark的ui界面关注读取速率

二、spark写入mysql数据慢

在写入的时候要关注mysql的链接数量，和写入sql是否是批量和链接数量是否过少

1.批量写入mysql数据

修改链接mysql的url开启批量写入，这里多条输入据合并到一个insert语句里面，url里面添加 &rewriteBatchedStatements=true

jdbc:mysql://192.168.4.22:3306/dsp?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&useSSL=false&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai

2.计算完之后在重新分区，防止数据倾斜写入的时候特别慢

在sql后面加入 distribute by rand()

3.调整shuffle的分区数量

config("spark.sql.shuffle.partitions","500")

三、SparkSql关联查的坑

1.spark 区分大小写 mysql不区分，关联查的时候尽量都转大写，并且要去除前后空格

on trim(upper(t1.SKU))=trim(upper(t5.sku))

2.SparkSql时区问题

spark 指定时区后，可能不造成数据计算错误和mysql不一致，数据链接时区、代码指定时区、运行指定时区 这几个一定要一样，在计算结果后一定要校验计算前和计算后的时间
spark 的date和to_date等时间处理函数，会自动对时间进行时区转化，

3.spark在进行比较或者过滤的时候是区分类型的

mysql在对 数值类型比较的时候 可以 用 <>’’ 不等于空串 这个是不起作用的
可以用 not in(’’,0)