spark sql length 函数

第1章 SparkSQL概述

1.1 SparkSQL是什么 

1.2 Hive and SparkSQL

的前身是Shark，给熟悉RDBMS但又不理解MapReduce的技术人员提供快速上手的工具。

　　Hive是早期唯一运行在Hadoop上的SQL-on-Hadoop工具。但是MapReduce计算过程中大量的中间磁盘落地过程消耗了大量的I/O，降低的运行效率，为了提高SQL-on-Hadoop的效率，大量的SQL-on-Hadoop工具开始产生，其中表现较为突出的是：Drill、Impala、Shark。

　　其中Shark是伯克利实验室Spark生态环境的组件之一，是基于Hive所开发的工具，它修改了下图所示的右下角的内存管理、物理计划、执行三个模块，并使之能运行在Spark引擎上。

　　Shark的出现使得SQL-on-Hadoop的性能比Hive有了10-100倍的

　　1)SparkSQL既支持SQL处理数据，又支持代码的方法（RDD）处理数据，更加灵活

　　2）对接多种数据源，数据可以在MySQL，也可以在redis、Hbase、ES、Hive

　　3）SparkSQL无缝支持HQL，若使用SparkSQL处理的数据是Hive管理起来的数据，则称为Spark On Hive；若使用的是SparkSQL来处理MySQL管理的数据，则称为Spark On MySQL

　　4）HQL编程方式单一，处理的数据默认都是存储在HDFS中

　　HiveOnSpark与SparkSQL的相同点与不同点：

　　　　相同点：计算的数据都由Hive进行管理，使用Hive建表，将数据导入表中

　　　　不同点：

　　　　　　SparkSQL：

　　　　　　　　1）可以写处理数据，也可以写代码处理数据

　　　　　　　　2）只需要按照Spark，使用的是 SPARK_HOME/bin/spark-sql | spark-shell | spark-submit

　　　　　　　　3）程序运行效率不同（sparksql更优，官方声称，是HiveOnSpark的十倍）：HQL-->Spark的SQL解析器解析-->Spark的SQL优化器优化-->Spark App

　　　　　　HiveOnSpark：

　　　　　　　　1）只能写HQL

　　　　　　　　2）本质上使用的是Hive，安装的是Hive，使用HIVE_HOME/bin/hive（cli），hiveserver2

　　　　　　　　3）HQL-->Hive的SQL解析器解析-->Hive的SQL优化器优化-->Spark App

　　但是，随着Spark的发展，对于野心勃勃的Spark团队来说，Shark对于Hive有太多依赖（如采用Hive的语法解析器、查询优化器等等），制约了Spark的One Stack Rule Them All的既定方针，制约了Spark各个组件的相互集成，所以提出了SparkSQL项目。SparkSQL抛弃原有Shark的代码，汲取了Shark的一些优点，如内存列存储（In-Memory Columnar Storage）、Hive兼容性等，重新开发了SparkSQL代码；由于摆脱了对Hive的依赖性，SparkSQL无论在数据兼容、性能优化、组件扩展方面都得到了极大的方便，真可谓“退一步，海阔天空”。

　　数据兼容方面，SparkSQL不但兼容Hive，还可以从RDD、parquet文件、JSON文件中获取数据，未来版本甚至支持获取RDBMS数据以及cassandra等NOSQL数据；
　　性能优化方面，除了采取In-Memory Columnar Storage、byte-code generation等优化技术外、将会引进Cost Model对查询进行动态评估、获取最佳物理计划等等；
　　组件扩展方面，无论是SQL的语法解析器、分析器还是优化器都可以重新定义，进行扩展；

　　2014年6月1日Shark项目和SparkSQL项目的主持人Reynold Xin宣布：停止对Shark的开发，团队将所有资源放SparkSQL项目上，至此，Shark的发展画上了句话。

　　但也因此发展出两个支线：SparkSQL和Hive on Spark

