Spark学习之路（九）——Spark SQL

1、Spark SQL架构

使用Spark核心API的应用以SparkContext对象作为程序入口，而Spark SQL应用则以SparkSession对象作为程序入口。SparkSession实质上是SQLContext和HiveContext的组合。在交互式shell中，入口SparkSession初始化的实例名为spark，实例中包含对元数据库的引用。
Spark SQL很好地支持SQL查询，一方面可编写Spark应用程序使用SQL语言进行数据查询；另一方面可使用标准数据库连接器（JDBC/ODBC）连接Spark进行SQL查询。

2、DataFrame与RDD的异同

DataFrame与RDD都是分布式数据集合。DataFrame提供了详细的结构信息（schema），即DataFrame所表示的二维表数据集的每一列都有列名和类型。RDD无法得知所存数据元素的具体内部结构。DataFrame的各种转换操作和RDD一样，都采用惰性机制，只记录DAG图。

3、DataFrame的创建

from pyspark import SparkConf,SparkContext
from pyspark.sql import SparkSession

conf=SparkConf().setMaster("local[*]").setAppName("DF_operation")
sc = SparkContext(conf = conf)
spark=SparkSession.builder.appName(“DF”).enableHiveSupport().getOrCreate()

rdd = sc.parallelize([("Rong",24),("Hainan",23)])
df = spark.createDataFrame(rdd)

print(df.collect())

#output
#[Row(_1='Rong', _2=24), Row(_1='Hainan', _2=23)]

①从已有RDD创建DataFrame
.createDataFrame()
语法：SparkSession.createDataFrame(data,schema=None,samplingRatio=None)
功能：.createDataFrame()方法从已有RDD创建DataFrame对象。参数data接收由元组或列表元素组成的RDD对象；参数schema表示要对DataFrame对象投影的表结构；参数samplingRatio用于设置数据采样率。

②从Hive表创建DataFrame
(1).sql()
语法：SparkSession.sql(sqlQuery)
功能：.sql()方法根据提供的sqlQuery参数，从Hive表或对Hive表执行的DML（数据操作语言）操作创建DataFrame对象。参数sqlQuery可以接收任意有效的HiveQL语句（包括WHERE、SELECT *、SELECT）。

(2).table()
语法：SparkSession.table(tableName)
功能：.table()方法从Hive表创建DataFrame对象。与.sql()方法的区别是，.table()方法不允许对原始表的列进行裁剪，也不能使用WHERE子句进行过滤部分行。

③从JSON对象创建DataFrame
JSON是在网络服务响应中经常使用的一种常见的、标准的、可读的序列化方式或数据传输格式。
.read.json()
语法：DataFrameReader.read.json(path, schema=None, primitivesAsString=None, prefersDecimal=None, allowComments=None, allowUnquotedFieldNames=None, allowSingleQuotes=None, allowNumericLeadingZero=None, allowBackslashEscapingAnyCharacter=None, mode=None, columnNameOfCorruptRecord=None, dateFormat=None, timestampFormat=None, multiline=None)
功能：DataFrameReader的.json()方法从JSON文件创建DataFrame对象（DataFrameReader可通过SparkSession对象的read方法访问到）。参数path指向JSON文件的完整路径（本地文件系统或HDFS之类的远程文件系统）；参数schema可显示指定生成的DataFrame的表结构；其他参数用来指定相关格式。

④从普通文件创建DataFrame
(1).text()
语法：DataFrameReader.read.text(path)
功能：DataFrameReader的.text()方法用于从外部文件系统（本地文件系统、NFS、HDFS、S3等）上的文本文件读取DataFrame。该方法类似于RDD中的sc.textFile()方法。参数path可以是一个文件，也可是目录，或文件通配符（通配符类似于正则表达式，返回满足指定条件的文件列表）。

(2).parquet()
Parquet是一种常见的通用列式存储格式，Parquet是官方推荐用于Spark SQL处理的存储格式。Parquet格式将表的结构信息与数据封装在一个结构中，Spark默认使用Gzip编码来压缩Parquet文件，更改如下（如Snappy）：
sqlContext.setConf(“spark.sql.parquet.compression.codec.”, ”snappy”)
语法：DataFrameReader.read.parquet(paths)
功能：DataFrameReader的.parquet()方法用于读取以Parquet列式存储格式存储的文件。参数paths指向单个或者多个Parquet文件，或是Parquet文件组成的目录。

