4.1Spark SQL概述、数据帧与数据集

一、Spark SQL

（一）Spark SQL概述
Spark SQL是Spark用来处理结构化数据的一个模块，它提供了一个编程抽象结构叫做DataFrame的数据模型（即带有Schema信息的RDD），Spark SQL作为分布式SQL查询引擎，让用户可以通过SQL、DataFrames API和Datasets API三种方式实现对结构化数据的处理。
（二）Spark SQL功能
Spark SQL可从各种结构化数据源中读取数据，进行数据分析。
Spark SQL包含行业标准的JDBC和ODBC连接方式，因此它不局限于在Spark程序内使用SQL语句进行查询。
Spark SQL可以无缝地将SQL查询与Spark程序进行结合，它能够将结构化数据作为Spark中的分布式数据集（RDD）进行查询。
（三）Spark SQL结构
1、Spark SQL架构图
Spark SQL架构与Hive架构相比，把底层的MapReduce执行引擎更改为Spark，还修改了Catalyst优化器，Spark SQL快速的计算效率得益于Catalyst优化器。从HiveQL被解析成语法抽象树起，执行计划生成和优化的工作全部交给Spark SQL的Catalyst优化器进行负责和管理。

2、Spark SQL三大过程
Spark要想很好地支持SQL，需要完成解析（Parser）、优化（Optimizer）、执行（Execution）三大过程。

3、Spark SQL内部五大组件
Catalyst优化器在执行计划生成和优化的工作时，离不开内部的五大组件。

（四）Spark SQL工作流程
在解析SQL语句之前，会创建SparkSession，涉及到表名、字段名称和字段类型的元数据都将保存在SessionCatalog中；
当调用SparkSession的sql()方法时就会使用SparkSqlParser进行解析SQL语句，解析过程中使用的ANTLR进行词法解析和语法解析；
使用Analyzer分析器绑定逻辑计划，在该阶段，Analyzer会使用Analyzer Rules，并结合SessionCatalog，对未绑定的逻辑计划进行解析，生成已绑定的逻辑计划；
使用Optimizer优化器优化逻辑计划，该优化器同样定义了一套规则（Rules），利用这些规则对逻辑计划和语句进行迭代处理；
使用SparkPlanner对优化后的逻辑计划进行转换，生成可以执行的物理计划SparkPlan；
使用QueryExecution执行物理计划，此时则调用SparkPlan的execute()方法，返回RDDs。
（五）Spark SQL主要特点
1、将SQL查询与Spark应用程序无缝组合
Spark SQL允许使用SQL或熟悉的DataFrame API在Spark程序中查询结构化数据。与Hive不同的是，Hive是将SQL翻译成MapReduce作业，底层是基于MapReduce的；而Spark SQL底层使用的是Spark RDD。
在Spark应用程序中嵌入SQL语句
val res = spark.sql( "SELECT * FROM student")
1
2、Spark SQL以相同方式连接多种数据源
Spark SQL提供了访问各种数据源的通用方法，数据源包括Hive、Avro、Parquet、ORC、JSON、JDBC等。
读取HDFS中的JSON文件，基于文件内容创建临时视图，最后与其他表根据指定的字段关联查询
// 读取JSON文件
val userScoreDF = spark.read.json("hdfs://master:9000/users.json")
// 创建临时视图user_score
userScoreDF.createTempView("user_score")
// 根据name关联查询
val resDF = spark.sql("SELECT i.age, i.name, c.score FROM user_info i INNER JOIN user_score c ON i.name = c.name")
1
2
3
4
5
6
3、在现有数据仓库上运行SQL或HiveQL查询
Spark SQL支持HiveQL语法以及Hive SerDes和UDF（用户自定义函数），允许访问现有的Hive仓库。

二、数据帧 - DataFrame

（一）DataFrame概述
DataFrame是Spark SQL提供的一个编程抽象，与RDD类似，也是一个分布式的数据集合，但与RDD不同，DataFrame的数据都被组织到有名字的列中，就像关系型数据库中的表一样。在Spark 1.3.0版本之前，DataFrame被称为SchemaRDD。此外，多种数据都可以转化为DataFrame，例如Spark计算过程中生成的RDD、结构化数据文件、Hive中的表、外部数据库等。
（二）将RDD转成DataFrame
DataFrame在RDD的基础上添加了数据描述信息（Schema，模式，即元信息），因此看起来更像是一张数据库表。

