spark sql保存DataFrame到mysql & 从mysql读取数据

在hdfs上存储一个sql文件

cust.txt:

1,smith,12
2,bob,13
3,alex,14
4,alice,15
6,mike,26

1.保存DataFrame到mysql

import org.apache.spark.sql.SparkSession

/**
  * 将数据框保存成数据库的表中
  */
object SparkSQLJDBCWriteDemo {
  def main(args: Array[String]) {
    val spark=SparkSession.builder().appName("sparkSQL").master("local[*]").enableHiveSupport().getOrCreate()
    val rdd1=spark.sparkContext.textFile("/user/hadoop/data/cust.txt")
    import  spark.implicits._
    val df1=rdd1.map(line=>{
      val arr=line.split(",")
      (arr(0).toInt,arr(1),arr(2).toInt)
    }).toDF("id","name","age")
    df1.show(1000,false)

    //
    val url="jdbc:mysql://localhost:3306/big"
    val table="cust"
    val prop=new java.util.Properties()
    prop.put("driver","com.mysql.jdbc.Driver")
    prop.put("user","root")
    prop.put("password","123456")

    //表自动创建
    df1.where("id>2").write.jdbc(url,table,prop)

  }
}

mysql结果数据如下：

 

2.从mysql读取数据代码：

import org.apache.spark.sql.SparkSession

/**
  * 从mysql读取数据
  */
object SparkSQLJDBCReaderDemo {
  def main(args: Array[String]) :Unit={
    val spark=SparkSession.builder().appName("sparkSQL").master("local[*]").enableHiveSupport().getOrCreate()
    val url="jdbc:mysql://localhost:3306/big"
    val table="cust"
    val prop=new java.util.Properties()
    prop.put("driver","com.mysql.jdbc.Driver")
    prop.put("user","root")
    prop.put("password","123456")
    val df1=spark.read.jdbc(url,table,prop)
    df1.show(1000,false)
  }
}