Spark Sql知识点总结

一、Spark SQL概述

1.1 Spark SQL是什么？

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

之前学习了Hive，它是将Hive SQL转换成MapReduce然后提交到集群上执行，大大简化了编写MapReduc的程序的复杂性，由于MapReduce这种计算模型执行效率比较慢。之后有Spark SQL，它是使用Hive解析sql生成AST语法树，将其后的逻辑计划生成、优化、物理计划都自己完成，而不依赖Hive；执行计划和优化交给优化器Catalyst。

Spark SQL是将Spark SQL转换成RDD，然后提交到集群执行，执行效率非常快。

1.2 特点

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

1.3 Spark SQL相较于RDD的优势在哪？

1. 提供了更好的外部数据源读写支持

   因为大部分外部数据源是有结构化的，需在RDD之外有一个新的解决方案，来整合这些结构化的数据源

2. 提供了直接访问列的能力

   因为Spark Sql主要用于处理结构化数据，所以其提供的API具有一些普通数据库的能力

3. 存储的数据不同

   RDD：半结构化和非结构化

   Spark Sql：结构化（速度比RDD块）

二、Spark Sql编程

2.1 Spark Session

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

SparkSession是Spark最新的SQL查询起始点，实质上是SQLContext和HiveContext的组合，所以在SQLContext和HiveContext上可用的API在SparkSession上同样是可以使用的。

SparkSession内部封装了SparkContext，所以计算实际上是由SparkContext 完成的。

SparkSession作为RDD的创建入口，其主要作用有如下两点：

1. 创建RDD，主要是通过读取文件创建RDD
2. 监控和调度任务，包含了一系列组件，例如DAGScheduler，TaskScheduler

2.2 DataFrame

是一个分布式数据容器，DataFrame更像传统数据库的二维表格，除了记录数据的结构信息，即schema；同时，与Hive类似，DataFrame也支持文件嵌套数据类型（struct，array和map等）

2.2.1 创建

1. 通过Spark的数据源进行创建
2. 从一个存在的RDD进行转换
3. 从Hive Table进行查询返回

2.2.2 SQL风格语法

对DataFrame创建一个临时表：df.createOrReplaceTempView("临时表名")

临时表是Session范围内的，Session退出后，表就失效了。

如果向应用范围内有效，可以使用全局表；注意使用全局表时需要全路径访问，如：global_temp.people

对DataFrame创建一个全局表：df.createGlobalTempView("全局表名")

查看对DataFrame的Schema信息：df.printSchema

2.2.3 RDD转换为DataFrame

注意：如果需要RDD与DF或者DS之间操作，那么都需要引入 import spark.implicits._ （spark不是包名，而是sparkSession对象的名称）

前置条件：导入隐式转换并创建一个RDD

1. 通过手动确定转换（.toDF）
2. 通过反射确定（需要用到样例类）
3. 通过编程的方式（创建Schema，根据数据及给定的schema创建DataFrame）

2.2.4 DataFrame转换为RDD

直接调用rdd即可： df.rdd

2.3 DataSet

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

2.3.1 创建

创建一个样例类，通过样例类创建DataSet

2.3.2 RDD转换为DataSet

SparkSQL能够自动将包含有case类的RDD转换成DataFrame，case类定义了table的结构，case类属性通过反射变成了表的列名（创建样例类）（.toDS）

2.3.3 DataSet转换为RDD

调用rdd方法即可：DS.rdd

2.4 DataFrame与DataSet的相互操作

1. DataFrame转换为DataSet

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

   创建一个样例类

   转换：df.as[Person]

2. DataSet转换为DataFrame

   这个很简单，因为只是把case class封装成Row

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

   创建一个样例类

   转换： ds.toDF

2.5 RDD、DataFrame、DataSet

2.5.1 三者的共性

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

2.5.2 三者的区别

	优点	缺点
