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的读取速度。已经达到了一个很高的性能。