SparkSQL核心编程

目录

一、概述

1、DataFrame是什么

2、DataSet是什么

二、SparkSQL核心编程

2.1 新的起点

2.2 DataFrame (类型不安全)

2.2.1 创建DataFrame（三种方式）

2.2.2 SQL语法

2.2.3 DSL语法

2.2.4 RDD转换为DataFrame

2.2.5 DataFrame转换为RDD

2.3 DataSet (类型安全)

2.3.1 创建DataSet

2.3.2 RDD转换为DataSet   (.toDS)

2.3.3 DataSet转换为RDD（.rdd）

2.4 DataFrame和DataSet转换

2.5 RDD、DataFrame、DataSet三者的关系

2.5.1 三者的共性

2.5.2 三者的区别

2.5.3 三者的互相转换

2.6 IDEA开发SparkSQL

2.6.1 添加依赖

2.6.2 代码实现

2.7 用户自定义函数

2.7.1 UDF

2.7.2 UDAF

2.8 数据的加载和保存

2.8.1 通用的数据加载和保存方式

2.8.2 Parquet

2.8.3 JSON

2.8.4 CSV

2.8.5 MySQL

2.8.6 Hive

三、SparkSQL项目实战

3.1 数据准备

3.2 需求 ： 各区域热门商品 Top3

---

Spark Core中，如果想要执行应用程序，需要首先构建上下文环境对象SparkContext，Spark SQL其实可以理解为对Spark Core的一种封装，不仅仅在模型上进行了封装，上下文环境对象也进行了封装。

在老的版本中，SparkSQL提供两种SQL查询起始点：一个叫SQLContext，用于Spark自己提供的SQL查询；一个叫HiveContext，用于连接Hive的查询。

SparkSession是Spark最新的SQL查询起始点，实质上是SQLContext和HiveContext的组合，所以在SQLContex和HiveContext上可用的API在SparkSession上同样是可以使用的。SparkSession内部封装了SparkContext，所以计算实际上是由sparkContext完成的。当我们使用 spark-shell 的时候, spark 会自动的创建一个叫做spark的SparkSession对象, 就像我们以前可以自动获取到一个sc来表示SparkContext对象一样

一、概述

1、DataFrame是什么

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

-- DataFrame 与RDD的区别
   1. DF带有结构(Schema)信息,即包含了二维表数据集每一列的列名和类型；
   2. Hive类似，DataFrame也支持嵌套数据类型（struct、array和map）
   3. 在性能上，DF也是懒执行的，但是执行性能优于RDD，因为它底层会自动优化执行过程，它是如何做到的呢？利用基于关系代数的等价变换，
将高成本的操作替换为低成本操作的过程，如简化shuffle阶段，先过滤再进行IO等等。

2、DataSet是什么

DataSet是分布式数据集合。DataSet是Spark 1.6中添加的一个新抽象，是DataFrame的一个扩展。它提供了RDD的优势（强类型，使用强大的lambda函数的能力）以及Spark SQL优化执行引擎的优点。DataSet也可以使用功能性的转换（操作map，flatMap，filter等等）。

a. DataSet是DataFrame API的一个扩展，是SparkSQL最新的数据抽象
b. 用户友好的API风格，既具有类型安全检查也具有DataFrame的查询优化特性；
c. 用样例类来对DataSet中定义数据的结构信息，样例类中每个属性的名称直接映射到DataSet中的字段名称；
d. DataSet是强类型的。比如可以有DataSet[Car]，DataSet[Person]。
e. DataFrame是DataSet的特列，DataFrame=DataSet[Row] ，所以可以通过as方法将DataFrame转换为DataSet。Row是一个类型，
跟Car、Person这些的类型一样，所有的表结构信息都用Row来表示。获取数据时需要指定顺序

二、SparkSQL核心编程

2.1 新的起点

Spark Core中，如果想要执行应用程序，需要首先构建上下文环境对象SparkContext，Spark SQL其实可以理解为对Spark Core的一种封装，不仅仅在模型上进行了封装，上下文环境对象也进行了封装。

在老的版本中，SparkSQL提供两种SQL查询起始点：一个叫SQLContext，用于Spark自己提供的SQL查询；一个叫HiveContext，用于连接Hive的查询。

SparkSession是Spark最新的SQL查询起始点，实质上是SQLContext和HiveContext的组合，所以在SQLContex和HiveContext上可用的API在SparkSession上同样是可以使用的。SparkSession内部封装了SparkContext，所以计算实际上是由sparkContext完成的。当我们使用 spark-shell 的时候, spark 会自动的创建一个叫做spark的SparkSession对象, 就像我们以前可以自动获取到一个sc来表示SparkContext对象一样

2.2 DataFrame (类型不安全)

Spark SQL的DataFrame API 允许我们使用 DataFrame 而不用必须去注册临时表或者生成 SQL 表达式。DataFrame API 既有 transformation操作也有action操作。

2.2.1 创建DataFrame（三种方式）

在Spark SQL中SparkSession是创建DataFrame和执行SQL的入口，创建DataFrame有三种方式：

通过Spark的数据源进行创建
从一个存在的RDD进行转换
还可以从Hive Table进行查询返回

从Spark数据源进行创建

查看Spark支持创建文件的数据源格式

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

在spark的bin/data目录中创建user.json文件
{"username":"zhangsan","age":20}

读取json文件创建DataFrame
scala> val df = spark.read.json("data/user.json")

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

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

展示结果
df.show    df.collect(返回是数组)
+---+--------+
|age|username|
+---+--------+
| 20|zhangsan|
+---+--------+

从RDD进行转换和从Hive Table进行查询返回在下面讲

2.2.2 SQL语法

SQL语法风格是指我们查询数据的时候使用SQL语句来查询，这种风格的查询必须要有临时视图（局部：只能在spark session范围用）或者全局（Spark Context范围内有效）视图来辅助（spark Context 创建 spark Session）

