You get it --> Spark SQL 中的JDBC

Spark SQL 可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame,通过对DataFrame一系列的计算后,还可以将数据再写回关系型数据库中

从MySQL中加载数据(Spark Shell方式)

1.启动Spark Shell,必须指定mysql连接驱动jar包

 /usr/local/spark-1.6.1/bin/spark-shell \--master spark://minimaster:7077 \--jars /usr/local/spark-1.6.1/mysql-connector-java-5.1.6-bin.jar \--driver-class-path /usr/local/spark-1.6.1/mysql-connector-java-5.1.6-bin.jar

2.从mysql中加载数据

val jdbcDF = sqlContext.read.format("jdbc").options(Map("url" -> "jdbc:mysql://192.168.222.156:3306/bigdata", "driver" -> "com.mysql.jdbc.Driver", "dbtable" -> "location_info", "user" -> "root", "password" -> "root")).load()

3.执行查询

将数据写入到MySQL中
编写Spark SQL程序

package myRPC.qf.itcast.RDDimport java.util.Propertiesimport org.apache.spark.rdd.RDDimport org.apache.spark.sql.{DataFrame, Row, SQLContext, types}import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}import org.apache.spark.{SparkConf, SparkContext}object InsertDataToMySQL {  def main(args: Array[String]): Unit = {    val conf = new SparkConf().setAppName("InsertDataToMySQL")//.setMaster("local[2]")    val sc = new SparkContext(conf)    val sqlContext = new SQLContext(sc)    //获取数据    val linesRDD: RDD[Array[String]] = sc.textFile("hdfs://minimaster:9000/person2.txt").map(_.split(" "))    //StructType指定Schema[表结构]    val schema: StructType = StructType {      List(        StructField("name", StringType, false),        StructField("age", IntegerType, false)      )    }    //linesRDD[数据]映射到Row[表结构顺序]    val personRDD: RDD[Row] = linesRDD.map(p => Row(p(0),p(1).toInt))    //生成DataFrame    val personDF: DataFrame = sqlContext.createDataFrame(personRDD,schema)    //声明一个属性,用于封装请求MySQL的一些配置    val prop = new Properties()    prop.put("user","root")    prop.put("password","root")    prop.put("driver","com.mysql.jdbc.Driver")    //写入数据到MySQL    personDF.write.mode("append").jdbc("jdbc:mysql://minimaster/bigdata","person",prop)    sc.stop()  }}

用maven将程序打包

将jar包提交到spark集群中(启动hdfs)

/usr/local/spark-1.6.1/bin/spark-submit \--class myRPC.qf.itcast.RDD.InsertDataToMySQL \--master spark://minimaster:7077 \/home/wc.jar \--jars /usr/local/spark-1.6.1/mysql-connector-java-5.1.6-bin.jar \--driver-class-path /usr/local/spark-1.6.1/mysql-connector-java-5.1.6-bin.jar

运行结果:
可以使用第三方工具[navicat]查看