Spark读写Oracle性能深度调优

Spark作为一个分布式计算框架，可以很好的适用于Hadoop。那么Spark是否可以与关系型数据库较好的进行兼容呢？以Spark作为计算，以关系型数据库（例如Oracle）作为存储？
  答案当然是可以，笔者经过不断地测试和优化，终于将Spark与Oracle进行了兼容匹配与读写优化。

1、环境准备
  在本次测试环境中，用6台机器搭建了CDH的Hadoop平台，Spark2.2版本搭配Yarn进行资源分配。
  Spark通常采用JDBC来读写Oracle，所以在Spark环境下需要确保包含了JDBC包。笔者下载了ojdbc7.jar，放置在spark2的lib/spark2/jars目录下，避免每次spark2-submit指定jar包。

2、PySpark读写Oracle的基本代码
  PySpark读取Oracle数据表的常规代码案例如下

ods_bdz = spark.read.format(“jdbc”)
.option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “ODS_BDZ”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.load()

代码实现的功能是将数据库中的ODS_BDZ的表读取到名为ods_bdz的Dataframe中。其中dbstring为数据库连接字符串、dbuser为数据库用户名、dbpasswd为数据库用户对应的密码，具体的值根据你读取的数据库来填写。
  如果ODS_BDZ的数据量比较小，这种获取方式是可行的。如果ODS_BDZ的数据量达到了百万级、千万级或更多，这种方式的效率就会很低。
  PySpark写入数据到Oracle的常规代码案例如下

ods_bdz.write.jdbc(“jdbc:oracle:thin:@”+dbstring,
‘ODS_BDZ’,
‘append’,
{‘user’:dbuser,‘password’:dbpasswd,‘driver’:‘oracle.jdbc.driver.OracleDriver’}
)

以上实现的功能是将ods_bdz这个Dataframe中的数据以append的方式追加到表ODS_BDZ。

3、基于分区键的读优化
  如果ODS_BDZ这个表有一个数值的字段，叫PART_NUM，数值范围为1-20，那么我们可以优化成如下代码：

ods_bdz = spark.read.format(“jdbc”)
.option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “ods_bdz”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.option(“numPartitions”, 20)
.option(“partitionColumn”, “part_num”)
.option(“lowerBound”, 1)
.option(“upperBound”, 21)
.load()

这样子在oracle数据里面就会生成20条SQL：

Select * from ods_bdz where part_num<2;
Select * from ods_bdz where part_num<3 and part_num>=2;
Select * from ods_bdz where part_num<4 and part_num>=3;
……
Select * from ods_bdz where part_num>=20;

如果spark申请了20个executor的资源，那么相当于每个executor独立地连接oracle运行其中1条sql去获取数据，理论上效率可以提高20倍（实际上达不到20倍，会受到oracle的资源分配、网络、IO等影响），但是基本上效率可以提升5-10倍。
  对于90万（数据库中段大小约250M）的数据，不采用分区的方式加载缓存到spark内存的时间大约是40秒；采用分区键（3个分区）的方式加载缓存到spark内存的大约是13秒，性能提高了3倍。
  在JDBC的要求中，分区字段必须是数值，但是很多时候有些数据库表没有数值型的字段可以作为分区键，而且即使有分区字段，根据分区键的值分区后可能会导致数据倾斜，那么这些问题如何解决呢？

4、误区：采用ROWNUM自定义分区键
  当没有分区键，可以考虑采用ROWNUM的分页方式来定义分区键，那么以上的代码可以改写成如下形式：

ods_bdz = spark.read.format(“jdbc”)
.option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “(SELECT ROWNUM RN,A.* FROM ODS_BDZ A)”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.option(“numPartitions”, 20)
.option(“partitionColumn”, “RN”)
.option(“lowerBound”, 0)
.option(“upperBound”, 1000000)
.load()

这里将dbtable不采用表名，而是一个子查询，注意的是对于这个子查询要加上括号。通过ROWNUM命名的别名RN作为分区键，基于以上的修改，在ORACLE会生成20条SQL：

