Spark通过JDBC读取和写入MySQL（postgreSQL）

 背景说明

Spark SQL 还包括一个数据源，该数据源可以使用 JDBC 从其他数据库读取数据。这 功能应优先于使用 JdbcRDD。 这是因为返回了结果 作为 DataFrame，它们可以很容易地在 Spark SQL 中处理或与其他数据源联接。 JDBC 数据源也更易于从 Java 或 Python 使用，因为它不需要用户 提供 ClassTag。

说明：JDBC加载和保存可以通过load/save或JDBC方法实现。

参考官方文档：http://spark.apache.org/docs/2.4.8/sql-data-sources-jdbc.html

1．从JDBC源加载数据

Dataset<Row> jdbcDF = spark.read()
  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load();

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

Dataset<Row> jdbcDF2 = spark.read()
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties);

 

2．将数据保存到JDBC源

jdbcDF.write()
  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save();

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

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

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

 

3．影响JDBC读取和写入的属性

属性名称	含义
url	要连接到的JDBC URL。可以在URL中指定特定于源的连接属性。 例如：jdbc:postgresql://localhost/test?user=fred&password=secret
dbtable	应该从中读取或写入的JDBC表。请注意，在读取路径中使用它时，可以使用SQL查询的FROM子句中有效的任何内容。例如，您也可以使用括号中的子查询来代替完整的表。不允许同时指定“dbtable”和“query”选项。 dbtable使用SQL查询示例：（SELECT * FROM test_part3 WHERE ds BETWEEN 20230101 AND 20230103) AS T
query	将用于将数据读取到Spark中的查询。指定的查询将用括号括起来，并用作FROM子句中的子查询。Spark还将为子查询子句分配一个别名。例如，spark将向JDBC源发出以下形式的查询。 SELECT <columns> FROM (<user_specified_query>) spark_gen_alias
	以下是使用此选项时的几个限制。 1、不允许同时指定“dbtable”和“query”选项。 2、不允许同时指定“query”和“partitionColumn”选项。当需要指定“partitionColumn”选项时，可以使用“dbtable”选项来指定子查询，并且可以使用作为“dbtable’的一部分提供的子查询别名来限定分区列。
	例子: spark.read.format("jdbc") .option("url", jdbcUrl) .option("query", "select c1, c2 from t1") .load()
