Spark RDD 操作数据库

第1关：Spark RDD 插入数据

任务描述

本关任务：给定一个 RDD，通过 JDBC 的方式将其数据插入到 MySQL 数据库的 user 表中。

相关知识

为了完成本关任务，你需要掌握：

1. 什么是 JDBC；
2. 如何插入数据到 MySQL中。

JDBC 概述

JDBC（Java DataBase Connectivity）是一种用于执行 SQL 语句的 Java API，它由一组用 Java 语言编写的类和接口组成。换句话说：就是可以直接通过 Java 语言，去操作数据库。

讲到这里，相信有些会感到疑惑：从概念上来看，JDBC 是 Java 的 API，而我们用的是Scala 语言，怎么能使用 JDBC 操作数据库呢？

这里，我们需要给大家科普一下 Scala 和 Java 之间的联系。

Scala 来源于 Java，但又高于 Java。Scala 是一种有趣的语言。它一方面吸收继承了多种语言中的优秀特性，一方面又没有抛弃 Java 这个强大的平台，它运行在 Java 虚拟机（JVM）之上，轻松实现和丰富的 Java 类库互联互通。它既支持面向对象的编程方式，又支持函数式编程。

所以，我们虽然使用的不是 Java 语言，但我们依然可以通过调用 JDBC 的相关 API 来操作数据库。

JDBC API

JDBC API 提供以下接口和类：

• DriverManager：此类管理数据库驱动程序列表。可在 JDBC 下识别某个子协议的第一个驱动程序，用于建立数据库连接；

  方法名称	功能描述
  getConnection(String url,String user,String password)	指定 3 个入口参数（依次为连接数据库的 url、用户名和密码）来获取与数据库的连接
  setLoginTimeout()	获取驱动程序试图登陆到某一数据库时可以等待的最长时间，以秒为单位

• Connection：此接口具有用于联系数据库的所有方法。 Connection 对象表示通信上下文，即与数据库的所有通信仅通过连接对象；

  方法名称	功能描述
  createStatement	用于创建一个 Statement 对象来将 SQL 语句发送到数据库
  preparedStatement(String sql)	用于创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库
  commit()	使所有上一次提交/回滚后进行的更改成为持久更改
  close()	立即释放此 Connection 对象的数据库和 JDBC 资源，而不是等待它们被释放

• PrepareStatement：动态地执行 SQL，其将被预编译并保存到 PrepareStatement 实例中，从而可以反复地执行该 SQL 语句；

  方法名称	功能描述
  executeUpdate()	在此 PreparedStatement 对象中执行 SQL 语句，该语句必须是一个 DML 语句，或者无返回内容的 SQL 语句，比如 DDL 语句
  setInt(int parameterIndex, int x)	将指定的参数设置为 int 值
  setFloat(int parameterIndex, float x)	将指定的参数设置为 Float 值
  setString(int parameterIndex, String x)	将指定参数设置的给定的 String 值

• SQLException：此类处理数据库应用程序中发生的任何错误。

使用 JDBC 的步骤如下：

加载数据库驱动 → 建立数据库连接（Connection） → 创建执行 SQL 语句的 Statement 对象 → 返回结果 → 释放资源。

加载数据库驱动

驱动加载是为了打开与数据库的通信通道。

在注册驱动前我们需要装载特定厂商的数据库驱动程序，导入 mysq-connector-java 的 jar 包，这里我们使用的是 maven 项目，所以我们只需在 pom.xml 中 引入 MySQL 驱动的依赖即可（本实训已经在 pom.xml 中加入该依赖，无需自己再加）。

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

将 MySQL 驱动引入项目之后我们就开始注册驱动：

加载数据库驱动通常是使用 Class 类的静态方法 forName()，语法格式如下：

var conn: Connection = null
// 连接数据库，参数：连接的 url、MySQL 用户名、MySQL 密码
conn = DriverManager.getConnection(url, user, password);

如果加载成功，会将加载的驱动类注册给 DriverManager；加载失败，会抛出 ClassNotFoundException 异常。

建立连接

成功加载完数据库驱动后，就可以建立数据库的连接了，使用 DriverManager 的静态方法 getConnection() 来实现。如下:

var conn: Connection = null
// 连接数据库，参数：连接的 url、MySQL 用户名、MySQL 密码
conn = DriverManager.getConnection(url, user, password);

注意：需要抛出 SQLException 的异常。

URL 用于标识数据库的位置，通过 URL 地址告诉 JDBC 程序连接信息。

若不存在数据库，只建立连接，URL 的写法为：

若存在数据库 test，URL 的写法为：

其中 localhost 可以换成 IP 地址 127.0.0.1 ，3306 为 MySQL 数据库的默认端口号。

执行编写的 SQL 语句

