SparkSQL 学习笔记

一.Spark SQL概述

1.1 什么是Spark SQL

Spark SQL是spark用于结构化数据处理的Spark模块

1.2 为什么要有Spark SQL

Hadoop => MapReduce => Hive(HQL)
Spark => RDD => Shark(Spark+Hive) => SparkSQL & Hive on Spark

Hive on Spark：Hive 既作为存储元数据又负责SQL的解析优化，语法是HQL语法，执行引擎变成了Spark，Spark负责采用RDD执行
Spark on Hive：Hive只作为存储元数据，Spark负责SQL解析优化，语法是Spark SQL语法，Spark负责采用优化后的RDD执行

1.3 Spark SQL原理

1.3.1 什么是DataFrame
1）DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库中的二维表格。
2）DataFrame与RDD的主要区别在于，前者带有schema元信息，即DataFrame所表示的二维表数据集的每一列都带有名称和类型。

左侧的RDD[Person]虽然以Person为类型参数，但Spark框架本身不了解Person类的内部结构。而右侧的DataFrame却提供了详细的结构信息，使得Spark SQL可以清楚地知道该数据集中包含哪些列，每列的名称和类型各是什么。
3）Spark SQL性能上比RDD要高。因为Spark SQL了解数据内部结构，从而对藏于DataFrame背后的数据源以及作用于DataFrame之上的变换进行了针对性的优化，最终达到大幅提升运行时效率的目标。反观RDD，由于无从得知所存数据元素的具体内部结构，Spark Core只能在Stage层面进行简单、通用的流水线优化。

1.3.2 什么是DataSet
DataSet是分布式数据集合。
DataSet是强类型的。比如可以有DataSet[Car]，DataSet[User]。具有类型安全检查
DataFrame是DataSet的特例，type DataFrame = DataSet[Row] ，Row是一个类型，跟Car、User这些的类型一样，所有的表结构信息都用Row来表示。

1.3.3 RDD、DataFrame和DataSet之间关系
1）发展历史
RDD（Spark1.0）=》Dataframe（Spark1.3）=》Dataset（Spark1.6）
如果同样的数据都给到这三个数据结构，他们分别计算之后，都会给出相同的结果。不同是的他们的执行效率和执行方式。在后期的Spark版本中，DataSet有可能会逐步取代RDD和DataFrame成为唯一的API接口。
2）三者的共性
（1）RDD、DataFrame、DataSet全都是Spark平台下的分布式弹性数据集，为处理超大型数据提供便利
（2）三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action行动算子如foreach时，三者才会开始遍历运算
（3）三者有许多共同的函数，如filter，排序等
（4）三者都会根据Spark的内存情况自动缓存运算
（5）三者都有分区的概念

1.4 Spark SQL的特点

1）易整合
无缝的整合了SQL查询和Spark编程。

2）统一的数据访问方式
使用相同的方式连接不同的数据源。

3）兼容Hive
在已有的仓库上直接运行SQL或者HiveSQL。

4）标准的数据连接
通过JDBC或者ODBC来连接

二.Spark SQL编程

2.1 SparkSession新的起始点

在老的版本中，SparkSQL提供两种SQL查询起始点：
一个叫SQLContext，用于Spark自己提供的SQL查询；
一个叫HiveContext，用于连接Hive的查询。
SparkSession是Spark最新的SQL查询起始点，实质上是SQLContext和HiveContext的组合，所以在SQLContext和HiveContext上可用的API在SparkSession上同样是可以使用的。
SparkSession内部封装了SparkContext，所以计算实际上是由SparkContext完成的。当我们使用spark-shell的时候，Spark框架会自动的创建一个名称叫做Spark的SparkSession，就像我们以前可以自动获取到一个sc来表示SparkContext。

[linux@node1 spark-local]$ bin/spark-shell 
21/07/21 17:17:00 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Spark context Web UI available at http://node1:4040
Spark context available as 'sc' (master = local[*], app id = local-1626859028749).
Spark session available as 'spark'.
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 3.0.0
      /_/
         
Using Scala version 2.12.10 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_212)
Type in expressions to have them evaluated.
Type :help for more information.

scala> 

2.2 DataFrame

DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库中的二维表格。

2.2.1 创建DataFrame
在Spark SQL中SparkSession是创建DataFrame和执行SQL的入口，创建DataFrame有三种方式：
1.通过Spark的数据源进行创建；
2.从一个存在的RDD进行转换；
3.还可以从Hive Table进行查询返回。

1）从Spark数据源进行创建
（1）数据准备，在/opt/module/spark-local目录下创建一个user.json文件

{"age":20,"name":"qiaofeng"}
{"age":19,"name":"xuzhu"}
{"age":18,"name":"duanyu"}

（2）查看Spark支持创建文件的数据源格式，使用tab键查看

scala> spark.read.
csv   format   jdbc   json   load   option   options   orc   parquet   schema   table   text   textFile

（3）读取json文件创建DataFrame

