一、说明
- 实验环境:Spark2.4.8 + MySQL5.7 + IDEA
二、引入MySQL依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
三、读数据库表
import java.sql.DriverManager
import org.apache.spark.rdd.JdbcRDD
import org.apache.spark.{SparkConf, SparkContext}
object JDBCDemo {
def main(args: Array[String]): Unit = {
val conf = new SparkConf().setAppName("Practice").setMaster("local[2]")
val sc = new SparkContext(conf)
val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://hadoop201:3306/rdd"
val userName = "root"
val passWd = "aaa"
val rdd = new JdbcRDD(
sc,
() => {
Class.forName(driver)
DriverManager.getConnection(url, userName, passWd)
},
"select id, name from user where id >= ? and id <= ?",
1,
20,
2,
result => (result.getInt(1), result.getString(2))
)
rdd.collect.foreach(println)
}
}
四、写数据库表
import java.sql.{Connection, DriverManager, PreparedStatement}
import org.apache.spark.rdd.RDD
import org.apache.spark.{SparkConf, SparkContext}
object JDBCDemo2 {
def main(args: Array[String]): Unit = {
val conf = new SparkConf().setAppName("Practice").setMaster("local[2]")
val sc = new SparkContext(conf)
val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://hadoop201:3306/rdd"
val userName = "root"
val passWd = "aaa"
val rdd: RDD[(Int, String)] = sc.parallelize(Array((110, "police"), (119, "fire")))
rdd.foreachPartition(it => {
Class.forName(driver)
val conn: Connection = DriverManager.getConnection(url, userName, passWd)
it.foreach(x => {
val statement: PreparedStatement = conn.prepareStatement("insert into user values(?, ?)")
statement.setInt(1, x._1)
statement.setString(2, x._2)
statement.executeUpdate()
})
})
}
}