连接建立完毕后，创建（获取）数据库操作对象，用于执行编写的 SQL 语句。

PreparedStatement preparedStatement(String sql) ：主要用来预编译 SQL 语句，并且执行。解决了 SQL 注入的漏洞

例子：

var ps: PreparedStatement = null
// 编写 SQL 语句
val sql = "insert into user(userName,age) values (?, ?)";
// 动态执行该 SQL 语句
ps = conn.prepareStatement(sql);
// 执行更新操作
preparedStatement.executeUpdate();

释放资源

JDBC 程序运行完后，切记要释放程序在运行过程中创建的那些与数据库进行交互的对象，这些对象通常是 ResultSet，Statement 和 Connection 对象。

特别是 Connection 对象，它是非常稀有的资源，用完后必须马上释放，如果 Connection 不能及时、正确的关闭，极易导致系统宕机。

Connection 的使用原则是尽量晚创建，尽量早的释放。

为确保资源释放代码能运行，资源释放代码放在 finally 语句中。

finally {
if (ps != null) {
ps.close()
}
if (conn != null) {
conn.close()
}
}

案例

接下来我们通过一个例子来感受 JDBC 的使用。

首先，我们创建一个 RDD，里面存放着用户的姓名和年龄。

val rdd1: RDD[(String, Int)] = sc.parallelize(List(("alice", 10), ("ben", 20), ("kee", 30)))

接下来按照我们以往所学，是不是通过 foreach 遍历该 RDD，将 RDD 中的每条数据插入到 MySQL 数据库中呢

rdd1.foreach(data =>{
var conn: Connection = null
var ps: PreparedStatement = null
val user = "root"
val password = "123123"
val sql = "insert into user(userName,age) values (?, ?)"
val driver = "com.mysql.jdbc.Driver"
try {
// 注册数据库驱动
Class.forName(driver)
// 建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb",user, password)
// 创建预编译 prepareStatement 对象来执行该 SQL
ps = conn.prepareStatement(sql)
// 将 RDD 中的数据对应放入 user 表的字段 userName 和 age 中
ps.setString(1, data._1)
ps.setInt(2, data._2)
// 执行更新操作
ps.executeUpdate()
} catch {
case e: SQLException => println(e.printStackTrace())
} finally {
// 关闭资源连接
if (ps != null) {
ps.close()
}
if (conn != null) {
conn.close()
}
}
})

可是，通过这个代码我们会发现，每插入一条数据到 MySQL 中，都会连接一次 MySQL，那如果插入上百万、千万条数据，那显然是及其耗费资源和时间的。那么我们应该怎样解决这个问题呢？

这个时候我们便应该用到 foreachPartition 了。这个函数同 foreach 一样，也是根据传入的 function 进行处理。但是不同之处在于 foreach 过程中，我们每得到一条数据就得处理一条数据，但是 foreachPartition 得到的是一个分区的数据。

这样的好处便在于我们只需在每个分区中连接一次 MySQL 数据库，然后再针对这个分区中的数据通过 foreach 遍历将每条数据插入数据库中。这样便大大的缩减了我们的时间和资源。

所以我们的代码便可以修改为：

rdd1.foreachPartition(it =>{
// it 是一个分区的数据
var conn: Connection = null
var ps: PreparedStatement = null
val user = "root"
val password = "123123"
val sql = "insert into user(userName,age) values (?, ?)"
val driver = "com.mysql.jdbc.Driver"
try {
// 注册数据库驱动
Class.forName(driver)
// 连接数据库
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb",user, password)
// 对分区中的数据进行遍历
it.foreach(data => {
// 创建预编译 prepareStatement 对象来执行该 SQL
ps = conn.prepareStatement(sql)
// 将 RDD 中的数据对应放入 user 表的字段 userName 和 age 中
ps.setString(1, data._1)
ps.setInt(2, data._2)
// 执行更新操作
ps.executeUpdate()
}
)
} catch {
case e: SQLException => println(e.printStackTrace())
} finally {
// 关闭资源连接
if (ps != null) {
ps.close()
}
if (conn != null) {
conn.close()
}
}
})

编程要求 

仔细阅读右侧编辑区内给出的代码框架及注释，在 Begin-End 中通过 JDBC 的方式将所给的 RDD date 的数据插入到 MySQL 数据库的 user 表中。

相关数据说明

MySQL 的 mydb 数据库；

用户表 t_user；

列名	类型	非空	注释
userId	int	√	用户 ID 主键
userName	varchar	√	用户名
passWord	varchar	√	用户密码

MySQL 连接配置：

• Driver：com.mysql.jdbc.Driver；

• URL：jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8；

• user：root；

• password：123123。

测试说明

平台将使用测试集运行你编写的程序代码，若全部的运行结果正确，则通关。 可在右侧“测试结果”区查看具体的测试集详情。