读取JSON文件创建DataFrame
scala> val df = spark.read.json("data/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint， username: string]

对DataFrame创建一个临时表
scala> df.createOrReplaceTempView("people")


通过SQL语句实现查询全表
scala> val sqlDF = spark.sql("SELECT * FROM people")
sqlDF: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

结果展示
scala> sqlDF.show

+---+--------+
|age|username|
+---+--------+
| 20|zhangsan|
| 30|     lisi|
| 40|   wangwu|
+---+--------+

注意：普通临时表是Session范围内的，如果想应用范围内有效，可以使用全局临时表。使用全局临时表时需要全路径访问，如：global_temp.people

对于DataFrame创建一个全局表
scala> df.createGlobalTempView("people")

通过SQL语句实现查询全局表
scala> spark.sql("SELECT * FROM global_temp.people").show()

+---+--------+
|age|username|
+---+--------+
| 20|zhangsan|
| 30|     lisi|
| 40|   wangwu|
+---+--------+

scala> spark.newSession().sql("SELECT * FROM global_temp.people").show()

+---+--------+
|age|username|
+---+--------+
| 20|zhangsan|
| 30|     lisi|
| 40|   wangwu|
+---+--------+
（）括号可以省略

2.2.3 DSL语法

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

创建一个DataFrame
scala> val df = spark.read.json("data/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint， name: string]

查看DataFrame的Schema信息
scala> df.printSchema

root
 |-- age: Long (nullable = true)
 |-- username: string (nullable = true)  当前字段可以为null

只查看"username"列数据，
scala> df.select("username").show()

+--------+
|username|
+--------+
|zhangsan|
|    lisi|
|  wangwu|
+--------+

查看"username"列数据以及"age+1"数据
注意:涉及到运算的时候, 每列都必须使用$, 或者采用引号表达式：单引号+字段名

scala> df.select($"username",$"age" + 1).show

scala> df.select('username, 'age + 1).show()

scala> df.select('username, 'age + 1 as "newage").show()
+--------+---------+
|username|(age + 1)|
+--------+---------+
|zhangsan|       21|
|     lisi|      31|
|  wangwu|       41|
+--------+---------+

查看"age"大于"30"的数据
scala> df.filter($"age">30).show

+---+---------+
|age| username|
+---+---------+
| 40|   wangwu|
+---+---------+

按照"age"分组，查看数据条数
scala> df.groupBy("age").count.show

+---+-----+
|age|count|
+---+-----+
| 20|    1|
| 30|    1|
| 40|    1|
+---+-----+

2.2.4 RDD转换为DataFrame

在IDEA中开发程序时，如果需要RDD与DF或者DS之间互相操作，那么需要引入 import spark.implicits._  （扩展RDD功能，toDF）

这里的spark不是Scala中的包名，而是创建的sparkSession对象的变量名称，所以必须先创建SparkSession对象再导入。这里的spark对象不能使用var声明，因为Scala只支持val修饰的对象的引入。

spark-shell中无需导入，自动完成此操作。

scala> val idRDD = sc.textFile("data/id.txt")
scala> idRDD.toDF("id").show   toDF+列明信息

+---+
| id|
+---+
|  1|
|  2|
|  3|
|  4|
+---+

实际开发中，一般通过样例类将RDD转换为DataFrame

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

scala> sc.makeRDD(List(("zhangsan",30), ("lisi",40))).map(t=>User(t._1, t._2)).toDF.show     

+--------+---+
|     name|age|
+--------+---+
|zhangsan| 30|
|    lisi| 40|
+--------+---+

2.2.5 DataFrame转换为RDD

DataFrame其实就是对RDD的封装，所以可以直接获取内部的RDD

scala> val df = sc.makeRDD(List(("zhangsan",30), ("lisi",40))).map(t=>User(t._1, t._2)).toDF
df: org.apache.spark.sql.DataFrame = [name: string, age: int]


scala> val rdd = df.rdd
rdd: org.apache.spark.rdd.RDD[org.apache.spark.sql.Row] = MapPartitionsRDD[46] at rdd at <console>:25

scala> val array = rdd.collect
array: Array[org.apache.spark.sql.Row] = Array([zhangsan,30], [lisi,40])
注意：此时得到的RDD存储类型为Row

scala> array(0)
res28: org.apache.spark.sql.Row = [zhangsan,30]

scala> array(0)(0)
res29: Any = zhangsan

scala> array(0).getAs[String]("name")
res30: String = zhangsan

2.3 DataSet (类型安全)

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

2.3.1 创建DataSet

使用样例类序列创建DataSet
scala> case class Person(name: String, age: Long)
defined class Person

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

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

使用基本类型的序列创建DataSet
scala> val ds = Seq(1,2,3,4,5).toDS
ds: org.apache.spark.sql.Dataset[Int] = [value: int]

scala> ds.show
+-----+
|value|
+-----+
|    1|
|    2|
|    3|
|    4|
|    5|
+-----+
注意：在实际使用的时候，很少用到把序列转换成DataSet，更多的是通过RDD来得到DataSet

2.3.2 RDD转换为DataSet   (.toDS)

SparkSQL能够自动将包含有case类的RDD转换成DataSet，case类定义了table的结构，case类属性通过反射变成了表的列名。Case类可以包含诸如Seq或者Array等复杂的结构。

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

scala> sc.makeRDD(List(("zhangsan",30), ("lisi",49))).map(t=>User(t._1, t._2)).toDS
res11: org.apache.spark.sql.Dataset[User] = [name: string, age: int]

2.3.3 DataSet转换为RDD（.rdd）

DataSet其实也是对RDD的封装，所以可以直接获取内部的RDD
scala> case class User(name:String, age:Int)
defined class User

scala> sc.makeRDD(List(("zhangsan",30), ("lisi",49))).map(t=>User(t._1, t._2)).toDS
res11: org.apache.spark.sql.Dataset[User] = [name: string, age: int]

scala> val rdd = res11.rdd
rdd: org.apache.spark.rdd.RDD[User] = MapPartitionsRDD[51] at rdd at <console>:25

scala> rdd.collect
res12: Array[User] = Array(User(zhangsan,30), User(lisi,49))

2.4 DataFrame和DataSet转换

DataFrame其实是DataSet的特例，所以它们之间是可以互相转换的。

DataFrame转换为DataSet  （简单-->复杂）

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

scala> val df = sc.makeRDD(List(("zhangsan",30), ("lisi",49))).toDF("name","age")
df: org.apache.spark.sql.DataFrame = [name: string, age: int]

scala> val ds = df.as[User]
ds: org.apache.spark.sql.Dataset[User] = [name: string, age: int]


DataSet转换为DataFrame （简单）
scala> val ds = df.as[User]
ds: org.apache.spark.sql.Dataset[User] = [name: string, age: int]

scala> val df = ds.toDF
df: org.apache.spark.sql.DataFrame = [name: string, age: int]

2.5 RDD、DataFrame、DataSet三者的关系

在SparkSQL中Spark为我们提供了两个新的抽象，分别是DataFrame和DataSet。他们和RDD有什么区别呢？首先从版本的产生上来看：

Spark1.0 => RDD
Spark1.3 => DataFrame
Spark1.6 => Dataset

如果同样的数据都给到这三个数据结构，他们分别计算之后，都会给出相同的结果。不同是的他们的执行效率和执行方式。在后期的Spark版本中，DataSet有可能会逐步取代RDD和DataFrame成为唯一的API接口。

2.5.1 三者的共性

RDD、DataFrame、DataSet全都是spark平台下的分布式弹性数据集，为处理超大型数据提供便利;
三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action如foreach时，三者才会开始遍历运算;
三者有许多共同的函数，如filter，排序等;
在对DataFrame和Dataset进行操作许多操作都需要这个包:import spark.implicits._（在创建好SparkSession对象后尽量直接导入）
三者都会根据 Spark 的内存情况自动缓存运算，这样即使数据量很大，也不用担心会内存溢出
三者都有partition的概念
DataFrame和DataSet均可使用模式匹配获取各个字段的值和类型

2.5.2 三者的区别

RDD
RDD一般和spark mllib(machine learning)同时使用
RDD不支持sparksql操作


DataFrame
与RDD和Dataset不同，DataFrame每一行的类型固定为Row，每一列的值没法直接访问，只有通过解析才能获取各个字段的值
DataFrame与DataSet一般不与 spark mllib 同时使用
DataFrame与DataSet均支持 SparkSQL 的操作，比如select，groupby之类，还能注册临时表/视窗，进行 sql 语句操作
DataFrame与DataSet支持一些特别方便的保存方式，比如保存成csv，可以带上表头，这样每一列的字段名一目了然(后面专门讲解)


DataSet
Dataset和DataFrame拥有完全相同的成员函数，区别只是每一行的数据类型不同。 DataFrame其实就是DataSet的一个特例  type DataFrame = Dataset[Row]
DataFrame也可以叫Dataset[Row],每一行的类型是Row，不解析，每一行究竟有哪些字段，各个字段又是什么类型都无从得知，只能用上面提到的getAS方法或者共性中的第七条提到的模式匹配拿出特定字段。而Dataset中，每一行是什么类型是不一定的，在自定义了case class之后可以很自由的获得每一行的信息

2.5.3 三者的互相转换

-- 1. RDD <=> DF
      a、RDD --> DF 
         "rdd.toDF("列名1"，"列名2"，...)"
      b、DF --> RDD
         "df.rdd"

-- 2. RDD  <=> DS
      a、 RDD => DS
           将rdd的数据转换为样例类的格式。
           "rdd.toDS"  
      b、 DS => RDD
           "ds.rdd"

-- 3. DF <=> DS
     a、DF => DS 
       "df.as[样例类]"，该样例类必须存在，而且df中的数据和样例类对应
     b、 DS => DF
       "ds.toDF"
       
-- 说明：
        a、通过DF转换得来的RDD的数据类型是ROW。
        b、通过DS转换得来的RDD的数据类型和DS的数据类型一致
        c、RDD:只关心数据本身
           DataFrame:关心数据的结构
           DataSet:关心数据类型

2.6 IDEA开发SparkSQL

2.6.1 添加依赖

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

2.6.2 代码实现

object SparkSQL01_Demo {
  def main(args: Array[String]): Unit = {
    //创建上下文环境配置对象
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")

    //创建SparkSession对象
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()

    //RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
    //spark不是包名，是上下文环境对象名
    import spark.implicits._

    //读取json文件 创建DataFrame  {"username": "lisi","age": 18}
    val df: DataFrame = spark.read.json("input/test.json")
    //df.show()

    //SQL风格语法
    df.createOrReplaceTempView("user")
    //spark.sql("select avg(age) from user").show

    //DSL风格语法
    //df.select("username","age").show()

    //*****RDD=>DataFrame=>DataSet*****
    //RDD
    val rdd1: RDD[(Int, String, Int)] = spark.sparkContext.makeRDD(List((1,"zhangsan",30),(2,"lisi",28),(3,"wangwu",20)))

    //DataFrame
    val df1: DataFrame = rdd1.toDF("id","name","age")
    //df1.show()

    //DateSet
    val ds1: Dataset[User] = df1.as[User]

    //ds1.show()
    //*****DataSet=>DataFrame=>RDD*****
    //DataFrame
    val df2: DataFrame = ds1.toDF()

    //RDD  返回的RDD类型为Row，里面提供的getXXX方法可以获取字段值，类似jdbc处理结果集，但是索引从0开始
    val rdd2: RDD[Row] = df2.rdd
    //rdd2.foreach(a=>println(a.getString(1)))
    //*****RDD=>DataSet*****
    rdd1.map{
      case (id,name,age)=>User(id,name,age)
    }.toDS()
    //*****DataSet=>=>RDD*****
    ds1.rdd
    //释放资源
    spark.stop()
  }
}
case class User(id:Int,name:String,age:Int)

2.7 用户自定义函数

-- 什么是UDF，什么是UDAF?
    a、UDF : UserDefinedFunction,用户自定义函数，可以类比为map方法，给你一个数据，然后对每条数据进行处理，如取出日期中年信息
    b、UDAF : UserDefinedAggregateorFunction，用户自定义"聚合"函数，可以类比sql中的count，sum、avg、max、min等方法

2.7.1 UDF

 1. 创建dataFrame
    2. 自定义和注册udf
         方法：spark.udf.register(形参)
         形参：有两个形参
              形参1：自定义函数的名字
              形参2：自定义函数的逻辑，是一个函数
       
     3. 使用自定义函数：
        3.1 使用自定义的udf函数用于sql语法
            a、创建临时或全局视图
            b、使用spark.sql(sql文)的方法调用自定的UDF方法
            
        3.2 使用于DSL语法
            a、获取注册udf的返回值
            b、使用df.select(调用函数(列名))，调用自定的udf函数

代码实现

  // 1. 创建dataFrame
    val frame: DataFrame = spark.read.json("input/people.json")

    /*
    2. 自定义和注册udf，并获取返回值
     方法：spark.udf.register(形参)
     形参：有两个形参
          形参1：自定义函数的名字
          形参2：自定义函数的逻辑，是一个函数
      */
     val udf: UserDefinedFunction = spark.udf.register("newName", (x :String) => "Name:"  + x )


    // 3.1、使用sql语法使用自定义udf函数
    //  a、创建临时或全局视图
    frame.createOrReplaceTempView("people")
    // 使用spark.sql(sql文)的方法调用自定的UDF方法
    spark.sql("select newName(name) from people").show()


    // 3.2、使用于DSL语法使用自定义udf函数
    frame.select(udf('name)).show()

2.7.2 UDAF

自定义的UDAF分为两种：弱类型自定义聚合函数、强类型自定义函数

   --1. 关于强类型自定义函数的说明：
    将数据转换为dataset，此时二维表中的一条数据封装为一个对象因为聚合函数是强类型，那么sql中没有类型的概念，所以SQL语法无法使用， 可以采用DSL语法方法进行访问将聚合函数转换为查询的列让DataSet访问

    --2、强类型自定义函数和弱类型自定义的区别：
       a、"使用范围区别"：
          弱类型自定义函数支持SQL语法和DSL语法，
          而强类型语言仅支持DSL语法，因为SQL没有类型的概念
       b、"声明自定义方法方式的差别"：
          "弱类型语言"：

             1）自定义函数，继承extends UserDefinedAggregatorFunction
             2) 重写8个方法：
                方法1：指明输入值的数据类型，不能是map类型，只能是anyval中的类型，特别注意数据的格式
                override def inputSchema: StructType = ???
                方法2：指明缓冲区的数据类型
                override def bufferSchema: StructType = ???
                方法3：指明输出值的数据类型
                override def dataType: DataType = ???
                方法4：数据的稳定性，设定true
                override def deterministic: Boolean = ???
                方法5：初始化缓冲区的值，对缓冲区
                override def initialize(buffer: MutableAggregationBuffer): Unit = ???
                方法6：更新缓冲区的数据，每来一条数据，将数据更新到缓冲区内
                override def update(buffer: MutableAggregationBuffer, input: Row): Unit = ???
                方法7：合并缓冲区中的数据，两两合并
                override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = ???
                方法8： 计算最后的结果
                override def evaluate(buffer: Row): Any = ???

          "强类型语言"：
                1）自定义函数，继承extends Aggregator
                2) 指明父类中的三个泛型
                   [-IN, BUF, OUT]
                    -IN: 指输入的数据类型，和java中 ? super IN相同，指传入的参数类型为IN类型或是IN的父类类型
                    BUF：指缓冲区的数据类型
                    OUT：指输出的数据类型

                    此时-IN：是指原表的一条数据，因为已经是被封装成了一个对象，所以是People
                    BUF：(对象的年龄，次数)，(Int,Int)
                    OUT：(平均年龄)，(Int)
                 3) 重写6个方法
                    方法1：初始化缓冲区的值
                    override def zero: avgAGE
                    方法2：将数据添加到缓冲区
                    override def reduce(b: avgAGE, a: People): avgAGE
                    方法3：合并缓冲区，两两进行合并
                    override def merge(b1: avgAGE, b2: avgAGE): avgAGE
                    方法4：计算最后的结果
                    override def finish(reduction: avgAGE): Long
                    方法5：固定写法，输入的编码器
                    override def bufferEncoder: Encoder[avgAGE] = Encoders.product
                    方法6：固定的写法，输入的解码器
                    override def outputEncoder: Encoder[Long] = Encoders.scalaLong


       c、 "使用上的区别"
             "弱语言"：
               使用在sql语法上：
               a、创建临时视图或者全局视图
               b、new 自定义方法
                  val uadf =  new MyUADF
               c、注册自定义方法
                   spark.udf.register(函数名，uadf)
               d、使用自定义方法
                   spark.sql("select 函数名(输入列) from  全局表").show()

               使用在DSL上：
               a、new 自定义方法
                  val uadf =  new MyUADF
               b、注册自定义方法，并获取表达式的返回值
                  val UADF: UserDefinedAggregateFunction = spark.udf.register(函数名，uadf)
               c、使用自定义方法
                  df.select(UADF(输入列)).show()

             "强类型"：只用在DSL的语法中
                    // 1. 创建df
                    val frame: DataFrame = spark.read.json("input/people.json")
                    // 2. 将df转换成ds
                    val ds: Dataset[People] = frame.as[People]
                    // 3. 创建自定义的UADF对象
                    val udaf = new MyUADF
                    // 4. 将对象转化为一个列
                    val column: TypedColumn[People, Long] = udaf.toColumn
                    // 5. 调用自定义函数
                    ds.select(column).show()


     --3、如何选择强类型和弱类型自定义方法
        当自定义的方法中，考虑数据作为一个对象进行传输时，需要考虑使用强类型自定义函数

█   弱类型自定聚合函数

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

    val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("udaf")
    val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
    import  spark.implicits._

    // 创建dataframe
    val rdd: RDD[User] = spark.sparkContext.makeRDD( 
        List(User("scala", 20), User("spark", 15), User("context", 30)))
    val frame: DataFrame = rdd.toDF()

    // a、注册自定义的uadf
    val udaf = new MyUDAF
    // b、注册自定义函数，并获取返回值。
     val UADF: UserDefinedAggregateFunction = spark.udf.register("myUADF",udaf)


    // 方式1：使用与mysql语法
    spark.sql("select myUADF(age) from User").show()
    // 方式2： 使用与DSL语法
    frame.select(UADF('age)).show()


  }

 // 1. 需求：求年龄的平均值
  class MyUDAF extends UserDefinedAggregateFunction{
    // 方法1：输入数据的类型,将年龄一个一个给传进来
    override def inputSchema: StructType = {
      StructType(Array(StructField("age",IntegerType)))
    }
    // 方法2：缓冲区的数据类型,数据的类型，需要记录年龄的总和，次数,可以存放到一个map集合中
    // 关于数据的格式有：LongType
    override def bufferSchema: StructType = {
      StructType(Array(
        StructField("totalAge",IntegerType),
        StructField("count",IntegerType)))
    }
    // 方法3：结果数据的数据类型,返回值的数据类型
    override def dataType: DataType = IntegerType
    // 方法4：数据是否安全
    override def deterministic: Boolean = true
    // 方法5：缓冲区初始化,就是给缓冲区的每个值设定一个初始值
    override def initialize(buffer: MutableAggregationBuffer): Unit = {
      buffer(0)=0
      buffer(1)=0
    }
    // 方法6：更新缓冲区
    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {

      buffer(0) = buffer.getInt(0) +  input.getInt(0)
      buffer(1) = buffer.getInt(1) + 1

    }
    // 方法7：因为是多executor执行，合并所有分区的数据
    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
      buffer1(0) = buffer1.getInt(0) +  buffer2.getInt(0)
      buffer1(1) = buffer1.getInt(1) + buffer2.getInt(1)

    }
    // 方法8：计算最后的结果
    override def evaluate(buffer: Row): Any = {
      buffer.getInt(0) / buffer.getInt(1)
    }
  }
  case class User (name:String,age :Int)
}

