Spark-SQL入门介绍+动手小实验

什么是 SparkSQL

• Spark SQL 是 Spark 用来处理结构化数
  据（Structure Data）的一个模块。
• 它提供了一个编程抽象结构叫做
  DataFrame 。

Spark SQL 的特点

• 容易整合（集成）——可混合SQL进行数据查询
• 统一的数据访问方式——支持多种数据源，例如csv,
  json, JDBC, Hive等
• 采用标准的数据连接——支持 JDBC(Java Database
  Connectivity) 和 ODBC(Open Database
  Connectivity)

Spark SQL执行过程

如下图所示，分为加载，创建DataFrame，操作DataFrame，保存四个部分。

有了整体的框架后，我们来了进一步解一下DataFrame是什么，以及它与RDD之间又有什么区别呢？

什么是 DataFrame

• DataFrame是一个表格型的数据结构，它含有一组有序的列，每列可以是不同额的值类型（数值、字符串、布尔值等）。
• 在Spark中，DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库中的二维表格。
• DataFrame既有行索引也有列索引。
  

DataFrame 特点

• 有 Schema 信息，数据结构和类型。
• 和 Hive 一样，都支持嵌套类型如（Struct，Array，
  Map）
• 提供高层次的关系操作，比 RDD API 更简单易用

RDD 和 DataFrame 主要区别

• DataFrame有Schema信息。

好处是什么呢？这使得Spark SQL得以洞察更多的结构信息，从而对藏于DataFrame背后的数据源以及作用于DataFrame之上的变换进行了针对性的优化，最终达到大幅提升运行时效的作用。

而RDD，由于无从得知所存数据元素的具体内部结构，Spark Core只能在stage层面进行简单、通用的流水线优化。

• DataFrame = Schema（表结构）+ Data（表数据）

---

插个题外知识，用来进一步对比RDD与DataFrame的区别。

什么是 DataSet

• DataSet是分布式数据集合，它提供了强类型支持，也是在RDD的每行数据加了类型约束。
• DataSet可以通过JVM的对象进行构建，可以用函数式的转换（map/flatmap/filter）进行多种操作。

---

咱们用图说话，看看同样的数据，对于RDD、DataFrame、Dataset到底长啥样：

DataFrame创建

• RDD.toDF() 转换为 DataFrame
• SparkSession.createDataFrame()

RDD.toDF()

>>>rdd=sc.parallelize([('Michael',29),('Andy',30),('Justin',19)])
>>>df=rdd.toDF() # 类型自动推断，默认列名
DataFrame[_1: string, _2: bigint]
>>>df=rdd.toDF(['name','age']) # 指定列名，没有指定类型
>>>df.collect()
[Row(name='Michael', age=29),
 Row(name='Andy', age=30),
 Row(name='Justin', age=19)]
>>>df.dtypes
[('name', 'string'), ('age', 'bigint')]

RDD.toDF(Schema)

• Schema 是 StructType（pyspark.sql.types.StrutType）
• StructField(列名，类型，是否可以为None)

>>>from pyspark.sql.types import *
>>>rdd1=sc.parallelize([('Michael',29),('Andy',30),('Justin',19)])
>>>schema = StructType([StructField("name",StringType(), True),StructField("age", IntegerType(),True)])
>>>df = rdd1.toDF(schema)
>>>df.collect()
[Row(name='Michael', age=29),
Row(name='Andy', age=30),
Row(name='Justin', age=19)]
>>>df.dtypes
[('name', 'string'), ('age', 'int')]

---

StructField具体有哪些呢？下面罗列给大家：
官方文档传送门：pyspark.sql.types module
DataType
NullType
BinaryType
DateType
TimestampType
DecimalType
DoubleType
FloatType
ByteType
IntegerType
LongType
ShortType
ArrayType
MapType
StringType()

RDD.toDF(‘列名：类型，…’)

>>>from pyspark.sql.types import *
>>>rdd1=sc.parallelize([('Michael',29),('Andy',30),('Justin',19)])
>>>df = rdd1.toDF("name: string, age: int")
>>>df.collect()
[Row(name='Michael', age=29),
 Row(name='Andy', age=30),
 Row(name='Justin', age=19)]

前面我们讲到DataFrame类似二维数组，那么我们来尝试一下以二维数组的方式操作一下看看

>>>rdd1=sc.parallelize([('Michael',29),('Andy',30),('Justin',19)])
>>> df = rdd1.toDF("name:string,age:int")
>>> rows=df.collect()
>>> rows[0]
Row(name='Michael', age=29)
>>> rows[0][0]
'Michael'
>>> rows[0]['name'] #试试 rows[0].name
'Michael'
>>> rows[2]['age'] #这行输出什么？

SparkSession.createDataFrame()

• createDataFrame(data, schema=None,samplingRatio=None,verifySchema=True)

• schema ：指定表结构。

• samplingRatio：指定用于类型推断的样本的比例。含义是：如果df的某列的类型不确定，则抽样百分之samplingRatio的数据来看是什么类型。

• verifySchema：验证每行的数据类型是否符合schema的定义。

#1 data每行数据为元组，没有指定schema
spark.createDataFrame([('Alice', 1)]).collect()
[Row(_1='Alice', _2=1)]

