SparkSQL 连接 MySQL
package sparkSQL.study
import java.util.Properties
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object JdbcConnect {
def main(args: Array[String]): Unit = {
val sparkConf = new SparkConf().setMaster("local[*]").setAppName("jdbc...")
val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
import sparkSession.implicits._
sparkSession.read
.format("jdbc")
.option("url", "jdbc:mysql://127.0.0.1:3306/market")
.option("driver", "com.mysql.jdbc.Driver")
.option("user", "root")
.option("password", "123456")
.option("dbtable","goods")
.load().show
sparkSession.read.format("jdbc")
.options(Map("url"->"jdbc:mysql://127.0.0.1:3306/market?user=root&password=123456",
"dbtable"->"goods", "driver"->"com.mysql.jdbc.Driver"))
.load().show
val properties = new Properties()
properties.setProperty("user", "root")
properties.setProperty("password", "123456")
val dataFrame = sparkSession.read.jdbc("jdbc:mysql://127.0.0.1:3306/market", "orders", properties)
dataFrame.show
sparkSession.stop()
}
}
SparkSQL 数据写入
package sparkSQL.study
import java.util.Properties
import org.apache.spark.SparkConf
import org.apache.spark.sql.{SaveMode, SparkSession}
object JdbcWrite {
def main(args: Array[String]): Unit = {
val sparkConf = new SparkConf().setMaster("local[*]").setAppName("jdbcwrite...")
val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
import sparkSession.implicits._
val datas = sparkSession.sparkContext.makeRDD(List(Goods(1, "zhangsan", 19), Goods(2, "lisi", 20)))
val dsDatas = datas.toDS()
dsDatas.write
.format("jdbc")
.option("url", "jdbc:mysql://127.0.0.1:3306/student_test")
.option("driver", "com.mysql.jdbc.Driver")
.option("user", "root")
.option("password", "123456")
.option("dbtable","students1906")
.mode(SaveMode.Append)
.save()
val properties = new Properties()
properties.setProperty("user", "root")
properties.setProperty("password", "123456")
dsDatas.write.mode(SaveMode.Append).jdbc("jdbc:mysql://127.0.0.1:3306/student_test",
"students1906", properties)
sparkSession.stop()
}
case class Goods (id: Int, name: String, age: Int)
}
SparkSQL 项目pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE project [
<!ELEMENT project (modelVersion|groupId|artifactId|version|dependencies)*>
<!ATTLIST project
xmlns CDATA #REQUIRED
xmlns:xsi CDATA #REQUIRED
xsi:schemaLocation CDATA #REQUIRED>
<!ELEMENT modelVersion (#PCDATA)>
<!ELEMENT groupId (#PCDATA)>
<!ELEMENT artifactId (#PCDATA)>
<!ELEMENT version (#PCDATA)>
<!ELEMENT dependencies (dependency)*>
<!ELEMENT dependency (groupId|artifactId|version)*>
]>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>sparkDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.12</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.7.7</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.12</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
</dependencies>
</project>