　　其中SparkSQL作为Spark生态的一员继续发展，而不再受限于Hive，只是兼容Hive；而Hive on Spark是一个Hive的发展计划，该计划将Spark作为Hive的底层引擎之一，也就是说，Hive将不再受限于一个引擎，可以采用Map-Reduce、Tez、Spark等引擎。

　　对于开发人员来讲，SparkSQL可以简化RDD的开发，提高开发效率，且执行效率非常快，所以实际工作中，基本上采用的就是Spark SQL。Spark SQL为了简化RDD的开发，提高开发效率，提供了两个编程抽象：DataFrame 和 DataSet

1.3 SparkSQL特点

1.3.1 易整合

　　无缝的整合了 SQL 查询和 Spark 编程

1.3.2 统一的数据访问

　　使用相同的方式连接不同的数据源

1.3.3 兼容Hive

　　在已有的仓库上直接运行 SQL 或者 HiveQL

1.3.4 标准数据连接

　　通过 JDBC 或者 ODBC 来连接

1.4  DataFrame是什么（DF = RDD(数据) + schame(元数据) ，1.3版本推出，是弱类型）

　　在Spark中，DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库中的二维表格。DataFrame与RDD的主要区别在于，前者带有schema元信息，即DataFrame所表示的二维表数据集的每一列都带有名称和类型。这使得Spark SQL得以洞察更多的结构信息，从而对藏于DataFrame背后的数据源以及作用于DataFrame之上的变换进行了针对性的优化，最终达到大幅提升运行时效率的目标。反观RDD，由于无从得知所存数据元素的具体内部结构，Spark Core只能在stage层面进行简单、通用的流水线优化。
　　同时，与Hive类似，DataFrame也支持嵌套数据类型（struct、array和map）。从 API 易用性的角度上看，DataFrame API提供的是一套高层的关系操作，比函数式的RDD API 要更加友好，门槛更低，下图可以直观地体现了DataFrame和RDD的区别：

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

users.join(events,users("id") === events("uid")).filter(events("date") > "2021-06-06")

　　为了说明查询优化，我们来看上图展示的人口数据分析的示例。图中构造了两个DataFrame，将它们join之后又做了一次filter操作。如果原封不动地执行这个执行计划，最终的执行效率是不高的。因为join是一个代价较大的操作，也可能会产生一个较大的数据集。如果我们能将filter下推到 join下方，先对DataFrame进行过滤，再join过滤后的较小的结果集，便可以有效缩短执行时间。而Spark SQL的查询优化器正是这样做的。简而言之，逻辑查询计划优化就是一个利用基于关系代数的等价变换，将高成本的操作替换为低成本操作的过程。 

1.5 DataSet是什么（Dataframe= DataSet[Row]，是强类型，Row是一行数据）

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

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

第2章  SparkSQL核心编程

　　学习如何使用 Spark SQL所提供的 DataFrame和DataSet模型进行编程.，以及了解它们之间的关系和转换，关于具体的SQL书写不是我们的重点

2.1 新的起点

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

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

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

2.2 DataFrame

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

2.2.1 创建DataFrame

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

从Spark数据源进行创建

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

　　　　（2）在HDFS中的/spark/input目录中创建user.json文件

{"username":"zhangsan","age":20}

　　　　（3）读取json文件创建DataFrame

val df = spark.read.json("/spark/input/user.json");

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

　　　　（4）展示结果

df.show();

从RDD进行转换（在后续章节中讨论）

从Hive Table进行查询返回（在后续章节中讨论）

2.2.2 SQL语法

语法风格是指我们查询数据的时候使用SQL语句来查询，这种风格的查询必须要有临时视图或者全局视图来辅助

读取JSON文件创建DataFrame

val df = spark.read.json("/spark/input/user.json");

对DataFrame创建一个临时表

df.createOrReplaceTempView("people");

通过SQL语句实现查询全表

