大数据处理讲课笔记4.1 Spark SQL概述、数据帧与数据集

零、本讲学习目标
了解Spark SQL的基本概念
掌握DataFrame的基本概念
掌握Dataset的基本概念
会基于DataFrame执行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")
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")
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行数据

三、数据集 - Dataset
（一）Dataset概述
Dataset是一个分布式数据集，是Spark 1.6中添加的一个新的API。相对于RDD，Dataset提供了强类型支持，在RDD的每行数据加了类型约束。而且使用Dataset API同样会经过Spark SQL优化器的优化，从而提高程序执行效率。
（二）将RDD转成DataSet
一个RDD中有3行数据
（三）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,郑秀芸,女,20
2,王志峰,男,18
3,陈燕文,女,21
4,郑国栋,男,19
5,肖雨涵,男,20

执行命令：vim student.txt，创建student.txt文件
将student.txt上传到HDFS的/input目录（如果目录不存在，就创建起来）

（三）加载数据为Dataset
启动Spark Shell，执行命令：spark-shell --master spark://master:7077

1、读文件得数据集
调用SparkSession对象的read.textFile()可以读取指定路径中的文件内容，并加载为一个Dataset
执行命令：val ds = spark.read.textFile("hdfs://master:9000/input/student.txt")

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

 

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)
})

4、对学生数据集进行操作
（1）显示数据集内容

执行命令：studentDS.show()

可以看到，studentDS中的数据类似于一张关系型数据库的表。
（2）打印数据集模式

执行命令：studentDS.printSchema()

（3）对数据集进行投影操作

显示学生的姓名和年龄字段，执行命令：studentDS.select("name", "age").show()

（4）对数据集进行过滤操作

显示女生记录，执行命令：studentDS.filter("gender == '女'").show()

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

 

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

 
（5）对数据集进行统计操作

求20岁以上的女生人数

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

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

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

 

（6）对数据集进行排序操作

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

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

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

（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()

（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(studentDF("age").desc).show()

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

（7）重命名数据帧字段

执行命令：df.select(df("id").as("学号"), df("name").as("姓名"), df("gender").as("性别"), df("age").as("年龄")).show()

（六）基于数据帧进行SQL查询
1、基于数据帧创建临时视图
基于学生数据帧studentDF，创建一个临时视图student，就可以对student视图进行SQL操作
执行命令：studentDF.createTempView("student")

 
 其实，也可以执行命令：studentDF.createOrReplaceTempView("student")

 如果指定的视图不存在，那就创建，如果存在，那就替换。
2、使用spark对象执行SQL查询
在Spark Shell环境里，系统已经创建了名为spark的SparkSession对象
（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(id) 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()，无法解析中文别名