sparksql加載mysql表中的數(shù)據(jù)

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.38</version> </dependency> import java.util.Propertiesimport org.apache.spark.SparkConf import org.apache.spark.sql.{DataFrame, SparkSession}//todo:利用sparksql加載mysql表中的數(shù)據(jù) object DataFromMysql {def main(args: Array[String]): Unit = {//1、創(chuàng)建SparkConf對(duì)象val sparkConf: SparkConf = new SparkConf().setAppName("DataFromMysql").setMaster("local[2]")//2、創(chuàng)建SparkSession對(duì)象val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()//3、讀取mysql表的數(shù)據(jù)//3.1 指定mysql連接地址val url="jdbc:mysql://node1:3306/spark"//3.2 指定要加載的表名val tableName="iplocation"// 3.3 配置連接數(shù)據(jù)庫(kù)的相關(guān)屬性val properties = new Properties()//用戶名properties.setProperty("user","root")//密碼properties.setProperty("password","123456")val mysqlDF: DataFrame = spark.read.jdbc(url,tableName,properties)//打印schema信息mysqlDF.printSchema()//展示數(shù)據(jù)mysqlDF.show()//把dataFrame注冊(cè)成表mysqlDF.createTempView("iplocation")spark.sql("select * from iplocation where total_count >1500").show()spark.stop()} }

sparksql保存結(jié)果數(shù)據(jù)到mysql表中

import java.util.Propertiesimport org.apache.spark.sql.{DataFrame, SparkSession}//todo:通過(guò)sparksql把結(jié)果數(shù)據(jù)寫入到mysql表中 object Data2Mysql {def main(args: Array[String]): Unit = {//1、創(chuàng)建SparkSessionval spark: SparkSession = SparkSession.builder().appName("Data2Mysql") .getOrCreate()//2、讀取mysql表中數(shù)據(jù)//2.1 定義url連接val url="jdbc:mysql://node1:3306/spark"//2.2 定義表名val table="iplocation"//2.3 定義屬性val properties=new Properties()properties.setProperty("user","root")properties.setProperty("password","123456")val mysqlDF: DataFrame = spark.read.jdbc(url,table,properties)//把dataFrame注冊(cè)成一張表mysqlDF.createTempView("iplocation")//通過(guò)sparkSession調(diào)用sql方法//需要統(tǒng)計(jì)經(jīng)度和維度出現(xiàn)的人口總數(shù)大于1000的記錄 保存到mysql表中val result: DataFrame = spark.sql("select * from iplocation where total_count >1000")//保存結(jié)果數(shù)據(jù)到mysql表中//mode:指定數(shù)據(jù)的插入模式//overwrite: 表示覆蓋，如果表不存在，事先幫我們創(chuàng)建//append :表示追加， 如果表不存在，事先幫我們創(chuàng)建//ignore :表示忽略，如果表事先存在，就不進(jìn)行任何操作//error :如果表事先存在就報(bào)錯(cuò)（默認(rèn)選項(xiàng)）result.write.mode(args(0)).jdbc(url,args(1),properties)//關(guān)閉spark.stop()} }

提交任務(wù)腳本
spark-submit
–master spark://node1:7077
–class com.sql.DataMysql
–executor-memory 1g
–total-executor-cores 4
–driver-class-path /opt/hive/lib/mysql-connector-java-5.1.38.jar
–jars /export/servers/hive/lib/mysql-connector-java-5.1.35.jar
spark_class01-1.0-SNAPSHOT.jar
append user

總結(jié)

以上是生活随笔為你收集整理的sparksql加载mysql表中的数据的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。