(3).orc()
ORC是一种来自Hive项目的格式。
语法：DataFrameReader.read.orc(path)
功能：DataFrameReader的.orc()方法用于从单个ORC格式文件或由多个ORC格式文件组成的目录读取DataFrame。参数path指向一个包含ORC文件的目录。

4、将DataFrame转为RDD

语法：DataFrame.rdd

5、DataFrame数据模型（pyspark.spl.types）

原生类型

类型	等价Hive类型	等价Python类型
ByteType	TINYINT	int
ShortType	SMALLINT	int
IntegerType	INT	int
LongType	BIGINT	long
FloatType	FLOAT	float
DoubleType	DOUBLE	float
BooleanType	BOOLEAN	bool
StringType	STRING	string
BinaryType	BINARY	bytearray
TimestampType	TIMESTAMP	datetime.datetime
DateType	DATE	datetime.date

复杂类型

类型	等价Hive类型	等价Python类型
ArrayType	ARRAY	list、tuple或array
MapType	MAP	dict
StructType	STRUCT	list或tuple

6、推断DataFrame表结构

语法：DataFrame.printSchema()
功能：.printSchema()方法将表结构以树状格式打印到控制台上。

7、定义DataFrame表结构

（创建一个包含一组StructField对象的StructType对象）

from pyspark.sql.types import *

myschema = StructType([StructField(“station_id”,IntegerType(),True),StructField(“name”,StringType(),True),StructField(“age”,FloatType(),True)])

rdd = sc.parallelize([(0,"Rong",24),(1,"Hainan",22),(2,"Qing",23)]).map(lambda x:(int(x[0]),str(x[1]),float(x[2])))

df = spark.createDataFrame(rdd,myschema)
df.show()
df.printSchema()

#output:
#+----------+------+----+
#|station_id|  name| age|
#+----------+------+----+
#|         0|  Rong|24.0|
#|         1|Hainan|22.0|
#|         2|  Qing|23.0|
#+----------+------+----+
#root
# |-- station_id: integer (nullable = true)
# |-- name: string (nullable = true)
# |-- age: float (nullable = true)

8、DataFrame操作

DataFrame元数据操作
①.columns()
语法：DataFrame.columns()
功能：.columns()方法返回由DataFrame的列名组成的列表。

df.columns

#output
#['station_id', 'name', 'age']

②.dtypes()
语法：DataFrame.dtypes()
功能：.dtypes()方法返回由二元组组成的列表，其中每个二元组包含给定DataFrame对象的一个列名和该列的数据类型。

df.dtypes

#output
#[('station_id', 'int'), ('name', 'string'), ('age', 'float')]

DataFrame基本操作
①.show()
语法：DataFrame.show(n=20, truncate=True)
功能：.show()方法将DataFrame的前n行打印到控制台上。与collect()或take(n)不同，show()并不把结果返回到变量。参数truncate指定是否截取过长的字符串并在单元格内右对齐。

②.select()
语法：DataFrame.select(cols)
功能：.select()方法根据参数cols指定的列返回一个新的DataFrame对象（星号"*"表示选出DataFrame中所有的列而不进行任何操作）。

people = df.select([“name”, “age”])
people.show()

#output
#+------+----+
#|  name| age|
#+------+----+
#|  Rong|24.0|
#|Hainan|22.0|
#|  Qing|23.0|
#+------+----+

③.drop()
语法：DataFrame.drop(col)
功能：.drop()方法返回一个删除了参数col指定的列的新DataFrame。

④.filter()
语法：DataFrame.filter(condition)
功能：.filter()方法返回仅包含满足给定条件的行的新DataFrame，筛选条件由参数condition提供，求值结果为True或False。（.where()方法是.filter()方法的别名，可互相替代）

df1 = df.filter(df.age >= 23)
df1.show()

