SparkJDBC并行查询RDBMS数据库的参数方法

当通过spark读取mysql时,如果数据量比较大,为了加快速度,通常会起多个task并行拉取mysql数据。
api:

def
jdbc(url: String, table: String, columnName: String, lowerBound: Long, upperBound: Long, numPartitions: Int, connectionProperties: Properties): DataFrame

Spark JDBC连接关系型数据库方式如下:

// 读出数据
val jdbcDF = spark.read()
  .format("jdbc")
  .option("url", "jdbc:mysql://dbserver:port/db")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()


val connectionProperties = new Properties()
connectionProperties.put("user", "username")
connectionProperties.put("password", "password")

val jdbcDF2 = spark.read()
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)
// Specifying the custom data types of the read schema
connectionProperties.put("customSchema", "id DECIMAL(38, 0), name STRING")
val jdbcDF3 = spark.read
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)


// 写入数据
jdbcDF.write
  .format("jdbc")
  .option("url", "jdbc:mysql://dbserver:port/db")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

jdbcDF2.write
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)


// 在写入时指定创建表列数据类型
jdbcDF.write
  .option("createTableColumnTypes", "name CHAR(64), comments VARCHAR(1024)")
  .jdbc("jdbc:mysql://dbserver:port/db", "schema.tablename", connectionProperties)

Spark通过JDBC读取关系型数据库,默认查全表,只有一个任务去执行查询操作,大量数据情况下,效率是很慢的,也经常会卡死。  这时,可以通过构造多个任务并行连接RDBMS提升效率, 如下:

Spark SQL JDBC属性介绍

增加SparkSQL参数来构造多个任务(并行)来提升效率:

参数名含义
url

要连接的JDBC URL。可以在URL中指定特定于源的连接属性。例如,jdbc:postgresql://localhost/test?user=fred&password=secret

或者 jdbc:datadirect:greenplum// hostname:[ port ] [; property = value [; ...]]

dbtable应该读取的JDBC表。请注意,可以使用在SQL查询的FROM子句中有效的任何内容。例如,您也可以在括号中使用子查询,而不是完整的表:
.option("dbtable", s"(SELECT * FROM public.db_table limit 100")
driver用于连接到此URL的JDBC驱动程序的类名
partitionColumn,lowerBound,upperBound如果指定了其中任何一个选项,则必须全部指定这些选项。它们描述了在从多个工作者并行读取时如何对表进行分区。partitionColumn必须是相关表中的数字列。请注意,lowerBound和upperBound仅用于决定分区步幅,而不是用于过滤表中的行。因此,表中的所有行都将被分区并返回。此选项为(选填)
numPartitions表读取和写入中可用于并行的最大分区数。这还确定了最大并发JDBC连接数。如果要写入的分区数超过此限制,我们通过在写入之前调用coalesce(numPartitions)将其减少到此限制。此选项为(选填)

更详细参数:JDBC到其他数据库

Ps:lowerBound和upperBound仅用于决定划分分区时的步长,而不是用于按照这两个值对数据进行过滤。 因此,无论这两个值如何设置,表中的所有行都将被读取。

同时需要注意的是,尽量不要创建太多分区,否则很容易将mysql搞挂。

关于具体的分区示例代码,参考如下(本部分代码参考spark源码org.apache.spark.sql.execution.datasources.jdbc中columnPartition方法 )。代码如下:

  

1.单分区模式

只需要传入JDBC URL、表名及对应的账号密码Properties即可。但是计算此DF的分区数后发现,这种不负责任的写法,并发数是1

Scala> jdbcDF.rdd.partitions.size=1

操作大数据集时,spark对MySQL的查询语句等同于可怕的:select * from table; ,而单个分区会把数据都集中在一个executor,当遇到较大数据集时,都会产生不合理的资源占用:MySQL可能hang住,spark可能会OOM,所以不推荐生产环境使用;


参考:Spark JDBC系列--取数的四种方式(https://www.jianshu.com/p/c18a8197e6bf)

 

Spark 并行查询RDBMS

通过增加 Task 数量来提升访问关系型数据(这里gp为例)的效率,大致有以下两种方法:

一、指定 numPartitions,partitionColumn,lowerBound 参数

val spark = SparkSession
  .builder()
  .config("spark.sql.warehouse.dir", warehouseLocation)
  .appName("load data from gp test")
  .getOrCreate()

// 开始时间
val startTime = System.currentTimeMillis()

val gpRDF = spark.read
  .format("jdbc")
  .option("driver", "com.pivotal.jdbc.GreenplumDriver")
  .option("url", "jdbc:pivotal:greenplum://ip:port;DatabaseName=testdb")
  .option("partitionColumn", "person_id")
  .option("lowerBound", lowerBound)
  .option("upperBound", upperBound)
  .option("numPartitions", numPartitions)
  .option("dbtable", "public.t_test")
  .option("user", "gpadmin")
  .option("password", "gpadmin")
  .load()

二、"dbtable"参数构造子查询

不用 numPartitions,partitionColumn, lowerBound, upperBound,可以通过 dbtable 构造子查询,并行执行多个查询得到多个结果 RDD,最后通过 reduce 合并成一个 RDD。

Ps:针对表数据量很大且资源不足的情况很有用,分段对全量数据进行计算再汇总

val stride = Math.ceil(dataNums / numPartitions).toInt

val spark = SparkSession
  .builder()
  .config("spark.sql.warehouse.dir", warehouseLocation)
  .appName("load data from gp")
  .getOrCreate()

// 创建 numPartitions 个 task
val registerDF = Range(0, numPartitions)
  .map(index => {
    spark
      .read
      .format("jdbc")
      .option("driver", "com.pivotal.jdbc.GreenplumDriver")
      .option("url", "jdbc:pivotal:greenplum://ip:port;DatabaseName=testdb")
      .option("dbtable", s"(SELECT * FROM public.t_test WHERE person_id > ${stride * index} AND person_id <= ${stride * (index + 1)}) AS t_tmp_${index}")
      .option("user", "gpadmin")
      .option("password", "gpadmin")
      .load()
  })
  .reduce((rdd1, rdd2) => rdd1.union(rdd2))

总结

对于上述的两种方式:

第一种有时会造成数据分布不均匀,有些 task 数据量很大,有些 task 数据量几乎为 0,这是因为 Spark 是根据指定的分区列 partitionColumn 来进行分区,如果指定的 partitionColumn 不是连续的数(分布不均匀),那么每个 task 中的数据量就会分配不均匀;

第二种自定义 sql,相对可控,当然自定义也就意味着代码要稍微复杂。

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页