RDD	面向对象的操作方式；可以处理任何类型的数据	运行速度比较慢，执行过程没有优化；API比较僵硬，对结构化数据的访问和操作没有优化
DataFrame	针对结构化数据高度优化，可以通过列名访问和转换数据；增加Catalyst优化器，执行过程是优化的，避免了因为开发者的原因影响效率	只能操作结构化数据；只有无类型的API，也就是只能针对列和SQL操作数据，API依然僵硬
DateSet	结合了RDD和DataFrame的API，既可以操作结构化数据，也可以操作非结构化数据；既有有类型的API，也有无类型的API，灵活选择

1. DataFrame表达的含义是一个支持函数式操作的表，而DataSet表达的是一个类似RDD的东西，DataSet可以处理任何对象
2. DataFrame中所存放的是Row对象，而DataSet中可以存放任何类型的对象
3. DataFrame的操作方式和DataSet是一样的，但是对于强类型操作而言，它们处理的类型不同
4. DataFrame只能做到运行时类型检查，DataSet能做到编译和运行时都有类型检查

2.6 用户自定义UDF函数

在 Shell 窗口中可以通过 spark.udf 功能用户可以自定义函数

/**
 * 通过spark.udf功能用户可以自定义函数
 */
object Udf {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()

    // 通过Spark的数据源进行创建DataFrame
    val df = spark.read.json("D://people.json")
    // 打印数据
    df.show()

    // 用户自定义函数，并注册
    spark.udf.register("addName", (n: String) => "Name:" + n)

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

    spark.sql("select addName(name) Name, age from people").show()

  }

  // 自定义函数
  def udfFunction(name: String): String = {
    val result = "Name:" + name
    result
  }
}

2.7 用户自定义聚合函数

强类型的DataSet和弱类型的DataFrame都提供了相关的聚合函数，除此之外，用户可以设定自己的自定义聚合函数

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

/**
 * 弱类型用户自定义聚合函数：通过继承UserDefinedAggregateFunction来实现用户自定义聚合函数
 */
object Udaf {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()

    val df = spark.read.json("D://students.json")
    df.printSchema()

    // SparkSql自定义聚合函数，并注册函数
    spark.udf.register("myAverage", new MyAverage)

    // 对DataFrame创建一个临时表
    df.createOrReplaceTempView("students")
    df.show()

    val resulr = spark.sql("select name, myAverage(score) avg_score from students group by name").show()
  }

  class MyAverage extends UserDefinedAggregateFunction {
    /*
     * 聚合函数输入参数的数据类型
     */
    override def inputSchema: StructType = StructType(StructField("score", LongType) :: Nil)

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

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

    /*
     * 对于相同的输入是否一直返回相同的输出
     */
    override def deterministic: Boolean = true

    /*
     * 初始化
     */
    override def initialize(buffer: MutableAggregationBuffer): Unit = {
      // 存分数的总成绩
      buffer(0) = 0L
      // 存分数的个数
      buffer(1) = 0L
    }

    /*
     * 相同Executor间的数据合并
     */
    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
      if(!input.isNullAt(0)) {
        // 对输入的成绩不断求和
        buffer(0) = buffer.getLong(0) + input.getLong(0)
        // 不断计数
        buffer(1) = buffer.getLong(1) + 1
      }
    }

    /*
     * 不同Executor间的数据合并
     */
    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
      // 成绩合并
      buffer1(0) = buffer1.getLong(0) + buffer2.getLong(0)
      // 计数合并
      buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
    }

    /*
     * 计算最终结果
     */
    override def evaluate(buffer: Row): Any = {
      // 平均成绩 = 成绩总和 / 计数
      buffer.getLong(0).toDouble / buffer.getLong(1)
    }
  }
}

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

/**
 * 强类型用户自定义聚合函数：通过继承Aggregator来实现强类型自定义聚合函数
 */
object Udaf {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()

    import spark.implicits._

    val ds = spark.read.json("D://students.json").as[Students]
    ds.printSchema()
    ds.show()

    // 将函数转换为‘TypedColumn’并给它起一个名字
    val myAverage1 = new MyAverage1
    val avg_score = myAverage1.toColumn.name("avg_score")
    ds.select(avg_score).show()
  }
}

