使用Spark SQL JDBC

柔情只为你懂 2022-12-29 06:53 149阅读 0赞

# Spark SQL JDBC #

## 写 ##

我们可以使用一个 JDBC 的链接来定义一个 Spark SQL 的表或者视图，这里用表来做示例：

我们先在 mysql 中建立一个需要同步的表 test：

CREATE TABLE my.test (
    	id BIGINT ( 20 ) PRIMARY KEY NOT NULL auto_increment,
    	create_time TIMESTAMP NOT NULL COMMENT '创建时间',
    	name VARCHAR ( 127 ) COMMENT '用户姓名',
    	age INT COMMENT '用户年龄'
    );

然后在 Spark SQL 中建立一张关联表关联 mysql 中的表:

CREATE TABLE IF NOT EXISTS bi.test
    USING jdbc
    OPTIONS (
      url "jdbc:mysql://localhost:3306",
      dbtable "my.test",
      user "root",
      password "123456"
    )

当我们使用 Spark SQL 向定义好的表 bi.test 中插入数据，同时也会将数据传输到 mysql 的 my.test 表。

insert into bi.test
    select null, from_unixtime(unix_timestamp()) as create_time, name, age from other_table;

注意，如果你没有在 mysql 中提前定义好 my.test 表，Spark SQL 会自动帮你创建，不过此时 my.test 表的定义格式会和你预期的不符，即字段类型可能不是自己想要的类型。故我们提前定义好 my.test 表。

同样我们也可以使用 overwrite 插入数据:

insert overwrite table bi.test
    select null, from_unixtime(unix_timestamp()) as create_time, name, age from othertable;

但是此时会导致提前建好的 my.tes t表**被删除，然后重新创建**，这里的重新创建是 Spark SQL 给你创建的，这里就会有一个问题，就是上面所说的此时 Spark SQL 自动创建的my.test表的定义格式会和你预期的不符。

那如何解决这种问题呢？我们可以自己清空 bi.test 然后再使用 insert into 插入数据。

truncate table bi.test;
    insert into bi.test
    select null, from_unixtime(unix_timestamp()) as create_time, name, age from othertable;

这样做除了可以保持表是自己定义的结构，同时还会让自增的 id 重新从1开始计数。

## 读 ##

> 参考：https://docs.databricks.com/data/data-sources/sql-databases.html\#optimize-performance-when-reading-data

Spark SQL JDBC 读小表性能问题不大，但是读大表性能就堪忧了。为了并行读取数据，Spark JDBC数据源必须配置适当的分区信息，以便它能够向外部数据库发出多个并发查询。如果您忽略了配置分区，那么将使用一个JDBC获取 driver 所需的所有数据，这可能会导致 driver 抛出**OOM异常**。

下面是一个配置了分区的JDBC读取示例:

分区列:partitionColumn，指定为表中的列名，两个范围端点(lowerBound, upperBound)，以及指定最大分区数的numPartitions参数。

CREATE TABLE IF NOT EXISTS bi.test
    USING jdbc
    OPTIONS
    (
      url "jdbc:mysql://localhost:3306",
      dbtable "my.test",
      user "root",
      password "123456",
      partitionColumn "id",
      lowerBound 1,
      upperBound 10000,
      numPartitions 5
    );

这里有两个注意点：因为读取数据会全表扫描，partitonColumn指定的列最好是数据库的索引列。numPartitons要考虑从外部数据库读取数据时使用过多的分区可能会使数据库的查询过多，从而使数据库过载。

如果我们只需要数据库表中的部分数据，可以通过下面的方法限定读取的数据量：

CREATE TABLE IF NOT EXISTS bi.test
    USING jdbc
    OPTIONS
    (
        url "jdbc:mysql://localhost:3306",
        dbtable "(select * from my.test where report_day = '2021-05-01') alias",
        user "root",
        password "123456"
    );