Spark SQL基础知识

一、Spark SQL 简介

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

1.1 为什么要学习Spark SQL？

我们已经学习了Hive，它是将Hive SQL转换成MapReduce然后提交到集群上执行，大大简化了编写MapReduce的程序的复杂性，由于MapReduce这种计算模型执行效率比较慢。所以Spark SQL的应运而生，它是将Spark SQL转换成RDD，然后提交到集群执行，执行效率非常快！同时Spark SQL也支持从Hive中读取数据。

1.2 Spark SQL的特点：

（1）容易整合（集成）

（2）统一的数据访问方式

（3）兼容Hive

（4）标准的数据连接

二、基本概念：Datasets和DataFrames

2.1 DataFrame

DataFrame是组织成命名列的数据集。它在概念上等同于关系数据库中的表，但在底层具有更丰富的优化。DataFrames可以从各种来源构建

例如：

结构化数据文件
hive中的表
外部数据库或现有RDDs

DataFrame API支持的语言有Scala，Java，Python和R。

从上图可以看出，DataFrame多了数据的结构信息，即schema。RDD是分布式的 Java对象的集合。DataFrame是分布式的Row对象的集合。DataFrame除了提供了比RDD更丰富的算子以外，更重要的特点是提升执行效率、减少数据读取以及执行计划的优化

2.2 Datasets

Dataset是数据的分布式集合。Dataset是在Spark 1.6中添加的一个新接口，是DataFrame之上更高一级的抽象。它提供了RDD的优点（强类型化，使用强大的lambda函数的能力）以及Spark SQL优化后的执行引擎的优点。一个Dataset 可以从JVM对象构造，然后使用函数转换（map， flatMap，filter等）去操作。 Dataset API 支持Scala和Java。 Python不支持Dataset API。

三、测试数据

使用员工表的数据，并已经将其保存到了HDFS上。

创建两个文件：emp.csv、dept.csv （注意：文件类型“.csv”）

emp.csv的内容如下:

7369,SMITH,CLERK,7902,1980/12/17,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
7839,KING,PRESIDENT,,1981/11/17,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
7900,JAMES,CLERK,7698,1981/12/3,950,,30
7902,FORD,ANALYST,7566,1981/12/3,3000,,20
7934,MILLER,CLERK,7782,1982/1/23,1300,,10

dept.csv的内容如下：

10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

四、创建DataFrames

4.1 通过Case Class创建DataFrames

（1）定义case class（相当于表的结构：Schema）

注意：由于mgr和comm列中包含null值，简单起见，将对应的case class类型定义为String

（2）将HDFS上的数据读入RDD，并将RDD与case Class关联

（3）将RDD转换成DataFrames

（4）通过DataFrames查询数据

4.2 使用Spark Session

（1）什么是SparkSession
Apache Spark 2.0引入了SparkSession，其为用户提供了一个统一的切入点来使用Spark的各项功能，并且允许用户通过它调用DataFrame和Dataset相关API来编写Spark程序。最重要的是，它减少了用户需要了解的一些概念，使得我们可以很容易地与Spark交互。

在2.0版本之前，与Spark交互之前必须先创建SparkConf和SparkContext。然而在Spark 2.0中，我们可以通过SparkSession来实现同样的功能，而不需要显式地创建SparkConf, SparkContext 以及 SQLContext，因为这些对象已经封装在SparkSession中。

（2）创建StructType，来定义Schema结构信息

val myschema = StructType(List(StructField("empno", 
DataTypes.IntegerType), StructField("ename", 
DataTypes.StringType),StructField("job", 
DataTypes.StringType),StructField("mgr", 
DataTypes.StringType),StructField("hiredate", 
DataTypes.StringType),StructField("sal", 
DataTypes.IntegerType),StructField("comm", 
DataTypes.StringType),StructField("deptno", 
DataTypes.IntegerType)))

注意：需要引入import org.apache.spark.sql.types._

（3）读入数据并且切分数据

（4）将RDD中的数据映射成Row

注意：需要导入import org.apache.spark.sql.Row

（5）创建DataFrames

val df = spark.createDataFrame(rowRDD,myschema)

4.3 使用JSon文件来创建DataFame

（1）源文件：$SPARK_HOME/examples/src/main/resources/people.json

（2）val df = spark.read.json(“源文件”)

（3）查看数据和Schema信息

五、DataFrame操作

DataFrame操作也称为无类型的Dataset操作

（1）查询所有的员工姓名

（2）查询所有的员工姓名和薪水，并给薪水加100块钱

（3）查询工资大于2000的员工

（4）求每个部门的员工人数

完整的例子，请参考： 链接:
http://spark.apache.org/docs/2.1.0/api/scala/index.html#org.apache.spark.sql.Dataset

（*）在DataFrame中使用SQL语句

①　将DataFrame注册成表（视图）：df.createOrReplaceTempView("emp")

②　执行查询：

spark.sql("select * from emp").show
spark.sql("select * from emp where deptno=10").show
spark.sql("select deptno,sum(sal) from emp group by deptno").show

六、Global Temporary View

上面使用的是一个在Session生命周期中的临时views。在Spark SQL中，如果你想拥有一个临时的view，并想在不同的Session中共享，而且在application的运行周期内可用，那么就需要创建一个全局的临时view。并记得使用的时候加上global_temp作为前缀来引用它，因为全局的临时view是绑定到系统保留的数据库global_temp上。