scala> val df = spark.read.json("/opt/module/spark-local/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

注意：如果从内存中获取数据，Spark可以知道数据类型具体是什么，如果是数字，默认作为Int处理；但是从文件中读取的数字，不能确定是什么类型，所以用BigInt接收，可以和Long类型转换，但是和Int不能进行转换。
（4）查看DataFrame算子

scala> df.

（5）展示结果

scala> df.show
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
| 18|  duanyu|
+---+--------+

2）从RDD进行转换

3）Hive Table进行查询返回

2.2.2 SQL风格语法

SQL语法风格是指我们查询数据的时候使用SQL语句来查询，这种风格的查询必须要有临时视图或者全局视图来辅助。
视图：对特定表的数据的查询结果重复使用。View只能查询，不能修改和插入。
select * from t_user where age > 30 的查询结果可以存储在临时表v_user_age中，方便在后面重复使用。例如：select * from v_user_age

1）临时视图
（1）创建一个DataFrame

scala> val df = spark.read.json("/opt/module/spark-local/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

（2）对DataFrame创建一个临时视图

scala> df.createOrReplaceTempView("user")

（3）通过SQL语句实现查询全表

scala> val sqlDF = spark.sql("SELECT * FROM user")
sqlDF: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

（4）结果展示

scala> sqlDF.show
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
| 18|  duanyu|
+---+--------+

（5）求年龄的平均值

scala> val sqlDF = spark.sql("SELECT avg(age) from user")
sqlDF: org.apache.spark.sql.DataFrame = [avg(age): double]

（6）结果展示

scala> sqlDF.show
+--------+                                                                      
|avg(age)|
+--------+
|    19.0|
+--------+

（7）创建一个新会话再执行，发现视图找不到

scala> spark.newSession().sql("SELECT avg(age) from user ").show()
org.apache.spark.sql.AnalysisException: Table or view not found: user; line 1 pos 14;

注意：普通临时视图是Session范围内的，如果想全局有效，可以创建全局临时视图。

2）全局视图
（1）对于DataFrame创建一个全局视图

scala> df.createGlobalTempView("user")

（2）通过SQL语句实现查询全表

scala> spark.sql("SELECT * FROM global_temp.user").show()
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
| 18|  duanyu|
+---+--------+

（3）新建session，通过SQL语句实现查询全表

scala> spark.newSession().sql("SELECT * FROM global_temp.user").show()
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
| 18|  duanyu|
+---+--------+

2.2.3 DSL风格语法
DataFrame提供一个特定领域语言（domain-specific language，DSL）去管理结构化的数据，可以在Scala，Java，Python和R中使用DSL，使用DSL语法风格不必去创建临时视图了。
1）创建一个DataFrame

scala> val df = spark.read.json("/opt/module/spark-local/user.json")

df: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

2）查看DataFrame的Schema信息

scala> df.printSchema
root
 |-- age: Long (nullable = true)
 |-- name: string (nullable = true)

3）只查看“name”列数据

scala> df.select("name").show()
+--------+
|    name|
+--------+
|qiaofeng|
|   xuzhu|
|  duanyu|
+--------+

4）查看年龄和姓名，且年龄大于18

scala> df.select("age","name").where("age>18").show
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
+---+--------+

5）查看所有列

scala> df.select("*").show
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
| 19|   xuzhu|
| 18|  duanyu|
+---+--------+

6）查看“name”列数据以及“age+1”数据
注意：涉及到运算的时候，每列都必须使用$，或者采用引号表达式：单引号+字段名

scala> df.select($"name",$"age" + 1).show
scala> df.select('name, 'age + 1).show()
scala> df.select('name, 'age + 1 as "newage").show()

+--------+---------+
|    name|(age + 1)|
+--------+---------+
|qiaofeng|       21|
|   xuzhu|       20|
|  duanyu|       19|
+--------+---------+

7）查看“age”大于“19”的数据

scala> df.filter("age>19").show
+---+--------+
|age|    name|
+---+--------+
| 20|qiaofeng|
+---+--------+

8）按照“age”分组，查看数据条数

scala> df.groupBy("age").count.show
+---+-----+
|age|count|
+---+-----+
| 19|    1|
| 18|    1|
| 20|    1|
+---+-----+

2.3 DataSet

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

2.3.1 创建DataSet（基本类型序列）
使用基本类型的序列创建DataSet
（1）将集合转换为DataSet

scala> val ds = Seq(1,2,3,4,5,6).toDS

ds: org.apache.spark.sql.Dataset[Int] = [value: int]

（2）查看DataSet的值

scala> ds.show
+-----+
|value|
+-----+
|    1|
|    2|
|    3|
|    4|
|    5|
|    6|
+-----+

2.3.2 创建DataSet（样例类序列）
使用样例类序列创建DataSet
（1）创建一个User的样例类

scala> case class User(name: String, age: Long)
defined class User

（2）将集合转换为DataSet

scala> val caseClassDS = Seq(User("wangyuyan",2)).toDS()
caseClassDS: org.apache.spark.sql.Dataset[User] = [name: string, age: bigint]

（3）查看DataSet的值

scala> caseClassDS.show
+---------+---+
|     name|age|
+---------+---+
|wangyuyan|  2|
+---------+---+

注意：在实际使用的时候，很少用到把序列转换成DataSet，更多是通过RDD来得到DataSet

2.4 RDD、DataFrame、DataSet相互转换

三.SparkSQL代码

在pom.xml文件中添加如下依赖

 <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-sql_2.12</artifactId>
      <version>3.0.0</version>
 </dependency>

代码实现

3.1 创建DataFrame

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.types.{IntegerType, StringType, StructType}
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
import org.junit.Test

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

class $01_DataFrameCreate {

  val spark: SparkSession = SparkSession.builder().master("local[4]").appName("sparkSql").getOrCreate()

  //导入隐式转换

  import spark.implicits._

  /**
   * dataframe创建的方式:
   * 1、通过rdd创建:
   * 如果rdd中元素类型是case class，那么rdd.toDF转成dataFrame的时候，会自动将case class的属性名当成列名
   * 如果rdd中元素类型是元组,那么rdd.toDF(列名1,列名2,..)的方式重命名列名
   * 2、根据集合创建
   * 如果集合中元素类型是case class，那么集合.toDF转成dataFrame的时候，会自动将case class的属性名当成列名
   * 如果集合中元素类型是元组,那么集合.toDF(列名1,列名2,..)的方式重命名列名
   * 3、通过createDataFrame api创建
   * 4、读取文件创建
   */

  @Test
  def createDataFrameByRdd(): Unit = {
    val rdd: RDD[Person] = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    val df: DataFrame = rdd.toDF()
    df.show()

    val rdd2: RDD[(String, Int)] = spark.sparkContext.parallelize(List(("wagnwu", 20), ("zhaoliu", 30), ("qianqi", 25)))
    val df2: DataFrame = rdd2.toDF("name", "age")
    df2.show()
  }

  @Test
  def createDataFrameByCollection(): Unit = {
    val list = List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25))
    val df: DataFrame = list.toDF()
    df.show()

    val list2 = List(("wagnwu", 20), ("zhaoliu", 30), ("qianqi", 25))
    val df2: DataFrame = list2.toDF("name", "age")
    df2.show()
  }

  @Test
  def createDataFrameByApi(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    val df: DataFrame = spark.createDataFrame(rdd)
    df.show()

    val rdd2: RDD[Row] = spark.sparkContext.parallelize(List(Row("wagnwu", 20), Row("zhaoliu", 30), Row("qianqi", 25)))
    val schema = new StructType().add("name", StringType).add("age", IntegerType)
    val df2: DataFrame = spark.createDataFrame(rdd2, schema)
    df2.show()
  }

  @Test
  def createDataFrameByFile(): Unit = {
    //spark.read.textFile()除了此方法返回dataset，其他读取文件都是返回dataframe
    val df: DataFrame = spark.read.csv("datas/product.txt")
    df.show()
  }

}

