SparkSQL简介及使用

SparkSQL简介及使用

1 简介

1.1 什么是 Spark SQL

Spark SQL是Spark用来处理结构化数据的一个模块，它提供了两个编程抽象分别叫做DataFrame和DataSet，它们用于作为分布式SQL查询引擎。

1.2 Spark SQL 的特点

1.内存列存储–可以大大优化内存的使用率，减少内存消耗，避免GC对大量数据性能的开销

2.字节码生成技术–可以使用动态的字节码技术优化性能

3.Scala代码的优化

4.易整合

5.统一的数据访问方式

6.兼容hive

7.提供了统一的数据连接方式(JDBC/ODBC)

SparkSql 官网是: http://spark.apache.org/sql/

1.3 什么是DataFrames

与RDD类似，DataFrame也是一个分布式数据容器。然而DataFrame更像传统数据库的二维 表格，除了数据以外，还记录数据的结构信息，即schema。同时，与Hive类似，DataFrame也支持嵌套数据类型（struct、array和map）。从API易用性的角度上看，DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好，门槛更低。由于与R和Pandas的DataFrame类似，Spark DataFrame很好地继承了传统单机数据分析的开发体验。DataFrame给RDD增加了结构,可以将RDD当一个表来操作，就是需要将RDD转换成 DataFrames。

1.4 什么是DataSet

DataSet是一种强类型的数据结构面向对象,将一条数据看一个对象，通过对象来方式来访问 数据。DataSet是从Spark 1.6开始引入的一个新的抽象。DataSet是特定域对象中的强类型集合，它可以使用函数或者相关操作并行地进行转换等操作。每个DataSet都有一个称为DataFrame的 非类型化的视图，这个视图是行的数据集。为了有效地支持特定域对象，DataSet引入了Encoder（编码器）。例如，给出一个Person的类，有两个字段name(string)和age(int)， 通过一个encoder来告诉spark在运行的时候产生代码把Person对象转换成一个二进制结构。这种二进制结构通常有更低的内存占用，以及优化的数据处理效率（例如在一个柱状格式）。若要了解数据的内部二进制表示，请使用schema(表结构)函数。 在DataSet上的操作，分为transformations和actions。transformations会产生新的 DataSet，而actions则是触发计算并产生结果。transformations包括：map，fifilter，select 和aggregate等操作。而actions包括：count，show或把数据写入到文件系统中等操作。RDD也是可以并行化的操作，DataSet和RDD主要的区别是：DataSet是特定域的对象集合；然而RDD是任何对象的集合。DataSet的API总是强类型的；而且可以利用这些模式进行优化，然而RDD却不行。DataFrame是特殊的DataSet，它在编译时不会对模式进行检测。

2 编程方式执行Spark SQL查询

2.1 依赖

<dependency> 
	<groupId>org.apache.spark</groupId> 
	<artifactId>spark-sql_2.11</artifactId> 
	<version>2.1.0</version> 
</dependency> 

2.2 代码

1、 通过反射推断Schema

package com.nml.sparkSQL
import org.apache.spark.sql.{SQLContext, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}
object SQLDemo {
  def main(args: Array[String]): Unit = {
    //本地运行
    val conf = new SparkConf().setAppName("SQLDemo").setMaster("local[*]")
    //val conf = new SparkConf().setAppName("SQLDemo") 要打包到spark集群上运行则不需要后面的setMaster("local[*]")
    //SQLContext要依赖SparkContext
    val sc = new SparkContext(conf)
    //创建SQLContext spark1.6.1以下的写法
    //val sqlContext = new SQLContext(sc)
    //spark2.0 以上的写法
    val sqlContext = SparkSession.builder().config(conf).getOrCreate()
    //提交到spark集群上运行，需要设置用户，否则无权限执行，本地运行则无需
    //System.setProperty("user.name", "bigdata")
    //集群hdfs路径 hdfs://node-1.itcast.cn:9000/person.txt
    //下面由于是本地运行，所以采用本地路径
    //将RDD和case class关联
    val personRdd =
      sc.textFile("in/user.txt").map({line =>
        val fields = line.split(",")
        Person(fields(0).toLong, fields(1), fields(2).toInt)
      })
    //导入隐式转换，如果不导入无法将RDD转换成DataFrame
    //将RDD转换成DataFrame
    import sqlContext.implicits._
    val personDf = personRdd.toDF()
    //采用SQL编写风格 注册表
    //personDf.registerTempTable("person") spark 1.6.1以下的写法
    personDf.createOrReplaceTempView("person")
    sqlContext.sql("select * from person where age >= 20 order by age desc limit 2").show()
  }
}
//case class一定要放到外面
case class Person(id:Long, name:String, age:Int)

//查询结果
/**
+----+----+---+
|  id|name|age|
+----+----+---+
|1003| lan| 26|
|1001| nie| 25|
+----+----+---+
* */

2、通过StructType直接指定Schema

