Spark SQL综述----硬核解析

一、Spark SQL原理

1.1Spark SQL概述

Spark SQL是 Spark 1.0的新加入成员，前身是 Shark，此后 Shark已停止维护。 Shark与 Hive高度兼容，但 Shark运行时却依赖 Hive Shark诞生的原因是为了能够在让 Spark更好的处理 SQL任务，替代当时流行的 Hive，但 Shark在多个方面存在先天不足的情况，主要是对 Hive的严重依赖：

1. Hive 的语法解析和查询优化等模块本身针对的是 MapReduce ，限制了在 Spark系统上的深度优化和维护；
2. 过度依赖 Hive制约了 Spark的“ One Stack Rule Them All”既定方针 可以在一套软件栈内完成前述各种大数据分析任务 ）），也制约了技术栈中各个组件的灵活集成。

所以，Shark后来被放弃并另起炉灶，便是 Spark SQL。 Spark SQL抛弃了Shark原有的架构，但汲取了 Shark的一些优点。除了覆盖 Shark的所有功能外，还提供了 SQL DSL API和灵活的程序扩展，摆脱了对 Hive的依赖。

1.2 Spark SQL特点

Spark SQL是 Spark用来处理结构化数据的一个模块，它提供了一个编程抽象叫做 DataFrame并且作为分布式 SQL查询引擎的作用。
Hive是将SQL转换成 MapReduce然后提交到集群上执行，大大简化了编写 MapReduce的程序的复杂性，但是由于MapReduce这种计算模型执行效率比较慢，所以 Spark SQL的应运而生，它是将 Spark SQL转换成 RDD，然后提交到集群执行，执行效率非常快！
Spark SQL特点：

• 易整合
• .统一的数据访问方式
• 兼容 Hive
• 标准的数据连接

SparkSQL可以看做是一个转换层，向下对接各种不同的结构化数据源，向上提供不同的数据访问方式。

1.3 Spark SQL核心

Spark SQL的核心是 Catalyst，所有 SQL操作最终都通过 Catalyst翻译成类似的 Spark程序代码被 Spark Core调度执行，即将查询语句转化成RDD运行，其过程也有 Job、 Stage、 Task的概念。如下图所示：

Spark SQL执行需要经过两个阶段：逻辑阶段→物理阶段
Catalyst优化器，作用便是将逻辑计划转为物理计划，如下图所示：

SQL执行过程 1


SQL执行过程 2

逻辑计划阶段 会将用户所写的SQL 语句转换成树型数据结构（逻辑算子树）， SQL 语句中蕴含的逻辑映射到逻辑算子树的不同节点。顾名思义，逻辑计划阶段生成的逻辑算子树并不会直接提交执行，仅作为中间阶段 。 最终逻辑算子树的生成过程经历三个子阶段，分别对应：

1. 未解析的逻辑算子树 Unresolved LogicaPlan ，仅仅是数据结构，不包含任何数据信息等）；
2. 解析后的逻辑算子树 Analyzed LogicalPlan ，节点中绑定各种信息
3. 优化后的逻辑算子树 Optimized LogcalPlan ，应用各种优化规则对一些低效的逻辑计划进行转换）

物理计划阶段 将上一步逻辑计划阶段生成的逻辑算子树进行进一步转换，生成物理算子树。物理算子树的节点会直接生成 RDD 或对 RDD 进行transformation 操作（注：每个物理计划节点中都实现了对 RDD 进行转换的 execute 方法）。物理计划阶段也包含三个子阶段：

1. 首先，根据逻辑算子树，生成物理算子树的列表 Iterator[PhysicaPlan] （同样的逻辑算子树可能对应多个物理算子树）；
2. 然后，从列表中按照 一 定的策略选取最优的物理算子树（ SparkPlan）
3. 最后，对选取的物理 算子树进行提交前的准备工作，例如，确保分区操作正确、物理算子树节点重用、执行代码生成等，得到“准备后 ”的物理算子树（Prepared SparkPlan）

经过上述步骤后，物理算子树生成的RDD 执行 action 操作，即可提交执行

从SQL 语句的解析一直到提交之前，上述整个转换过 Spark 集群 Driver 端进行，不涉及分布式环境 。 SparkSession 类的 sql方法调用 SessionState 中的各种对象，包括上述不同阶段对应的 SparkSqlParser 类、 Analyzer 类、Optimizer SparkPlanner 类等，最后封装成 Query Execution 对象。因此，在进行Spark SQL 开发时，可以很方便地将每一步生成的计划单独剥离出来分析。