3.2 SparkSql代码编写

import org.apache.spark.sql.{DataFrame, SparkSession}
import org.junit.Test

case class Emp(name: String, age: Int, province: String)

class $02_SparkSql {

  val spark: SparkSession = SparkSession.builder().master("local[4]").appName("SparkSql").getOrCreate()

  //导入隐式转换

  import spark.implicits._

  /**
   * sparksql代码编写有两种方式:
   * 1、sql方式
   * 2、DSL方式[使用算子、方法的方式]
   */

  @Test
  def sparksqlBySql(): Unit = {
    var rdd = spark.sparkContext.parallelize(List(
      Emp("zhangsan", 20, "shenzhen"),
      Emp("lisi", 30, "beijing"),
      Emp("wangwu", 25, "shanghai"),
      Emp("zhaoliu", 53, "beijing"),
      Emp("qianqi", 42, "shenzhen"),
      Emp("wangba", 37, "beijing"),
      Emp("wangba", 29, "beijing")
    ))

    val df: DataFrame = rdd.toDF()
    //需要将df的数据集注册成表
    //此种方式创建的表只能在当前SparkSession中使用 【常用】
    //df.createOrReplaceTempView("emp")

    /*spark.sql(
      """select province,max(age)
        |from emp where age<=40
        |group by province""".stripMargin).show()*/

    //此种方式创建的表可以在所有的SparkSession中使用
    df.createOrReplaceGlobalTempView("emp")

    val spark2: SparkSession = spark.newSession()
    spark2.sql(
      """
        |select province,max(age)
        |from global_temp.emp where age<=40
        |group by province
      """.stripMargin).show()
  }