val dfSQL = spark.sql("select * from people");

　　4) 结果展示（普通临时表是Session范围内的，如果想应用范围内有效，可以使用全局临时表。使用全局临时表时需要全路径访问，如：global_temp.people）

dfSQL.show();

对于DataFrame创建一个全局表

//数据准备
{"username": "zhangsan","age": 25}
{"username": "lisi","age": 18}
{"username": "wangwu","age": 45}

df.createGlobalTempView("people")

通过SQL语句实现查询全表

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

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

2.2.3 DSL语法

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

创建一个DataFrame

val df = spark.read.json("/spark/input/user.json");

查看DataFrame的Schema信息

df.printSchema

只查看"username"列数据，

df.select("username").show()

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

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

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

df.select('username,'age + 1 as "newage").show()

age"大于"30"的数据（往user.json文件添加数据）

{"username":"lisi","age":25}
{"username":"wangwu","age":45}

//重新读取文件
val df = spark.read.json("/spark/input/user.json");

df.filter($"age" > 30).show()

age"分组，查看数据条数

df.groupBy("age").count.show()

2.2.4 RDD转换为DataFrame

　　在IDEA中开发程序时，如果需要RDD与DF或者DS之间互相操作，那么需要引入 import spark.implicits._  ，这里的spark不是Scala中的包名，而是创建的sparkSession对象的变量名称，所以必须先创建SparkSession对象再导入。这里的spark对象不能使用var声明，因为Scala只支持val修饰的对象的引入

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

val rdd = sc.textFile("/spark/input/1.txt");

rdd.toDF("name").show()

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

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

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

2.2.5 DataFrame转换为RDD

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

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

val rdd = df.rdd

val array = rdd.collect

得到的RDD存储类型为Row

array(0)

array(0)(0)

array(0).getAs[String]("name")

2.3 DataSet

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

2.3.1 创建DataSet

使用样例类序列创建DataSet

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

val caseClassDS = Seq(Person("zhangsan",2)).toDS()

caseClassDS.show

使用基本类型的序列创建DataSet（在实际使用的时候，很少用到把序列转换成DataSet，更多的是通过RDD来得到DataSet）

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

ds.show

2.3.2 RDD转换为DataSet

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

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

sc.makeRDD(List(("zhangsan",30), ("lisi",49))).map(t=>User(t._1, t._2)).toDS

2.3.3 DataSet转换为RDD

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

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

val result = sc.makeRDD(List(("zhangsan",30), ("lisi",49))).map(t=>User(t._1, t._2)).toDS

val rdd = result.rdd

rdd.collect

2.4 DataFrame和DataSet转换

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

DataFrame转换为DataSet

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

val df = sc.makeRDD(List(("zhangsan",30), ("lisi",49))).toDF("name","age")

val ds = df.as[User]

　　2）DataSet转换为DataFrame

val df = ds.toDF

2.5 RDD、DataFrame、DataSet三者的关系

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

=> RDD

=> DataFrame

=> Dataset

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

2.5.1 三者的共性

、DataFrame、DataSet全都是spark平台下的分布式弹性数据集，为处理超大型数据提供便利;

　　2）三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action如foreach时，三者才会开始遍历运算;

　　3）三者有许多共同的函数，如filter，排序等;

　　4）在对DataFrame和Dataset进行操作许多操作都需要这个包:import spark.implicits._（在创建好SparkSession对象后尽量直接导入）

　　5）三者都会根据 Spark 的内存情况自动缓存运算，这样即使数据量很大，也不用担心会内存溢出

　　6）三者都有partition的概念

和DataSet均可使用模式匹配获取各个字段的值和类型

2.5.2 三者的区别

　　1) RDD

一般和spark mlib同时使用

不支持sparksql操作

　　2) DataFrame

与RDD和Dataset不同，DataFrame每一行的类型固定为Row，每一列的值没法直接访问，只有通过解析才能获取各个字段的值

