SparkSql将数据写入到MySQL中

2023-12-27 15:24| 来源: 网络整理| 查看: 265

通过IDEA编写SparkSql代码

将person.txt文本文件写入到mysql数据库表当中去

思路解析：通过sparkContext读取文件，然后转换成RDD，将RDD转换成为DataFrame，然后注册成为一张表，查询出来数据，插入保存到mysql当中去

代码实现：

import java.util.Properties import org.apache.spark.SparkContext import org.apache.spark.rdd.RDD import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession} case class Peson(id:Int,name:String,age:Int) object Spark_Mysql { /** * 读取文本文件数据，然后写入到mysql数据库表当中去 * */ def main(args: Array[String]): Unit = { //获取sparkSession val sparkSession: SparkSession = SparkSession.builder().appName("spark_Mysql").master("local[2]").getOrCreate() //通过sparkSession得到sparkContext val sparkContext: SparkContext = sparkSession.sparkContext //通过sparkContext 读取文本文件内容，得到RDD val arrRDD: RDD[Array[String]] = sparkContext.textFile("file:///F:\\person.txt").map(x => x.split(" ")) //通过RDD，配合样例类，将我们的数据转换成样例类对象 val personRDD: RDD[Person] = arrRDD.map(x => Person(x(0).toInt,x(1),x(2).toInt)) //导入sparkSession当中的隐式转换，将我们的样例类对象转换成DataFrame import sparkSession.implicits._ val personDF: DataFrame = personRDD.toDF() //打印dataFrame当中的数据 val personDFShow: Unit = personDF.show() //将DataFrame注册成为一张表模型 val personView: Unit = personDF.createTempView("person_view") //获取表当中的数据 val result: DataFrame = sparkSession.sql("select * from person_view") //获取mysql连接 val url ="jdbc:mysql://localhost:3306/userdb" val tableName = "person" val properties = new Properties() properties.setProperty("user","root") properties.setProperty("password","root123") //将我们查询的结果写入到mysql当中去 val jdbc: Unit = result.write.mode(SaveMode.Append).jdbc(url,tableName,properties) sparkContext.stop() sparkSession.close() } }

所需pom.xml文件

bigdata bigdata 1.0-SNAPSHOT 4.0.0

day03_spark

2.11.8 2.2.0 org.scala-lang scala-library ${scala.version}

org.apache.spark spark-core_2.11 ${spark.version}

org.apache.spark spark-sql_2.11 ${spark.version}

org.apache.hadoop hadoop-client 2.7.5

org.apache.spark spark-hive_2.11 2.2.0

mysql mysql-connector-java 5.1.38

src/main/scala src/test/scala org.apache.maven.plugins maven-compiler-plugin 3.0 1.8 1.8 UTF-8 net.alchim31.maven scala-maven-plugin 3.2.0 compile testCompile -dependencyfile ${project.build.directory}/.scala_dependencies org.apache.maven.plugins maven-shade-plugin 3.1.1 package shade *:* META-INF/*.SF META-INF/*.DSA META-INF/*.RSA

打包提交运行： spark2-submit --class Spark_Mysql --master spark://cdh01:7077 /data/SparkMysql-1.0-SNAPSHOT.jar

运行结果：

【本文地址】

公司简介

联系我们