  @Test
  def sparksqlByDSL(): Unit = {

    val rdd = spark.sparkContext.parallelize(List(
      Emp("zhangsan",20,"shenzhen"),
      Emp("lisi",30,"beijing"),
      Emp("lisi",30,"beijing"),
      Emp("wangwu",25,"shanghai"),
      Emp("wangwu",25,"shenzhen"),
      Emp("zhaoliu",53,"beijing"),
      Emp("qianqi",42,"shenzhen"),
      Emp("wangba",37,"beijing"),
      Emp("wangba",29,"beijing")
    ))

    val df: DataFrame = rdd.toDF()

    //选择查询的列
    df.select('name,'age).show()
    df.select('name,'age).where("age>=30").show()

    //过滤
    df.where("age>=30").show()
    df.filter("age>=30").show()

    //去重
    df.distinct().show()
    df.dropDuplicates("name").show()

    //列裁剪
    df.select('name,'age).show

    //使用函数
    //既可以查询具体的列也可以写函数使用 【常用】
    df.selectExpr("max(age)").show()

    //导入函数方法
    import org.apache.spark.sql.functions._
    df.select(max('age)).show
  }

}

3.3 创建Column对象的几种方式

import org.apache.spark.sql.{Column, DataFrame, SparkSession}
import org.junit.Test

class $03_Column {

  val spark: SparkSession = SparkSession.builder().master("local[4]").appName("SparkSql").getOrCreate()

  //导入隐式转换

  import spark.implicits._

  /**
   * Column创建方式:
   * 1、有绑定
   * 2、无绑定
      * 1、通过 '列名 创建
      * 2、$"列名" 创建
      * 3、col(列名) 创建
      * 4、column(列名) 创建
   */

  @Test
  def createColumn(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(
      Emp("zhangsan", 20, "shenzhen"),
      Emp("lisi", 30, "beijing"),
      Emp("lisi", 30, "beijing"),
      Emp("wangwu", 25, "shanghai"),
      Emp("wangwu", 25, "shenzhen"),
      Emp("zhaoliu", 53, "beijing"),
      Emp("qianqi", 42, "shenzhen"),
      Emp("wangba", 37, "beijing"),
      Emp("wangba", 29, "beijing")
    ))

    val df: DataFrame = rdd.toDF()
    //无绑定
    //1、通过 '列名 创建
    val column1: Column = 'name
    df.select(column1).show()
    //2、通过$"列名" 创建
    val column2: Column = $"name"
    df.select(column2).show()
    //3、col(列名) 创建
    import org.apache.spark.sql.functions._
    val column3 = col("name")
    df.select(column3).show()
    //4、column(列名) 创建
    val column4 = column("name")
    df.select(column4).show()

    //有绑定
    val column5: Column = df.col("name")
    val df2: DataFrame = rdd.toDF()
    df2.select(column5).show()
  }
}

3.4 Row类型对象

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
import org.junit.Test

class $04_Row {

  val spark = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  import spark.implicits._

  /**
   * Row代表一行数据
   * Row对象一般用于DataFrame
   * row对象的取值: row.getAs[列的类型](列名)
   */

  @Test
  def row(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    val df: DataFrame = rdd.toDF()
    val rdd2: RDD[Row] = df.rdd
    //row对象取值
    rdd2.foreach(row => {
      //取出年龄的值
      val age: Int = row.getAs[Int]("age")
      println(age)
    })
  }
}

3.5 创建DataSet

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{Dataset, SparkSession}
import org.junit.Test

class $05_DataSetCreate {
  val spark: SparkSession = SparkSession.builder().master("local[4]").appName("SparkSql").getOrCreate()

  import spark.implicits._

  /**
   * dataset创建方式:
   * 1、通过rdd创建
   * 如果rdd的里面的元素是case class，那么通过rdd.toDS将rdd转成dataset，dataset的列名就是case class属性名
   * 如果rdd的里面的元素不是case class，那么通过rdd.toDS将rdd转成dataset，dataset的列名是默认列名
   * 2、通过集合创建
   * 3、通过createDataSet api创建
   * 4、读取文件文件【读取文本文件才能创建dataset】
   *
   * dataset与dataFrame使用时机:
   * 1、rdd转换的时候,如果rdd中的元素是case class，那么此时转成dataFrame/dataset都行
   * 如果rdd中的元素不是case class，那么此时推荐转成dataFrame,因为通过toDF指定列名
   * 2、如果想要使用强类型算子[算子参数类型不是Row、Column类型]的算子<比如map、flatMap等算子>,此时推荐使用DataSet
   *
   * dataFrame与dataSet区别:
   * dataFrame是弱类型,是运行期安全
   * dataset是强类型,是运行期和编译器都安全
   */

  @Test
  def createDataSetByRdd(): Unit = {
    val rdd1 = spark.sparkContext.parallelize(List(Person("wagnwu",20),Person("zhaoliu",30),Person("qianqi",25)))
    val rdd2 = spark.sparkContext.parallelize(List(("wagnwu",20),("zhaoliu",30),("qianqi",25)))
    val ds: Dataset[Person] = rdd1.toDS()
    val ds2: Dataset[(String, Int)] = rdd2.toDS()
    ds.show()
    ds2.show()
    ds2.filter(x=>x._2<30).show()
    ds2.toDF().filter(row=>row.getAs[Int]("_2")<30).show()
    ds2.toDF().filter("_2=30").show()
  }