与DataSet一般不与 spark mlib 同时使用

与DataSet均支持 SparkSQL 的操作，比如select，groupby之类，还能注册临时表/视窗，进行 sql 语句操作

与DataSet支持一些特别方便的保存方式，比如保存成csv，可以带上表头，这样每一列的字段名一目了然

　　3) DataSet

和DataFrame拥有完全相同的成员函数，区别只是每一行的数据类型不同。 DataFrame其实就是DataSet的一个特例  type DataFrame = Dataset[Row]

也可以叫Dataset[Row],每一行的类型是Row，不解析，每一行究竟有哪些字段，各个字段又是什么类型都无从得知，只能用上面提到的getAS方法或者共性中的第七条提到的模式匹配拿出特定字段。而Dataset中，每一行是什么类型是不一定的，在自定义了case class之后可以很自由的获得每一行的信息

2.5.3 三者的互相转换

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 代码实现

package com.yuange.spark.day07

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}

case class User(id:Int,name:String,age:Int)

/**
 * @作者：袁哥
 * @时间：2021/6/7 19:19
 */
object TestSparkSQL {
  def main(args: Array[String]): Unit = {
    //创建上下文，配置环境变量
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TestSparkSQL")
    //创建SparkSession对象
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    //RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
    //spark不是包名，是上下文环境对象名
    import spark.implicits._

    //读取json文件 创建DataFrame
    //{"username": "zhangsan","age": 25}
    //{"username": "lisi","age": 18}
    //{"username": "wangwu","age": 45}
    val df: DataFrame = spark.read.json("datas/testSparkSQL.json")
    df.show()

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

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

    //创建一个RDD
    val rdd2: RDD[(Int,String,Int)] = spark.sparkContext.makeRDD(List((1,"zhangsan",30),(2,"lisi",28),(3,"wangwu",20)))
    //rdd-->DataFrame
    val df2: DataFrame = rdd2.toDF("id","name","age")
    df2.show()
    //DataFrame-->DataSet
    val ds2: Dataset[User] = df2.as[User]
    ds2.show()
    //DataSet-->DataFrame
    val df3: DataFrame = ds2.toDF()
    df3.show()
    //DataFrame-->rdd：返回的RDD类型为Row，里面提供的getXXX方法可以获取字段值，类似jdbc处理结果集，但是索引从0开始
    val rdd3: RDD[Row] = df3.rdd
    rdd3.foreach(println)

    //rdd-->DataSet
    rdd2.map{
      case (id,name,age) => User(id,name,age)
    }.toDS().show()
    //DataSet-->rdd
    ds2.rdd.foreach(println)
  }
}

2.7 用户自定义函数

可以通过spark.udf功能添加自定义函数，实现自定义功能。

2.7.1 UDF

创建DataFrame

val df = spark.read.json("/spark/input/user.json");

注册UDF

spark.udf.register("addName",(x: String) => "Name:" + x)

　　3) 创建临时表

df.createOrReplaceTempView("people")

应用UDF

spark.sql("Select addName(username),age from people").show()

2.7.2 UDAF

　　强类型的Dataset和弱类型的DataFrame都提供了相关的聚合函数， 如 count()，countDistinct()，avg()，max()，min()。除此之外，用户可以设定自己的自定义聚合函数。通过继承UserDefinedAggregateFunction来实现用户自定义聚合函数。

　　需求（一个需求可以采用很多种不同的方法实现需求）：计算平均工资

　　1) 实现方式 - RDD

package com.yuange.spark.day07

import org.apache.spark.{SparkConf, SparkContext}

/**
 * @作者：袁哥
 * @时间：2021/6/7 20:25
 */