如下图所示，左上角是SQL 语句，生成的逻辑算子树中有 Relation、 Filter、Project 节点，分别对应数据 、过滤逻辑（ age>18 ）和列剪裁逻辑 。下一步的物理算子树从逻辑算子树一对一映射得到， Relation逻辑节点转换为FileSourceScanExec执行节点， Filter逻辑节点转换为 FilterExec 执行节点，Project 逻辑节点转换为 ProjectExec 执行节点。

SQL-->RDD实际转换过程

二、Spark SQL优化器 – Catalyst Optimizer

Catalyst 的目标是将逻辑计划转化为物理计划，执行过程可简单概述为：
SQL语句翻译成语法树 —> 生成逻辑计划 —> 优化逻辑计划 —> 在投影上检查过滤器 —> 检查过滤器是否可以下压 —>合并project —> 生成物理计划
Catalyst优化器常见的三种优化规则：谓词下推、常量累加、列剪枝

• 谓词下推：扫描数据量过滤
• 常量累加：减少常量操作
• 列剪枝：对列式数据库提高扫描效率，减少网络、内存数据量消耗

sparkSQL  //查看优化器方法
queryExecution  //查看逻辑执行计划
explain  //查看物理执行计划

三、创建Dataset和DataFrame

3.1创建Dataset

Dataset=RDD+Schema，所以Dataset与RDD有大部共同的函数，如map、filter等，Dataset有三种创建方法：

①： createDataset() 方法，例： spark.createDataset(1 to 3).show

②： rdd.toDS() 方法生成Dataset

③： 通过 DataFrame 转化生成，例： val ds = df.as[T]

---

其中 createDataset() 方法原型如下：

createDataset[T:Encoder](List[T]):Dataset[T]
createDataset[T:Encoder](Seq[T]):Dataset[T]
createDataset[T:Encoder](RDD[T]):Dataset[T]

参数可为List、Seq、RDD，参数类型 T表示 Dataset中的元素的类型：

• 如果T是样例类，其属性名称自动变为 Dataset的 Schema（即 StructType数据结构信息），此时视图会显示带表头
• 如果T是List、Seq，则对应的序列下标为Dataset的 Schema，如下文例2、例3所示
• 如果T是基本类型，则 value 为其Schema
• 注：RDD有具体的值类型，可以为集合或者数组或者样例类，说T是RDD类型时太模糊了，应该具体指定到RDD的具体值类型，如下文 3.1.3例① 中截图4、截图1对应方法所示

3.1.1使用定义域对象中的强类型集合创建Dataset

在 spark-shell 命令行输入：
例1：

scala> spark.createDataset(1 to 3).show

例2：

scala> spark.createDataset(List(("a",1),("b",2),("c",3))).show

例3：

scala> spark.createDataset(sc.parallelize(List(("a",1,1),("b",2,2)))).show

3.1.2使用“样例类”创建Dataset

import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}

object CreateDataSetDemo1 {
  //创建样例类Point Category  样例类中自动获取了apply方法，创建对象不需要new关键字
  case class Point(label:String,x:Double,y:Double)
  case class Category(id:Long,name:String)

  def main(args: Array[String]): Unit = {
    //TODO 创建一个SparkSession对象
    val spark = SparkSession.builder()
      .master("local[*]").appName("test01")
      .getOrCreate()
    val sc = spark.sparkContext

    import spark.implicits._
    /*
    这里需导包，隐式转换，spark是上表面定义的变量名，不是固定的，._ 包含两种隐式函数
    spark.implicits.newIntEncoder 能够将 JVM的 Int对象写入 Spark SQL Rows
    spark.implicits.rddToDatasetHolder 将rdd隐式转换为 DatasetHolder，其中便定义了toDS()方法
    */

    //TODO 直接使用样例类创建Dataset
    val points=List(Point("bar",3.0,5.6),Point("foo",-1.0,3.0)).toDS   //List中元素为新创建的Point对象
    points.show()  //打印结果见截图1
    val categories=List(Category(1,"foo"), Category(2,"bar")).toDS  //List中元素为新创建的Category对象
    categories.show()  //打印结果见截图2
    //将points、categories两个表关联起来(关联条件：Points.label=categories.name)
    val union: DataFrame = points.join(categories, points("label") === categories("name"))  //使用两个表关联实际上就将类型转化为了DataFrame
    union.show()  //打印结果见截图3
  }
}

---

截图1

---

截图2

---

截图3

---

3.1.3使用“RDD”创建Dataset