  @Test
  def createDataSetByCollection():Unit = {
    val list = List(Person("wagnwu",20),Person("zhaoliu",30),Person("qianqi",25))
    val ds: Dataset[Person] = list.toDS()
    ds.show()
    val list2 = List(("wagnwu",20),("zhaoliu",30),("qianqi",25))
    val ds2: Dataset[(String, Int)] = list2.toDS()
    ds2.show()
  }

  @Test
  def createDataSetByApi():Unit = {
    val rdd: RDD[Person] = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    val ds: Dataset[Person] = spark.createDataset(rdd)
    ds.show()
  }

  @Test
  def createDatasetByFile():Unit = {
    val ds = spark.read.textFile("datas/wc.txt")
    ds.show()
  }

}

3.6 RDD与DataFrame与DataSet相互转换

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}
import org.junit.Test

case class Student(name: String, age: Int)

class $06_RddToDataFrameToDataSet {

  val spark = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  import spark.implicits._

  /**
   * rdd与dataFrame的相互转换
   */
  @Test
  def rddToDataFrame(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    //rdd转换DataFrame: toDF
    val df: DataFrame = rdd.toDF()
    //dataFrame转换rdd: df.rdd
    val rdd2: RDD[Row] = df.rdd
  }

  /**
   * rdd与dataset的转换
   */
  @Test
  def rddToDataSet(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    //rdd转dataset: toDS
    val ds: Dataset[Person] = rdd.toDS()
    //dataset转rdd: ds.rdd
    val rdd2: RDD[Person] = ds.rdd
  }

  /**
   * dataset和dataFrame相互转换
   */
  @Test
  def dataFrameToDataSet(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(Person("wagnwu", 20), Person("zhaoliu", 30), Person("qianqi", 25)))
    //rdd转dataset: toDS
    val ds: Dataset[Person] = rdd.toDS()
    //dataset转换dataFrame: toDF
    val df: DataFrame = ds.toDF()
    df.show()
    //dataFrame转dataSet: as[元素类型]
    //<首先元素类型如果是case class, case class的属性名要与dataframe的列名要一致>
    val ds2: Dataset[Student] = df.as[Student]
    val ds3: Dataset[(String, Int)] = df.as[(String, Int)]
    ds3.show()
  }

}

四.用户自定义函数

4.1 UDF

1）UDF：一行进入，一行出
2）代码实现

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.junit.Test

class $01_UDF extends Serializable {

  val spark: SparkSession = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  import spark.implicits._

  @Test
  def test(): Unit = {
    val rdd: RDD[(String, String)] = spark.sparkContext.parallelize(List(
      ("00123", "zhangsan"),
      ("256", "lisi"),
      ("0135", "wangwu"),
      ("000368", "qianqi"),
      ("00378", "zhaoliu")
    ))

    val df: DataFrame = rdd.toDF("id", "name")
    // 需求: 将员工id不满8位的以0补齐
    //注册udf函数
    //spark.udf.register("prefixId", prefixId)
    //方法转函数
    spark.udf.register("prefixId", prefixId2 _)
    df.selectExpr("prefixId(id) preId").show()

    spark.udf.register("myconcat", concat _)
    df.selectExpr("myconcat(id,name) concatidname").show()
  }

  //定义udf函数
  var prefixId = (id: String) => {
    var num = 8 - id.length
    s"${"0" * num}${id}"
  }

  //定义方法
  def prefixId2(id: String): String = {
    var num = 8 - id.length
    s"${"0" * num}${id}"
  }

  def concat(id: String, name: String): String = {
    s"${id}_${name}"
  }
}

4.2 UDAF

1）UDAF：输入多行，返回一行。
2）Spark3.x推荐使用extends Aggregator自定义UDAF，属于强类型的Dataset方式。
3）Spark2.x使用extends UserDefinedAggregateFunction，属于弱类型的DataFrame
4）案例实操
需求：实现求平均年龄
（1）自定义聚合函数实现

import org.apache.spark.sql.SparkSession
import org.junit.Test

class $02_UDAF {

  val spark = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  import spark.implicits._

  @Test
  def test(): Unit = {
    val rdd = spark.sparkContext.parallelize(List(
      (1, "zhangsan", 20, "开发部"),
      (2, "wanwu", 25, "产品部"),
      (3, "aa", 26, "开发部"),
      (3, "lisi", 40, "开发部"),
      (4, "bb", 30, "产品部"),
      (5, "cc", 28, "产品部")
    ))

    //统计每个部门的员工年龄平均值
    val df = rdd.toDF("id", "name", "age", "dept")
    df.createOrReplaceTempView("user")
    spark.sql("select dept,avg(age) from user group by dept").show()

    //注册udaf函数
    //弱类型
    //spark.udf.register("myAvg",new MyAvgUDAF)
    //强类型
    import org.apache.spark.sql.functions._
    spark.udf.register("myAvg", udaf(new MyAvgUDAF2))

    spark.sql("select dept,myAvg(age) from user group by dept").show
  }
}

