【SparkSQL】partitionColumn, lowerBound, upperBound, numPartitions如何加速数据库抽取（oracle）

在SparkSQL中，读取数据的时候可以分块读取。例如下面这样，指定了partitionColumn，lowerBound，upperBound，numPartitions等读取数据的参数。简单来说，就是并行读取。

 

关于这四个参数的意思，SparkSQL官方解释是：

Property Name	Meaning
partitionColumn, lowerBound, upperBound	These options must all be specified if any of them is specified. In addition, numPartitions must be specified. They describe how to partition the table when reading in parallel from multiple workers. partitionColumn must be a numeric column from the table in question. Notice that lowerBound and upperBound are just used to decide the partition stride, not for filtering the rows in table. So all rows in the table will be partitioned and returned. This option applies only to reading.
numPartitions	The maximum number of partitions that can be used for parallelism in table reading and writing. This also determines the maximum number of concurrent JDBC connections. If the number of partitions to write exceeds this limit, we decrease it to this limit by calling coalesce(numPartitions) before writing.

 

从上面的解释来看，分区列得是数字类型；所谓的并行读取其实就是开了多个数据库连接，分块读取的。另外需要注意的是：

  Notice that lowerBound and upperBound are just used to decide the partition stride, not for filtering the rows in table. So all rows in the table will be partitioned and returned.

也就是说，这些参数的设置不会过滤数据，所以sql中读取了多少数据，那么返回的就是多少条数据，lowerBound和upperBound并不会过滤数据。那么如果说设置的lowerBound偏大（可能读取的数据中分区列的值比这个小），或者设置的upperBound数值设置的大小偏小（可能读取的数据中分区列中最大的值比upperBound大），这个时候数据是怎么读取和返回的呢？

举个例子：

如果一个表分10个分区读取，id为分区列，其值从0-101，但是设置的lowerBound是1，upperBound是100，那么读取的规则如下： 
第一个分区：select * from tablename where id<=10; 
第二个分区：select * from tablename where id >=10 and id<20; 
第三个分区：select * from tablename where id >=20 and id <30; 
…… 
第十个分区：select * from tablename where id >=90；
 

 
 人话

spark = SparkSession.builder\
            .appName('spark-oracle')\
            .enableHiveSupport()\
            .getOrCreate()
d_sql = "(select t.*,ROWNUM rownum_rn from ******* t)"
d_oracle = spark.read.format('jdbc')\
    .option("url",'jdbc:oracle:thin:@//******')\
    .option('dbtable',d_sql)\
    .option('user','****')\
    .option('password','****')\
    .option('fetchsize',100000)\
    .option('partitionColumn','rownum_rn')\
    .option('lowerBound',0)\
    .option('upperBound',500000)\
    .option('numPartitions',5)\
    .load()\
    .drop('rownum_rn')

d_oracle.show(10)

# 情况一：
if partitionColumn || lowerBound || upperBound || numPartitions 有任意选项未指定，报错
# 情况二：
if numPartitions == 1 忽略这些选项，直接读取，返回一个分区
# 情况三：
if numPartitions > 1 && lowerBound > upperBound 报错
# 情况四： 
numPartitions = min(upperBound - lowerBound, numPartitions)
if numPartitions == 1 同情况二
else 返回numPartitions个分区
delta = (upperBound - lowerBound) / numPartitions
分区1数据条件：partitionColumn <= lowerBound + delta || partitionColumn is null
分区2数据条件：partitionColumn > lowerBound + delta && partitionColumn <= lowerBound + 2 * delta
...
最后分区数据条件：partitionColumn > lowerBound + n*delta