例:①：在3.1.2的基础上继续添加代码，使用 RDD 创建Dataset

    //TODO 创建一个RDD: pointRDD
    // pointRDD列表中数据为新创建的Point对象，由于是样式类，自动获取了apply方法，创建对象不需要new关键字
    val pointRDD: RDD[(String, Double, Double)] = sc.makeRDD(List(("bar", 3.0, 4.0), ("foo", 2.0, 2.5)))

    //TODO: 使用RDD来创建Dataset
    val ds1 = pointRDD.toDS()   //此时Dataset的类型是List
    ds1.show()  //打印结果见截图4
    //加入了map算子，可以转化Dataset参数类型
    val pointDS: Dataset[Point] = pointRDD.map(x => Point(x._1, x._2, x._3)).toDS()  //此时用pointRDD.map遍历Point，Dataset的类型是 case Ponint 样例类
    pointDS.show()  //打印结果见截图1

    val categoryRDD =  sc.parallelize(List((1,"foo"),(2,"bar")))
    val categoryDS = categoryRDD.map(x=>Category(x._1,x._2)).toDS()
    categoryDS.show()  //打印结果见截图2

    //将pointDS、categoryDS两个表关联起来(关联条件：Points.label=categories.name)
    val ds = pointDS.join(categoryDS,pointDS("label")===categoryDS("name"))  //格式   A.join(B,关联条件)  此处相等要用三等号 ===
    ds.show()  //打印结果见截图3

截图4

---

例②： 通过读取 .csv表格 形式创建RDD，再创建Dataset

如下图，orders.csv 中有如下数据（部分截图）：

表数据链接：https://pan.baidu.com/s/1bV7MDj1ohSsA-xQuL3GPDA
提取码：5f8q

读取数据代码如下：

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

object CreateDataSetDemo2 extends App {
  case class Order(id:String,date:String,customerId:String,status:String)
  //TODO: 创建一个 SparkSession 对象
  val spark: SparkSession = SparkSession.builder()
    .master("local[*]").appName("test03")
    .getOrCreate()
  //导包
  import spark.implicits._

  val sc:SparkContext = spark.sparkContext

  val orderFile:RDD[String] = sc.textFile("file:///F:\\orders.csv")  //选择路径
  var orderDS: Dataset[Order] = orderFile.map(x => {
  val fields: Array[String] = x.split(",").map(y => y.replace("\"", ""))  //csv表格默认用逗号分割字段，再使用replace替换掉字段前后的双引号
    Order(fields(0),fields(1), fields(2), fields(3))
  }).toDS()
  orderDS.show()  //打印结果见截图5,默认显示20行数据
}

---

截图5

---

3.2创建DataFrame

DataFrame可以看做是Dataset的子集，二者可以相互转化，Dataset转化成DataFrame：val df = ds.toDF()
其他两者创建方法，通过RDD创建，createDataFrame() 方法与创建Dataset都很类似

3.2.1使用RDD创建DataFrame

例： 通过读取文本 .txt 创建RDD
创建一个 people.txt 文件，内容如下：

zhangsan,29
lisi,30
wangwu,19

创建DataFrame代码部分如下：

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

//创建People样例类
case class People(name:String,age:Int)

object CreateDataFrameDemo1 extends App {

  //TODO 创建一个SparkSession对象
  val spark = SparkSession.builder()
    .master("local[*]").appName("test01")
    .getOrCreate()
  import spark.implicits._
  val sc = spark.sparkContext

  //读取文件创建RDD
  val textRDD: RDD[Array[String]] = sc.textFile("file:///f:/people.txt")
    .map(_.split(","))
  //TODO 通过样例类，将RDD转换成 DF
  private val peopleDF: DataFrame = textRDD.map(x => People(x(0),x(1).trim.toInt)).toDF()
  peopleDF.show()
  peopleDF.printSchema()  //查看表结构信息，打印结果见截图6

  //TODO 定义schema信息
  private val schema = StructType(Array(
    StructField("name", StringType, true),
    StructField("age", IntegerType, true)
  ))

  //TODO 把rdd转换成 Row
  private val mapRDD: RDD[Row] = textRDD.map(x => Row(x(0),x(1).trim.toInt))

  //TODO 把RDD转换成Data Frame
  private val df1: DataFrame = spark.createDataFrame(mapRDD,schema)
  df1.show()
  df1.printSchema()   //打印结果见截图7
 
  //TODO 将Data Frame转换成RDD
  private val rddres: RDD[Row] = df1.rdd
  println(rddres.collect().mkString(","))  //打印结果见截图8
}

---

                截图6

---

                截图7

---

                截图8

---

3.2.2通过读取json文件创建DataFrame

例：
创建一个 people.json 文件，内容如下：

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

创建DataFrame代码如下：

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

object CreateDataFrameDemo2 extends App {
  //TODO 创建一个SparkSession对象
  val spark = SparkSession.builder()
    .master("local[*]").appName("test01")
    .getOrCreate()
  import spark.implicits._
  val sc = spark.sparkContext

