Spark四之Spark SQL

Spark SQL

一、Spark SQL概述

​ Spark SQL是Spark用来处理结构化数据的一个模块，它提供了2个编程抽象：DataFrame和DataSet，并且作为分布式SQL查询引擎的作用。

spark Context --> 抽象类 RDD

spark streaming --> 抽象类 DStream

spark sql —> 抽象类 DataFrame 和 DataSet

​ 我们已经学习了Hive，它是将Hive SQL转换成MapReduce然后提交到集群上执行，大大简化了编写MapReduc的程序的复杂性，由于MapReduce这种计算模型执行效率比较慢。所有Spark SQL应运而生，它是将Spark SQL转换成RDD，然后提交到集群执行，执行效率非常快！

1.什么是DataFrame

​ 与RDD类似，DataFrame也是一个分布式数据容器。然而DataFrame更像传统数据库的二维表格，除了数据以外，还记录数据的结构信息，即schema。从API易用性的角度上看，DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好，门槛更低。

基于下边两种数据结构完成统计Person的年龄总和

rdd.reduce(…)

select sum(age) from xx

上图直观地体现了DataFrame和RDD的区别。左侧的RDD[Person]虽然以Person为类型参数，但Spark框架本身不了解Person类的内部结构。而右侧的DataFrame却提供了详细的结构信息，使得Spark SQL可以清楚地知道该数据集中包含哪些列，每列的名称和类型各是什么。DataFrame是为数据提供了Schema的视图。可以把它当做数据库中的一张表来对待，DataFrame也是懒执行的。性能上比RDD要高，主要原因：优化的执行计划：查询计划通过Spark catalyst optimiser进行优化。

性能上比RDD要高的原因?

主要想突出spark SQL底层catalyst的牛逼

底层按照最优的方式生成RDD，也会优于我们写的RDD

2.什么是DataSet

​ 1）是Dataframe API的一个扩展，是Spark最新的数据抽象。

​ 2）用户友好的API风格，既具有类型安全检查也具有Dataframe的查询优化特性。

​ 3）样例类被用来在Dataset中定义数据的结构信息，样例类中每个属性的名称直接映射到DataSet中的字段名称。

//声明一个样例类
case class Person{name:String,age:Int}
//样例类
//1.方便创建对象
Person("zz",20)
//2.样例类，scala中的模式匹配（类似于Java中的case when）

​ 4） Dataframe是Dataset的特列，DataFrame=Dataset[Row] ，所以可以通过as方法将Dataframe转换为Dataset。Row是一个类型，跟Car、Person这些的类型一样，所有的表结构信息我都用Row来表示。

dataFrame和dataSet的关系以及区别？
DataFrame是DataSet的特列，DataFrame = DataSet[Row]

​ 5）DataSet是强类型的。比如可以有DataSet[Car]，DataSet[Person].

​ 6）DataFrame只是知道字段，但是不知道字段的类型，所以在执行这些操作的时候是没办法在编译的时候检查是否类型失败的，比如你可以对一个String进行减法操作，在执行的时候才报错，而DataSet不仅仅知道字段，而且知道字段类型，所以有更严格的错误检查。就跟JSON对象和类对象之间的类比。

二、Spark SQL编程

• 添加依赖

<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-core_2.11</artifactId>
    <version>2.4.3</version>
</dependency>

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

1.DataFrame读取json文件

1.1 创建DataFrame

1. 创建a.json

{"name":"zhangsan","age":20}
{"name":"李四","age":22}
{"name":"wanguw","age":24}

2. 读取上边创建的文件,创建DataFrame对象

package day7
import org.apache.spark.sql.{DataFrame, SparkSession}
object Test_Spark_SQL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_Spark_SQL").getOrCreate()

    val df: DataFrame = spark.read.json("file:///d:/test/a.json")
    df.show()
    spark.stop()
  }
}

3. 展示结果

+---+--------+
|age|    name|
+---+--------+
| 20|zhangsan|
| 22|    李四|
| 24|  wanguw|
+---+--------+

1.2 SQL风格语法 [重点]

需求：读取json文件创建DataFrame，建立临时表，通过SQL语句查询数据

val df: DataFrame = spark.read.json("file:///d:/test/a.json")
df.createOrReplaceTempView("t_user")

val sqlDF: DataFrame = spark.sql("select * from t_user")
sqlDF .show()

package day7
import org.apache.spark.sql.{DataFrame, SparkSession}
object Test_Spark_SQL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_Spark_SQL").getOrCreate()
    val df: DataFrame = spark.read.json("file:///d:/test/a.json")
    df.createOrReplaceTempView("t_user")
    val sql =
      """
        |select avg(age) from t_user
        |""".stripMargin	//去除外边距
    val sqlDF: DataFrame = spark.sql(sql)
    sqlDF.show()
    spark.stop()
  }
}

展示结果

