抽取MYSQL数据并保存到新的表中
import org.apache.spark.sql.{SaveMode, SparkSession}
import java.util.Properties
object SparkReadMysql3 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("sparksql").master("local").getOrCreate()
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "123456")
val url = "jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"
val dataFrame = spark.read.jdbc(url, "user", prop).where("id >= 3")
dataFrame.write.mode(SaveMode.Append).jdbc(url, "test02", prop)
spark.stop()
}
}
读取本地文件存入MYSQL
import org.apache.spark.sql.types.{LongType, StringType, StructField, StructType}
import org.apache.spark.sql.{Row, SaveMode, SparkSession}
import java.util.Properties
object SparkReadMysql4 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("test").master("local").getOrCreate()
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "123456")
val url = "jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"
val rdd = spark.sparkContext.textFile("文件路径")
val rdd2 = rdd.flatMap(_.split(",")).distinct().zipWithIndex().map(t => {
Row(t._2, t._1)
})
val schema = StructType {
List(
StructField("id", LongType, true),
StructField("username", StringType, true)
)
}
val dataFrame = spark.createDataFrame(rdd2, schema)
dataFrame.write.mode(SaveMode.Overwrite).jdbc(url, "user", prop)
spark.stop()
}
}