15分钟学会SparkSQL通过JDBC连接外部数据库（PostgreSQL为例）

本文基于Spark2.1.0版本

1，先简单介绍一下通过SparkSQL JDBC连接数据库的好处，不过这不是本文的重点 。

JDBC(Java DataBase Connectivity)：是一套统一访问各类数据库的标准Java接口，为各个数据库厂商提供了标准的实现。通过JDBC技术，开发人员可以用纯Java语言和标准的SQL语句编写完整的数据库应用程序，并且真正地实现了软件的跨平台性。

Spark SQL also includes a data source that can read data from other databases using JDBC. This functionality should be preferred over using JdbcRDD. This is because the results are returned as a DataFrame and they can easily be processed in Spark SQL or joined with other data sources. The JDBC data source is also easier to use from Java or Python as it does not require the user to provide a ClassTag.

简单点来说，通过JDBC连接DB，得到DataFrame，可以更容易、更高效的使用Spark SQL引擎来处理数据（优于JdbcRDD）。

2，官方提供了2种使用JDBC接口的方法。

第一种：

使用DataFrameReader 类提供的load()方法从指定数据库读取数据

对应的代码是：

Dataset<Row>jdbcDF = spark.read().format("jdbc")   #代表使用jdbc方式

.option("url", "jdbc:postgresql:dbserver") 

#url的格式是jdbc:postgresql://数据库IP:端口号/数据库名称

.option("dbtable", "schema.tablename") #要访问的具体的表

.option("user", "username")                      #操作该数据库的用户名

.option("password", "password")              #操作该数据库的用户密码

.load();

使用DataFrameWrite类的save()方法向指定数据库保存数据

对应的代码是：

jdbcDF.write().format("jdbc")                       #代表使用jdbc方式

.option("url", "jdbc:postgresql:dbserver")

#url的格式是jdbc:postgresql://数据库IP:端口号/数据库名称

.option("dbtable", "schema.tablename")    #要访问的具体的表

.option("user", "username")                          #操作该数据库的用户名

.option("password", "password")                 #操作该数据库的用户密码

.save();

第二种：

使用DataFrameReader 类提供的jdbc()方法从指定数据库读取数据

对应的代码是：   

Properties connectionProperties = new Properties();   #实例化一个Properties对象

connectionProperties.put("user", "username");           #保存访问数据库的用户名

connectionProperties.put("password", "password");  #保存访问数据库的用户密码

DatasetjdbcDF2=spark.read().jdbc("jdbc:postgresql:dbserver","schema.tablename",

connectionProperties);

#url的格式是jdbc:postgresql://数据库IP:端口号/数据库名称，第二个参数是具体访问的表名称

使用DataFrameWrite类的jdbc()方法向指定数据库保存数据

对应的代码是：

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

#url的格式是jdbc:postgresql://数据库IP:端口号/数据库名称，第二个参数是具体访问的表名称

两种方法区别不大，从数据库读取数据时都返回DataFream，不过第二种方法在保存数据时，代码量会少一点，因为它在读取数据的时候，已经保存了连接属性connectionProperties。

3，下面，我会从一步一步的来教你如何使用这些方法。

首先，找一台主机来安装PostgreSQL：

我的操作系统是Centos 7.2.1511，自带的PostgreSQL资源是9.2版本。

root用户下执行： yum install postgresql*

安装完毕后，继续在root用户下执行：postgresql-setup initdb #初始化数据库

修改 /var/lib/pgsql/data/postgresql.conf  #允许远程访问

listen_addresses ='*'

port=5432

修改 /var/lib/pgsql/data/pg_hba.conf        #允许远程访问

# IPv4 local connections:

host  all       all       127.0.0.1/32      trust 

host  all      all      0.0.0.0 0.0.0.0  trust (如果想对密码强验证，可以写成md5) 

我没有开启防火墙，如果你开启了防火墙，请自行修改允许要访问数据库服务器的IP通过。

之后启动服务：

systemctl start postgresql

下面进入psql，创建新的用户、数据库、表。

su - postgres       #切换用户，执行后提示符会变为 '-bash-4.2

psql -U postgres #登录数据库，执行后提示符变为 'postgres=#'

CREATE USER test WITH PASSWORD '123456';  #创建新的用户test及设置密码为123456

CREATE DATABASE mypsql;          #创建新的数据库mypsql

\c mypsql;                                         #切换到新创建的数据库mypsql

create table mytable(t1 int, t2 int);  #创建一张表，有2列

数据库相关的配置完成了，为了SparkSQL通过JDBC访问PostgreSQL，我们还应该下载对应版本的依赖包postgresql-9.2-1002-jdbc4.jar，并把它放在所有节点的SPARK_HOME/jars下。 

地址：www.findmaven.com/detail/central/org.jumpmind.symmetric.jdbc/postgresql/9.2-1002-jdbc4/0/jar/20/1

ok，所有的准备工作做完了，下面就开始进行jdbc调用的步骤了。

4，两种方法来调用JDBC。

第一种，通过java代码生成jar包，使用spark-submit方式提交应用程序来访问PostgreSQL数据库。（下面会将前文说到的两种API方法都写成代码）

load、save方法访问数据库

jdbc方法访问数据库

可以看到，代码中通过JDBC接口，在PostgreSQL的数据库中生成了test1，test2表（这是以前操作找到的图，数据库和表的名字不是本文中说的mypsql、mytable等，请自动忽略我的懒惰）

第二种方式，是通过spark-shell交互式的访问PostgreSQL，结果就不贴图了，和上面的效果一样。

load／save方法读写数据库

val jdbcDF = spark.read.format("jdbc").option("url", "jdbc:postgresql://wl1:5432/mypsql")

.option("dbtable", "mytable").option("user", "test")

.option("password", "123456").load()   #load方法读

jdbcDF.write.format("jdbc").option("url", "jdbc:postgresql://wl1:5432/mypsql")

.option("dbtable", "mytable").option("user", "test")

.option("password", "123456").save()  #save方法写

#jdbc方法读写数据库

val connectionProperties = new Properties()

connectionProperties.put("user", "test")

connectionProperties.put("password", "123456")

val jdbcDF2 = spark.read

.jdbc("jdbc:postgresql://wl1:5432/mypsql", "mytable", connectionProperties) #jdbc方法读

jdbcDF2.write

.jdbc("jdbc:postgresql://wl1:5432/mypsql", "mytable", connectionProperties) #jdbc方法写

5，上述操作，在4040 web ui的显示

SQL页面

Job页面

Stages页面

作者：俺是亮哥
链接：https://www.jianshu.com/p/cc67f2638473