█   解析：

   buffer(0) = buffer.getInt(0) +  input.getInt(0)
   buffer(0): 理解为设置索引位置为0的值
   buffer.getInt(0) ：获取索引为0位置的数据

█   强类型自定义的函数

object SparkSQL5_UADF {
  def main(args: Array[String]): Unit = {
    val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("UADF")
    val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
    import  spark.implicits._
    // 1. 创建df
    val frame: DataFrame = spark.read.json("input/people.json")
    // 2. 将df转换成ds
    val ds: Dataset[People] = frame.as[People]
    // 3. 创建自定义的UADF对象
    val udaf = new MyUADF
    // 4. 将对象转化为一个列
    val column: TypedColumn[People, Long] = udaf.toColumn
   // 5. 调用自定义函数
    ds.select(column).show()
  }

  /*
  需求：统计平均年龄
  1. 自定义类，继承与extends Aggregator
  2. 指定Aggregator的泛型，分别是：
    [-IN, BUF, OUT]
    -IN: 指输入的数据类型，和java中 ? super IN相同，指传入的参数类型为IN类型或是IN的父类类型
    BUF：指缓冲区的数据类型
    OUT：指输出的数据类型

    此时-IN：是指原表的一条数据，因为已经是被封装成了一个对象，所以是People
    BUF：(对象的年龄，次数)，(Int,Int)
    OUT：(平均年龄)，(Int)

   */
  case class People(var name:String, var age : Long)