（2）自定义聚合函数实现-强类型

import org.apache.spark.sql.{Encoder, Encoders}
import org.apache.spark.sql.expressions.Aggregator

/**
 * 强类型自定义udaf函数:
 * 1、定义一个class继承Aggregator[统计的列的类型,中间变量类型,输出结果类型]
 * 2、重写方法
 * 3、注册
 * import org.apache.spark.sql.functions._
 *         spark.udf.register(函数名, udaf(自定义udaf对象))
 */
case class AgeBuff(var sum: Int, var count: Int)

class MyAvgUDAF2 extends Aggregator[Int, AgeBuff, Double] {
  /**
   * 初始化中间变量
   *
   * @return
   */
  override def zero: AgeBuff = {
    AgeBuff(0, 0)
  }

  /**
   * 在每个task上进行统计
   *
   * @param b
   * @param a
   * @return
   */
  override def reduce(buff: AgeBuff, age: Int): AgeBuff = {
    //更新sum与count的值
    buff.sum = buff.sum + age
    buff.count = buff.count + 1
    buff
  }

  /**
   * 聚合task的结果
   *
   * @param b1
   * @param b2
   * @return
   */
  override def merge(b1: AgeBuff, b2: AgeBuff): AgeBuff = {
    //合并sum与count的值
    b1.sum = b1.sum + b2.sum
    b1.count = b1.count + b2.count
    b1
  }

  /**
   * 获取最终结果
   *
   * @param reduction
   * @return
   */
  override def finish(reduction: AgeBuff): Double = reduction.sum.toDouble / reduction.count

  /**
   * 编码中间变量类型
   *
   * @return
   */
  override def bufferEncoder: Encoder[AgeBuff] = Encoders.product[AgeBuff]

  /**
   * 编码最终结果类型
   *
   * @return
   */
  override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
}

（3）自定义聚合函数实现-弱类型（过时——了解）

import org.apache.spark.sql.Row
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DoubleType, IntegerType, StructType}

/**
 * 自定义udaf函数:
 * 1、定义class 继承 UserDefinedAggregateFunction
 * 2、重写方法
 * 3、注册: spark.udf.register
 * 4、使用
 */
class MyAvgUDAF extends UserDefinedAggregateFunction {

  /**
   * 指定待统计的列的类型
   *
   * @return
   */
  override def inputSchema: StructType = new StructType().add("input", IntegerType)

  /**
   * 指定中间变量的数据类型
   *
   * @return
   */
  override def bufferSchema: StructType = new StructType().add("sum", IntegerType).add("count", IntegerType)

  /**
   * 指定最终结果类型
   *
   * @return
   */
  override def dataType: DataType = DoubleType

  /**
   * 稳定性: 同样的输入输出是否相同
   *
   * @return
   */
  override def deterministic: Boolean = true

  /**
   * 初始化中间变量
   *
   * @param buffer
   */
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    //给sum赋予初始值
    buffer.update(0, 0)
    //给count赋予初始值
    buffer.update(1, 0)
  }

  /**
   * 在每个task中进行统计
   *
   * @param buffer
   * @param input
   */
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    //更新sum的值
    buffer.update(0, buffer.getAs[Int](0) + input.getAs[Int](0))

    //更新count的值
    buffer.update(1, buffer.getAs[Int](1) + 1)
  }

  /**
   * 合并每个task的统计结果
   *
   * @param buffer1
   * @param buffer2
   */
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    //合并sum值
    buffer1.update(0, buffer1.getAs[Int](0) + buffer2.getAs[Int](0))
    //合并count值
    buffer1.update(1, buffer1.getAs[Int](1) + buffer2.getAs[Int](1))
  }

  /**
   * 计算最终结果
   *
   * @param buffer
   * @return
   */
  override def evaluate(buffer: Row): Any = {
    buffer.getAs[Int](0).toDouble / buffer.getAs[Int](1)
  }
}

4.3 UDTF（没有）

输入一行，返回多行（Hive）；
SparkSQL中没有UDTF，Spark中用flatMap即可实现该功能

五.数据的加载与保存

在pom文件种加入mysql连接的依赖

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

5.1 加载数据

import java.util.Properties
import org.apache.spark.sql.{DataFrame, Dataset, Encoder, Encoders, SaveMode, SparkSession}
import org.junit.Test

class $01_SparkSqlRead {

  val spark = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  /**
   * sparksql读取文件有两种方式
   * 1.spark.read
   *     .format("")   --指定读取的数据格式
   *     [.option(..)  --指定读取的时候需要的参数
   *     .option(..)]
   *     .load(path)   --加载数据
   *
   * 2、spark.read[.option].json/csv/jdbc/parquet
   */

  //读取文本文件
  @Test
  def readTextFile(): Unit = {
    val df: DataFrame = spark.read.format("text").load("datas\\wc.txt")

    val ds: Dataset[String] = spark.read.textFile("datas\\wc.txt")
  }