一个RDD中有5行数据

将RDD转成DataFrame

使用DataFrame API结合SQL处理结构化数据比RDD更加容易，而且通过DataFrame API或SQL处理数据，Spark优化器会自动对其优化，即使写的程序或SQL不高效，也可以运行得很快。

三、数据集 - Dataset

（一）Dataset概述
Dataset是一个分布式数据集，Spark 1.6中添加的一个新的API。相对于RDD，Dataset提供了强类型支持，在RDD的每行数据加了类型约束。而且使用Dataset API同样会经过Spark SQL优化器的优化，从而提高程序执行效率。
（二）将RDD转成DataSet
一个RDD中有5行数据

将RDD转换为Dataset

（三）DataFrame与Dataset的关系
在Spark中，一个DataFrame所代表的是一个元素类型为Row的Dataset，即DataFrame只是Dataset[Row]的一个类型别名。
四、简单使用Spark SQL
（一）了解SparkSession
Spark Shell启动时除了默认创建一个名为sc的SparkContext的实例外，还创建了一个名为spark的SparkSession实例，该spark变量可以在Spark Shell中直接使用。
从Spark2.0以上版本开始, Spark使用全新的SparkSession接口替代Spark1.6中的SQLContext及HiveContext接口来实现其对数据加载、转换、处理等功能。SparkSession实现了SQLContext及HiveContext所有功能。
SparkSession只是在SparkContext基础上的封装，应用程序的入口仍然是SparkContext。SparkSession允许用户通过它调用DataFrame和Dataset相关API来编写Spark程序，支持从不同的数据源加载数据，并把数据转换成DataFrame，然后使用SQL语句来操作DataFrame数据。
（二）准备工作
1、准备数据文件
1,郑秀芸,女,20
2,王志峰,男,18
3,陈燕文,女,21
4,郑国栋,男,19
5,肖雨涵,男,20
1
2
3
4
5
在/home目录里创建student.txt文件

将student.txt上传到HDFS的/student/input目录

2、启动Spark Shell
启动Spark Shell，执行命令：spark-shell --master spark://master:7077

（三）加载数据为Dataset
1、读文件得数据集
调用SparkSession对象的read.textFile()可以读取指定路径中的文件内容，并加载为一个Dataset

执行命令：val ds = spark.read.textFile("hdfs://master:9000/student/input/student.txt")

从变量ds的类型可以看出，textFile()方法将读取的数据转为了Dataset。除了使用textFile()方法读取文本内容外，还可以使用csv()、jdbc()、json()等方法读取CSV文件、JDBC数据源、JSON文件等数据。(csv: comma separated value)

2、显示数据集内容
执行命令：ds.show

可以看出，Dataset将文件中的每一行看作一个元素，并且所有元素组成了一列，列名默认为value。

3、显示数据集模式
执行命令：ds.printSchema

这样的单列数据集显得太粗糙，应该加点元数据信息，让它更精细化。
（四）给数据集添加元数据信息
1、定义学生样例类
定义一个样例类Student，用于存放数据描述信息（Schema）

执行命令：case class Student(id: Int, name: String, gender: String, age: Int)

基于样例类创建对象很简单，不需要new关键字，只需要传入相应参数即可创建对象

2、导入隐式转换
导入SparkSession的隐式转换，以便后续可以使用Dataset的算子
执行命令：import spark.implicits._ （_表示implicits包里所有的类，类似于Java里的*）

3、将数据集转换成学生数据集
调用Dataset的map()算子将每一个元素拆分并存入Student样例对象
执行命令:paste进入粘贴模式，然后执行红框类的命令

val studentDS = ds.map(line => {
      val fields = line.split(",")
      val id = fields(0).toInt
      val name = fields(1)
      val gender = fields(2)
      val age = fields(3).toInt
      Student(id, name, gender, age)
   }
)
1
2
3
4
5
6
7
8
9
4、对学生数据集进行操作
（1）显示数据集内容
执行命令：studentDS.show

