SparkSQL 之 MySQL的连接，数据的写入

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._

    // 1.通过load方式读取
    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

    // 2.通过load读取，参数不同
    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

    // 3.通过jdbc方法读取
    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 = {

    //创建SparkSession对象
    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()

    // 1.通用的方式 format指定写出类型
    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()

    // 2. 通过 jdbc方法
    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>