  //TODO 通过spark  read读取json文件，生成DataFrame
  private val jsontoDF: DataFrame = spark.read.json("file:///f:\\people.json")
  jsontoDF.show()  //打印结果见截图9

  //TODO 打印schema信息
  jsontoDF.printSchema()  //打印结果见截图10

  //TODO下面使用sql语句操作DataFrame查询
  //TODO select
  jsontoDF.select("name").show() //打印结果见截图11

  jsontoDF.select(jsontoDF("name"),jsontoDF("age")+1).show()  //打印结果见截图12
  //也可以简写成这样：
  jsontoDF.select($"name",$"age"+1).show()  //打印结果见截图13

  //TODO filter
  jsontoDF.filter($"age">20).show()  //打印结果见截图14

  //TODO groupBy
  jsontoDF.groupBy("age").count().show()  //打印结果见截图15

  //TODO 把DataFrame注册成临时表
  jsontoDF.createOrReplaceTempView("people")  //创建全局视图
  private val df2: DataFrame = spark.sql("select * from people where age>20")
  df2.show()  //打印结果见截图16
  
  //通过创建临时Session的方式需在查询语句全局视图变量名前加上 global_temp.变量名  
  jsontoDF.createOrReplaceGlobalTempView("p1")
  spark.newSession().sql("select * from global_temp.p1 where age>20").show()  //打印结果见截图17
  
  spark.stop()
}

---

截图9

---

截图10

---

截图11

---

截图12

---

截图13

---

截图14

---

截图15

---

截图16

---

截图17

---

3.2.3通过读取 csv 文件创建DataFrame

使用 SparkSession对象.read.format(“文件格式”).load(“路径”) 方法直接读取csv文件，这是一个通用的方法，适用常见文件类型的读取，它还提供了一些其他的方法比如分割字符、去除字段的单双引号、自动推测字段类型等，非常方便

例： 读取 3.1.3例②中的 orders.csv 表格数据
代码如下：

import org.apache.spark.sql.SparkSession

object CreateDataFrameDemo3 extends App{
  //创建SparkSession对象
  val spark = SparkSession.builder()
    .master("local[*]").appName("test02")
    .getOrCreate()

  val df1 = spark.read.format("csv")  //指定csv格式
//    .option("delimiter",",")   //指定字段分隔符，默认提供逗号分隔的方法，本例中csv文件是也是逗号分隔的，故可省略不写
//    .option("header","true")  //指定第一行作为Schema表头，而非内容，本例中csv文件第一行不是表头字段名，所以不写
//    .option("quote","\"")  //去除数据中的引号字符，默认提供去除双引号的方法  ，本例中数据包含双引号，故此项也可省略不写
    .option("inferSchema","true")  //自动推测字段类型，选写
    .load("file:///f:/orders.csv")  //加载路径
    .withColumnRenamed("_c0","id")  //默认字段名为_c0，可以设置字段别名，下同
    .withColumnRenamed("_c1","date")
    .withColumnRenamed("_c2","customerId")
    .withColumnRenamed("_c3","status")

  df1.printSchema()
  df1.show()  //打印结果见截图18

  spark.stop()
}

---

截图18

---

3.3Dataset与DataFrame的区别

• DataFrame可以看做是Dataset的子集，DataFrame=Dataset[Row] ，可以通过as方法将Dataframe转换为Dataset
• Row是一个类型，跟Car、Person这些的类型一样，所有的表结构信息都可以用用Row来表示
• DataSet是强类型的，比如可以有Dataset[Car]，Dataset[Person]，DataSet可以在编译时检查类型
• DataFrame只是知道字段，但是不知道字段的类型，所以在执行这些操作的时候是没办法在编译的时候检查是否类型失败的

3.4RDD、Dataset、DataFrame相互转换

三者之间既有区别，也有联系，详细说明如下表所示：

	优点	缺点