object TestSparkSQLTwo {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setAppName("app").setMaster("local[*]")
    val sc: SparkContext = new SparkContext(conf)
    val res: (Int, Int) = sc.makeRDD(List(("zhangsan", 20), ("lisi", 30), ("wangw", 40))).map {
      case (name, age) => {
        (age, 1)
      }
    }.reduce {
      (t1, t2) => {
        (t1._1 + t2._1, t1._2 + t2._2)
      }
    }
    println(res._1/res._2)
    // 关闭连接
    sc.stop()
  }
}

　　2) 实现方式 - 累加器

package com.yuange.spark.day07

import org.apache.spark.util.AccumulatorV2

/**
 * @作者：袁哥
 * @时间：2021/6/7 20:27
 */
class MyAccumulatorV2 extends AccumulatorV2[Int,Int]{
  var sum: Int = 0
  var count: Int = 0

  override def isZero: Boolean = {
    return sum == 0 && count == 0
  }

  override def copy(): AccumulatorV2[Int, Int] = {
    val newMyAccumulatorV2 = new MyAccumulatorV2
    newMyAccumulatorV2.sum = this.sum
    newMyAccumulatorV2.count = this.count
    newMyAccumulatorV2
  }

  override def reset(): Unit = {
    sum = 0
    count = 0
  }

  override def add(v: Int): Unit = {
    sum += v
    count += 1
  }

  override def merge(other: AccumulatorV2[Int, Int]): Unit = {
    other match {
      case o: MyAccumulatorV2 => {
        sum += o.sum
        count += o.count
      }
      case _ => {}
    }
  }

  override def value: Int = sum/count
}

　　3) 实现方式 - UDAF - 弱类型

package com.yuange.spark.day07

import org.apache.spark
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DoubleType, IntegerType, LongType, StructField, StructType}

/**
 * @作者：袁哥
 * @时间：2021/6/7 20:39
 */
/*
定义类继承UserDefinedAggregateFunction，并重写其中方法
*/
class MyAveragUDAF extends UserDefinedAggregateFunction {

  // 聚合函数输入参数的数据类型
  def inputSchema: StructType = StructType(Array(StructField("age",IntegerType)))

  // 聚合函数缓冲区中值的数据类型(age,count)
  def bufferSchema: StructType = {
    StructType(Array(StructField("sum",LongType),StructField("count",LongType)))
  }

  // 函数返回值的数据类型
  def dataType: DataType = DoubleType

  // 稳定性：对于相同的输入是否一直返回相同的输出。
  def deterministic: Boolean = true

  // 函数缓冲区初始化
  def initialize(buffer: MutableAggregationBuffer): Unit = {
    // 存年龄的总和
    buffer(0) = 0L
    // 存年龄的个数
    buffer(1) = 0L
  }

  // 更新缓冲区中的数据
  def update(buffer: MutableAggregationBuffer,input: Row): Unit = {
    if (!input.isNullAt(0)) {
      buffer(0) = buffer.getLong(0) + input.getInt(0)
      buffer(1) = buffer.getLong(1) + 1
    }
  }

  // 合并缓冲区
  def merge(buffer1: MutableAggregationBuffer,buffer2: Row): Unit = {
    buffer1(0) = buffer1.getLong(0) + buffer2.getLong(0)
    buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
  }

  // 计算最终结果
  def evaluate(buffer: Row): Double = buffer.getLong(0).toDouble / buffer.getLong(1)
}

object MyAveragUDAF{
  def main(args: Array[String]): Unit = {
    //创建上下文，配置环境变量
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TestSparkSQL")
    //创建SparkSession对象
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    //RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
    //spark不是包名，是上下文环境对象名
    import spark.implicits._

    //创建聚合函数
    var myAverage = new MyAveragUDAF
    //在spark中注册聚合函数
    spark.udf.register("avgAge",myAverage)

    val df: DataFrame = spark.read.json("datas/testSparkSQL.json")
    //SQL风格语法
    df.createOrReplaceTempView("user")

    spark.sql("select avgAge(age) from user").show()
  }
}

　　4) 实现方式 - UDAF - 强类型

package com.yuange.spark.day07