+---+--------+
|age|    name|
+---+--------+
| 20|zhangsan|
| 22|    李四|
| 24|  wanguw|
+---+--------+

1.3 DSL风格语法

需求：读取json文件创建DataFrame，通过DSL方法查询

package day7
import org.apache.spark.sql.{DataFrame, SparkSession}
object Test_Spark_SQL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_Spark_SQL").getOrCreate()
    import spark.implicits._
    val df = spark.read.json("file:///d:/test/a.json")
    df.printSchema()               		 //打印Schema结构
    //指定列查询
    df.select("name").show()
    df.select($"age"+2).show()
    //查询大于20岁的
    df.filter($"age">20).show()
    //根据年龄分组，统计个数
    df.groupBy("age").count().show()
    spark.stop()
  }
}

1.4 RDD转换为DataFrame[重要]

注意：如果需要RDD与DF或者DS之间操作，那么都需要引入 import spark.implicits._

1. 创建a.txt

zhangsan	20
lisi	20
wangwu	23
zhaoliu	45

2. 读取上边创建的文件,创建RDD后，转换为DataFrame对象

import spark.implicits._        //这是一行代码,做隐式转换 spark是上边定义的变量
//使用rdd读取一个txt文件,将rdd对象转换成DataFrame对象
val rdd1: RDD[String] =  spark.sparkContext.textFile("d:/text/a.txt")
val df: DataFrame = rdd1.map(v => {
    val p: Array[String] = v.split("\t")
    (p(0), p(1))
}).toDF("name", "age")
df.show()

1.5 DataFrame转换为RDD

val df = spark.read.json("d:text/a.json")
val rdd: RDD[Row] = df.rdd
rdd.collect().foreach(v=>println(v))

2.DataSet

Dataset是具有强类型的数据集合，需要提供对应的类型信息。

2.1 创建DataSet

1. 定义case class

case class Person(name:String,age:Long)

2. 创建List集合，转换为DataSet对象

import spark.implicits._

val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",22)).toDS()
ds.show()

2.2 RDD转换为DataSet

import spark.implicits._

val rdd1: RDD[String] = spark.sparkContext.textFile("d:/text/a.txt")
rdd1.map(v=>{
      val arr: Array[String] = v.split("\t")
      Person(arr(0),arr(1).toLong)
}).toDS().show()

2.3DataFrame转换为DataSet

import spark.implicits._

val df: DataFrame = spark.read.json("d:/text/a.json")
val ds: Dataset[Person] = df.as[Person]
ds.show()

2.4 DataSet转换为RDD

val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",23)).toDS()
val rdd: RDD[Person] = ds.rdd

三者之间的转换：

3.DataFrame和DataSet的互操作

3.1 DataFrame转换为DataSet

import spark.implicits._

val df: DataFrame = spark.read.json("F:\\datas\\a.json")
val ds: Dataset[Person] = df.as[Person]
ds.show()

3.2 DataSet转换为DataFrame

import spark.implicits._

val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",23)).toDS()
val df: DataFrame = ds.toDF()
df.show()

RDD、DataFrame、DataSet
共同点：支持分布式计算DataFrame和DataSet底层依赖于RDD，会拥有很多相似的算子。
不同点：RDD不支持SQL语法；
	   DataFrame是一个特殊的DataSet；
	   DataFrame里面元素的类型只能是Row，DataSet更灵活一些；
	   rdd可以相互转换，一旦rdd转换为DataFrame，DataFrame再转为rdd（元素类型就只能为row了）
重点：DataFrame（SQL）
	 RDD/DataFrame/DataSet转换方式

三、自定义函数

1. 单行函数(udf)

数据库提供了很多函数，函数可以分为单行函数和聚合函数

当数据库提供的函数不满于我们使用时，我们还可以自定义函数

import spark.implicits._

val df: DataFrame = List(("zhangsan", 20, 1), ("lisi", 20, 1), ("xiaohong", 18, 0))
					.toDF("name", "age", "sex")

df.createOrReplaceTempView("t_user")

spark.udf.register("convert_sex",(sex:Int)=>{
    sex match {
   	 	case 0 => "女"
    	case 1 => "男"
    }
})
spark.sql("select name,age,convert_sex(sex) sex from t_user").show()

2. 聚合函数（udaf）

1. 定义求和的聚合函数

