sparksql在Mysql中生成表

1 提供比较全的pom.xml

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spark.version>2.2.0</spark.version>
        <hadoop.version>2.9.0</hadoop.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-core_2.11</artifactId>
            <version>${spark.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.11</artifactId>
            <version>${spark.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-streaming_2.11</artifactId>
            <version>${spark.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-hive_2.11</artifactId>
            <version>${spark.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>${hadoop.version}</version>
            <scope>compile</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-streaming-kafka -->
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-streaming-kafka_2.11</artifactId>
            <version>1.6.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-mllib_2.11</artifactId>
            <version>${spark.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.typesafe</groupId>
            <artifactId>config</artifactId>
            <version>1.2.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>
    </dependencies>
    <build>
        <sourceDirectory>src/main/scala</sourceDirectory>

    </build>

2 实现代码，需要注意mysql事先没有对应的表

import java.util.Properties


import org.apache.spark.SparkConf
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SQLContext, SparkSession}
case class Student(xid:Int,xname:String)
object MySqlDemo {
  def main(args: Array[String]): Unit = {
    val conf=new SparkConf()
    conf.setMaster("local[2]")
    conf.setAppName("SparkSqlToMysql")
    //todo:1、创建sparkSession对象
    val spark: SparkSession = SparkSession.builder()
      .config(conf)
      .getOrCreate()
    //todo:2、读取数据
    val data: RDD[String] = spark.sparkContext.textFile("file:///d:/ok.txt")
    //todo:3、切分每一行,
    val arrRDD: RDD[Array[String]] = data.map(_.split(" "))
    //todo:4、RDD关联Student
    val studentRDD: RDD[Student] = arrRDD.map(x=>Student(x(0).toInt,x(1)))
    //todo:导入隐式转换
    import spark.implicits._
    //todo:5、将RDD转换成DataFrame
    val studentDF: DataFrame = studentRDD.toDF()
    //todo:6、将DataFrame注册成表
    studentDF.createOrReplaceTempView("student")
    //todo:7、操作student表 ,按照年龄进行降序排列
    val resultDF: DataFrame = spark.sql("select * from student order by xid desc")


    //todo:8、把结果保存在mysql表中
    //todo:创建Properties对象，配置连接mysql的用户名和密码
    val prop =new Properties()
    prop.setProperty("user","root")
    prop.setProperty("password","123456")


    resultDF.write.jdbc("jdbc:mysql://192.168.162.130:3306/shopdb?useUnicode=true&characterEncoding=utf8","xhello",prop)


    //todo:写入mysql时，可以配置插入mode，overwrite覆盖，append追加，ignore忽略，error默认表存在报错
    //resultDF.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.162.130:3306/shopdb","student",prop)
    spark.stop()
  }
}