RDD	1.内置很多函数操作，group、map、filter等，方便处理结构化或非结构化数据 2.面向对象编程，直接存储java对象，类型转化安全	1.由于它基本和hadoop一样万能，因此没有针对特殊场景的优化，比如对结构化数据处理比sql麻烦 2.默认采用java序列号方式，序列化结果比较大，而且数据存储在 java堆内存中，导致gc垃圾回收比较频繁
DataFrame	1.结构化数据处理非常方便，支持 Avro, CSV, elastic search, and Cassandra等 kv数据，也支持HIVE tables, MySQL等传统数据表 2.有针对性的优化，由于数据结构元信息spark已经保存，序列化时 不需要带上元信息，大大减少了序列化大小，而且数据保存在堆外内存中，减少了 gc次数 3.hive兼容，支持hql、udf等	1.编译时不能类型转化安全检查，运行时才能确定是否有问题 2.对于对象支持不友好，rdd内部数据直接以java对象存储，DataFrame内存存储的是row对象而不是自定义对象
Dataset	1.Dataset整合了RDD和DataFrame的优点，支持结构化和非结构化数据 2.和 RDD一样，支持自定义对象存储 3.和 DataFrame 一样，支持结构化数据的 sql查询 4.采用堆外内存存储，gc友好 5.类型转化安全，代码友好	很多情况下，Dataset的性能实际比DataFrame要差，因为 Dataset会涉及到额外的数据格式转换成本

三者的相互转换：
①：RDD -> Dataset

val ds = rdd.toDS()

②：RDD -> DataFrame

val df=rdd.toDF()

③：Dataset -> RDD

val rdd = ds.rdd

④： Dataset -> DataFrame

val df = ds.toDF()

⑤：DataFrame -> RDD

val rdd = df.rdd

⑥：DataFrame -> Dataset

val ds = df.as[T]   

例：val ds2:Dataset[Point] = df1.as[Point] //Point是一个类对象

四、使用Spark SQL操作外部数据源（hive、mysql）

Spark SQL支持外部数据源有很多，本文着重介绍hive与mysql两种

4.1使用Spark SQL创建并读取 Parquet 文件

Parquet 是一种流行的列式存储格式，以二进制存储，文件中包含数据与元数据，也 Spark默认的文件存储格式
例：

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.types.{ArrayType, IntegerType, StringType, StructField, StructType}
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object ParquetDemo {

  def main(args: Array[String]): Unit = {
    //TODO 1.创建一个SparkSession 对象

    val spark: SparkSession = SparkSession.builder()
      .master("local[4]").appName("test07")
      .getOrCreate()
    val sc: SparkContext = spark.sparkContext

    val rdd1: RDD[(String, String, Array[Int])] = sc.parallelize(List(   //造数据
      ("zhangsan", "green", Array(3, 5, 6, 9)),
      ("zhangsan", null, Array(3, 5, 6, 10)),
      ("lisi", "red", Array(3, 5, 6, 33)),
      ("zhangsan2", "green", Array(3, 5, 223, 9)),
      ("zhangsan3", "green", Array(3, 43, 44, 9))
    ))

    //todo 设置schema
    val structType = StructType(Array(
      StructField("name", StringType),
      StructField("color", StringType),
      StructField("numbers", ArrayType(IntegerType))
    ))
    val rowRDD: RDD[Row] = rdd1.map(p=>Row(p._1,p._2,p._3))
    val df: DataFrame = spark.createDataFrame(rowRDD,structType)  //将RDD与schema传入到createDataFrame方法中

    // TODO 读写parquet格式文件
//    df.write.parquet("file:///D:\\Data\\user")
    //最后一级子目录要求user不能先创建，不然会报错
    //TODO 读取parquet文件
    val parquetRDD: DataFrame = spark.read.parquet("file:///D:\\Data\\user")
    parquetRDD.printSchema()
    parquetRDD.show()  //打印结果见截图19

    spark.stop()
  }
}

---

截图19

---

4.2使用Spark SQL操作hive数据库

在linux系统中，首先需修改hive安装根目录/conf/目录下的 hive-site.xml 文件
添加以下代码：

<property>
<name>hive.metastore.uris</name>
<!--hadoop-single是linux主机名 -->
<value>thrift://hadoop-single:9083</value>
</property>

将文件拷贝至spark安装根目录/conf/目录下：

 copy /opt/install/hive/conf/hive-site.xml /opt/install/spark220/conf/

4.2.1在linux界面操作

启动hive前需先启动hive元数据服务：

hive --service metastore

hive命令行：
如图，在 hive默认数据库（default） 中有一个 customers 表数据，查询得到信息：

---

**spark-shell命令行：**

val df = spark.table("customers")
df.printSchema 
df.show

查询表数据信息如下所示：

---

注： 如果要查询其他数据库的表数据，则在spark-shell命令行table名指定 “数据库名.表名” 即可，如下 图所示：
hive命令行：

---

spark-shell命令行：

---

4.2.2使用IDEA操作hive数据库

需要引入hive、mysql及相关jar包，配置Maven的pom.xml文件，参考链接：https://blog.csdn.net/and52696686/article/details/107962405

示例：

import org.apache.spark.sql.SparkSession