package day7
import org.apache.spark.sql.Row
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, IntegerType, StructType}
//自定义一个求和的聚合函数,设计一个my_sum，作用等同于数据库自带的sum聚合函数
//select my_sum(age) from t_user
class CustomSum extends UserDefinedAggregateFunction{
  //聚合函数的参数，接收输入的年龄
  override def inputSchema: StructType = {
    new StructType().add("input1",IntegerType)
  }
  //聚合函数的缓冲区,对输入的年龄进行累加放在缓冲区
  override def bufferSchema: StructType = {
    new StructType().add("sum",IntegerType)
  }
  //聚合函数执行完返回的数据类型
  override def dataType: DataType = IntegerType
  //对于相同的输入是否一直返回相同的输出
  override def deterministic: Boolean = true
  //对上述定义在缓冲区的变量初始化即sum=0
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer(0)=0
    //buffer(1)=10
  }
  //根据输入的数据对缓冲区进行更新   my_sum(age)即将缓冲区的数据加上输入的数据，然后更新到缓冲区
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    //缓冲区原有的数据
    val a1: Int = buffer.getAs[Int](0)
    //获取输入的数据
    val a2: Int = input.getAs[Int](0)
    //将缓冲区的数据和输入的数据进行累加，然后再更新到缓冲区中
    buffer.update(0,a1+a2)
    //buffer.update(1,a1-a2)
  }
  //由于datarfame在底层是并行计算，所以底层会根据分区创建多个分区
  //将不同缓冲区中的数据累加到一起，才是最终的结果
  //将其他缓冲区的结果 规约到一起
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    val s1: Int = buffer1.getAs[Int](0)
    val s2: Int = buffer2.getAs[Int](0)
    buffer1.update(0,s1+s2)
  }
  //输出计算结果
  override def evaluate(buffer: Row): Any = buffer.getAs[Int](0)
}

2. 测试自定义求和聚合函数

package day7
import org.apache.spark.sql.{DataFrame, SparkSession}
object Test_UDAF {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_UDAF").getOrCreate()
  import spark.implicits._
    val df1: DataFrame = List(("zs", 20, 1), ("lisi", 22, 1), ("xiao", 23, 0)).toDF("name", "age", "sex")
    df1.createOrReplaceTempView("t_user")
    spark.sql("select * from t_user").show()
    spark.udf.register("my_sum",new CustomSum)
    spark.sql("select my_sum(age) sumage from t_user").show()
    spark.stop()
  }
}

//测试结果

±—±–±--+
|name|age|sex|
±—±–±--+
| zs| 20| 1|
|lisi| 22| 1|
|xiao| 23| 0|
±—±–±--+

±-----+
|sumage|
±-----+
| 65|
±-----+

四、Load/Save

Spark SQL的DataFrame接口支持多种数据源的操作。

1.parquet

​ Parquet是一种流行的列式存储格式，可以高效地存储具有嵌套字段的记录。Parquet格式经常在Hadoop生态圈中被使用，它也支持Spark SQL的全部数据类型。Spark SQL 提供了直接读取和存储 Parquet 格式文件的方法。

package day8
import org.apache.spark.sql.{DataFrame, SparkSession}
object Test_Parquet {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_Parquet").getOrCreate()
  import spark.implicits._
    /*//保存数据
    val tuples = List(("zs", 20), ("lisi", 22), ("xiao", 23))
    val df1: DataFrame = tuples.toDF("name", "age")
    df1.write.parquet("file:///d:/test/parquet")*/
	//读取数据
    spark.read.parquet("file:///d:/test/parquet").show()
    spark.stop()
  }
}

可以采用SaveMode执行存储操作，SaveMode定义了对数据的处理模式。需要注意的是，这些保存模式不使用任何锁定，不是原子操作。此外，当使用Overwrite方式执行时，在输出新数据之前原数据就已经被删除。
SaveMode详细介绍如下表

例如：df.write.mode(SaveMode.Overwrite).parquet(“file:///d:/test/parquet”)

Scala	Meaning
SaveMode.ErrorIfExists (default)	如果文件存在，则报错
SaveMode.Append	原有数据基础上追加
SaveMode.Overwrite	新的数据覆盖旧的数据
SaveMode.Ignore	数据不存在则添加，数据存在则忽略

2.json

import spark.implicits._

val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
//将DataFrame对象存储在json文件中    
df.write.mode(SaveMode.Overwrite).json("file:///d:/test/a.json")
//读取json文件中的数据
spark.read.json("file:///d:/test/a.json").show()

3.CSV

csv是一个文本文件，只是可以使用excel软件打开

csv的应用场景：poi技术可以将数据导出到excel表格中，如果数据量达到十万+，此时可以将数据倒成csv格式

val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
//将DataFrame对象存储在csv文件中，并且设置表头列名
df.write.mode(SaveMode.Overwrite).option("header", "true").csv("file:///d:/test/csv")
//读取csv文件中的数据，并且读出表头列名
spark.read.option("header", "true").csv("file:///d:/test/csv").show()

4.JDBC

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

package day8
import java.util.Properties
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
object Test_Mysql {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test_Mysql").getOrCreate()
    import spark.implicits._

    val map = new Properties()
    map.put("user", "root")
    map.put("password", "123456")

    val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
    df.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://localhost:3306/test1?useSSL=false", "df_user", map)
    spark.read.jdbc("jdbc:mysql://localhost:3306/test1?useSSL=false","spark_user",map).show()
    spark.stop()
  }
}

需要添加jdbc的驱动依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>