import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, Dataset, Encoder, Encoders, SparkSession, TypedColumn}
import org.apache.spark.sql.expressions.Aggregator

//输入数据类型
case class User2(username:String,age:Long)
//缓存类型
case class AgeBuffer(var sum:Long,var count:Long)

/**
 * @作者：袁哥
 * @时间：2021/6/7 20:50
 * 定义类继承org.apache.spark.sql.expressions.Aggregator
 * 重写类中的方法
 */
class MyAveragUDAF2 extends Aggregator[User2,AgeBuffer,Double]{
  override def zero: AgeBuffer = {
    AgeBuffer(0L,0L)
  }

  override def reduce(b: AgeBuffer, a: User2): AgeBuffer = {
    b.sum = b.sum + a.age
    b.count = b.count + 1
    b
  }

  override def merge(b1: AgeBuffer, b2: AgeBuffer): AgeBuffer = {
    b1.sum = b1.sum + b2.sum
    b1.count = b1.count + b2.count
    b1
  }

  override def finish(buff: AgeBuffer): Double = {
    buff.sum.toDouble/buff.count
  }
  //DataSet默认额编解码器，用于序列化，固定写法
  //自定义类型就是product自带类型根据类型选择
  override def bufferEncoder: Encoder[AgeBuffer] = {
    Encoders.product
  }

  override def outputEncoder: Encoder[Double] = {
    Encoders.scalaDouble
  }

}

object MyAveragUDAF2{
  def main(args: Array[String]): Unit = {
    //创建上下文，配置环境变量
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TestSparkSQL")
    //创建SparkSession对象
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    //RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
    //spark不是包名，是上下文环境对象名
    import spark.implicits._

    val df: DataFrame = spark.read.json("datas/testSparkSQL.json")

    //封装为DataSet
    val ds: Dataset[User2] = df.as[User2]

    //创建聚合函数
    var myAgeUdaf2 = new MyAveragUDAF2
    //将聚合函数转换为查询的列
    val col: TypedColumn[User2, Double] = myAgeUdaf2.toColumn

    //查询
    ds.select(col).show()
  }
}

2.8 数据的加载和保存

2.8.1 通用的加载和保存方式

提供了通用的保存数据和数据加载的方式。这里的通用指的是使用相同的API，根据不同的参数读取和保存不同格式的数据，SparkSQL默认读取和保存的文件格式为parquet

　　1) 加载数据（spark.read.load 是加载数据的通用方法）

spark.read.

　　　　如果读取不同格式的数据，可以对不同的数据格式进行设定

spark.read.format("…")[.option("…")].load("…")

：指定加载的数据类型，包括"csv"、"jdbc"、"json"、"orc"、"parquet"和"textFile"。

：在"csv"、"jdbc"、"json"、"orc"、"parquet"和"textFile"格式下需要传入加载数据的路径。

：在"jdbc"格式下需要传入JDBC相应参数，url、user、password和dbtable

　　　　我们前面都是使用read API 先把文件加载到 DataFrame然后再查询，其实，我们也可以直接在文件上进行查询:  文件格式.`文件路径`

spark.sql("select * from json.`/spark/input/user.json`").show

　　2) 保存数据（df.write.save 是保存数据的通用方法）

df.write.

　　　　如果保存不同格式的数据，可以对不同的数据格式进行设定

df.write.format("…")[.option("…")].save("…")

：指定保存的数据类型，包括"csv"、"jdbc"、"json"、"orc"、"parquet"和"textFile"。

：在"csv"、"orc"、"parquet"和"textFile"格式下需要传入保存数据的路径。

：在"jdbc"格式下需要传入JDBC相应参数，url、user、password和dbtable

　　　　保存操作可以使用 SaveMode, 用来指明如何处理数据，使用mode()方法来设置（这些 SaveMode 都是没有加锁的, 也不是原子操作），SaveMode是一个枚举类，其中的常量包括：