  case class avgAGE (var sumAge: Long,var count:Long)

  class MyUADF extends  Aggregator[People,avgAGE,Long]{
      //  方法1：初始化缓冲区的值
      override def zero: avgAGE = {
      avgAGE(0L,0L)
    }
   // 方法2：将数据添加到缓冲区
    override def reduce(b: avgAGE, a: People): avgAGE = {
      b.sumAge += a.age
      b.count +=  1
      b
    }
    //  方法3：合并缓冲区，两两进行合并
    override def merge(b1: avgAGE, b2: avgAGE): avgAGE = {
      b1.count = b1.count + b2.count
      b1.sumAge =b1.sumAge + b2.sumAge
      b1
    }
    //  方法4：计算最后的结果
    override def finish(reduction: avgAGE): Long = {
      reduction.sumAge / reduction.count

    }
   //  方法5：固定写法，输入的编码器
    override def bufferEncoder: Encoder[avgAGE] = Encoders.product
  //  方法6：固定的写法，输入的解码器
    override def outputEncoder: Encoder[Long] = Encoders.scalaLong
  }

}

2.8 数据的加载和保存

数据加载和保存的方式有很多种方式，根据不同的需求，主要有以下几种：
--方式1：通用的数据加载和保存方式
--方式2：parquet
--方式3：JSON
--方式4：CSV
--方式5：Mysql
--方式6：Hive