  //读取json数据
  @Test
  def readJson(): Unit = {
    val df: DataFrame = spark.read.format("json").load("datas\\pmt.json")

    val df2: DataFrame = spark.read.json("datas\\pmt.json")
  }

  //读取parquet文件,spark默认就是读取的parquet文件
  @Test
  def readParquet(): Unit = {
    spark.read /*.format("parquet")*/ .load("output\\parquet").show()

    spark.read.parquet("output\\parquet").show()
  }

  //读取csv文件
  @Test
  def readCsv(): Unit = {
    spark.read.format("csv")
      .option("header", "true") //指定是否以第一行作为列名
      .option("sep", ",") //指定列之间的分隔符
      .option("inferSchema", "true") //自动推断列的类型
      .load("datas\\presidential_polls.csv").show()

    spark.read.option("sep", "\t\t").csv("datas/mycsv").show()

    val df: DataFrame = spark.read.option("sep", "\t").option("inferSchema", "true").csv("datas/product.txt")
    df.toDF("name", "price", "datestr", "market", "province", "city").show()
  }

  //读取mysql数据
  @Test
  def readJdbc(): Unit = {
    //第一种方式
    spark.read.format("jdbc")
      .option("url", "jdbc:mysql://node1:3306/bigdata")
      .option("dbtable", "runoob_tbl")
      .option("user", "root")
      .option("password", "root")
      .load().show()

    //第二种方式,这种方式读取mysql数据有3种API
    //第一种简写形式读取mysql  [大数据量不用]
    //此种方式读取mysql数据只有一个分区<只用于很少数据量读取>
    val properties = new Properties()
    properties.setProperty("user", "root")
    properties.setProperty("password", "root")
    val df: DataFrame = spark.read.jdbc("jdbc:mysql://node1:3306/bigdata", "runoob_tbl", properties)
    println(df.rdd.partitions.length)
    df.show()

    //第二种简写方式读取mysql 【不常用】
    //此种方式读取mysql数据的分区数 = 条件个数
    //指定数据分区条件
    val condition = Array[String]("id<5", "id>=5 and id<15", "id>=15")
    val df2: DataFrame = spark.read.jdbc("jdbc:mysql://node1:3306/bigdata", "runoob_tbl", condition, properties)
    println(df2.rdd.partitions.length)

    //第三种简写形式读取mysql
    //此种规则是工作常用
    //获取lowerBund 与 upserBound的值
    val df3: DataFrame = spark.read.jdbc("jdbc:mysql://node1:3306/bigdata", "(select max(runoob_id) maxid,min(runoob_id) minid from runoob_tbl) runoob_tb1", properties)
    //将df3转成ds，并讲df3中数据一并转成(Long,Long)类型
    val encoder: Encoder[(Long, Long)] = Encoders.tuple[Long, Long](Encoders.scalaLong, Encoders.scalaLong)
    val ds: Dataset[(Long, Long)] = df3.as[(Long, Long)](encoder)
    ds.show()
    val arr: (Long, Long) = ds.collect().head
    val frame: DataFrame = spark.read.jdbc("jdbc:mysql://node1:3306/bigdata", "runoob_tbl", "runoob_id", arr._2, arr._1, 50, properties)
    frame.show()
    println(frame.rdd.partitions.length)
  }

}

5.2 保存数据

import java.util.Properties
import org.apache.spark.sql.{DataFrame, Dataset, SaveMode, SparkSession}
import org.junit.Test

class $02_SparksqlWrite {

  val spark = SparkSession.builder().master("local[4]").appName("test").getOrCreate()

  import spark.implicits._

  /**
   * 数据写入有两种方式:
   * 第一种方式
   *      df/ds.write
   *           .mode(SaveMode)
   *           .format() --指定文件写入格式[json.csv,..]
   *           .option(.) --指定写入需要的参数
   *           .save(path) --指定数据保存
   * 第二种方式:
   *      df/ds.write.mode(SaveMode).csv(..)
   *
   *   SaveMode.Append: 如果目标目录/表 已经存在，则将数据追加进去[写入mysql常用]
   *   SaveMode.Overwrite: 如果目标目录/表 已经存在，将目录/表删除,重新写入[写入HDFS常用]
   *   SaveMode.ErrorIfExists: 如果目标目录/表 已经存在,报错
   *   SaveMode.Ignore：: 如果目标目录/表 已经存在,啥也不干
   */