Scala/Java	Any Language	Meaning
SaveMode.ErrorIfExists(default)	"error"(default)	如果文件已经存在则抛出异常
SaveMode.Append	"append"	如果文件已经存在则追加
SaveMode.Overwrite	"overwrite"	如果文件已经存在则覆盖
SaveMode.Ignore	"ignore"	如果文件已经存在则忽略

df.write.mode("append").json("/spark/output")

2.8.2 Parquet

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

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

　　1) 加载数据（先将/opt/module/spark-yarn/examples/src/main/resources路径下的users.parquet上传至/spark/input）

hadoop fs -put /opt/module/spark-yarn/examples/src/main/resources/users.parquet /spark/input/

val df = spark.read.load("/spark/input/users.parquet")

df.show()

　　2) 保存数据

var df = spark.read.json("/spark/input/user.json")

df.write.mode("append").save("/spark/output/save/")

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 = "/spark/input/user.json"
val df= spark.read.json(path)

　　3）创建临时表

df.createOrReplaceTempView("user")

　　4）数据查询

spark.sql("SELECT username FROM user WHERE age BETWEEN 13 AND 19").show()

2.8.4 CSV

可以配置CSV文件的列表信息，读取CSV文件，CSV文件的第一行设置为数据列

spark.read.format("csv").option("sep", ";").option("inferSchema", "true").option("header", "true").load("data/user.csv")

2.8.5 MySQL

可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame，通过对DataFrame一系列的计算后，还可以将数据再写回关系型数据库中。如果使用spark-shell操作，可在启动shell时指定相关的数据库驱动路径或者将相关的数据库驱动放到spark的类路径下。

bin/spark-shell --jars jars/mysql-connector-java-5.1.27-bin.jar

　　这里只演示在Idea中通过JDBC对Mysql进行操作

　　1）导入依赖

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

　　2）读取数据

package com.yuange.spark.day08

import java.util.Properties

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

/**
 * @作者：袁哥
 * @时间：2021/6/7 21:30
 */
object TestMysql {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TestMysql")
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    import spark.implicits._

    //方式一：load方法读取
    spark.read.format("jdbc")
      .option("url", "jdbc:mysql://hadoop102:3306/gmall")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("user", "root")
      .option("password", "root123")
      .option("dbtable", "activity_info")
      .load().show

    //方式二：load方法读取（另一种参数形式）
    spark.read.format("jdbc")
        .options(Map("url"->"jdbc:mysql://hadoop102:3306/gmall?user=root&password=root123",
          "dbtable"->"activity_info","driver"->"com.mysql.jdbc.Driver")).load().show()

    //方式三：使用jdbc读取
    val properties: Properties = new Properties()
    properties.setProperty("user","root")
    properties.setProperty("password","root123")
    spark.read.jdbc("jdbc:mysql://hadoop102:3306/gmall","activity_info",properties).show()

    spark.stop()
  }
}

　　3）写入数据（首先在mysql中创建user表）

package com.yuange.spark.day08

import java.util.Properties

import org.apache.spark.SparkConf
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{Dataset, SaveMode, SparkSession}

case class User3(username: String, age: Int)

/**
 * @作者：袁哥
 * @时间：2021/6/7 22:31
 */
object TestMysqlTwo {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
    //创建SparkSession对象
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    import spark.implicits._

    val rdd: RDD[User3] = spark.sparkContext.makeRDD(List(User3("zhangsan",18),User3("lisi",34)))
    val ds: Dataset[User3] = rdd.toDS()

    //方式一：通用
    ds.write.format("jdbc")
      .option("url", "jdbc:mysql://hadoop102:3306/gmall")
      .option("user", "root")
      .option("password", "root123")
      .option("dbtable", "user")
      .mode(SaveMode.Append)
      .save()