2.8.1 通用的数据加载和保存方式

--什么是通用的方式？
  指使用相同的API，根据不同的参数，读取和保存不同格式的数据。
  
-- 说明：
   SparkSQL默认读取和保存的文件格式是parquet。

 

2.8.1.1 读取数据

--1. 通用的数据加载方式[默认为parquet格式]
    方式：spark.read.load(pat:String)
--2. 读取不同格式的数据，可以对不同的数据格式进行设定
    方式：spark.read.format("数据格式":string).load(path:String)

    说明：
    a、format("数据格式":string):数据格式可以是："csv"、"mysql"、"json"、"jdbc"、"textFile"、"orc"
    b、数据的路径

--3. 还可以加option，导入一些参数
     方式：spark.read.format("…")[.option("…")].load("…")
     在"jdbc"格式下需要传入JDBC相应参数，url、user、password和dbtable
我们前面都是使用read API 先把文件加载到 DataFrame然后再查询，其实，我们也可以直接在文件上进行查询:  "文件格式.`文件路径`"

// 情况1：直接load
spark.read.load("input/users.parquet").show()
// 情况2：指定文件格式
spark.read.format("json").load("input/people.json").show()
// 情况3：使用文件格式.`文件路径`方式
spark.sql("select * from json.`input/people.json`").show()