SELECT * FROM (SELECT ROWNUM RN,A.* FROM ODS_BDZ A) WHERE RN<50000;
SELECT * FROM (SELECT ROWNUM RN,A.* FROM ODS_BDZ A) WHERE RN<100000 and RN>=50000;
SELECT * FROM (SELECT ROWNUM RN,A.* FROM ODS_BDZ A) WHERE RN<150000 and RN>=100000;
……
SELECT * FROM (SELECT ROWNUM RN,A.* FROM ODS_BDZ A) WHERE RN>=950000;

看起来很完美，效率上也非常的高。但是我在1600万的数据加载过程中出现了数据重复和丢失现象，也就是说这20条SQL中的每条记录的RN号不是一定相同的，导致有的记录在2条或多条SQL中都被加载到了，而有的记录则没有覆盖。
  可能有人会质疑为什么不用ROW_NUMBER()OVER()或者增加ORDER BY的方式来固定ROWNUM，但是经过测试，这种在Oracle里做排序的时间太长，同样达不到效率提升。看来ROWNUM虽然效率高，但是加载的数据容易混乱，不能作为分区的手段。

5、采用ROWID自定义分区键
  ROWNUM伪列确实会出现不确定性，但是ROWID伪列是固定的，如果不对表进行行迁移、MOVE等操作，ROWID是不会发生变化的。基于此，我们采用ROWID的最后一位的ASCII码对20进行取模，得到的模是0-19之间的，这样就可以将这个值作为分区键，每条数据记录将会划分到固定的分区。
  那么代码可以改写为：

ods_bdz = spark.read.format(“jdbc”)
.option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “(SELECT MOD(ASCII(SUBSTR(ROWID,-1)),20) RN,A.* FROM ODS_BDZ A)”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.option(“numPartitions”, 20)
.option(“partitionColumn”, “RN”)
.option(“lowerBound”, 0)
.option(“upperBound”, 20)
.load()

这样子数据可以划分到20个区间，虽然会由于ROWID最后一位字符的ASCII码的不确定性，导致轻微的数据倾斜，但是在效率提升上仍然可以达到之前数倍的效果。

6、设置fetchsize优化读，batchsize优化写
  根据以上的分区，已经可以帮助我们提高几倍的效率，如果还希望能够进一步提高效率，可以设置fetchsize来提高读效率，设置batchsize来提高写效率。
  fetchsize设置的代码为：

ods_bdz = spark.read.format(“jdbc”)
.option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “(SELECT MOD(ASCII(SUBSTR(ROWID,-1)),20) RN,A.* FROM ODS_BDZ A)”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.option(“numPartitions”, 20)
.option(“partitionColumn”, “RN”)
.option(“lowerBound”, 1)
.option(“upperBound”, 1000000)
.option(“fetchsize”,100000)
.load()

Jdbc默认的fetchsize是10，这里设置的fetchsize为10万，可以略微提高读的效率。经过测试，将fetchsize分别设置为1万、10万、30万、100万，这几种值之间效率相差不大。

设置batchsize则需要将2中的代码格式修改一下，如下：

ods_bdz.write.mode(“append”).format(“jdbc”).option(“url”, “jdbc:oracle:thin:@”+dbstring)
.option(“dbtable”, “ODS_BDZ”)
.option(“user”, dbuser)
.option(“password”, dbpasswd)
.option(“driver”,‘oracle.jdbc.driver.OracleDriver’)
.option(“batchsize”,100000)
.save()

相对于不做设置效率会略有提升。

7、Linux随机数优化
  Linux随机数也会与Spark读写Oracle有关吗？确实如此。

在用Spark读取Oralce的过程中经常会出现以上的警告日志，而由于连接重置，导致了executor任务重做而导致数据读写时间变长。经过核查，发现是由于连接Oracle时Java使用的是Linux的/dev/random来生成安全随机数，而这种方式是阻塞的；将其修改为/dev/urandom来生成随机数就不会阻塞了，当然也会降低安全性。
找到$JAVA_HOME/jre/lib/ security目录下的java.security文件，将

securerandom.source=file:/dev/random

修改为

securerandom.source=file:/dev/./urandom

即可。

8、最终性能优化结果
  根据以上优化方式优化后，以6台机器构成的集群，40个executor通过自定义分区键从Oracle读取3100万的数据（在数据库存储约9.5G）到Spark的时间大约是2.5—3分钟，也就是大约50-60M/S的读取速度。已经达到了一个很高的性能。
————————————————
版权声明：本文为CSDN博主

「超叔csdn」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sbaz/article/details/84189138