driver	用于连接到此URL的JDBC驱动程序的类名。
partitionColumn, lowerBound, upperBound	如果指定了其中任何选项，则必须全部指定这些选项。此外，必须指定numPartitions。它们描述了在从多个工作线程并行读取时如何对表进行分区。 partitionColumn必须是相关表中的数字、日期或时间戳（numeric, date, or timestamp）列。请注意，lowerBound和upperBound用于指定分区列的下界和上界，仅用于决定分区步长，而不用于筛选表中的行，因此表中的所有行都将被分区并返回。此选项仅适用于读取数据。
numPartitions	在表读取和写入过程中可用于并行的最大分区数。这也决定了并发JDBC连接的最大数量。如果要写入的分区数超过了这个限制，我们会在写入之前通过调用coalize（numPartitions）将其减少到这个限制。
queryTimeout	驱动程序将等待Statement对象执行到给定秒数的秒数。零意味着没有限制。在写入路径中，此选项取决于JDBC驱动程序如何实现API setQueryTimeout，例如，h2 JDBC驱动程序检查每个查询的超时，而不是整个JDBC批处理的超时。默认值为0。
fetchsize	JDBC获取大小，它决定每次往返要获取多少行。这有助于JDBC驱动程序的性能，JDBC驱动程序默认为低获取大小。此选项仅适用于读取数据。
batchsize	JDBC批处理大小，它决定每次往返要插入多少行。这有助于JDBC驱动程序的性能。此选项仅适用于写入数据。默认值为1000。
isolationLevel	应用于当前连接的事务隔离级别。它可以是NONE、READ_COMMITTED、READ_UNCOMITTED、REPEATABLE_READ或SERIALIZABLE中的一个，对应于JDBC的Connection对象定义的标准事务隔离级别，默认值为READ_UNCCOMMITED。
sessionInitStatement	在打开每个数据库会话到远程数据库之后，在开始读取数据之前，此选项将执行自定义 SQL 语句（或 PL/SQL 块）。使用它来实现会话初始化代码。例：option("sessionInitStatement", """BEGIN execute immediate 'alter session set "_serial_direct_read"=true'; END;""")
truncate	这是一个与 JDBC 编写器相关的选项。启用后，此选项将导致 Spark 截断现有表，而不是删除并重新创建它。这样可以提高效率，并防止删除表元数据（例如索引）。但是，在某些情况下，例如当新数据具有不同的架构时，它将不起作用。默认值为SaveMode.Overwritefalse 。此选项仅适用于写入数据。
cascadeTruncate	这是一个与JDBC写入相关的选项。如果JDBC数据库（目前是PostgreSQL和Oracle）启用并支持此选项，则此选项允许执行TRUNCATE TABLE t CASCADE（在PostgreSQL的情况下，执行TRUNCATE TABLE ONLY t CASCADE以防止无意中截断子代表）。这将影响其他表格，因此应小心使用。此选项仅适用于写入数据。它默认为相关JDBC数据库的默认级联截断行为，在每个JDBCDialect的isCascadeTruncate中指定。
createTableOptions	这是一个与JDBC写入相关的选项。如果指定，此选项允许在创建表时设置特定于数据库的表和分区选项（例如，CREATE table t（name string）ENGINE=InnoDB）。此选项仅适用于写入数据。
createTableColumnTypes	创建表时要使用的数据库列数据类型，而不是默认值。数据类型信息应以与CREATE TABLE列语法相同的格式指定（例如：“name CHAR（64），comments VARCHAR（1024）”）。指定的类型应该是有效的spark-sql数据类型。此选项仅适用于写入数据。
customSchema	用于从JDBC连接器读取数据的自定义模式。例如，“id DECIMAL（38，0），name STRING”。也可以指定部分字段，其他字段使用默认类型映射。例如，“id DECIMAL（38，0）”。列名应该与JDBC表的相应列名相同。用户可以指定Spark SQL对应的数据类型，而不必使用默认值。此选项仅适用于读取数据。
pushDownPredicate	启用或禁用谓词下推到JDBC数据源的选项。默认值为true，在这种情况下，Spark将尽可能向下推送JDBC数据源的过滤器。否则，如果设置为false，则不会向JDBC数据源下推任何筛选器，因此所有筛选器都将由Spark处理。当Spark执行谓词筛选的速度比JDBC数据源快时，谓词下推通常会关闭。

Java读取PostgreSQL表的示例：

        // 创建SparkSession
        SparkSession spark = SparkSession.builder()
                .appName("PostgreSQLReader")
                .master(master)
                .getOrCreate();

        // 读取PostgreSQL表,partitionColumn分区列的类型应当是numeric, date, or timestamp
        Dataset<Row> df3 = spark.read()
                .format("jdbc")
                .option("url", url)
                .option("dbtable", "(SELECT * FROM  <tableName> WHERE <ds> BETWEEN  <rangeStart>  AND  <rangeEnd> ) AS T")
                .option("user", user)
                .option("password", password)
                .option("partitionColumn",rangeColumn)
                .option("lowerBound",Integer.parseInt(rangeStart))
                .option("upperBound",Integer.parseInt(rangeEnd))
                .option("numPartitions",numpartitions)
                .option("fetchsize",fetchSize)                //仅适用于读数据，每次提取的行数
                .option("pushDownPredicate", true)            // 启用下推过滤
                .option("driver", "org.postgresql.Driver")
                .load();

        long num = df3.count();
        System.out.println("count: " + num);

        // 使用foreach进行遍历
        df3.foreach(row3 -> {
            System.out.println(row3.toString());
        });

        // 停止SparkSession
        spark.stop();