#2 data每行数据是dict，没有指定schema
spark.createDataFrame( [{'name': 'Alice', 'age':1}]).collect()

#3 data用rdd，没有指定schema
rdd = sc.parallelize([('Alice', 1)])
spark.createDataFrame(rdd).collect()

#4 data每行数据用Row，没有指定schema
from pyspark.sql import Row
spark.createDataFrame([Row('Alice',1)]).collect()

#5 data每行数据用元组，指定schema为列表
spark.createDataFrame([('Alice', 1)], ['name','age']).collect()

#6 data用rdd，指定schema为列表
rdd = sc.parallelize([('Alice', 1)])
df = spark.createDataFrame(rdd, ['name', 'age']).collect()

进入正片环节，实操实验

实验素材：
实验源数据传送门
https://pan.baidu.com/s/1cXF22MqeySyIaj2q4gpRaQ#提取码:gpj6

实验环境：
• 操作系统：Ubuntu 16.04
• Spark2.x
• PyCharm 或 Jupyter Notebook

---

实验要求：
（1）创建RDD，并将RDD转为DataFrame。
（2）调用 createOrReplaceTempView，创建临时视图名为book030
（3）使用 SQL 语句查询前10条数据。
（4）统计书名包含“微积分”的书的数量
（5）查询评分大于等于9分，小于等于9.5分，且书名包含“艺术”的书，只展示前10条的序号、书名和评分，按分数从高到低排列。
（6）计算所有书名包含“艺术”的评分平均值
（7）统计出版书数量最大的出版社前10名，按数量从高到低排列。

---

创建RDD

from pyspark.sql.types import *
from pyspark.sql import SQLContext,Row,SparkSession,functions
from pyspark import SparkContext

# 将数据的rating列改成float类型，因为字符串在后面avg运算时会出错
def tofloat(x):
    x[2] = float(x[2])
    return x

# read csv file
book = sc.textFile('./book.txt') 
book_rdd1 = book.map(lambda line:line.split(','))
book_rdd2 = book_rdd1.map(lambda x:tofloat(x))

# 生成DataFrame
book_df = book_rdd2.toDF("id:string, name:string, rating:float, price:string, publish:string, url:string")

book_df.show(5)

效果图如下：

创建临时视图

# 调用createOrReplaceTempView，创建临时视图
book_df.createOrReplaceTempView('book030')
book_df.printSchema()

效果图如下，这是我们打印出来的Schema信息

使用SQL语句查询前10条数据

# SQL语句
spark.sql("select * from book030 limit 10").show()
spark.sql("select * from book030").show(10)
# DataFrame操作
book_df.show(10)

上面的代码效果都是一样的，效果图如下。(ps:怎么会有书能卖2400那么贵？！？)

统计书名包含“微积分”的书的数量

spark.sql("select count(*) as count from book030 where name like '%微积分%'").show()

效果图就不上啦，count的结果是23~

查询评分大于等于9分，小于等于9.5分，且书名包含“艺术”的书，只展示前10条的序号、书名和评分，按分数从高到低排列。

乍一看是不是要求很多，没关系，我们先分析一波，看看要用到哪些SQL语句，“评分大于等于9分，小于等于9.5分，且书名包含“艺术”的书”，这里肯定是一波WHERE ... AND ...带走，接下来再看"只展示前10条的序号、书名和评分"，说明就是SELECT A,B,C ... LIMIT 10,我们只查询3列中的10条数据，最后"按分数从高到低排列" ,这还不容易，嘿嘿直接ORDER BY ... DESC

---

好我们来看一下使用SQL和DataFrame操作分别怎么写

# 使用DataFrame方法
task = book_df.select('id','name','rating').where("name like '%艺术%'")
task1 = task.where("rating <=9.5")
task2 = task1.where("rating >9.0").show(10)

# 使用SQL语句
spark.sql('select id,name,rating from book030 where name like "%艺术%" and rating<=9.5 and rating>=9.0 order by rating desc').show(10)
spark.sql('select id,name,rating from book030 where name like "%艺术%" and rating<=9.5 and rating>=9.0 order by rating desc limit 10').show()

效果图如下：

计算所有书名包含’艺术’的评分平均值

这里注意funtions是需要导入模块的，如果报错记得from pyspark.sql import functions

# 使用SQL语句
spark.sql("select avg(rating) from book030 where name like '%艺术%'").show()

# 计算所有书名包含'艺术'的评分平均值
book_df.where("name like '%艺术%'").agg(functions.avg("rating")).show()

平均值如下：

统计出版书数量最大的出版社前10名，按数量从高到低排列

# 使用SQL语句
spark.sql('select publish,count(publish) from book030 group by publish order by count(publish) desc limit 10').show()

效果图如下：

总结

SparkSQL的常用操作，其实跟操作数据库无异。需要注意的是数据库对表的操作，在SparkSQL中变成了对视图的操作。

源数据的坑

如果直接拿Book.txt转换成RDD是会报错的，为啥呢，我们查看一下就不难发现，源文件里头含有列名字段，所以会出现类型无法转换的报错。手动删除列名即可。