#output
# +----------+----+----+
#|station_id|name| age|
#+----------+----+----+
#|         0|Rong|24.0|
#|         2|Qing|23.0|
#+----------+----+----+

⑤.distinct()
语法：DataFrame.distinct()
功能：.distinct()方法返回包含输入DataFrame中不重复的行的新DataFrame，本质是过滤掉重复的行。

9、DataFrame内建函数（Spark SQL中可用的内建函数）

类型	可用函数
字符串函数	startswith、substr、concat、lower、upper、regexp_extract、regexp_replace
数学函数	abs、ceil、floor、log、round、sqrt
统计函数	avg、max、min、mean、stddev
日期函数	date_add、datediff、from_utc_timestamp
哈希函数	md5、sha1、sah2
算法函数	soundex、levenshtein
窗口函数	over、rank、dense_rank、lead、lag、ntile

10、在DataFrame中实现自定义函数

.udf()
语法：pyspark.sql.functions.udf(func, returnType=StringType)
功能：.udf()方法创建表示用户自定义函数的列表达式。参数func可以是具名函数或者匿名函数，它对DataFrame中单行内的一列进行操作；参数returnType指定函数返回对象的数据类型（必须为pyspark.sql.types或者pyspark.sql.types.DataType）。

from pyspark.sql.functions import *
from pyspark.sql.types import *

ageadd = udf(lambda x:x+5,FloatType())

df2 = df.select(df.name,ageadd(df.age))
df2.show()

#output
# +------+-------------+
#|  name|<lambda>(age)|
#+------+-------------+
#|  Rong|         29.0|
#|Hainan|         27.0|
#|  Qing|         28.0|
#+------+-------------+

11、多DataFrame操作

①.join()
语法：DataFrame.join(other, on=None, how=None)
功能：.join()方法将当前DataFrame与参数other引用的DataFrame（连接的右侧）做连接操作，使用连接操作的结果创建新的DataFrame；参数on指定一个列、一组列，或者一个表达式，用于连接操作的求值；参数how指定要执行的连接类型（inner（默认）、outer、left_outer、right_outer和leftsemi）

②.orderBy()
语法：DataFrame.orderBy(cols, ascending)
功能：.orderBy()方法根据参数cols指定的列对DataFrame进行排序，生成新的DataFrame。参数ascending是布尔类型，默认值为True，表示升序。(DataFrame中，.sort()和.orderBy()是同义函数)

③.groupBy()
语法：DataFrame.groupBy(cols)
功能：.groupBy()方法将输入的DataFrame按照参数cols指定的列进行分组，使用分组结果创建新的DataFrame。

12、DataFrame缓存、持久化与重新分区

DataFrame支持缓存、持久化与重新分区，方法类似于Spark RDD中的对应操作：cache()、persist()、unpersist()、coalesce()和repartition()等。
Spark SQL还添加了cacheTable()方法，用于在内存中缓存Spark SQL或Hive中的表，clearCache()方法可以从内存中删除缓存的表。

13、保存DataFrame输出

数据写入Hive表
①.saveAsTable()
语法：DataFrame.write.saveAsTable(name, format=None, mode=None, partitionBy=None)
功能：.saveAsTable()方法把DataFrame中的数据写入参数name指定的Hive表中；参数format可指定输出到目标表时使用的格式（默认Partquet）；参数mode指定如果目标表已经存在时使用的行为（append：追加写、overwrite：覆盖、error：报错、ignore：忽略）。

数据写入文件
①.write.csv()
语法：DataFrameWriter.write.csv(path, mode=None, compression=None, sep=None, quote=None, escape=None, header=None, nullValue=None, escapeQuotes=None, quoteAll=None, dateFormat=None, timestampFormat=None, ignoreLeadingWhiteSpace=None, ignoreTrailingWhiteSpace=None)
功能：DataFrameWriter类的.write.csv()方法可以通过DataFrame.write.csv()接口访问，把DataFrame的内容以CSV文件的形式写入参数path指定的路径。

②.write.parquet()
语法：DataFrameWriter.write.parquet(path, mode=None, partitionBy=None)
功能：.write.parquet()方法把DataFrame中的数据以Parquet格式写入指定的目录。