spark了解mysql_Spark学习笔记——读写MySQL

1.使用Spark读取MySQL中某个表中的信息

build.sbt文件

name := "spark-hbase"

version := "1.0"

scalaVersion := "2.11.8"

libraryDependencies ++= Seq(

"org.apache.spark" %% "spark-core" % "2.1.0",

"mysql" % "mysql-connector-java" % "5.1.31",

"org.apache.spark" %% "spark-sql" % "2.1.0"

)

Mysql.scala文件

import org.apache.spark.{SparkConf, SparkContext}

import org.apache.spark.sql.{SQLContext, SaveMode}

import java.util.Properties

/**

* Created by mi on 17-4-11.

*/

case class resultset(name: String,

info: String,

summary: String)

object MysqlOpt {

def main(args: Array[String]): Unit = {

val conf = new SparkConf().setAppName("WordCount").setMaster("local")

val sc = new SparkContext(conf)

val sqlContext = new SQLContext(sc)

import sqlContext.implicits._

//定义数据库和表信息

val url = "jdbc:mysql://localhost:3306/baidubaike?useUnicode=true&characterEncoding=UTF-8"

val table = "baike_pages"

//读MySQL的方法1

val reader = sqlContext.read.format("jdbc")

reader.option("url", url)

reader.option("dbtable", table)

reader.option("driver", "com.mysql.jdbc.Driver")

reader.option("user", "root")

reader.option("password", "XXX")

val df = reader.load()

df.show()

//读MySQL的方法2

// val jdbcDF = sqlContext.read.format("jdbc").options(

// Map("url"->"jdbc:mysql://localhost:3306/baidubaike?useUnicode=true&characterEncoding=UTF-8",

// "dbtable"->"(select name,info,summary from baike_pages) as some_alias",

// "driver"->"com.mysql.jdbc.Driver",

// "user"-> "root",

// //"partitionColumn"->"day_id",

// "lowerBound"->"0",

// "upperBound"-> "1000",

// //"numPartitions"->"2",

// "fetchSize"->"100",

// "password"->"XXX")).load()

// jdbcDF.show()

}

}

输出

2.使用Spark写MySQL中某个表中的信息

import org.apache.spark.{SparkConf, SparkContext}

import org.apache.spark.sql.{SQLContext, SaveMode}

import java.util.Properties

/**

* Created by mi on 17-4-11.

*/

case class resultset(name: String,

info: String,

summary: String)

object MysqlOpt {

def main(args: Array[String]): Unit = {

val conf = new SparkConf().setAppName("WordCount").setMaster("local")

val sc = new SparkContext(conf)

val sqlContext = new SQLContext(sc)

import sqlContext.implicits._

//定义数据库和表信息

val url = "jdbc:mysql://localhost:3306/baidubaike?useUnicode=true&characterEncoding=UTF-8"

val table = "baike_pages"

//写MySQL的方法1

val list = List(

resultset("名字1", "标题1", "简介1"),

resultset("名字2", "标题2", "简介2"),

resultset("名字3", "标题3", "简介3"),

resultset("名字4", "标题4", "简介4")

)

val jdbcDF = sqlContext.createDataFrame(list)

jdbcDF.collect().take(20).foreach(println)

// jdbcDF.rdd.saveAsTextFile("/home/mi/coding/coding/Scala/spark-hbase/output")

val prop = new Properties()

prop.setProperty("user", "root")

prop.setProperty("password", "123456")

//jdbcDF.write.mode(SaveMode.Overwrite).jdbc(url,"baike_pages",prop)

jdbcDF.write.mode(SaveMode.Append).jdbc(url, "baike_pages", prop)

}

}