// 既然是强类型，可能有样例类
// 定义输入的样例类
case class Students(name: String, score: Long)
// 定义缓存的样例类
case class Average(var sum: Long, var count: Long)

/**
 * 自定义聚合函数
 */
class MyAverage1 extends Aggregator[Students, Average, Double] {
  /*
   * 定义一个数据结构，保存成绩总分和成绩总条数，初识都为0
   */
  override def zero: Average = {
    Average(0L, 0L)
  }

  /*
   * 组合两个值以生成一个新值；为了提高性能，函数可以修改“buffer”并返回它，而不是构造一个新的对象
   */
  override def reduce(buffer: Average, students: Students): Average = {
    buffer.sum += students.score
    buffer.count += 1
    buffer
  }

  /*
   * 聚合不同Executor的结果
   */
  override def merge(buffer1: Average, buffer2: Average): Average = {
    buffer1.sum += buffer2.sum
    buffer1.count += buffer2.count
    buffer1
  }

  /*
   * 计算输出
   */
  override def finish(reduction: Average): Double = {
    reduction.sum.toDouble / reduction.count
  }

  /*
   * 设定之间值类型的编码器，要转换成case类
   * Encoders.product是进行scala元组和case类型转换的编码器
   */
  override def bufferEncoder: Encoder[Average] = Encoders.product

  /*
   * 设定最终输出值的编码器
   */
  override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
}

三、SparkSQL数据源

3.1 通用加载／保存方法

SparkSql的DataFrame接口支持多种数据源的操作；一个DataFrame可以进行RDDs方式的操作，也可以被注册为临时表；把DataFrame注册为临时表之后，就可以对该DataFrame执行SQL查询

SparkSql的默认数据源为Parquet格式

Parquet是一种流行的格式存储格式，可以高效地存储具有嵌套字段地记录

可以采用SaveMode执行存储操作，SaveMode定义了对数据的处理模式，这些保存模式不使用任何锁定，不是原子操作

Scala/Java	Any Language	Meaning
SaveMode.ErrorIfExists(default)	“error”(default)	如果文件存在，则报错
SaveMode.Append	“append”	追加
SaveMode.Overwrite	“overwrite”	覆写
SaveMode.Ignore	“ignore”	数据存在，则忽略

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

class ReadAndWrite {
  val spark = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()

  /**
   * 通用加载/保存方法
   * 数据源为Parquet文件时，SparkSql可以方便的执行所有的操作；修改配置项spark.sql.sources.default，可修改默认数据源格式
   * 当数据源格式不是Parquet格式文件时，需要手动指定数据源的格式；数据源格式需要指定全名（例如：org.apache.spark.sql.parquet）
   * 如果数据源格式为内置格式，则只需要指定简称（json，parquet，jdbc，orc，libsvm，csv，text）来指定数据的格式
   */
  @Test
  def saveTest(): Unit = {
    // 通过SparkSession提供的read.load方法用于通用加载数据
    val df = spark.read.format("json").load("D://students.json")
    // 使用write和save保存数据
    df.write.format("parquet").save("D://out/0001")
    // SaveMode定义数据的处理模式
    df.write.format("orc").mode("append").save("D://out/0001")
    df.show()

    // 还可以直接运行SQL在文件上
    val sqlDF = spark.sql("select * from parquet.`D://users.parquet`")
    sqlDF.write.format("json").save("D://out/0002")
    sqlDF.show()
  }

  /**
   * JSON文件
   * SparkSql能够自动推荐JSON数据集的结构，并将它加载为一个DataSet[Row]
   * 可以通过SparkSession.read.json()去加载一个一个JSON文件
   * 注意：这个JSON文件不是一个传统的JSON文件，每一行都要时一个JSON串
   */
  @Test
  def jsonTest(): Unit = {
    // JSON数据集是通过路径指向的；路径可以是单个文本文件，也可以是存储文本文件的目录
    val path = "D://students.json"
    val df = spark.read.json(path)

    df.printSchema()

    df.createOrReplaceTempView("students")

    spark.sql("select * from students where score between 80 and 90").show()

    // 也可以为表示的JSON数据集创建DataFrame；为每个字符串存储一个JSON对象的Dataset[String]
    import spark.implicits._
    val ds = spark.createDataset(
    """{"score":"98","name":"zhangsan"}""" :: Nil
    )
    spark.read.json(ds).show()
  }
}