package com.nml.sparkSQL
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.types._
import org.apache.spark.{SparkConf, SparkContext}
object SQLSchemaDemo {
  def main(args: Array[String]): Unit = {
    //本地运行
    val conf = new SparkConf().setAppName("SQLSchemaDemo").setMaster("local[*]")
    val sc = new SparkContext(conf)
    val sqlContext = SparkSession.builder().config(conf).getOrCreate()
    //从指定的地址创建RDD
    val userRDD = sc.textFile("in/user.txt").map(_.split(","))
    //通过StructType直接指定每个字段的schema
    val schema = StructType(
      List(
        StructField("id",IntegerType,true),
        StructField("name",StringType,true),
        StructField("age",IntegerType,true)
      )
    )
    //将RDD映射到rowRDD
    val rowRDD = userRDD.map(p=>Row(p(0).toInt,p(1).trim,p(2).toInt))
    //将schema信息应用到rowRDD上
    val userDataFrame = sqlContext.createDataFrame(rowRDD,schema)
    //注册表
    userDataFrame.createOrReplaceTempView("user")
    //执行SQL
    val df = sqlContext.sql("select * from user where age >= 20 order by age desc limit 3").show()
    sc.stop()
  }
}
//运行结果
/*+----+----+---+
|  id|name|age|
+----+----+---+
|1003| lan| 26|
|1001| nie| 25|
|1002|  li| 23|
+----+----+---+*/

3、读取json文件

package com.nml.sparkSQL
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
//样例类
case class Stud(id:BigInt,name:String,age:BigInt,addr:String)
object SparkSql_json {
  def main(args: Array[String]): Unit = {
    //conf
    val conf=new SparkConf().setMaster("local[*]").setAppName("SparkSql_json")
    //sparkSession
    val spark=SparkSession.builder().config(conf).getOrCreate()
    val df=spark.read.format("json").load("json/stud.json");
    import spark.implicits._
    val ds=df.as[Stud]
    ds.show()
  }
}

json文件内容

{"id":101,"name":"java","age":23,"addr":"wuhan"}
{"id":102,"name":"hello","age":24,"addr":"shanghai"}
{"id":103,"name":"scala","age":25,"addr":"changsa"}
{"id":104,"name":"spark","age":26,"addr":"shanghai"}

4、读mysql

package com.nml.sql
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}

object SparkSql_Mysql {
  //1.启动mysql  加载mysql驱动
  def main(args: Array[String]): Unit = {
    //conf
    val conf=new SparkConf().setMaster("local[*]").setAppName("SparkSql_Demo1")
    //sparkSession
    val spark=SparkSession.builder().config(conf).getOrCreate()
    val sc=spark.sparkContext
    import spark.implicits._

    val jdbcDF: DataFrame = spark.read.format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/test")
      .option("dbtable", "test")
      .option("user", "root")
      .option("password", "root").load()
    jdbcDF.show()
    sc.stop()
  }
}

3 spark-shell方式和spark-submit读写mysql

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

从Mysql中加载数据库

1）启动Spark Shell，必须指定mysql连接驱动jar包

spark-shell --master local[*] \
--jars /opt/soft/mysql-connector-java-5.1.35-bin.jar \
--driver-class-path /opt/soft/mysql-connector-java-5.1.35-bin.jar

2）从mysql中加载数据

val sqlContext=new org.apache.spark.sql.SQLContext(sc) 
val jdbcDF = sqlContext.read.format("jdbc").options(Map("url" -> 
"jdbc:mysql://192.168.1.139:3306/test", "driver" -> "com.mysql.jdbc.Driver", "dbtable" -> 
"tbl_user", "user" -> "root", "password" -> "root")).load() 
jdbcDF.show() 

将数据写入到MySQL中

1）编写代码

package com.nml.sparkSQL
import java.util.Properties
import org.apache.spark.sql.{Row, SQLContext, SparkSession}
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}
import org.apache.spark.{SparkConf, SparkContext}
object JdbcDFDemo {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("MysqlDemo").setMaster("local[2]")
    val sc = new SparkContext(conf)
    val sqlContext = SparkSession.builder().config(conf).getOrCreate()
    //通过并行化创建RDD
    val personRDD = sc.parallelize(Array("1 tom 5", "2 jerry 3", "3 kitty 6")).map(_.split(" "))
    //通过StructType直接指定每个字段的schema
    val schema = StructType(
      List(
        StructField("id", IntegerType, true),
        StructField("name", StringType, true),
        StructField("age", IntegerType, true)
      )
    )
    //将RDD映射到rowRDD
    val rowRDD = personRDD.map(p=>Row(p(0).toInt, p(1).trim, p(2).toInt))
    //将schema信息应用到rowRDD上
    val personDataFrame = sqlContext.createDataFrame(rowRDD, schema)
    //创建Properties存储数据库相关属性
    val prop = new Properties()
    prop.put("user", "root")
    prop.put("password", "root")
    //将数据追加到数据库
    personDataFrame.write.mode("append").jdbc("jdbc:mysql://localhost:3306/test","bigdata.person", prop)
    sc.stop()
  }
}

2）用maven将程序打包
3）将jar包提交到spark集群

spark-submit \
--class com.nml.sparkSQL.jdbcDF \
--master local[*] \
--jars mysql-connector-java-5.1.35-bin.jar \
--driver-class-path mysql-connector-java-5.1.35-bin.jar \
/root/demo.jar