测试输入：无

预期输出：

1. 1 sunyue 123456
2. 2 iteblog 123481
3. 3 com 135486

参考答案

// 导入必要的Java SQL包，用于数据库操作
import java.sql.{Connection, DriverManager, PreparedStatement, SQLException}
// 导入Spark核心类库
import org.apache.spark.{SparkConf, SparkContext}
// 导入RDD（弹性分布式数据集）相关的类
import org.apache.spark.rdd.RDD

// 定义一个名为user的对象，包含Spark作业的主函数
object user {
  def main(args: Array[String]): Unit = {
    // 配置Spark应用的基本信息：应用名称为"saf"，运行模式为本地模式
    val conf = new SparkConf().setAppName("saf").setMaster("local")
    
    // 根据配置创建SparkContext，这是Spark程序的入口
    val sc = new SparkContext(conf)
    
    /********** Begin *********/

    // 创建一个RDD，数据源为包含用户信息的列表，每个元组含userId、userName、passWord
    val data = sc.parallelize(List((1,"sunyue","123456"), (2,"iteblog", "123481"), (3,"com", "135486")))

    // 请将 RDD 中的用户信息插入 MySQL mydb 数据库的表 t_user 中，列表中的三个元素分别表示 userId、userName和passWord
    
    // 加载MySQL JDBC驱动，以便进行数据库操作
    Class.forName("com.mysql.jdbc.Driver")

    // 定义数据库连接的URL、用户名和密码
    val url = "jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8"
    val user = "root"
    val password = "123123"

    // 使用DriverManager建立到MySQL数据库的连接
    val connection = DriverManager.getConnection(url, user, password)

    // 对RDD的每个分区执行操作，这样设计可以优化数据库连接的使用
    data.foreachPartition { iter =>
      // 为当前分区创建一个新的数据库连接，避免多个线程共享连接导致的并发问题
      val conn = DriverManager.getConnection(url, user, password)
      
      // 准备执行插入数据的预编译SQL语句，以防止SQL注入
      val statement = conn.prepareStatement("INSERT INTO t_user(userId, userName, passWord) VALUES (?, ?, ?)")
      
      // 遍历分区中的每个元素（即用户记录）
      iter.foreach { case (userId, userName, passWord) =>
        // 为预编译SQL设置参数，并执行插入操作
        statement.setInt(1, userId)
        statement.setString(2, userName)
        statement.setString(3, passWord)
        // 执行更新操作
        statement.executeUpdate()
      }
      
      // 完成操作后，关闭PreparedStatement和数据库连接，释放资源
      statement.close()
      conn.close()
    } 
    
    /********** End *********/
    // Spark任务结束后，停止SparkContext以释放资源
    sc.stop()
  }
}

第2关：Spark RDD查询MySQL数据

任务描述

本关任务：通过 Spark RDD 查询 MySQL 数据库中 t_user 表中 userId 在 1 到 5 之间的用户数据（包含 1 和 5）。

相关知识

为了完成本关任务，你需要掌握：JdbcRDD 的使用。

JdbcRDD

说到读取 MySQL 数据库的数据，Spark RDD 提供了一个 JdbcRDD 的算子，这个算子可以帮助我们连接数据库并获取数据库中查询到的数据。

听到这个名字，大家也可以猜到这个算子和 JDBC 有关系。没错，这个算子实际上就是将我们上关学到的 JDBC 操作数据库进行了一个封装，可以使我们更方便的操作数据库。 以下是 JdbcRDD 的定义：

class JdbcRDD[T: ClassTag](
sc: SparkContext,
getConnection: () => Connection,
sql: String,
lowerBound: Long,
upperBound: Long,
numPartitions: Int,
mapRow: (ResultSet) => T = JdbcRDD.resultSetToObjectArray _)

从定义上来看，我们可以得出 JdbcRDD 是一个类，所以我们想要使用它必须要进行创建。接着我们看它所需要的参数。

接下来我们通过一个例子来体验 JdbcRDD 算子的使用。

现在有一个名为 stu 的表，表详细信息如下。

id（int）	name（varchar）
1	mm
2	sa
3	de
4	hu

要求我们查询 id 在 1 到 3 之间的学生信息（包含 1 和 3）。

那我们可以先创建一个 JdbcRDD 的对象。

val rdd = new JdbcRDD()

紧接着我们补充它所需要的的参数信息。从定义上来看，我们可以看出它的第一个参数是 SparkContext 对象；

//配置spark信息
val conf = new SparkConf().setMaster("local[*]").setAppName("JdbcRDD")
//创建sparkContext
val sc: SparkContext = new SparkContext(conf)
val rdd = new JdbcRDD(sc)

紧接着我们来看它的第二个参数，该参数要求进行数据库的连接；