-- 因为在实际生产情况下，json文件的数据格式的场景是非常多的，Spark对于json文件格式要求如下：
    1. JSON文件的格式要求整个文件满足JSON的语法规则
    2. Spark读取文件默认是以行为单位进行读取
    3. Spark读取JSON文件时，要求文件中的每一行满足JSON格式
    4. 如果文件格式不正确，那么不会发生错误，而是解析结果不是我们预期的结果。

2.8.1.2 保存数据

    1. 保存的方法：
       spark.write.save(path:String)
    2. 默认的保存格式为parquet格式
    3. 如果想要指定保存的格式,增加format方法，在format的方法中指定形参数据保存的格式
       frame.write.format("json")save("output")
    4. 如果保存的路径已经存在，那么会报错：output already exists
    5. 如果文件路径已经存在时不能创建，那么在实际的生产的情况下，岂不是会生成很多小文件？
       解决方案：使用mode，指定模式
       方法：mode(形参)
       形参：saveMode: SaveMode,数据类型是一个枚举类
       枚举类的对象有：
       a、Append:追加，如果原文件目录或表存在，那么在原路径下进行生产一个新的文件
       b、Overwrite:重写，如果原文件目录或表存在，那么将原路径下的文件进行直接覆盖
       c、ErrorIfExists：默认值，如果原文件目录存在，则报错
       d、Ignore：如果原文件目录或表存在，那么不报错，但是也不保存数据

frame.write.mode("Overwrite").format("json").save("output")

2.8.2 Parquet

Spark SQL的默认数据源为Parquet格式。Parquet是一种能够有效存储嵌套数据的列式存储格式。

数据源为Parquet文件时，Spark SQL可以方便的执行所有的操作，不需要使用format。修改配置项spark.sql.sources.default，可修改默认数据源格式。

加载数据
scala> val df = spark.read.load("examples/src/main/resources/users.parquet")
scala> df.show

保存数据
scala> var df = spark.read.json("/opt/module/data/input/people.json")
//保存为parquet格式
scala> df.write.mode("append").save("/opt/module/data/output")

2.8.3 JSON

Spark SQL 能够自动推测JSON数据集的结构，并将它加载为一个Dataset[Row]. 可以通过SparkSession.read.json()去加载JSON 文件。

注意：Spark读取的JSON文件不是传统的JSON文件，每一行都应该是一个JSON串。格式如下：

{"name":"Michael"}
{"name":"Andy"， "age":30}
[{"name":"Justin"， "age":19},{"name":"Justin"， "age":19}]

1）导入隐式转换
import spark.implicits._

2）加载JSON文件
val path = "/opt/module/spark-local/people.json"
val peopleDF = spark.read.json(path)

3）创建临时表
peopleDF.createOrReplaceTempView("people")

4）数据查询
val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19")
teenagerNamesDF.show()

+------+
|  name|
+------+
|Justin|
+------+

2.8.4 CSV

//读取文件
    val frame: DataFrame = spark.read.format("csv")
      .option("sep", ";")
      .option("inferSchema", "true")
      .option("header", "true")
      .load("input/people.csv")
    frame.show()
 // 保存数据
    frame.write.mode("Append").format("json").save("output")

2.8.5 MySQL

 方式1：使用option的方式进行参数配置

 //从MYsql中读取数据
    val frame: DataFrame = spark.read.format("jdbc")
      .option("url", "jdbc:mysql://hadoop105:3306/mysql")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .option("dbtable", "db")
      .load()
   // 保存到数据库中
    frame.write.format("jdbc")
      .option("url", "jdbc:mysql://hadoop105:3306/mysql")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("user", "root")
      .option("password", "123456")
      .option("dbtable", "db1")
      .mode("Append").save()

方式2：使用jdbc的方法


    // 创建连接
    val props: Properties = new Properties()
    props.setProperty("user", "root")
    props.setProperty("password", "123456")
    val df: DataFrame = spark.read
                        .jdbc("jdbc:mysql://hadoop105:3306/mysql", "db", props)

    df.show()

    // 将数据保存到Mysql中
    val rdd: RDD[Int] = spark.sparkContext.makeRDD(List(1,2,3,6))
    val frame: DataFrame = rdd.toDF("id")
    frame.write.mode("Append").jdbc("jdbc:mysql://hadoop105:3306/mysql", "db2", props)

2.8.6 Hive

