1、数据存入Mysql 几种模式
默认为SaveMode.ErrorIfExists模式,该模式下,若数据库中已经存在该表,则会直接报异常,导致数据不能存入数据库;
SaveMode.Append 若表已经存在,则追加在该表中;若该表不存在,则会先创建表,再插入数据;
SaveMode.Overwrite 重写模式,其本质是先将已有的表及其数据全都删除,再重新创建该表,然后插入新的数据;
SaveMode.Ignore 若表不存在,则创建表,并存入数据;若表存在的情况下,直接跳过数据的存储,不会报错。
2、SaveMode.Overwrite模式在不删除已有的表的情况下即保留表结构的情况下,覆盖原有数据的实现
eg:
import java.sql.DriverManager
import java.util.Properties
import org.apache.spark.sql.{SaveMode, SparkSession}
object OverWrite {
def main(args: Array[String]): Unit = {
val ss = SparkSession
.builder()
.appName("OverWrite")
.master("local")
//.config("spark.sql.warehouse.dir", "file:///D://lenovo_pj//cpp//cpp")
.getOrCreate()
val url = "jdbc:mysql://10.96.91.186:3306/lenovo_channel_persona?rewriteBatchedStatements=true&" +
"useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull"
val driver = "com.mysql.jdbc.Driver"
val user = "root"
val password = "123456"
val prop = new Properties
prop.setProperty("user",user)
prop.setProperty("password",password)
prop.setProperty("driver",driver)
prop.setProperty("charset","UTF-8")
val arr = Array("aa","bb","cc")
val tableName = "test_overwrite"
//先清空MySQL表
Class.forName(driver)
val conn = DriverManager.getConnection(url,prop)
val sm = conn.prepareCall("truncate table "+tableName)
sm.execute()
sm.close()
//在往MySQL表追加数据
import ss.sqlContext.implicits._
ss.sparkContext
.parallelize(arr)
.toDF("node1","node2","node3")
.write
.mode(SaveMode.Append)
.jdbc(url,tableName,prop)
conn.close()
ss.stop()
}
}