//配置spark信息
val conf = new SparkConf().setMaster("local[*]").setAppName("JdbcRDD")
//创建sparkContext
val sc: SparkContext = new SparkContext(conf)
val rdd = new JdbcRDD(
sc,
()=>{
//定义连接 MySQL 数据库的参数
val driver ="com.mysql.jdbc.Driver"
val url = "jdbc:mysql://hadoop103:3306/mydb"
val userName = "root"
val password = "123123"
Class.forName(driver) // 注册数据库驱动
DriverManager.getConnection(url, userName, password) // 连接数据库
},

)

它的第三个参数是一个待查询的 SQL 语句，此查询语句必须包含两处占位符 ? 来作为分割数据库结果集的参数，例如：”select * from stu where ? < = id and id <= ?”；

//配置spark信息
val conf = new SparkConf().setMaster("local[*]").setAppName("JdbcRDD")
//创建sparkContext
val sc: SparkContext = new SparkContext(conf)
val rdd = new JdbcRDD(
sc,
()=>{
//定义连接 MySQL 数据库的参数
val driver ="com.mysql.jdbc.Driver"
val url = "jdbc:mysql://hadoop103:3306/mydb"
val userName = "root"
val password = "123123"
Class.forName(driver) // 注册数据库驱动
DriverManager.getConnection(url, userName, password) // 连接数据库
},
"select * from stu where id >=? and id<=?",
)

lowerBound，upperBound，numPartitions 分别为第一、第二占位符，partition（分区） 的个数。例如，给出 lowebound：1，upperbound：20， numpartitions 2，则查询分别为 (1, 10) 与 (11, 20)。这里我们查询 1 到 3 的信息，1 个分区；

//配置spark信息
val conf = new SparkConf().setMaster("local[*]").setAppName("JdbcRDD")
//创建sparkContext
val sc: SparkContext = new SparkContext(conf)
val rdd = new JdbcRDD(
sc,
()=>{
//定义连接 MySQL 数据库的参数
val driver ="com.mysql.jdbc.Driver"
val url = "jdbc:mysql://localhost:3306/mydb"
val userName = "root"
val password = "123123"
Class.forName(driver) // 注册数据库驱动
DriverManager.getConnection(url, userName, password) // 连接数据库
},
"select * from stu where id >=? and id<=?",
1,
3,
1,
)

最后一个参数便是我们上述查询后的结果集，它返回的是一个 ResultSet 的对象，而 JDBC 中提供了操作该类的 API。

• ResultSet：提供检索不同类型字段的方法。（操作对象为 Statement 执行 SQL 查询后的结果）；

  getString(String columnName)	用于获取指定字段的 String 类型的值，参数 columnIndex 代表字段名称
  getInt(String columnName)	用于获取指定字段的 int 类型的值，参数 columnIndex 代表字段名称
  getDate(String columnName)	用于获取指定字段的 Date类型的值，参数 columnIndex 代表字段名称

//配置spark信息
val conf = new SparkConf().setMaster("local[*]").setAppName("JdbcRDD")
//创建sparkContext
val sc: SparkContext = new SparkContext(conf)
val rdd = new JdbcRDD(
sc,
()=>{
//定义连接 MySQL 数据库的参数
val driver ="com.mysql.jdbc.Driver"
val url = "jdbc:mysql://localhost:3306/mydb"
val userName = "root"
val password = "123123"
Class.forName(driver) // 注册数据库驱动
DriverManager.getConnection(url, userName, password) // 连接数据库
},
"select * from stu where id >=? and id<=?", // 执行的 SQL 查询语句
1, // 上限
3, // 下限
1,
rs => (rs.getInt("id"), rs.getString("name"))
)

如此，我们便成功创建了一个 JdbcRDD，我们看它返回的结果：

val rdd: JdbcRDD[(Int, String)] = new JdbcRDD(...)

我们看到它返回的是一个 JdbcRDD 类型的数据，它里面是由整型和字符串组成的，此时这个 rdd 中便存放着我们从 MySQL 中查询到的结果集，最后我们将其打印：

rdd.foreach(println)

执行结果：

1. (1,mm)
2. (2,sa)
3. (3,de)

编程要求

仔细阅读右侧编辑区内给出的代码框架及注释，在 Begin-End 中通过 Spark RDD 查询 MySQL 数据库中 t_user 表中 userId 在 1 到 5 之间的用户数据（包含 1 和 5）。

测试说明

平台将使用测试集运行你编写的程序代码，若全部的运行结果正确，则通关。 可在右侧“测试结果”区查看具体的测试集详情。

测试输入：无

预期输出：

1. 1 sunfeng 147258
2. 2 dv 123456
3. 3 suning 845966

参考答案

ok啦，老铁们再找找别的资源吧🤣