    //方式二：jdbc方式
    val properties: Properties = new Properties()
    properties.setProperty("user","root")
    properties.setProperty("password","root123")
    ds.write.mode(SaveMode.Append).jdbc("jdbc:mysql://hadoop102:3306/gmall","user",properties)

    spark.stop()
  }
}

2.8.6 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。此外，如果你尝试使用 HQL 中的 CREATE TABLE (并非 CREATE EXTERNAL TABLE)语句来创建表，这些表会被放在你默认的文件系统中的 /user/hive/warehouse 目录中(如果你的 classpath 中有配好的 hdfs-site.xml，默认的文件系统就是 HDFS，否则就是本地文件系统)。

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

　　1）内嵌的HIVE（在实际使用中, 几乎没有任何人会使用内置的 Hive）

　　　　如果使用 Spark 内嵌的 Hive, 则什么都不用做, 直接使用即可.

的元数据存储在 derby 中, 默认仓库地址:$SPARK_HOME/spark-warehouse

spark.sql("show tables").show

spark.sql("create table testHive(id int)")

spark.sql("show tables").show

　　　　向表加载本地数据

spark.sql("load data local inpath '/opt/module/spark-yarn/bin/data/ids.txt' into table testHive")

spark.sql("select * from testHive").show

　　2）外部的HIVE

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

Spark要接管Hive需要把hive-site.xml拷贝到conf/目录下

cp /opt/module/hive/conf/hive-site.xml /opt/module/spark-yarn/conf/

　　　　（2）把Mysql的驱动copy到jars/目录下

cp /opt/module/hive/lib/mysql-connector-java-5.1.27-bin.jar /opt/module/spark-yarn/jars/

　　　　（3）如果访问不到hdfs，则需要把core-site.xml和hdfs-site.xml拷贝到conf/目录下

　　　　（4）重启spark-shell

spark.sql("show tables").show

　　3）运行Spark SQL CLI

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

bin/spark-sql

show databases;

use default;

show tables;

　　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/目录下

cp /opt/module/hive/conf/hive-site.xml /opt/module/spark-yarn/conf/

　　　　（2）把Mysql的驱动copy到jars/目录下

cp /opt/module/hive/lib/mysql-connector-java-5.1.27-bin.jar /opt/module/spark-yarn/jars/

　　　　（3）如果访问不到hdfs，则需要把core-site.xml和hdfs-site.xml拷贝到conf/目录下

　　　　（4）启动Thrift Server

sbin/start-thriftserver.sh

　　　　（5）使用beeline连接Thrift Server

bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu

show databases;

use default;

show tables;

　　5）代码操作Hive

　　　　1）导入依赖

<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>3.1.2</version>
</dependency>

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

　　　　2）将hive-site.xml文件拷贝到项目的resources目录中，代码实现

package com.yuange.spark.day08

import org.apache.spark.sql.SparkSession

/**
 * @作者：袁哥
 * @时间：2021/6/7 23:40
 */
object TestMySQLThree {
  def main(args: Array[String]): Unit = {
    //创建SparkSession
    val spark: SparkSession = SparkSession
      .builder()
      .enableHiveSupport()
　　　　.config("spark.sql.warehouse.dir","hdfs:hadoop102:8020/user/hive/warehouse")　　　//解决hive数据库创建在本地
      .master("local[*]")
      .appName("sql")
      .getOrCreate()
  }
}

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

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

　　　　可以代码最前面增加如下代码解决（或者以添加系统环境变量的方式也行）（此处的root改为你们自己的hadoop用户名称）：

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

解决数据库创建在本地问题

<!-- 在conf/hive-site.xml文件中添加如下配置 -->
<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>hdfs://hadoop102:8020/spark/hiveOnSpark</value>
  </property>

#或在spark/conf/spark-defaults.conf中添加
spark.sql.warehouse.dir  hdfs://hadoop102:9820/user/hive/warehouse

 　　　　完成之后重启（发现HDFS上创建了数据库）

bin/spark-sql

create database testHiveOne;