（1）创建一个普通的view和一个全局的view

df.createOrReplaceTempView("emp1")
df.createGlobalTempView("emp2")

（2）在当前会话中执行查询，均可查询出结果。

spark.sql("select * from emp1").show
spark.sql("select * from global_temp.emp2").show

（3）开启一个新的会话，执行同样的查询

spark.newSession.sql("select * from emp1").show     （运行出错）
spark.newSession.sql("select * from global_temp.emp2").show

七、创建Datasets

DataFrame的引入，可以让Spark更好的处理结构数据的计算，但其中一个主要的问题是：缺乏编译时类型安全。为了解决这个问题，Spark采用新的Dataset API (DataFrame API的类型扩展)。

Dataset是一个分布式的数据收集器。这是在Spark1.6之后新加的一个接口，兼顾了RDD的优点（强类型，可以使用功能强大的lambda）以及Spark SQL的执行器高效性的优点。所以可以把DataFrames看成是一种特殊的Datasets，即：Dataset(Row)

7.1 创建DataSet （方式一：使用序列）

（1）定义case class

case class MyData(a:Int,b:String)

（2）生成序列，并创建DataSet

val ds = Seq(MyData(1,"Tom"),MyData(2,"Mary")).toDS

（3）查看结果

7.2 创建DataSet，（方式二：使用JSON数据）

（1）定义case class

case class Person(name: String, gender: String)

（2）通过JSON数据生成DataFrame

val df = spark.read.json(sc.parallelize("""{"gender":
"Male", "name": "Tom"}""" :: Nil))

（3）将DataFrame转成DataSet

df.as[Person].show
df.as[Person].collect

7.3 创建DataSet，（方式三：使用HDFS数据）

（1）读取HDFS数据，并创建DataSet

val linesDS = spark.read.text("hdfs://hadoop111:9000/data/data.txt").as[String]

（2）对DataSet进行操作：分词后，查询长度大于3的单词

val words = linesDS.flatMap(_.split(" ")).filter(_.length > 3)
words.show
words.collect

（3）执行WordCount程序

val result = linesDS.flatMap(_.split(" ")).map((_,1)).groupByKey(x => x._1).count
result.show

排序：
result.orderBy($"value").show

八、Datasets的操作案例

数据导入：emp.json

{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":"7902","hiredate":"1980/12/17","sal":800,"comm":"","deptno":20}
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":"7698","hiredate":"1981/2/20","sal":1600,"comm":"300","deptno":30}
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":"7698","hiredate":"1981/2/22","sal":1250,"comm":"500","deptno":30}
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":"7839","hiredate":"1981/4/2","sal":2975,"comm":"","deptno":20}
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":"7698","hiredate":"1981/9/28","sal":1250,"comm":"1400","deptno":30}
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":"7839","hiredate":"1981/5/1","sal":2850,"comm":"","deptno":30}
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":"7839","hiredate":"1981/6/9","sal":2450,"comm":"","deptno":10}
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":"7566","hiredate":"1987/4/19","sal":3000,"comm":"","deptno":20}
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":"","hiredate":"1981/11/17","sal":5000,"comm":"","deptno":10}
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":"7698","hiredate":"1981/9/8","sal":1500,"comm":"0","deptno":30}
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":"7788","hiredate":"1987/5/23","sal":1100,"comm":"","deptno":20}
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":"7698","hiredate":"1981/12/3","sal":950,"comm":"","deptno":30}
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":"7566","hiredate":"1981/12/3","sal":3000,"comm":"","deptno":20}
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":"7782","hiredate":"1982/1/23","sal":1300,"comm":"","deptno":10}

8.1 使用emp.json 生成DataFrame

val empDF = spark.read.json("/root/resources/emp.json")

查询工资大于3000的员工

empDF.where($"sal" >= 3000).show

8.2 创建case class

case class Emp(empno:Long,ename:String,job:String,
hiredate:String,mgr:String,sal:Long,comm:String,deptno:Long)

8.3 生成DataSets，并查询数据

val empDS = empDF.as[Emp]

查询工资大于3000的员工

empDS.filter(_.sal > 3000).show

查看10号部门的员工

empDS.filter(_.deptno == 10).show

8.4 多表查询

（1）创建部门表

val deptRDD=sc.textFile("/root/temp/dept.csv").map(_.split(","))
case class Dept(deptno:Int,dname:String,loc:String)
val deptDS = deptRDD.map(x=>Dept(x(0).toInt,x(1),x(2))).toDS

（2）创建员工表

case class
Emp(empno:Int,ename:String,job:String,mgr:String,hiredate:String,sal:Int,comm:String,deptno:Int)

val empRDD = sc.textFile("/root/temp/emp.csv").map(_.split(","))

val empDS = empRDD.map(x =>
Emp(x(0).toInt,x(1),x(2),x(3),x(4),x(5).toInt,x(6),x(7).toInt)).toDS

（3）执行多表查询：等值链接

val result = deptDS.join(empDS,"deptno")

另一种写法：注意有三个等号

val result = deptDS.joinWith(empDS,deptDS("deptno")===empDS("deptno"))

joinWith和join的区别是连接后的新Dataset的schema会不一样

（4）查看执行计划：result.explain