object HiveSparkDemo extends App{
  //TODO: 创建一个 SparkSession 对象
  val spark: SparkSession = SparkSession.builder()
    .master("local[*]").appName("test02")
    .enableHiveSupport()
    //手动设置配置文件，也可以在resources文件夹中传入hive-site.xml文件，这样这一行就可以省略不写了
    .config("hive.metastore.uris","thrift://192.168.206.129:9083")
    .getOrCreate()

  val df1 = spark.sql("select * from customers")  //指定其他数据库表名-> "数据库名.表名"
  df1.printSchema()
  df1.show()
}

---

resources文件夹中传入hive-site.xml文件,如图所示：

---

4.3使用Spark SQL操作mysql数据库

在linux系统中需引入mysql jar 包至spark安装根目录/jars/目录下，我这里引入的版本是 mysql-connector-java-5.1.48-bin.jar

拷贝jar包：

cp /opt/install/hive/lib/mysql-connector-java-5.1.48-bin.jar /opt/install/spark220/conf                                                                                

4.3.1在linux界面操作

示例：
在mysql命令行查询以下信息：

---

在spark-shell命令行：

val url = "jdbc:mysql://localhost:3306/test"
val tableName = "student"
// 设置连接用户、密码、数据库驱动类
val prop = new java.util.Properties
prop.setProperty("user","bigdata")
prop.setProperty("password","ok")
prop.setProperty("driver","com.mysql.jdbc.Driver")
// 取得该表数据
val jdbcDF = spark.read.jdbc(url,tableName,prop)
jdbcDF.show
//DF存为新的表
jdbcDF.write.mode("append").jdbc(url,"t1",prop)

打印结果入下图所示：

---

4.3.2使用IDEA操作mysql数据库

import java.util.Properties
import org.apache.spark.sql.{DataFrame, SparkSession}

object MysqlDemo extends App {
  
  private val spark: SparkSession = SparkSession.builder()
    .master("local[2]").appName("mysql")
    .getOrCreate()

  //TODO 配置url,tableName，prop
  val url="jdbc:mysql://192.168.206.129:3306/test"  //指定数据库为test
   val prop = new Properties()
  prop.setProperty("user","bigdata")
  prop.setProperty("password","ok")
  prop.setProperty("driver","com.mysql.jdbc.Driver")

  //TODO spark连接mysql,读取mysql中的表并将其转换成df
  val tableName="student"
  val mysqlDF: DataFrame = spark.read.jdbc(url,tableName,prop)
  mysqlDF.printSchema()
  mysqlDF.show()  //打印结果如下图所示

  spark.stop()
}

---

五、Spark SQL常用内置函数示例

类别	函数示例
聚合函数	count()，countDistinct()，avg()，max()，min()
集合函数	sort_array、explode
日期、时间函数	hour、quarter、next_day
数学函数	asin、atan、sqrt、tan、round
开窗函数	row_number、rank_number
字符串函数	concat、format_number、regexp_extract
其他函数	isNaN、sha、randn、callUDF

例①：

import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, SparkSession}

object Demo02 extends App {
  val spark: SparkSession = SparkSession.builder()
    .master("local[*]").appName("test02")
    .getOrCreate()
  //导包
  import spark.implicits._
  val sc: SparkContext = spark.sparkContext

  //todo 导入Spark 内置函数所需要的包
  import org.apache.spark.sql.functions._

  //创建样式类Student
  case class Student(id:Int,name:String,gender:String,age:Int)
  private val stuDF: DataFrame = Seq(  //造数据
    Student(1001, "zhangsan", "F", 20),
    Student(1002, "zhang2", "F", 18),
    Student(1003, "zhang3", "M", 20),
    Student(1004, "zhang4", "M", 25),
    Student(1005, "zhang5", "M", 20),
    Student(1006, "zhang6", "F", 20)
  ).toDF()
  stuDF.printSchema()
  stuDF.show()
  /*

  root
  |-- id: integer (nullable = false)
  |-- name: string (nullable = true)
  |-- gender: string (nullable = true)
  |-- age: integer (nullable = false)

  +----+--------+------+---+
  |  id|    name|gender|age|
  +----+--------+------+---+
  |1001|zhangsan|     F| 20|
  |1002|  zhang2|     F| 18|
  |1003|  zhang3|     M| 20|
  |1004|  zhang4|     M| 25|
  |1005|  zhang5|     M| 20|
  |1006|  zhang6|     F| 20|
  +----+--------+------+---+

  */

  //TODO |id|name|gender|age|
  //TODO 按性别分组，求平均年龄
  stuDF.groupBy("gender").agg(avg("age")).show()
  stuDF.groupBy("gender").avg("age").show() //结果一样
/*

  +------+------------------+
  |gender|          avg(age)|
    +------+------------------+
  |     F|19.333333333333332|
  |     M|21.666666666666668|
  +------+------------------+

*/