  @Test
  def fileWrite(): Unit = {
    val properties = new Properties()
    properties.setProperty("user", "root")
    properties.setProperty("password", "root")
    val frame: DataFrame = spark.read.jdbc("jdbc:mysql://node1:3306/bigdata", "runoob_tbl", "runoob_id", 0, 20, 20, properties)
    //写入数据
    val df: Dataset[String] = spark.read.textFile("datas/wc.txt")
    df.write.mode(SaveMode.Overwrite).format("text").save("output/text")
    df.write.mode(SaveMode.Overwrite).text("output/text")

    //写成json数据
    frame.write.mode(SaveMode.Overwrite).format("json").save("output/json")
    frame.write.mode(SaveMode.Overwrite).json("output/json")

    //写成parquet文件【spark默认写成parquet】
    frame.write.mode(SaveMode.Overwrite)/*.format("parquet") */.save("output/parquet")
    frame.write.mode(SaveMode.Overwrite).parquet("output/parquet")

    //写成csv文件
    frame.write.mode(SaveMode.Overwrite).format("csv").option("sep", "\t").option("header", "true").save("output/csv")
    frame.write.mode(SaveMode.Overwrite).option("sep", "\t").option("header", "true").csv("output/csv")

    //写jdbc
    val df2: DataFrame = frame.select("runoob_title", "runoob_author", "submission_date")
    df2.write.mode(SaveMode.Append).format("jdbc")
      .option("url", "jdbc:mysql://node1:3306/bigdata?characterEncoding=utf8")
      .option("dbtable", "runoob_tbl")
      .option("user", "root")
      .option("password", "root")
      .save()
  }

}

六.与Hive交互

SparkSQL可以采用内嵌Hive，也可以采用外部Hive。企业开发中，通常采用外部Hive。

6.1 内嵌Hive应用

内嵌Hive，元数据存储在Derby数据库。
1）如果使用Spark内嵌的Hive，则什么都不用做，直接使用即可。

[linux@node1 spark-local]$ bin/spark-shell 

scala> spark.sql("show tables").show

注意：执行完后，发现多了$SPARK_HOME/metastore_db和derby.log，用于存储元数据
2）创建一张表

scala> spark.sql("create table user(id int, name string)")

注意：执行完后，发现多了$SPARK_HOME/spark-warehouse/user，用于存储数据库数据
3）查看表

scala> spark.sql("show tables").show

4）向表中插入数据

scala> spark.sql("insert into user values(1,'zs')")

5）查询数据

scala> spark.sql("select * from user").show

注意：然而在实际使用中，几乎没有任何人会使用内置的Hive，因为元数据存储在derby数据库，不支持多客户端访问。

6.2 外部Hive应用

如果Spark要接管Hive外部已经部署好的Hive，需要通过以下几个步骤。
0）为了说明内嵌Hive和外部Hive区别：删除内嵌Hive的metastore_db和spark-warehouse

[linux@node1 spark-local]$ rm -rf metastore_db/ spark-warehouse/

1）确定原有Hive是正常工作的

[linux@node1 hadoop-3.1.3]$ sbin/start-dfs.sh
[linux@node2 hadoop-3.1.3]$ sbin/start-yarn.sh
[linux@node1 ~]$ hiveservices.sh start

[linux@node1 hive]$ bin/hive

2）需要把hive-site.xml拷贝到spark的conf/目录下

[linux@node1 conf]$ cp /opt/module/hive/conf/hive-site.xml /opt/module/spark-local/conf/

3）如果以前hive-site.xml文件中，配置过Tez相关信息，注释掉（不是必须）
4）把MySQL的驱动copy到Spark的jars/目录下

[linux@node1 software]$ cp /opt/software/mysql/mysql-connector-java-5.1.48.jar /opt/module/spark-local/jars/

5）需要提前启动hive服务，/opt/module/hive/bin/hiveservices.sh start（不是必须）
6）如果访问不到HDFS，则需把core-site.xml和hdfs-site.xml拷贝到conf/目录（不是必须）
7）启动 spark-shell

[linux@node1 spark-local]$ bin/spark-shell

8）查询表

scala> spark.sql("show tables").show

9）创建一个数据库

scala> spark.sql("create table user(id int, name string)")

10）向表中插入数据

scala> spark.sql("insert into user values(1,'zs')")

11）查询数据

scala> spark.sql("select * from user").show

运行Spark SQL CLI
Spark SQL CLI可以很方便的在本地运行Hive元数据服务以及从命令行执行查询任务。在Spark目录下执行如下命令启动Spark SQL CLI，直接执行SQL语句，类似Hive窗口。

[linux@node1 spark-local]$ bin/spark-sql
spark-sql (default)> show tables;

IDEA操作Hive
1）添加依赖

<dependencies>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.12</artifactId>
        <version>3.0.0</version>
    </dependency>

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

    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-hive_2.12</artifactId>
        <version>3.0.0</version>
    </dependency>
</dependencies>

2）拷贝hive-site.xml到resources目录（如果需要操作Hadoop，需要拷贝hdfs-site.xml、core-site.xml、yarn-site.xml）
3）代码实现

import org.apache.spark.sql.SparkSession

object SparkHive {

  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "linux")
    val spark: SparkSession = SparkSession.builder()
      .master("local[4]")
      .appName("Sparksql")
      //开启hive支持
      .enableHiveSupport()
      .getOrCreate()

    //连接外部Hive，并进行操作
    spark.sql("show tables").show()
    spark.sql("create table user3(id int, name string)")
    spark.sql("insert into user3 values(1,'zs')")
    spark.sql("select * from user3").show
  }

}