可以看到，studentDS中的数据类似于一张关系型数据库的表。

（2）打印数据集模式
执行命令：studentDS.printSchema

（3）对数据集进行投影操作
显示学生的姓名和年龄字段，执行命令：studentDS.select("name", "age").show

对应的SQL语句：select name, age from student
执行命令：studentDS.select(studentDS("name"), studentDS("age") + 1).show
-
执行命令：studentDS.select(studentDS("name").as("姓名"), (studentDS("age") + 1).as("年龄")).show（给字段取别名）

相当于SQL语句：select name as 姓名, age + 1 as 年龄 from student;
（4）对数据集进行过滤操作
显示女生记录，执行命令：studentDS.filter("gender == '女'").show

显示年龄在[19, 20]之间的记录

两个数据集求交集

可以有更简单的处理方式，执行命令：studentDS.filter("age >= 19 and age <= 20").show

如果条件是age <19 or age > 20，那么两个数据集ds1与ds2就应该求并集：ds1.union(ds2)

逻辑运算    集合运算
not    补集 - complement
and    交集 - intersection
or    并集 - union
（5）对数据集进行统计操作
求20岁以上的女生人数

分组统计男女生总年龄，执行命令：studentDS.groupBy("gender").sum("age").show

相当于SQL语句：select gender, sum(age) from student group by gender;

分组统计男女生平均年龄：执行命令：studentDS.groupBy("gender").avg("age").show

相当于SQL语句：select gender, avg(age) from student group by gender;

分组统计男女生最大年龄，执行命令：studentDS.groupBy("gender").max("age").show

相当于SQL语句：select gender, max(age) from student group by gender;

分组统计男女生最小年龄，执行命令：studentDS.groupBy("gender").min("age").show

相当于SQL语句：select gender, min(age) from student group by gender;

（6）对数据集进行排序操作
按年龄升序排列，执行命令：studentDS.sort("age").show

相当于SQL语句：select * from student order by age; (默认是asc - ascending)

按年龄降序排列，执行命令：studentDS.sort(studentDS("age").desc).show

按年龄降序排列，执行命令：studentDS.sort(desc("age")).show

相当于SQL语句：select * from student order by age desc; (desc - descending)

先按性别升序排列，再按年龄降序排列，执行命令：studentDS.sort(asc("gender"), desc("age")).show

对应的SQL语句：select * from student order by gender asc, age desc;

（7）重命名数据集字段
执行命令：studentDS.select(studentDS("id").as("学号"), studentDS("name").as("姓名"), studentDS("gender").as("性别"), studentDS("age").as("年龄")).show

（五）将数据集转为数据帧
Spark SQL查询的是DataFrame中的数据，因此需要将存有元数据信息的Dataset转为DataFrame。调用Dataset的toDF()方法，将存有元数据的Dataset转为DataFrame。
1、将数据集转为数据帧
将学生数据集转为学生数据帧，执行命令：val studentDF = studentDS.toDF()

2、对学生数据帧进行操作
（1）显示数据帧内容
显示学生数据帧内容，执行命令：studentDF.show

（2）显示数据帧模式信息
打印学生数据帧模式信息，执行命令：studentDF.printSchema

（3）对数据帧进行投影操作
显示学生数据帧姓名与年龄字段，年龄加1，执行命令：studentDF.select(studentDF("name"), studentDF("age") + 1).show

执行命令：studentDF.select(studentDF("name").as("姓名"), (studentDF("age") + 1).as("年龄")).show （给字段取别名）

（4）对数据帧进行过滤操作
查询年龄在19岁以上的记录，执行命令：studentDF.filter(studentDF("age") > 19).show

查询20岁以上的女生记录，执行命令：studentDF.filter("age > 20 and gender == '女'").show()

（5）对数据帧进行统计操作
统计学生数据帧总记录数，执行命令：studentDF.count

分组统计男女生总年龄，执行命令：studentDF.groupBy("gender").sum("age").show，

分组统计男女生平均年龄，执行命令：studentDF.groupBy("gender").avg("age").show，

分组统计男女生最大年龄，执行命令：studentDF.groupBy("gender").max("age").show，