  //TODO 按性别分组，求年龄 平均，最大，最小
  stuDF.groupBy("gender").agg("age"->"avg","age"->"max","age"->"min").show()
/*

  |gender|          avg(age)|max(age)|min(age)|
    +------+------------------+--------+--------+
  |     F|19.333333333333332|      20|      18|
  |     M|21.666666666666668|      25|      20|
  +------+------------------+--------+--------+

*/

  //TODO 按性别和年龄进行分组
  stuDF.groupBy("gender","age").count().show()
/*
  |gender|age|count|
  +------+---+-----+
  |     F| 18|    1|
  |     M| 25|    1|
  |     M| 20|    2|
  |     F| 20|    2|
  +------+---+-----+

  */

  //TODO 按年龄排序
  stuDF.sort("age").show()
  /*
  |gender|age|count|
  +------+---+-----+
  |     F| 18|    1|
  |     M| 25|    1|
  |     M| 20|    2|
  |     F| 20|    2|
  +------+---+-----+
  */

  stuDF.sort($"age".desc).show()  //倒序排列

  /*
  +----+--------+------+---+
  |  id|    name|gender|age|
  +----+--------+------+---+
  |1004|  zhang4|     M| 25|
  |1001|zhangsan|     F| 20|
  |1003|  zhang3|     M| 20|
  |1006|  zhang6|     F| 20|
  |1005|  zhang5|     M| 20|
  |1002|  zhang2|     F| 18|
  +----+--------+------+---+
*/

  stuDF.sort($"age".desc,$"id".desc).show()
/*
  |  id|    name|gender|age|
  +----+--------+------+---+
  |1004|  zhang4|     M| 25|
  |1006|  zhang6|     F| 20|
  |1005|  zhang5|     M| 20|
  |1003|  zhang3|     M| 20|
  |1001|zhangsan|     F| 20|
  |1002|  zhang2|     F| 18|
  +----+--------+------+---+
  */
  
  stuDF.orderBy($"age".desc).limit(2).show()
/*
  |  id|  name|gender|age|
  +----+------+------+---+
  |1004|zhang4|     M| 25|
  |1006|zhang6|     F| 20|
  +----+------+------+---+
  */
  
  spark.stop()
}

---

例②：

package cn.kgc.day0812.test10

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

object InterFunctionDemo extends App {
  val spark: SparkSession = SparkSession.builder()
    .master("local[*]").appName("test02")
    .getOrCreate()
  val sc: SparkContext = spark.sparkContext

  //todo 导入Spark 内置函数所需要的包
  import org.apache.spark.sql.functions._

  //造数据，假设有一个日志文件，日期和id
   val accessLog = Array(
    "2020-08-13,1",
    "2020-08-13,1",
    "2020-08-13,2",
    "2020-08-13,2",
    "2020-08-13,3",
    "2020-08-14,1",
    "2020-08-14,1",
    "2020-08-14,2",
    "2020-08-14,3",
    "2020-08-15,1",
    "2020-08-15,2",
    "2020-08-15,2",
    "2020-08-15,3"
  )
  private val accessLogRDD: RDD[Row] = sc.parallelize(accessLog).map(row => {
    val splited: Array[String] = row.split(",")
    Row(splited(0), splited(1).toInt)
  })

  private val structType = StructType(Array(    //设置Schema表头信息
    StructField("day", StringType),
    StructField("userId", IntegerType)
  ))

  private val logDF: DataFrame = spark.createDataFrame(accessLogRDD,structType)
  logDF.printSchema()
  /*
  root
  |-- day: string (nullable = true)
  |-- userId: integer (nullable = true)
*/

  logDF.show()

/*
  +----------+------+
  |       day|userId|
  +----------+------+
  |2020-08-13|     1|
    |2020-08-13|     1|
    |2020-08-13|     2|
    |2020-08-13|     2|
    |2020-08-13|     3|
    |2020-08-14|     1|
    |2020-08-14|     1|
    |2020-08-14|     2|
    |2020-08-14|     3|
    |2020-08-15|     1|
    |2020-08-15|     2|
    |2020-08-15|     2|
    |2020-08-15|     3|
    +----------+------+

*/
  
  //TODO 需求1：求每天有多少访问量 （pv）
  logDF.groupBy("day").agg(count("userId")).show()

/*
  |       day|count(userId)|
    +----------+-------------+
  |2020-08-13|            5|
    |2020-08-14|            4|
    |2020-08-15|            4|
    +----------+-------------+
  */
  