3.2 JDBC

import java.util.Properties
import org.apache.spark.sql.SparkSession

/**
 * SparkSQL可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame
 * 通过对DataFrame一系列的计算后，还可以将数据再协会关系型数据库中
 * 注意：需要将相关的数据库驱动放到spark的类路径下
 */
object JDBC {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()

    // 从MySQL数据库加载数据方式一
    val jdbcDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8")
      .option("dbtable", "dynasty")
      .option("user", "root")
      .option("password", "123456")
      .load()
    jdbcDF.show()

    // 将数据写入MySQL方式一
    jdbcDF.write
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8")
      .option("dbtable", "dynasty")
      .option("user", "root")
      .option("password", "123456")
      .mode("ignore")
      .save()

    // 从MySQL数据库加载数据方式二
    val cp = new Properties()
    cp.put("user", "root")
    cp.put("password", "123456")
    val jdbcDF2 = spark.read.jdbc("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8", "emperor", cp)
    jdbcDF2.show()

    // 将数据写入MySQL方式二
    jdbcDF2.write.mode("ignore").jdbc("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8", "db", cp)
  }
}

3.3 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 EXTERNALTABLE）语句来创建表，这些表会被放在你默认的文件系统中的/user/hive/warehouse目录中（如果classpath中有配好的hdfs-site.xml，默认的文件系统就是HDFS，否则就是本地文件系统）。

Spark连接Hive三种方式

注意： 如果使用的是内部的Hive，在Spark2.0之后，spark.sql.warehouse.dir用于指定数据仓库的地址，如果需要是用HDFS作为路径，那么需要将core-site.xml和hdfs-site.xml加入到Spark conf目录，否则只会创建master节点上的warehouse目录，查询时会出现文件找不到的问题，这是需要使用HDFS，则需要将metastore删除，重启集群。

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.hive.HiveContext

/**
 * Spark SQL编译时可以包含Hive支持，也可以不包含
 * 包含Hive支持的Spark SQL可以支持Hive表访问、UDF（用户自定义函数）以及Hive查询语言（HiveQL/HQL）等
 */
object Hive {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").enableHiveSupport().getOrCreate()
    spark.sql("show databases").show()

    // 使用库
    spark.sql("use mydata")

    // 查看mydata库里的所有表
    spark.sql("show tables").show()

    // 创建第一张表
    var sql =
      """
        |create table if not exists mydata.studentInfo (
        |name string,
        |age int
        |)
        |row format delimited fields terminated by ','
        |lines terminated by '\n'
        |stored as textfile
        |""".stripMargin
    spark.sql(sql)
    // 加载数据（hdfs）
    sql = "load data inpath '/data/hive/student_infos.txt' into table mydata.studentInfo"
    spark.sql(sql)

    // 创建第二张表
    sql =
      """
        |create table if not exists mydata.studentScore (
        |name string,
        |score int
        |)
        |row format delimited fields terminated by ','
        |lines terminated by '\n'
        |stored as textfile
        |""".stripMargin
    spark.sql(sql)
    // 加载数据（hdfs）
    sql = "load data inpath '/data/hive/student_scores.txt' into table mydata.studentScore"
    spark.sql(sql)

    // 需求：关联两张表（join）
    sql =
      """
        |select in.name,in.age,sc.score
        |from mydata.studentInfo in
        |join mydata.studentScore sc
        |on in.name = sc.name
        |""".stripMargin
    spark.sql(sql).show()

    spark.stop()


//    val conf = new SparkConf().setAppName(this.getClass.getSimpleName).setMaster("local[2]").set("spark.executor.memory", "512m")
//    val sc = new SparkContext(conf)
//    val hc = new HiveContext(sc)
//    val sql = hc.sql("select * from mydata.psn_1").collect()
//    println(sql.toBuffer)
//    sc.stop()

  }
}