Apache Hive 是 Hadoop 上的 SQL 引擎，Spark SQL编译时可以包含 Hive 支持，也可以不包含。包含 Hive 支持的 Spark SQL 可以支持 Hive 表访问、UDF (用户自定义函数)以及 Hive 查询语言(HiveQL/HQL)等。需要强调的一点是，如果要在 Spark SQL 中包含Hive 的库，并不需要事先安装 Hive。一般来说，最好还是在编译Spark SQL时引入Hive支持，这样就可以使用这些特性了。如果你下载的是二进制版本的 Spark，它应该已经在编译时添加了 Hive 支持。

若要把 Spark SQL 连接到一个部署好的 Hive 上，你必须把 hive-site.xml 复制到 Spark的配置文件目录中($SPARK_HOME/conf)。即使没有部署好 Hive，Spark SQL 也可以运行。 需要注意的是，如果你没有部署好Hive，Spark SQL 会在当前的工作目录中创建出自己的 Hive 元数据仓库，叫作 metastore_db。此外，如果你尝试使用 HiveQL 中的 CREATE TABLE (并非 CREATE EXTERNAL TABLE)语句来创建表，这些表会被放在你默认的文件系统中的 /user/hive/warehouse 目录中(如果你的 classpath 中有配好的 hdfs-site.xml，默认的文件系统就是 HDFS，否则就是本地文件系统)。

spark-shell默认是Hive支持的；代码中是默认不支持的，需要手动指定（加一个参数即可）。

1.   内嵌的HIVE

如果使用 Spark 内嵌的 Hive, 则什么都不用做, 直接使用即可.
Hive 的元数据存储在 derby 中, 默认仓库地址:$SPARK_HOME/spark-warehouse

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

+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
+--------+---------+-----------+

scala> spark.sql("create table aa(id int)")
scala> spark.sql("show tables").show
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
| default|       aa|      false|
+--------+---------+-----------+

向表加载本地数据
scala> spark.sql("load data local inpath 'input/ids.txt' into table aa")

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

+---+
| id|
+---+
|  1|
|  2|
|  3|
|  4|
+---+

在实际使用中, 几乎没有任何人会使用内置的 Hive

2.     外部的HIVE

如果想连接外部已经部署好的Hive，需要通过以下几个步骤：

Spark要接管Hive需要把hive-site.xml拷贝到conf/目录下
把Mysql的驱动copy到jars/目录下
如果访问不到hdfs，则需要把core-site.xml和hdfs-site.xml拷贝到conf/目录下
重启spark-shell
scala> spark.sql("show tables").show

20/04/25 22:05:14 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException

+--------+--------------------+-----------+
|database|           tableName|isTemporary|
+--------+--------------------+-----------+
| default|                 emp|      false|
| default|hive_hbase_emp_table|      false|
| default| relevance_hbase_emp|      false|
| default|          staff_hive|      false|
| default|                 ttt|      false|
| default|   user_visit_action|      false|
+--------+--------------------+-----------+

 

3.    运行Spark SQL CLI

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

bin/spark-sql

4.     运行Spark beeline

Spark Thrift Server是Spark社区基于HiveServer2实现的一个Thrift服务。旨在无缝兼容HiveServer2。因为Spark Thrift Server的接口和协议都和HiveServer2完全一致，因此我们部署好Spark Thrift Server后，可以直接使用hive的beeline访问Spark Thrift Server执行相关语句。Spark Thrift Server的目的也只是取代HiveServer2，因此它依旧可以和Hive Metastore进行交互，获取到hive的元数据。

如果想连接Thrift Server，需要通过以下几个步骤：

Spark要接管Hive需要把hive-site.xml拷贝到conf/目录下
把Mysql的驱动copy到jars/目录下
如果访问不到hdfs，则需要把core-site.xml和hdfs-site.xml拷贝到conf/目录下
启动Thrift Server
sbin/start-thriftserver.sh

使用beeline连接Thrift Server
bin/beeline -u jdbc:hive2://hadoop102:10000 -n root

5.   代码操作Hive

a、导入依赖
<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-hive_2.12</artifactId>
    <version>3.0.0</version>
</dependency>

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>1.2.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.27</version>
</dependency>

b、将hive-site.xml文件拷贝到项目的resources目录中，代码实现
//创建SparkSession
val spark: SparkSession = SparkSession
  .builder()
  .enableHiveSupport()   《===》
  .master("local[*]")
  .appName("sql")
  .getOrCreate()



注意：在开发工具中创建数据库默认是在本地仓库，通过参数修改数据库仓库的地址: config("spark.sql.warehouse.dir", "hdfs://hadoop102:8020/user/hive/warehouse")

如果在执行操作时，出现如下错误：

可以代码最前面增加如下代码解决：

System.setProperty("HADOOP_USER_NAME", "root")

此处的root改为你们自己的hadoop用户名称

三、SparkSQL项目实战

3.1 数据准备

-- 我们这次 Spark-sql 操作中所有的数据均来自 Hive，首先在 Hive 中创建表,，并导入数据。一共有3张表： 1张用户行为表，1张城市表，1 张产品表


  //修改hadoop的用户
   System.setProperty("HADOOP_USER_NAME", "root")

    val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("pro")
    val spark: SparkSession = SparkSession.builder().enableHiveSupport().config(sparkConf).getOrCreate()
   
    spark.sql("use  spark_sql")
    spark.sql("show databases").show()

    spark.sql(
      """
        |CREATE TABLE `user_visit_action`(
        |  `date` string,
        |  `user_id` bigint,
        |  `session_id` string,
        |  `page_id` bigint,
        |  `action_time` string,
        |  `search_keyword` string,
        |  `click_category_id` bigint,
        |  `click_product_id` bigint,
        |  `order_category_ids` string,
        |  `order_product_ids` string,
        |  `pay_category_ids` string,
        |  `pay_product_ids` string,
        |  `city_id` bigint)
        |row format delimited fields terminated by '\t'
      """.stripMargin)

    spark.sql("load data local inpath 'input/user_visit_action.txt' into table user_visit_action")

    
    spark.sql(
      """
        |CREATE TABLE `product_info`(
        |  `product_id` bigint,
        |  `product_name` string,
        |  `extend_info` string)
        |row format delimited fields terminated by '\t'
      """.stripMargin)

    spark.sql("load data local inpath 'input/product_info.txt' into table product_info")

    spark.sql(
      """
        |CREATE TABLE `city_info`(
        |  `city_id` bigint,
        |  `city_name` string,
        |  `area` string)
        |row format delimited fields terminated by '\t'
      """.stripMargin)

    spark.sql("load data local inpath 'input/city_info.txt' into table city_info")

    spark.stop()