分组统计男女生最小年龄，执行命令：studentDF.groupBy("gender").min("age").show

分组统计男女生人数，执行命令：studentDF.groupBy("gender").count.show

（6）对数据帧进行排序操作
对年龄升序排列，执行命令：studentDF.sort("age").show

对年龄降序排列，执行命令：studentDF.sort(desc("age")).show

先按性别升序，再按年龄降序，- 执行命令：studentDF.sort(asc("gender"), desc("age")).show

（7）重命名数据帧字段
执行命令：studentDF.select(studentDF("id").as("学号"), studentDF("name").as("姓名"), studentDF("gender").as("性别"), studentDF("age").as("年龄")).show

直接对数据帧进行操作，其实并不是很方便，因此，我们需要基于数据帧创建临时视图，然后对于临时视图就可以进行SQL操作，那样就会十分方便。

（六）基于数据帧进行SQL查询
1、基于数据帧创建临时视图
基于学生数据帧studentDF，创建一个临时视图student，就可以对student视图进行SQL操作

执行命令：studentDF.createTempView("student")

如果临时视图存在，使用这个命令就会报错

此时，执行命令：studentDF.createOrReplaceTempView("student")，就不会报错

如果指定的视图不存在，那就创建，如果存在，那就替换。

2、使用spark对象执行SQL查询
在Spark Shell环境里，系统已经创建了名为spark的SparkSession对象
spark.sql()方法用于执行一个SQL查询，返回结果是一个数据帧
（1）查询全部表记录
执行命令：spark.sql("select * from student").show

（2）显示数据表结构
执行命令：spark.sql("describe student").show

（3）对表进行投影操作
执行命令：spark.sql("select name, age + 1 from student").show

（4）对表进行选择操作
查询年龄在19岁以上的记录，执行命令：spark.sql("select * from student where age > 19").show

查询20岁以上的女生记录，执行命令：spark.sql("select * from student where age > 20 and gender = '女'").show

（5）对表进行统计操作
查询学生表总记录数，执行命令：spark.sql("select count(*) count from student").show

大家试一试，可否使用中文别名。用中文别名要抛出解析异常。

分组统计男女生总年龄，执行命令：spark.sql("select gender, sum(age) from student group by gender").show

分组统计男女生平均年龄，执行命令：spark.sql("select gender, avg(age) from student group by gender").show

分组统计男女生最大年龄，执行命令：spark.sql("select gender, max(age) from student group by gender").show

分组统计男女生最小年龄，执行命令：spark.sql("select gender, min(age) from student group by gender").show

分组统计男女生人数，执行命令：spark.sql("select gender, count(*) count from student group by gender").show

（6）对表进行排序操作
按年龄升序排列，执行命令：spark.sql("select * from student order by age").show

按年龄降序排列，执行命令：spark.sql("select * from student order by age desc").show

先按性别升序，再按年龄降序，执行命令：spark.sql("select * from student order by gender asc, age desc").show

（7）重命名数据表字段
执行命令：spark.sql("select id stu_id, name stu_name, gender stu_gender, age stu_age from student").show

执行命令：spark.sql("select id 学号, name 姓名, gender 性别, age 年龄 from student").show()，无法解析中文别名

五、课后作业
成绩表，包含四个字段（姓名、语文、数学、英语），只有三条记录
姓名    语文    数学    英语
张钦林    78    90    76
陈燕文    95    88    98
卢志刚    78    80    60
在/home里创建scores.txt文件

将scores.txt文件上传到HDFS上指定目录

基于scores.txt文件，创建scoreDF数据帧

基于scoreDF数据帧进行下列操作
（1）显示数据帧内容
（2）显示数据帧模式信息
（3）对数据帧进行投影操作
（4）对数据帧进行过滤操作
（5）对数据帧进行统计操作
（6）对数据帧进行排序操作
（7）重命名数据帧字段

基于scoreDF数据帧创建临时表score

基于score数据表进行下列操作
（1）查询全部表记录
（2）显示数据表结构
（3）对表进行投影操作
（4）对表进行选择操作
（5）对表进行统计操作
（6）对表进行排序操作
（7）重命名数据表字段