Pyspark-数据导入任务从374min降到7min

概述

数据量8000w，存储空间5G。将hive中的数据导入到后台Mysql中，采用Pyspark组件，通过参数调整将任务执行时间从374min降低到7min

背景

任务是将hive中增量数据（8000w条/天）导入到后台Mysql中，并实现每天例行化，但由于数据量过大，导致spark写入效率极低

优化思路

Pyspark代码

from pyspark.sql import SparkSession
from pyspark import SparkContext
import sys
day = sys.argv[1]  # 参数
sc = SparkContext()
# 对于mysql8.x系列的，需要在venus的pyspark作业的"--jars"配置项中上传8.x系列的mysql jdbc驱动。
# mysql jdbc驱动下载地址：https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.25/mysql-connector-java-8.0.25.jar
# 如果是其他db，只需要把jdbc驱动、以及如下代码中的mysql8driver、driver、dburl调整成对应
def executeSql(url, sql):
    try:
        mysql8driver = sc._jvm.com.mysql.cj.jdbc.Driver()
        sc._jvm.java.sql.DriverManager.registerDriver(mysql8driver)
        conn = sc._jvm.java.sql.DriverManager.getConnection(url)
        stat = conn.createStatement()
        stat.executeUpdate(sql)
    finally:
        conn.close()
spark = SparkSession.builder.getOrCreate()
df = spark.sql(" \
    select \
    ds, \
    imp_date, \
    articleid,\
    vid, \
    content_type, \
    pubtime, \
    is_current_politics, \
    app, \
    readpv_or_playvv, \
    likevv, \
    commentpv \
    from xxxx.xxxxx\
    where ds = %s" % day)  # 查询hive表数据
# 写mdb
dburl = "jdbc:mysql://xxx.xxx.xxx:3306/omarticle"
# executeSql(dburl, " \
#     delete \
#     from \
#     omarticle.tbl_ads_qiehao_article_and_video_di_tmp_%s \
#     where ds = %s" % (day, day)) #先清除旧数据  已修改
w = df.write.format("jdbc").options(
    url = dburl,
    driver = "com.mysql.cj.jdbc.Driver",
    user = 'root',
    password = 'xxxx',
    dbtable = "xxxxxx.xxxxxxx%s" % day,  # 已修改
    rewriteBatchedStatements = 'true', # 添加rewriteBatchedStatements=true
    batchsize = 1000000,  # 每批数据大小
    isolationLevel = 'NONE',  # 事务隔离
    truncate ='true',
    characterEncoding = 'UTF8',
    useUnicode = 'true'
).mode("overwrite").save()
# 关于df.write.format("jdbc").options中，options内的更多调优参数，请参考 https://spark.apache.org/docs/2.2.0/sql-programming-guide.html#jdbc-to-other-databases
# 插入完成后，统计数量
countSql = "( \
            select \
            count(1) as cnt \
            from xxxxx.xxxxx%s \
            where ds = %s) t" % (day, day)  # 一定要这样写(select xx) t    已修改cnt
spark.read.format("jdbc").options(
    url=dburl,
    driver="com.mysql.cj.jdbc.Driver",
    user = 'root',
    password = 'xxxx',
    characterEncoding = 'UTF8',
    useUnicode = 'true',
    dbtable = countSql
).load().collect()[0]['cnt']  # 获取导入后的数量   已修改cnt

优化点

1. 使用.mode(“overwrite”)代替delete…where…
   采用覆盖的方式导入数据，防止因报错二次导入数据出现重复数据，因此最初在插入数据之前，会先执行delete语句将当天表格中的数据删除，随后再执行写入操作。
   优化：使用overwrite模式替代delete语句，直接完成数据表的覆盖，在overwrite模式下，可以设置truncate参数，以保证只删除表数据，不会影响到表结构

2. df.write.format(“jdbc”).option()中设置参数
   影响写入操作的参数：rewriteBatchedStatements、batchsize、truncate、isolationLevel

numPartitions：这些options仅适用于read数据。这些options必须同时被指定。他们描述，如何从多个workers并行读取数据时，分割表。partitionColumn必须是表中的数字列。lowerBound和upperBound仅用于决定分区的大小，而不是用于过滤表中的行。表中的所有行将被分割并返回。
fetchsize：仅适用于read数据。JDBC提取大小，用于确定每次获取的行数。这可以帮助JDBC驱动程序调优性能，这些驱动程序默认具有较低的提取大小（例如，Oracle每次提取10行）。
batchsize：仅适用于write数据。JDBC批量大小，用于确定每次insert的行数。这可以帮助JDBC驱动程序调优性能。默认为1000。
isolationLevel：仅适用于write数据。事务隔离级别，适用于当前连接。它可以是一个NONE，READ_COMMITTED，READ_UNCOMMITTED，REPEATABLE_READ，或SERIALIZABLE，对应于由JDBC的连接对象定义，缺省值为标准事务隔离级别READ_UNCOMMITTED。
truncate：仅适用于write数据。当SaveMode.Overwrite启用时，此选项会truncate在MySQL中的表，而不是删除，再重建其现有的表。这可以更有效，并且防止表元数据（例如，索引）被去除。但是，在某些情况下，例如当新数据具有不同的模式时，它将无法工作。它默认为false。
createTableOptions：仅适用于write数据。此选项允许在创建表（例如CREATE TABLE t (name string) ENGINE=InnoDB.）时设置特定的数据库表和分区选项。
rewriteBatchedStatements：开启批处理

参考

https://blog.csdn.net/cancer_t/article/details/108398739

https://spark.apache.org/docs/2.2.0/sql-programming-guide.html#jdbc-to-other-databases