3.2 需求 ： 各区域热门商品 Top3

--这里的热门商品是从点击量的维度来看的，计算各个区域前三大热门商品，并备注上每个商品在主要城市中的分布比例，超过两个城市用其他显示

*地区*	*商品名称*	*点击次数*	*城市备注*
*华北*	商品A	100000	北京21.2%，天津13.2%，其他65.6%
*华北*	商品P	80200	北京63.0%，太原10%，其他27.0%
*华北*	商品M	40000	北京63.0%，太原10%，其他27.0%
*东北*	商品J	92000	大连28%，辽宁17.0%，其他 55.0%

3.2.2 需求分析

	查询出来所有的点击记录，并与 city_info 表连接，得到每个城市所在的地区，与 Product_info 表连接得到产品名称
	按照地区和商品 id 分组，统计出每个商品在每个地区的总点击次数
	每个地区内按照点击次数降序排列
	只取前三名
	城市备注需要自定义 UDAF 函数

3.2.3 功能实现

连接三张表的数据，获取完整的数据（只有点击）
将数据根据地区，商品名称分组
统计商品点击次数总和,取Top3
实现自定义聚合函数显示备注

3.2.3 代码实现

object AreaTop3Product {
  def main(args: Array[String]): Unit = {
    //1.
    val sparkConf: SparkConf = new SparkConf().setAppName("AreaTop3Product").setMaster("local[*]")

    //2.创建SparkConf
    val spark: SparkSession = SparkSession
      .builder()
      .config(sparkConf)
      .enableHiveSupport()
      .getOrCreate()

    import spark.implicits._
    //注册UDFA函数
    spark.udf.register("cityRatio", functions.udaf(new AreaTop3ProductUDAF))

    //3.读取并过滤hive数据
    spark.sql(
      """
        |select
        |     ci.area,
        |     pi.product_name,
        |     ci.city_name
        |from (select
        |            *
        |      from user_visit_action
        |      where click_product_id != '-1'
        |      ) uv
        |join product_info pi
        |on  uv.click_product_id = pi.product_id
        |join city_info ci
        |on uv.city_id = ci.city_id
        |""".stripMargin
    ).createTempView("tempAreaProduct")

    //4.计算各个大区中对于某个商品的点击总次数
    spark.sql(
      """
        |select
        |      area,
        |      product_name,
        |      count(*) ct,
        |      cityRatio(city_name) cityratio
        |from
        |     tempAreaProduct
        |group by area,product_name
        |""".stripMargin
    ).createTempView("tmpAreaProductCount")

    //5.计算各个大区中商店点击次数排行
    spark.sql(
      """
        |select
        |       area,
        |       product_name,
        |       ct,
        |       cityratio,
        |       rank() over(partition by area order by ct desc) rk
        |from
        |     tmpAreaProductCount
        |""".stripMargin
    ).createTempView("tmpAreaProductCountRank")

    //6.取各个大区中商品点击次数前三名的数据
    spark.sql(
      """
        |select
        |       area,
        |       product_name,
        |       ct,
        |       cityratio
        |from
        |    tmpAreaProductCountRank
        |where rk <= 3
        |""".stripMargin
    ).show(100, false)

    //7.关闭资源
    spark.stop()
  }
}

class AreaTop3ProductUDAF extends Aggregator[String, mutable.HashMap[String, Int], String] {
  //初始化
  override def zero: mutable.HashMap[String, Int] = new mutable.HashMap[String, Int]()

  //内聚合
  override def reduce(b: mutable.HashMap[String, Int], a: String): mutable.HashMap[String, Int] = {
    b(a) = b.getOrElseUpdate(a, 0) + 1
    b
  }

  //区间聚合
  override def merge(b1: mutable.HashMap[String, Int], b2: mutable.HashMap[String, Int]): mutable.HashMap[String, Int] = {
    b2.foreach {
      case (cityName, count) =>
        b1(cityName) = b1.getOrElse(cityName, 0) + count
    }
    b1
  }

  //最终计算
  override def finish(reduction: mutable.HashMap[String, Int]): String = {
    //a.获取当前大区当前商品点击总数
    val totalCount: Int = reduction.values.sum
    //b.获取点击次数为前两名的城市
    val top2CityCount: List[(String, Int)] = reduction.toList.sortWith(_._2 > _._2).take(2)

    //c.定义其他占比
    var otherRatio: Double = 100D

    //d.取前两名城市的占比
    val ratios: List[CityRatio] = top2CityCount.map { case (cityName, count) =>
      val ratio: Double = Math.round(count * 1000D / totalCount) / 10D
      otherRatio -= ratio
      CityRatio(cityName, ratio)
    }

    //e.将其他占比放入集合
    val result: List[CityRatio] = ratios :+ CityRatio("其他", Math.round(otherRatio * 10D) / 10D)

    //f.返回值
    result.mkString(",")

  }

  override def bufferEncoder: Encoder[mutable.HashMap[String, Int]] = Encoders.kryo(classOf[mutable.HashMap[String, Int]])

  override def outputEncoder: Encoder[String] = Encoders.STRING
}

case class CityRatio(cityName: String, ratio: Double) {
  override def toString: String = {
    s"$cityName$ratio%"
  }
}