  logDF.groupBy("day").agg(count("userId").as("pv")).show()   //将"userId"取别名"pv"

/*
  |       day| pv|
  +----------+---+
  |2020-08-13|  5|
    |2020-08-14|  4|
    |2020-08-15|  4|
    +----------+---+
  */
  
  logDF.groupBy("day").agg(count("userId").alias("pv")).show()  //取别名的另一种方式

 /* |       day| pv|
  +----------+---+
  |2020-08-13|  5|
    |2020-08-14|  4|
    |2020-08-15|  4|
    +----------+---+
 
  */

  //TODO 需求2：求每天有多少访问用户 （uv）  需要去重
  logDF.groupBy("day").agg(countDistinct("userId").alias("uv")).show()

/*
  |       day| uv|
  +----------+---+
  |2020-08-13|  3|
    |2020-08-14|  3|
    |2020-08-15|  3|
    +----------+---+
  */
  
  //使用建表语句创建临时表，用sql语句查询每天的浏览量
  logDF.createOrReplaceTempView("logs")
  spark.sql(     //三引号之间的内容可以随意换行
    """
      |select day,count(userId) as pv
      |from logs
      |group by day
    """.stripMargin).show()

  /*
  +----------+---+
  |       day| pv|
  +----------+---+
  |2020-08-13|  5|
    |2020-08-14|  4|
    |2020-08-15|  4|
    +----------+---+
  */
  
  spark.stop()
}

六、自定义Spark SQL函数

示例：
写一个txt文本 hobbies.txt 内容如下：
两个字段名分别为姓名、爱好

alice   jogging,Coding,cooking
lina    travel,dance

自定义一个函数 hobby_num ，统计每个人爱好的数量，函数创建及使用代码如下：

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SparkSession}
  //创建样例类 Hobbies
case class Hobbies(name:String,hobbies:String)

object UDFDemo extends App {
  val spark: SparkSession = SparkSession.builder()
    .master("local[*]").appName("test02")
    .getOrCreate()
  //导包
  import spark.implicits._
   val sc: SparkContext = spark.sparkContext

  private val info: RDD[String] = sc.textFile("file:///f:/hobbies.txt")
   val hobbyDF: DataFrame = info.map(_.split("\t")).map(p=>Hobbies(p(0),p(1))).toDF()

  hobbyDF.createOrReplaceTempView("hobby")

  //TODO 重点：注册UDF函数，注意是匿名函数
  spark.udf.register("hobby_num",(s:String)=>s.split(",").size)  //统计数量
  
  //应用
  spark.sql(
    """
      |select name,hobbies,hobby_num(hobbies) as hobbyNum
      |from hobby
    """.stripMargin).show()
  //三引号表示实现多行字符串， stripMargin 表示每行固定对齐，默认使用 "|" 作为换行符
  
/*
  +-----+--------------------+--------+
  | name|             hobbies|hobbyNum|
  +-----+--------------------+--------+
  |alice|jogging,Coding,co...|       3|
  | lina|        travel,dance|       2|
    +-----+--------------------+--------+
  */

  spark.stop()
}

七、Spark性能优化

1. 序列化

• Java序列化：是Spark默认方式
• Kryo序列化：比Java序列化快约 10倍，但不支持所有可序列化类型 。使用方式：

conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer");
//向 Kryo注册自定义类型
conf.registerKryoClasses(Array(classOf[MyClass1], classOf[MyClass2]));

注： RDD和DataFrame默认的序列化机制是Java的序列化，可以修改为 Kyro的机制，而 Dataset使用自定义的专用数据编码器进行序列化和反序列化。

2. Join优化(表连接)

• 包含所有表的谓词 predicate

select * from t1 join t2 on t1.name = t2.full_name 
where t1.name = 'mike' and t2.full_name = 'mike'

• 最大的表放在第一位
• 广播最小的表
• 最小化表 join的数量

3. 使用DataFrame： DataFrame的执行速度比 Dataset快约 3倍,因为Dataset是类型安全的，必然会涉及到额外的数据格式转换成本
4. 使用对象数组、原始类型代替Java、Scala集合类（如HashMap）
5. 避免嵌套结构
6. 尽量使用数字作为Key，而非字符串
7. 以较大的RDD使用MEMORY_ONLY_SER
8. 加载CSV、JSON时，仅加载所需字段
9. 仅在需要时持久化中间结果（RDD/DS/DF）
10. 避免不必要的中间结果（RDD/DS/DF）的生成
11. 自定义RDD分区与spark.default.parallelism(该参数用于设置每个stage的默认task数量)
12. 将大变量广播出去，而不是直接使用
13. 尝试处理本地数据并最小化跨工作节点的数据传输