Spark3 入门指南（二）
四、Spark SQL：高级
第三章介绍了 Spark SQL 模块中的基本元素，包括核心抽象、用于操作结构化数据的结构化操作以及各种支持的数据源，用于读取和写入数据。在此基础之上，本章将介绍 Spark SQL 模块中的一些高级功能，并深入了解 Catalyst 优化器和钨引擎提供的优化和执行效率。为了帮助您执行复杂的分析，Spark SQL 提供了一组强大而灵活的聚合功能、连接多个数据集的能力、一大组内置的高性能函数、一种编写您自己的自定义函数的简单方法以及一组高级分析函数。本章详细介绍了这些主题。
聚集
对大数据执行任何有趣而复杂的分析通常都涉及聚合，以汇总数据，从而提取模式或见解或生成摘要报告。聚合通常需要对整个数据集或基于一个或多个列进行分组，然后对每个组应用聚合函数，如求和、计数或平均。Spark 提供了许多常用的聚合函数，并且能够将值聚合到一个集合中，然后可以对其进行进一步分析。行的分组可以在不同的级别上完成，Spark 支持以下级别。
-
将数据帧视为一个组。
-
使用一个或多个列将数据帧分成多个组，并对每个组执行一次或多次聚合。
-
将一个数据帧分成多个窗口，并执行移动平均、累积和或排序。如果窗口是基于时间的，则可以按照翻转或滑动窗口来进行聚合。
聚合函数
在 Spark 中，所有的聚合都是通过函数完成的。聚合函数设计用于对一组行执行聚合，无论这些行是由数据帧中的所有行还是行的子组组成。在 http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$ 可以找到 Scala 语言聚合函数的完整列表。对于 Spark Python APIs，有时在某些功能的可用性方面存在一些差距。
常见聚合函数
本节描述了一组常用的聚合函数，并提供了使用它们的示例。表 4-1 描述了聚合函数。完整列表请见 http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$ 。
表 4-1
常用的聚合函数
|操作
|
描述
|
| --- | --- |
| count(col) | 返回每组的项目数。 |
| countDistinct(col) | 返回每组的唯一项目数。 |
| approx_count_distinct(col) | 返回每组唯一项目的大致数量。 |
| min(col) | 返回每组中给定列的最小值。 |
| max(col) | 返回每组中给定列的最大值。 |
| sum(col) | 返回给定列中每组值的总和。 |
| sumDistinct(col) | 返回每组中给定列的不同值的总和。 |
| avg(col) | 返回每组中给定列的平均值。 |
| skewness(col) | 返回每组中给定列的值分布的偏斜度。 |
| kurtosis(col) | 返回每组中给定列的值的分布的峰度。 |
| variance(col) | 返回每组中给定列的值的无偏方差。 |
| stddev(col) | 返回每组中给定列的值的标准偏差。 |
| collect_list(col) | 返回给定列的值的集合。返回的集合可能包含重复值。 |
| collect_set(col) | 返回给定列的唯一值的集合。 |
为了演示这些函数的用法，让我们使用飞行摘要数据集，该数据集来自位于 www.kaggle.com/usdot/flight-delays/data 的 Kaggle 网站上的数据文件。该数据集包含 2015 年美国国内航班延误和取消情况。清单 4-1 是从这个数据集创建一个 DataFrame 的代码。
val flight_summary = spark.read.format("csv")
.option("header", "true")
.option("inferSchema","true")
.load("<path>/chapter5/data/flights/flight-summary.csv")
// use count action to find out number of rows in this dataset
flight_summary.count()
Long = 4693
Remember the count() function of the DataFrame is an action so it immediately returns a value to us. All the functions listed in Table 5-1 are lazily evaluated functions.
Below is the schema of the flight_summary dataset.
|-- origin_code: string (nullable = true)
|-- origin_airport: string (nullable = true)
|-- origin_city: string (nullable = true)
|-- origin_state: string (nullable = true)
|-- dest_code: string (nullable = true)
|-- dest_airport: string (nullable = true)
|-- dest_city: string (nullable = true)
|-- dest_state: string (nullable = true)
|-- count: integer (nullable = true)
Listing 4-1Create a DataFrame from Reading Flight Summary Dataset
每行代表从出发地机场到目的地机场的航班。“计数”列包含航班的数量。
以下所有聚合示例都是在整个数据帧级别执行聚合。本章后面给出了在子组级别执行聚合的示例。
计数(列)
计数是一种常用的合计方法，用于找出一个组中的项目数。清单 4-2 计算了origin_airport和dest_airport列的计数，正如所料，计数是相同的。为了提高结果列的可读性，可以使用as函数给出一个更友好的列名。注意，您需要调用show动作来查看结果。
flight_summary.select(count("origin_airport"), count("dest_airport").as("dest_count")).show
+--------------------------+---------------+
| count(origin_airport)| dest_count|
+--------------------------+---------------+
| 4693| 4693|
+--------------------------+---------------+
Listing 4-2Computing the Count for Two Columns in the flight_summary DataFrame
当计算一列中的项数时，count(col)函数在计算中不包括空值。为了包含空值，列名应该替换为*。清单 4-3 通过创建一个在某些列中包含空值的小型数据帧来演示这种行为。
import org.apache.spark.sql.Row
case class Movie(actor_name:String, movie_title:String, produced_year:Long)
val badMoviesDF = Seq( Movie(null, null, 2018L),
Movie("John Doe", "Awesome Movie", 2018L),
Movie(null, "Awesome Movie", 2018L),
Movie("Mary Jane", "Awesome Movie", 2018L)).toDF
badMoviesDF.show
+---------------+--------------------+-------------------+
| actor_name| movie_title| produced_year|
+---------------+--------------------+-------------------+
| null| null| 2018|
| John Doe| Awesome Movie| 2018|
| null| Awesome Movie| 2018|
| Mary Jane| Awesome Movie| 2018|
+---------------+--------------------+-------------------+
// now performing the count aggregation on different columns
badMoviesDF.select(count("actor_name"), count("movie_title"), count("produced_year"), count("*")).show
+------------------+-------------------+---------------------+---------+
| count(actor_name)| count(movie_title)| count(produced_year)| count(1)|
+------------------+-------------------+---------------------+---------+
| 2| 3| 4| 4|
+------------------+-------------------+---------------------+---------+
Listing 4-3Counting Items with Null Value
输出表确认了count(col)函数在最终计数中不包含 null。
countDistinct(列)
这个函数做的和它听起来一样。它只计算每组的唯一项目。清单 4-4 显示了countDistinct函数和count函数之间计数结果的差异。事实证明，在 flight_summary 数据集中有 322 个唯一的机场。
flight_summary.select(countDistinct("origin_airport"), countDistinct("dest_airport"), count("*")).show
+-------------------------------+-----------------------------+----------+
| count(DISTINCT origin_airport)| count(DISTINCT dest_airport)| count(1)|
+-------------------------------+-----------------------------+----------+
| 322| 322| 4693|
+-------------------------------+-----------------------------+----------+
approx_count_distinct (col, max_estimated_error=0.05)
Listing 4-4Counting Unique Items in a Group
在一个非常大的数据集中，计算每个组中唯一项目的准确数量是一项昂贵且耗时的工作。在某些用例中，有一个近似的唯一计数就足够了。其中一个用例是在线广告业务，每小时有数亿次广告投放。需要生成一份报告，说明每种类型的会员细分市场的独立访客数量。估算不同项目的数量是计算机科学中一个众所周知的问题。它也被称为基数估计问题。
幸运的是，已经有一个著名的算法叫做 HyperLogLog ( https://en.wikipedia.org/wiki/HyperLogLog )可以用来解决这个问题，Spark 已经在approx_count_distinct函数中实现了这个算法的一个版本。由于唯一计数是近似值，因此存在一定的误差。该函数允许您为该用例指定可接受的估计误差值。清单 4-5 展示了approx._count_distinct函数的用法和行为。随着估计误差的减小，这个函数完成并返回结果所需的时间越来越长。
// let's do the counting on the "count" column of flight_summary DataFrame.
// the default estimation error is 0.05 (5%)
flight_summary.select(count("count"),countDistinct("count"), approx_count_distinct("count", 0.05)).show
+--------------+----------------------+-----------------------------+
| count(count) | count(DISTINCT count)| approx_count_distinct(count)|
+--------------+----------------------+-----------------------------+
| 4693| 2033| 2252|
+--------------+----------------------+-----------------------------+
// to get a sense how much approx_count_distinct function is faster than countDistinct function,
// trying calling them separately
flight_summary.select(countDistinct("count")).show
// specify 1% estimation error
flight_summary.select(approx_count_distinct("count", 0.01)).show
// one my Mac laptop, the approx_count_distinct function took about 0.1 second and countDistinct function took 0.6 second. The larger the approximation estimation error, the less time approx_count_distinct function takes to complete.
Listing 4-5Counting Unique Items in a Group
最小(列)，最大(列)
组中项目的最小值和最大值是范围的两端。这两个函数很容易理解和使用。清单 4-6 从 count 列中提取这两个值。
flight_summary.select(min("count"), max("count")).show
+-------------+----------------+
| min(count)| max(count)|
+-------------+----------------+
| 1| 13744|
+-------------+----------------+
// looks like there is one very busy airport with 13744 incoming flights from another airport. It will be interesting to find which airport
Listing 4-6Get the Minimum and Maximum Values of the Count Column
总和(列)
此函数计算数值列中值的总和。清单 4-7 执行flight_summary数据集中所有航班的总和。
flight_summary.select(sum("count")).show
+---------------+
| sum(count)|
+---------------+
| 5332914|
+---------------+
Listing 4-7Using sum Function to Sum up the Count Values
sumDistinct(列)
这个函数做的和它听起来一样。它只对数值列的不同值求和。flight_summary数据帧中不同计数的总和应小于清单 4-7 中显示的总和。清单 4-8 计算不同值的总和。
flight_summary.select(sumDistinct("count")).show
+------------------------------+
| sum(DISTINCT count)|
+------------------------------+
| 3612257|
+------------------------------+
Listing 4-8Using sumDistinct Function to Sum up the Distinct Count Values
平均值(列)
此函数计算数值列的平均值。这个方便的函数只需将总数除以项目数。让我们看看清单 4-9 能否验证假设。
flight_summary.select(avg("count"), (sum("count") / count("count"))).show
+--------------------------+------------------------------------+
| avg(count)| (sum(count) / count(count))|
+--------------------------+------------------------------------+
| 1136.3549968037503| 1136.3549968037503|
+--------------------------+------------------------------------+
Listing 4-9Computing the Average Value of the Count Column Using Two Different Ways
偏度，峰度
在统计学中，数据集中值的分布揭示了数据集背后的无数故事。偏斜度衡量数据集中值分布的对称性，其值可以是正、零、负或未定义。在正态分布或钟形分布中，偏斜值为 0。正的倾斜表示右边的尾巴比左边的更长或更粗。负的倾斜表示相反的情况，左边的尾巴比右边的长或粗。当偏斜度为 0 时，两边的尾部是均匀的。图 4-1 显示了一个正负偏斜的例子。
图 4-1
https://en.wikipedia.org/wiki/Skewness 的反面和正面歪斜的例子
峰度是对分布曲线形状的一种度量，不管曲线是正态的、平坦的还是尖的。正峰度表示曲线细长而尖，负峰度表示曲线肥胖而平坦。清单 4-10 计算 flight_summary 数据集中计数分布的偏度和峰度。
flight_summary.select(skewness("count"), kurtosis("count")).show
+--------------------------+----------------------------+
| skewness(count)| kurtosis(count)|
+--------------------------+----------------------------+
| 2.682183800064101| 10.51726963017102|
+--------------------------+----------------------------+
Listing 4-10Compute the Skewness and Kurtosis of Column Count
结果表明，计数的分布是不对称的，右尾比左尾长或粗。峰度值表明分布曲线是尖的。
方差(列)，标准差(列)
在统计学中，方差和标准差衡量数据的分散性或分布。换句话说，它们告诉我们这些值与平均值的平均距离。当方差值较低时，这些值接近平均值。方差和标准差是相关的；后者是前者的平方根。图 4-2 显示了来自两个总体的样本，均值相同但方差不同。红色群体的平均值为 100，方差为 100。蓝色群体的平均值为 100，方差为 2500。这个例子出自 https://en.wikipedia.org/wiki/Variance 。
图 4-2
来自 https://en.wikipedia.org/wiki/Variance 两个总体的样本示例
variance和stddev分别计算方差和标准差。Spark 提供了这些功能的两种不同实现；一种使用抽样来加速计算，另一种使用整个人口。清单 4-11 显示了flight_summary数据帧中计数列的方差和标准差。
// use the two variations of variance and standard deviation
flight_summary.select(variance("count"), var_pop("count"), stddev("count"), stddev_pop("count")).show
+-----------------+------------------+------------------+-----------------+
| var_samp(count)| var_pop(count)| stddev_samp(count)| stddev_pop(count)|
+-----------------+------------------+------------------+-----------------+
|1879037.7571558713| 1878637.3655604832| 1370.779981308405| 1370.633928355957|
+-----------------+------------------+------------------+-----------------+
Listing 4-11Compute the Variance and Standard
Deviation Using variance and sttdev Functions
看起来计数值在flight_summary数据帧中相当分散。
分组聚合
本节介绍对一列或多列进行分组的聚合。聚合通常在包含一个或多个分类列的数据集上执行，这些分类列的基数较低。分类值的例子有性别、年龄、城市名称或国家名称。聚合是通过类似于前面提到的函数来完成的。但是，它们不是对数据帧中的全局组执行聚合，而是对每个子组执行聚合。
通过分组执行聚合是一个两步过程。第一步是通过使用groupBy(col1,col2,...)转换来执行分组，这是指定对哪些列进行分组的地方。与其他返回数据帧的转换不同，groupBy转换返回一个RelationalGroupedDataset类的实例，您可以对其应用一个或多个聚合函数。清单 4-12 展示了使用一个列和一个聚合的简单分组。注意groupBy列自动包含在输出中。
flight_summary.groupBy("origin_airport").count().show(5, false)
+------------------------------------------------------+-------+
| origin_airport | count|
+------------------------------------------------------+-------+
|Melbourne International Airport | 1|
|San Diego International Airport (Lindbergh Field) | 46|
|Eppley Airfield | 21|
|Kahului Airport | 18|
|Austin-Bergstrom International Airport | 41|
+------------------------------------------------------+-------+
Listing 4-12Grouping by origin_airport and Perform Count Aggregation
列表 4-12 显示了从墨尔本国际机场(佛罗里达州)出发的航班只飞往另外一个机场。然而，从卡胡鲁伊机场起飞的航班降落在其他 18 个机场中的一个。
为了让事情变得有趣一点，让我们尝试按两列分组来计算城市级别的相同指标。清单 4-13 展示了如何去做。
flight_summary.groupBy('origin_state, 'origin_city).count(). .where('origin_state === "CA").orderBy('count.desc).show(5)
+---------------+------------------+---------+
| origin_state| origin_city| count|
+---------------+------------------+---------+
| CA| San Francisco| 80|
| CA| Los Angeles| 80|
| CA| San Diego| 47|
| CA| Oakland| 35|
| CA| Sacramento| 27|
+---------------+------------------+---------+
Listing 4-13Grouping by origin_state and origin_city and Perform Count Aggregation
除了按两列分组之外，该语句还对行进行筛选，只筛选具有“CA”状态的行。orderBy转换可以轻松识别哪个城市拥有最多的目的地机场。加州的旧金山和洛杉矶拥有最多的目的地机场，这是有道理的。
RelationalGroupedDataset类提供了一组标准的聚合函数，可以用来应用于每个子组。他们是avg(cols), count(), mean(cols), min(cols), max(cols), sum(cols)。除了count()函数，其余的都是对数字列进行操作。
每组多个聚合
有时需要同时对每个组执行多个聚合。例如，除了计数之外，您还想知道最小值和最大值。RelationalGroupedDataset类提供了一个名为agg的非常强大的函数，它采用一个或多个列表达式，这意味着您可以使用任何聚合函数，包括表 4-1 中列出的那些函数。一件很酷的事情是这些聚合函数返回了一个Column类的实例，因此您可以使用提供的函数应用任何列表达式。一个常见的需求是在聚合完成后重命名列，使其更短、更易读、更易于引用。清单 4-14 展示了如何做到这一切。
import org.apache.spark.sql.functions._
flight_summary.groupBy("origin_airport")
.agg(
count("count").as("count"),
min("count"), max("count"),
sum("count")
).show(5)
+--------------------+-------+----------+----------+------------+
| origin_airport| count|min(count)|max(count)| sum(count)|
+--------------------+-------+----------+----------+------------+
|Melbourne Interna...| 1| 1332| 1332| 1332|
|San Diego Interna...| 46| 4| 6942| 70207|
| Eppley Airfield| 21| 1| 2083| 16753|
| Kahului Airport| 18| 67| 8313| 20627|
|Austin-Bergstrom ...| 41| 8| 4674| 42067|
+--------------------+-------+----------+----------+------------+
Listing 4-14Multiple Aggregations After a Group by of origin_airport
默认情况下，聚合列名是聚合表达式，这使得列名有点长，很难引用。因此，一种常见的模式是使用Column.as函数将列重命名为更合适的名称。
多功能的agg函数提供了一种通过基于字符串的键值映射来表达列表达式的额外方法。关键是列名，值是聚合方法，可以是avg, max, min, sum,或count。清单 4-15 提供了这种方法的一个例子。
flight_summary.groupBy("origin_airport")
.agg(
"count" -> "count",
"count" -> "min",
"count" -> "max",
"count" -> "sum")
.show(5)
Listing 4-15Specifying Multiple Aggregations Using a Key-Value Map
结果与清单 4-14 中的结果相同。请注意，重命名聚合结果列名并不容易。与第一种方法相比，这种方法的一个优点是可以通过编程生成地图。当编写生产 ETL 作业或执行探索性分析时，第一种方法比第二种更常用。
收集组值
collect_list(col)和collect_set(col)函数用于在应用分组后收集特定组的所有值。一旦每个组的值被放入一个集合中，就可以自由地以您选择的任何方式操作它们。这些函数的返回集合有一个小小的不同，那就是唯一性。collection_list函数返回包含重复值的集合，而collection_set函数返回包含唯一值的集合。清单 4-16 展示了如何使用the collection_list函数收集从每个始发州出发的超过 5500 个航班的目的地城市。
val highCountDestCities = flight_summary.where('count > 5500)
.groupBy("origin_state")
.agg(collect_list("dest_city")
.as("dest_cities"))
highCountDestCities.withColumn("dest_city_count",
size('dest_cities))
.show(5, false)
+------------+------------------------------------+----------------+
|origin_state| dest_cities | dest_city_count|
+------------+------------------------------------+----------------+
| AZ| [Seattle, Denver, Los Angeles]| 3|
| LA| [Atlanta] | 1|
| MN| [Denver, Chicago] | 2|
| VA| [Chicago, Boston, Atlanta] | 3|
| NV|[Denver, Los Angeles, San Francisco]| 3|
+------------+------------------------------------+----------------+
Listing 4-16Using collection_list to Collect High Traffic Destination Cities Per Origin State
旋转聚合
透视是一种汇总数据的方法，方法是指定一个分类列，然后对其他列执行聚合，以便将分类值从行转置到单独的列中。思考旋转的另一种方式是，它是一种在应用一个或多个聚合时将行转换为列的方式。这种技术通常用于数据分析或报告。透视过程从对一列或多列进行分组开始，在一列上进行透视，最后在一列或多列上应用一个或多个聚合结束。
清单 4-17 显示了一个学生小数据集的透视示例，其中每行包含学生的姓名、性别、体重和毕业年份。旋转使得计算每个毕业年度每个性别的平均体重变得容易。
import org.apache.spark.sql.Row
case class Student(name:String, gender:String, weight:Int, graduation_year:Int)
val studentsDF = Seq(Student("John", "M", 180, 2015),
Student("Mary", "F", 110, 2015),
Student("Derek", "M", 200, 2015),
Student("Julie", "F", 109, 2015),
Student("Allison", "F", 105, 2015),
Student("kirby", "F", 115, 2016),
Student("Jeff", "M", 195, 2016)).toDF
// calculating the average weight for gender per graduation year
studentsDF.groupBy("graduation_year").pivot("gender")
.avg("weight").show()
+----------------+------+---------+
| graduation_year| F| M|
+----------------+------+---------+
| 2015| 108.0| 190.0|
| 2016| 115.0| 195.0|
+----------------+------+---------+
Listing 4-17Pivoting on a Small Dataset
此示例只有一个聚合，性别分类列只有两个可能的唯一值；因此，结果表只有两列。如果性别列有三个可能的唯一值，则结果表中有三列。您可以利用agg函数来执行多个聚合，从而在结果表中创建更多的列。清单 4-18 是对清单 4-17 中的数据帧执行多重聚合的一个例子。
studentsDF.groupBy("graduation_year").pivot("gender")
.agg(
min("weight").as("min"),
max("weight").as("max"),
avg("weight").as("avg")
).show()
+---------------+------+-------+-------+-------+-------+------+
|graduation_year| F_min| F_max| F_avg| M_min| M_max| M_avg|
+---------------+------+-------+-------+-------+-------+------+
| 2015| 105| 110| 108.0| 180| 200| 190.0|
| 2016| 115| 115| 115.0| 195| 195| 195.0|
+---------------+------+-------+-------+-------+-------+------+
Listing 4-18Multiple Aggregations After Pivoting
在结果表的分组列之后添加的列数是透视列的唯一值数和聚合数的乘积。
如果透视列有许多不同的值，您可以有选择地选择为哪些值生成聚合。清单 4-19 展示了如何为旋转函数指定值。
studentsDF.groupBy("graduation_year").pivot("gender", Seq("M"))
.agg(
min("weight").as("min"),
max("weight").as("max"),
avg("weight").as("avg")
).show()
+---------------------+---------+----------+---------+
| graduation_year| M_min| M_max| M_avg|
+---------------------+---------+----------+---------+
| 2015| 180| 200| 190.0|
| 2016| 195| 195| 195.0|
+---------------------+---------+----------+---------+
Listing 4-19Selecting Values of Pivoting Column to Generate the Aggregations For
为透视列指定不同值的列表可以加快透视过程。否则，Spark 会花费一些精力自己找出一系列不同的值。
连接
为了执行任何复杂而有趣的数据分析或操作，您通常需要通过连接过程将来自多个数据集的数据集合在一起。在 SQL 术语中，这是一种众所周知的技术。执行连接会合并两个数据集(可以不同也可以相同)的列，合并后的数据集包含两端的列。这使您能够进一步分析组合的数据集，这样就不可能对每个数据集都进行分析。让我们以一家在线电子商务公司的两个数据集为例。一个表示包含哪些客户购买了哪些产品的信息的交易数据(也称为事实表)。另一个表示每个客户的信息(也称为维度表)。通过连接这两个数据集，您可以了解哪些产品在年龄或位置方面更受特定客户群的欢迎。
本节介绍如何使用join转换在 Spark SQL 中执行连接，以及它支持的各种类型的连接。本节的最后一部分描述了 Spark SQL 如何在内部执行连接。
Note
在使用 SQL 执行数据分析的世界中，连接是一种经常使用的技术。如果您是 SQL 新手，强烈建议您学习基本概念和不同种类的连接。维基百科。org/ wiki/ Join_ (SQL) 。 www.w3schools.com/sql/sql_join.asp 提供了一些关于连接的教程。
连接表达式和连接类型
执行两个数据集的连接需要您指定两条信息。第一个是一个连接表达式，它指定每一侧的哪些列应该确定两个数据集中的哪些行包含在连接的数据集中。第二个是连接类型，它决定了连接数据集中应包含的内容。表 4-2 提供了 Spark SQL 中支持的连接类型列表。
表 4-2
连接类型
|类型
|
描述
| | --- | --- | | 内部联接(又称等联接) | 当连接表达式的计算结果为 true 时，返回两个数据集中的行。 | | 左外部连接 | 即使联接表达式的计算结果为 false，也从左侧数据集中返回行。 | | 右外部联接 | 即使联接表达式的计算结果为 false，也从正确的数据集中返回行。 | | 外部连接 | 即使联接表达式的计算结果为 false，也从两个数据集中返回行。 | | 左反连接 | 当连接表达式的计算结果为 false 时，仅返回左侧数据集中的行。 | | 左半连接 | 当连接表达式的计算结果为 true 时，仅返回左侧数据集中的行。 | | 十字架(又名笛卡尔坐标) | 通过将左侧数据集中的每一行与右侧数据集中的每一行进行组合来返回行。行数是每个数据集大小的乘积。 |
为了帮助可视化一些连接类型，图 4-3 显示了一组来自 https://en.wikipedia.org/wiki/Join_ (SQL)#Outer_join 的常见连接类型的文氏图。
图 4-3
常见连接类型的维恩图
使用连接
我使用了两个小的数据帧来演示如何在 Sparking SQL 中执行连接。第一个表示雇员的列表，每行包含雇员的姓名和他们所属的部门。第二个包含一个部门列表，每行包含一个部门 ID 和部门名称。清单 4-20 包含创建这两个数据帧的代码片段。
case class Employee(first_name:String, dept_no:Long)
val employeeDF = Seq( Employee("John", 31),
Employee("Jeff", 33),
Employee("Mary", 33),
Employee("Mandy", 34),
Employee("Julie", 34),
Employee("Kurt", null.asInstanceOf[Int])
).toDF
case class Dept(id:Long, name:String)
val deptDF = Seq( Dept(31, "Sales"),
Dept(33, "Engineering"),
Dept(34, "Finance"),
Dept(35, "Marketing")
).toDF
// register them as views so we can use SQL for perform joins
employeeDF.createOrReplaceTempView("employees")
deptDF.createOrReplaceTempView("departments")
Listing 4-20Creating Two Small DataFrames to Use in the Following Join Type Examples
内部联接
这是最常用的连接类型，其连接表达式包含两个数据集的列的相等比较。仅当连接表达式被评估为 true 时，连接的数据集才包含这些行；换句话说，两个数据集中的连接列值是相同的。不具有匹配列值的行将从连接的数据集中排除。如果连接表达式使用相等比较，那么连接表中的行数只能与较小数据集的大小一样大。内部连接是 Spark SQL 中的默认连接类型，因此在连接转换中指定它是可选的。清单 4-21 提供了进行内部连接的例子。
// define the join expression of equality comparison
val deptJoinExpression = employeeDF.col("dept_no") === deptDF.col("id")
// perform the join
employeeDF.join(deptDF, joinExpression, "inner").show
// no need to specify the join type since "inner" is the default
employeeDF.join(deptDF, joinExpression).show
+-------------+----------+---+----------------+
| first_name| dept_no| id| name|
+-------------+----------+---+----------------+
| John| 31| 31| Sales|
| Jeff| 33| 33| Engineering|
| Mary| 33| 33| Engineering|
| Mandy| 34| 34| Finance|
| Julie| 34| 34| Finance|
+-------------+----------+---+----------------+
// using SQL
spark.sql("select * from employees JOIN departments on dept_no == id").show
Listing 4-21Performing Inner Join by the Department ID
正如预期的那样，连接的数据集只包含雇员和部门数据集中具有匹配部门 id 的行，以及两个数据集中的列。输出告诉您每个雇员属于哪个部门。
可以在join转换中或者使用where转换来指定连接表达式。如果列名是唯一的，可以使用简写版本引用连接表达式中的列。否则，您必须使用col函数指定特定列来自哪个数据帧。清单 4-22 展示了表达一个连接表达式的不同方式。
// a shorter version of the join expression
employeeDF.join(deptDF, 'dept_no === 'id).show
// specify the join expression inside the join transformation
employeeDF.join(deptDF, employeeDF.col("dept_no") === deptDF.col("id")).show
// specify the join expression using the where transformation
employeeDF.join(deptDF).where('dept_no === 'id).show
Listing 4-22Different Ways of Expressing a Join Expression
连接表达式只是一个布尔谓词，因此它可以像比较两列一样简单，也可以像链接多对列的多个逻辑比较一样复杂。
左外部连接
此连接类型的连接数据集包括内部连接的所有行，以及连接表达式评估为 false 的左侧数据集的所有行。对于那些不匹配的行，它为右侧数据集的列填充一个空值。清单 4-23 是一个左外连接的例子。
// the join type can be either "left_outer" or "leftouter"
employeeDF.join(deptDF, 'dept_no === 'id, "left_outer").show
// using SQL
spark.sql("select * from employees LEFT OUTER JOIN departments on dept_no == id").show
+--------------+----------+----+----------------+
| first_name| dept_no| id| name|
+--------------+----------+----+----------------+
| John| 31| 31| Sales|
| Jeff| 33| 33| Engineering|
| Mary| 33| 33| Engineering|
| Mandy| 34| 34| Finance|
| Julie| 34| 34| Finance|
| Kurt| 0|null| null|
+--------------+----------+----+----------------+
Listing 4-23Performing a Left Outer Join
不出所料，市场部在雇员数据集中没有任何匹配的行。关联数据集告诉您员工被分配到的部门以及哪些部门没有员工。
右外部联接
此连接类型的行为类似于左侧外部连接类型的行为，只是对右侧数据集应用了相同的处理。换句话说，连接的数据集包括内部连接中的所有行，以及连接表达式评估为 false 的右侧数据集中的所有行。清单 4-24 是一个右外连接的例子。
employeeDF.join(deptDF, 'dept_no === 'id, "right_outer").show
// using SQL
spark.sql("select * from employees RIGHT OUTER JOIN departments on dept_no == id").show
+-------------+-----------+----+----------------+
| first_name| dept_no| id| name|
+-------------+-----------+----+----------------+
| John| 31| 31| Sales|
| Mary| 33| 33| Engineering|
| Jeff| 33| 33| Engineering|
| Julie| 34| 34| Finance|
| Mandy| 34| 34| Finance|
| null| null| 35| Marketing|
+-------------+-----------+----+----------------+
Listing 4-24Performing a Right Outer Join
不出所料，市场部没有来自雇员数据集中的任何匹配行。关联数据集告诉您员工被分配到的部门以及哪些部门没有员工。
外部联接(也称为完全外部联接)
这种连接类型的行为实际上与合并左外部连接和右外部连接的结果是一样的。清单 4-25 是进行外部连接的一个例子。
employeeDF.join(deptDF, 'dept_no === 'id, "outer").show
// using SQL
spark.sql("select * from employees FULL OUTER JOIN departments on dept_no == id").show
+-------------+-----------+----+----------------+
| first_name| dept_no| id| name|
+-------------+-----------+----+----------------+
| Kurt| 0|null| null|
| Mandy| 34| 34| Finance|
| Julie| 34| 34| Finance|
| John| 31| 31| Sales|
| Jeff| 33| 33| Engineering|
| Mary| 33| 33| Engineering|
| null| null| 35| Marketing|
+-------------+-----------+----+----------------+
Listing 4-25Performing an Outer Join
外部联接的结果允许您查看某个雇员被分配到哪个部门，哪些部门有雇员，哪些雇员没有被分配到某个部门，以及哪些部门没有任何雇员。
左反连接
此连接类型可让您找出左侧数据集中哪些行在右侧数据集中没有任何匹配行，并且连接的数据集中仅包含左侧数据集中的列。清单 4-26 是一个做左反连接的例子。
employeeDF.join(deptDF, 'dept_no === 'id, "left_anti").show
// using SQL
spark.sql("select * from employees LEFT ANTI JOIN departments on dept_no == id").show
+-------------+-----------+
| first_name| dept_no|
+-------------+-----------+
| Kurt| 0|
+-------------+-----------+
Listing 4-26Performing a Left Anti-Join
左反联接的结果可以很容易地告诉您哪些员工没有被分配到某个部门。请注意，不存在正确的反联接类型；但是，您可以轻松地切换数据集来实现相同的目标。
左半连接
这种联接类型的行为类似于内部联接类型，只是联接的数据集不包括右侧数据集中的列。考虑这种连接类型的另一种方式是，它的行为与左反连接相反，在左反连接中，连接的数据集只包含匹配的行。清单 4-27 是做左半连接的一个例子。
employeeDF.join(deptDF, 'dept_no === 'id, "left_semi").show
// using SQL
spark.sql("select * from employees LEFT SEMI JOIN departments on dept_no == id").show
+-------------+-----------+
| first_name| dept_no|
+-------------+-----------+
| John| 31|
| Jeff| 33|
| Mary| 33|
| Mandy| 34|
| Julie| 34|
+-------------+-----------+
Listing 4-27Performing a Left Semi-Join
十字(又名笛卡尔)
就用法而言，这种连接类型是最容易使用的，因为不需要连接表达式。它的行为可能有点危险，因为它将左边数据集中的每一行与右边数据集中的每一行连接起来。连接数据集的大小是两个数据集大小的乘积。例如，如果每个数据集的大小为 1024，则连接的数据集的大小超过一百万行。因此，使用这种连接类型的方法是在DataFrame类中显式地使用一个专用的转换，而不是将这种连接类型指定为一个字符串。清单 4-28 是交叉连接的一个例子。
// using crossJoin transformation and display the count
employeeDF.crossJoin(deptDF).count
Long = 24
// using SQL and passing 30 value to show action to see all rows
spark.sql("select * from employees CROSS JOIN departments").show(30)
+-------------+----------+---+----------------+
| first_name| dept_no| id| name|
+-------------+----------+---+----------------+
| John| 31| 31| Sales|
| John| 31| 33| Engineering|
| John| 31| 34| Finance|
| John| 31| 35| Marketing|
| Jeff| 33| 31| Sales|
| Jeff| 33| 33| Engineering|
| Jeff| 33| 34| Finance|
| Jeff| 33| 35| Marketing|
| Mary| 33| 31| Sales|
| Mary| 33| 33| Engineering|
| Mary| 33| 34| Finance|
| Mary| 33| 35| Marketing|
| Mandy| 34| 31| Sales|
| Mandy| 34| 33| Engineering|
| Mandy| 34| 34| Finance|
| Mandy| 34| 35| Marketing|
| Julie| 34| 31| Sales|
| Julie| 34| 33| Engineering|
| Julie| 34| 34| Finance|
| Julie| 34| 35| Marketing|
| Kurt| 0| 31| Sales|
| Kurt| 0| 33| Engineering|
| Kurt| 0| 34| Finance|
| Kurt| 0| 35| Marketing|
+-------------+----------+---+----------------+
Listing 4-28Performing a Cross Join
处理重复的列名
有时，两个数据帧可能有一个或多个同名的列。在连接它们之前，最好在两个数据帧中的一个中重命名这些列，以避免访问不明确的问题；否则，连接的数据帧将有多个同名的列。清单 4-29 模拟了这种情况。
// add a new column to deptDF with name dept_no
val deptDF2 = deptDF.withColumn("dept_no", 'id)
deptDF2.printSchema
|-- id: long (nullable = false)
|-- name: string (nullable = true)
|-- dept_no: long (nullable = false)
// now employeeDF with deptDF2 using dept_no column
val dupNameDF = employeeDF.join(deptDF2, employeeDF.col("dept_no") === deptDF2.col("dept_no"))
dupNameDF.printSchema
|-- first_name: string (nullable = true)
|-- dept_no: long (nullable = false)
|-- id: long (nullable = false)
|-- name: string (nullable = true)
|-- dept_no: long (nullable = false)
Listing 4-29Simulate a Joined DataFrame with Multiple Names That Are the Same
请注意，dupNameDF数据帧现在有两列具有相同的名称，dept_no。当你使用清单 4-30 中的dept_no投射dupNameDF数据帧时，Spark 抛出一个错误。
dupNameDF.select("dept_no")
org.apache.spark.sql.AnalysisException: Reference 'dept_no' is ambiguous, could be: dept_no#30L, dept_no#1050L.;
Listing 4-30Projecting Column dept_no in the dupNameDF DataFrame
事实证明，有几种方法可以处理这个问题。
使用原始数据帧
在连接过程中，连接的数据帧会记住哪些列来自哪个原始数据帧。为了消除一个列来自哪个数据帧的歧义，只需告诉 Spark 在它前面加上原始数据帧的名称。清单 4-31 展示了如何做到这一点。
dupNameDF.select(deptDF2.col("dept_no"))
Listing 4-31Using the Original DataFrame deptDF2 to Refer to dept_no Column in the Joined DataFrame
联接前重命名列
避免列名不明确问题的另一种方法是使用withColumnRenamed转换重命名其中一个数据帧中的列。因为这很简单，所以我把它作为一个练习留给你。
使用联接的列名
当两个数据帧中的连接列名相同时，您可以利用某个版本的连接转换来自动删除连接数据帧中的重复列名。但是，如果它是一个自连接，意味着将一个数据帧连接到自身，那么就没有办法引用其他重复的列名。在这种情况下，您需要使用列重命名技术。清单 4-32 展示了一个使用连接列名执行连接的例子。
val noDupNameDF = employeeDF.join(deptDF2, "dept_no")
noDupNameDF.printSchema
|-- dept_no: long (nullable = false)
|-- first_name: string (nullable = true)
|-- id: long (nullable = false)
|-- name: string (nullable = true)
Listing 4-32Performing a Join Using Joined Column Name
注意在noDupNameDF数据帧中只有一个dept_no列。
连接实现概述
加入是 Spark 中最复杂、最昂贵的操作之一。在高层次上，Spark 使用一些策略来执行两个数据集的连接。它们是混洗散列连接和广播连接。选择特定策略的主要标准基于两个数据集的大小。当两个数据集的大小都很大时，则使用混洗散列连接策略。当其中一个数据集的大小足够小，可以放入执行器的内存中时，就使用广播连接策略。以下部分将详细介绍每种加入策略的工作原理。
无序散列连接
从概念上讲，连接就是将满足连接表达式中条件的两个数据集的行组合起来。为此，需要跨网络传输具有相同列值的行，这些行位于同一个分区上。
无序散列连接的实现包括两个步骤。第一步是计算每个数据集中每一行的连接表达式中的列的哈希值，然后将那些具有相同哈希值的行放入同一个分区。为了确定特定行移动到哪个分区，Spark 执行一个简单的算术运算，用分区的数量计算哈希值的模。第二步是合并那些具有相同列哈希值的行的列。在高层次上，这两个步骤类似于 MapReduce 编程模型中的步骤。
图 4-4 显示了在混洗散列连接中进行的混洗。由于通过网络在机器间传输大量数据，这是一项昂贵的操作。当通过网络移动数据时，数据通常要经过序列化和反序列化过程。想象一下，在两个大型数据集上执行连接，每个数据集的大小都是 100 GB。在这种情况下，它移动大约 200GB 的数据。当连接两个大型数据集时，不可能完全避免混洗散列连接。尽管如此，只要有可能，注意减少加入他们的频率是很重要的。
图 4-4
无序散列连接
广播散列连接
当其中一个数据集小到足以放入内存时，可以使用这种连接策略。由于知道混洗散列连接是一种开销很大的操作，广播散列连接避免了混洗两个数据集，而只混洗较小的数据集。像 shuffle hash 连接策略一样，这个策略也包含两个步骤。第一步是将较小数据集的副本广播到较大数据集的每个分区。第二步是遍历较大数据集中的每一行，并在较小数据集中查找具有匹配列值的相应行。图 4-5 显示了小数据集的广播。
图 4-5
广播散列连接
容易理解的是，当广播散列连接适用时，它是首选的。在大多数情况下，Spark SQL 可以在读取数据集时，根据它所拥有的关于数据集的统计信息，自动判断何时使用广播散列连接或混洗散列连接。然而，在使用join转换时，提示 Spark SQL 使用广播散列连接是可行的。清单 4-33 提供了一个这样做的例子。
import org.apache.spark.sql.functions.broadcast
// Use broadcast hash join strategy and print out execution plan
employeeDF.join(broadcast(deptDF), employeeDF.col("dept_no") === deptDF.col("id")).explain()
// User broadcast hash join hint in a SQL statement
spark.sql("select /*+ MAPJOIN(departments) */ * from employees JOIN departments on dept_no == id").explain()
== Physical Plan ==
*BroadcastHashJoin [dept_no#30L], [id#41L], Inner, BuildRight
:- LocalTableScan [first_name#29, dept_no#30L]
+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]))
+- LocalTableScan [id#41L, name#42]
Listing 4-33Provide a Hint to Use Broadcast Hash Join
功能
DataFrame APIs 旨在操作或转换数据集中的单个行，例如筛选和分组。如果您想要转换每一行的列值，例如将一个字符串从大写转换为骆驼大小写，您可以使用一个函数。函数是应用于列的方法。Spark SQL 提供了一大组常用函数和一种创建新函数的简单方法。在 Spark 3.0 版本中添加了大约 30 个新的内置函数。
使用内置函数
为了有效地使用 Spark SQL 执行分布式数据操作，您必须熟练使用 Spark SQL 内置函数。这些内置函数旨在生成优化的代码，以便在运行时执行，因此最好在使用自己的函数之前利用它们。这些函数的一个共同点是它们被设计成将同一行的一列或多列作为输入，并且它们只返回一列作为输出。Spark SQL 提供了 200 多个内置函数，它们被分成不同的类别。这些函数可用于数据帧操作，如select、filter和groupBy。
有关内置函数的完整列表，请参考位于 https://spark.apache.org/docs/latest/api/scala/org/apache/spark/sql/functions$.html 的 Spark API Scala 文档。表 4-3 将它们分为不同的类别。
表 4-3
每个类别的内置函数的子集
|类别
|
描述
| | --- | --- | | 日期时间 | unix_timestamp，from_unixtime，to_date，current_date，current_timesatmp，date_add，date_sub，add_months，datediff，months_between，dayofmonth，dayofyear，weekofyear，second，minute，hour，month，make_date，make_timestamp，make_interval | | 线 | concat，length，levenshtein，locate，lower，upper，ltrim，rtrim，trim，lpad，rpad，repeat，reverse，split，substring，base64 | | 数学 | cos、acos、sin、asin、tan、atan、ceil、floor、exp、factor、log、pow、radian、degree、sqrt、hex、unhex | | 密码系统 | cr32，哈希，md5，sha1，sha2 | | 聚合 | 近似的 _count_distinct，countDistinct，sumDistinct，avg，corr，count，first，last，max，min，skewness，sum， | | 募捐 | array_contain，explode，from_json，size，sort_array，to_json，size | | 窗户 | dense_rank，lag，lead，ntile，rank，row_number | | 杂项 | coalesce，isNan，isnull，isNotNull，单调递增 id，lit，when |
这些功能中的大部分都易于理解和直接使用。以下部分提供了一些有趣的工作示例。
使用日期时间函数
使用 Spark 执行数据分析的次数越多，遇到多一个日期或时间相关列的数据集的机会就越大。Spark 内置数据时间函数大致分为以下三类:将日期或时间戳从一种格式转换为另一种格式，执行数据时间计算，以及从日期或时间戳中提取特定值，如年、月、星期几等等。
日期-时间转换函数有助于将时间字符串转换为日期、时间戳或 Unix 时间戳，反之亦然。在内部，它使用 Java 日期格式模式语法，该语法记录在 http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html 中。这些函数使用的默认日期格式是 yyyy-MM-dd HH:mm:ss。因此，如果您的日期或时间戳列的日期格式不同，您需要向这些转换函数提供该模式。清单 4-34 展示了一个将字符串类型的日期和时间戳转换成 Spark 日期和时间戳类型的例子。
// the last two columns don't follow the default date format
val testDF = Seq((1, "2018-01-01", "2018-01-01 15:04:58:865",
"01-01-2018", "12-05-2017 45:50"))
.toDF("id", "date", "timestamp", "date_str",
"ts_str")
// convert these strings into date, timestamp and unix timestamp
// and specify a custom date and timestamp format
val testResultDF = testDF.select(to_date('date).as("date1"),
to_timestamp('timestamp).as("ts1"),
to_date('date_str,"MM-dd-yyyy").as("date2"),
to_timestamp('ts_str, "MM-dd-yyyy mm:ss").as("ts2"),
unix_timestamp('timestamp).as("unix_ts"))
.show(false)
// date1 and ts1 are of type date and timestamp respectively
testResultDF.printSchema
|-- date1: date (nullable = true)
|-- ts1: timestamp (nullable = true)
|-- date2: date (nullable = true)
|-- ts2: timestamp (nullable = true)
|-- unix_ts: long (nullable = true)
testDateResultDF.show
+----------+-------------------+----------+-------------------+-----------+
| date1| ts1| date2| ts2| unix_ts|
+----------+-------------------+----------+-------------------+-----------+
|2018-01-01|2018-01-01 15:04:58|2018-01-01|2017-12-05 00:45:50| 1514847898|
+----------+-------------------+----------+-------------------+-----------+
Listing 4-34Converting date and timestamp String to Spark Date and Timestamp Type
通过使用带有自定义日期格式的date_format函数或者使用from_unixtime函数将 Unix 时间戳(以秒为单位)转换为时间字符串，将日期或时间戳转换为时间字符串同样简单。清单 4-35 显示了转换的例子。
testResultDF.select(date_format('date1,"dd-MM-YYYY").as("date_str"),date_format('ts1, "dd-MM-YYYY HH:mm:ss").as("ts_str"),
from_unixtime('unix_ts,"dd-MM-YYYY HH:mm:ss").as("unix_ts_str"))
.show
+-------------+------------------------+------------------------+
| date_str| ts_str| unix_ts_str|
+-------------+------------------------+------------------------+
| 01-01-2018| 01-01-2018 15:04:58| 01-01-2018 15:04:58|
+-------------+------------------------+------------------------+
Listing 4-35Converting Date, Timestamp, and Unix Timestamp to Time String
日期时间计算函数对于计算两个日期或时间戳之间的差异以及执行日期或时间运算的能力非常有用。清单 4-36 显示了日期时间计算的工作示例。
val employeeData = Seq(("John", "2016-01-01", "2017-10-15"),
("May", "2017-02-06", "2017-12-25"))
.toDF("name", "join_date", "leave_date")
employeeData.show
+------+----------------+--------------+
| name| join_date| leave_date|
+------+----------------+--------------+
| John| 2016-01-01| 2017-10-15|
| May| 2017-02-06| 2017-12-25|
+------+----------------+--------------+
// perform date and month calculations
employeeData.select('name,
datediff('leave_date, 'join_date).as("days"),
months_between('leave_date, 'join_date).as("months"),
last_day('leave_date).as("last_day_of_mon"))
.show
+------+------+----------------+-----------------------+
| name| days| months| last_day_of_mon|
+------+------+----------------+-----------------------+
| John| 653| 21.4516129| 2017-10-31|
| May| 322| 10.61290323| 2017-12-31|
+------+------+----------------+-----------------------+
// perform date addition and subtraction
val oneDate = Seq(("2018-01-01")).toDF("new_year")
oneDate.select(date_add('new_year, 14).as("mid_month"),
date_sub('new_year, 1).as("new_year_eve"),
next_day('new_year, "Mon").as("next_mon"))
.show
+--------------+--------------------+----------------+
| mid_month| new_year_eve| next_mon|
+--------------+--------------------+----------------+
| 2018-01-15| 2017-12-31| 2018-01-08|
+--------------+--------------------+----------------+
Listing 4-36Date Time Calculation Examples
从日期或时间戳值(如年、月、小时、分钟和秒)中提取特定字段的能力非常方便。例如，当需要按季度、月或周对所有股票交易进行分组时，可以只从交易日期中提取信息，然后按这些值进行分组。清单 4-37 展示了从日期或时间戳中提取字段是多么容易。
val valentimeDateDF = Seq(("2018-02-14 05:35:55")).toDF("date")
valentimeDateDF.select(year('date).as("year"),
quarter('date).as("quarter"),
month('date).as("month"),
weekofyear('date).as("woy"),
dayofmonth('date).as("dom"),
dayofyear('date).as("doy"),
hour('date).as("hour"),
minute('date).as("minute"),
second('date).as("second"))
.show
+-----+--------+------+-----+-----+-----+------+-------+--------+
| year| quarter| month| woy| dom| doy| hour| minute| second|
+-----+--------+------+-----+-----+-----+------+-------+--------+
| 2018| 1| 2| 7| 14| 45| 5| 35| 55|
+-----+--------+------+-----+-----+-----+------+-------+--------+
Listing 4-37Extract Specific Fields from a Date Value
使用字符串函数
毫无疑问，大多数数据集中的大多数列都是字符串类型。Spark SQL 内置字符串函数提供了操作这种类型的列的多种功能强大的方法。这些功能分为两大类。第一个是关于转换字符串，第二个是关于应用正则表达式来替换字符串的某个部分，或者基于模式提取字符串的某些部分。
有许多方法来转换一个字符串。最常见的是修剪、填充、大写、小写和连接。修剪是指删除字符串左侧或右侧的空格，或者两者都删除。填充是将字符添加到字符串的左侧或右侧。清单 4-38 展示了使用各种内置字符串函数转换字符串的各种方法。
val sparkDF = Seq((" Spark ")).toDF("name")
// trimming - removing spaces on the left side, right side of a string, or both
// trim removes spaces on both sides of a string
// ltrim only removes spaces on the left side of a string
// rtrim only removes spaces on the right side of a string
sparkDF.select(trim('name).as("trim"),
ltrim('name).as("ltrim"),
rtrim('name).as("rtrim"))
.show
+-----+----------+---------+
| trim| ltrim| rtrim|
+-----+----------+---------+
|Spark| Spark | Spark|
+-----+----------+---------+
// padding a string to a specified length with given pad string
// first trim spaces around string "Spark" and then pad it so the final length is 8 characters long
// lpad pads the left side of the trim column with - to the length of 8
// rpad pads the right side of the trim colum with = to the length of 8
sparkDF.select(trim('name).as("trim"))
.select(lpad('trim, 8, "-").as("lpad"),
rpad('trim, 8, "=").as("rpad"))
.show
+---------+-------------+
| lpad| rpad|
+---------+-------------+
| ---Spark| Spark===|
+---------+-------------+
// transform a string with concatenation, uppercase, lowercase and reverse
val sparkAwesomeDF = Seq(("Spark", "is", "awesome"))
.toDF("subject", "verb", "adj")
sparkAwesomeDF.select(concat_ws(" ",'subject, 'verb,
'adj).as("sentence"))
.select(lower('sentence).as("lower"),
upper('sentence).as("upper"),
initcap('sentence).as("initcap"),
reverse('sentence).as("reverse"))
.show
+-----------------+-----------------+-----------------+-----------------+
| lower| upper| initcap| reverse|
+-----------------+-----------------+-----------------+-----------------+
| spark is awesome| SPARK IS AWESOME| Spark Is Awesome| emosewa si krapS|
+-----------------+-----------------+-----------------+-----------------+
// translate from one character to another
sparkAwesomeDF.select('subject, translate('subject, "ar",
"oc").as("translate"))
.show
+---------+------------+
| subject| translate|
+---------+------------+
| Spark| Spock|
+---------+------------+
Listing 4-38Different Ways of Transforming a String With Built-in String Functions
正则表达式是替换字符串的一部分或从字符串中提取子字符串的一种强大而灵活的方法。regexp_extract和regexp_replace功能就是专门为这些目的而设计的。Spark 利用 Java 正则表达式库来实现这两个字符串函数。
regexp_extract函数的输入参数是一个字符串列、一个要匹配的模式和一个组索引。一个字符串中可能有多个模式匹配；因此，需要组索引(从 0 开始)来识别哪一个。如果指定的模式没有匹配项，该函数将返回一个空字符串。清单 4-30 是使用regexp_extract函数的一个例子。
val rhymeDF = Seq(("A fox saw a crow sitting on a tree singing
\"Caw! Caw! Caw!\"")).toDF("rhyme")
// using a pattern
rhymeDF.select(regexp_extract('rhyme,"[a-z]*o[xw]",0)
.as("substring")).show
+------------+
| substring|
+------------+
| fox|
+------------+
Listing 4-39Using regexp_extract string Function to Extract “fox” Out Using a Pattern
字符串函数的输入参数是字符串列、要匹配的模式和要替换的值。清单 4-40 是使用regexp_replace函数的一个例子。
val rhymeDF = Seq(("A fox saw a crow sitting on a tree singing
\"Caw! Caw! Caw!\"")).toDF("rhyme")
// both lines below produce the same output
rhymeDF.select(regexp_replace('rhyme, "fox|crow", "animal")
.as("new_rhyme"))
.show(false)
rhymeDF .select(regexp_replace('rhyme, "[a-z]*o[xw]", "animal")
.as("new_rhyme"))
.show(false)
+----------------------------------------------------------------+
| new_rhyme |
+----------------------------------------------------------------+
|A animal saw a animal sitting on a tree singing "Caw! Caw! Caw!"|
+----------------------------------------------------------------+
Listing 4-40Using regexp_replace String Function to Replace “fox” and “crow” with “animal”
使用数学函数
第二种最常见的列类型是数字类型。在客户交易或物联网传感器相关数据集中尤其如此。大多数数学函数都一目了然，易于使用。本节介绍一个有用且常用的函数round，它根据给定的小数位数对数值进行上舍入。小数位数决定了要向上舍入的小数位数。这个函数有两种变体。第一个函数采用具有浮点值和小数位数的列，第二个函数只采用具有浮点值的列。第二个变量调用第一个变量，其值为 0。清单 4-41 演示了round函数的行为。
val numberDF =Seq((3.14159, 3.5, 2018)).toDF("pie","gpa", "year")
numberDF.select(round('pie).as("pie0"),
round('pie, 1).as("pie1"),
round('pie, 2).as("pie2"),
round('gpa).as("gpa"),
round('year).as("year"))
.show
// because it is a half-up rounding, the gpa value is rounded up to 4.0
+-----+------+-----+-----+------+
| pie0| pie1| pie2| gpa| year|
+-----+------+-----+-----+------+
| 3.0| 3.1| 3.14| 4.0| 2018|
+-----+------+-----+-----+------+
Listing 4-41Demonstrates the Behavior of round with Various Scales
使用集合函数
集合函数旨在处理复杂的数据类型，如数组、映射或结构。本节介绍两种特定类型的收集函数。第一个是关于使用数组数据类型。第二个是关于使用 JSON 数据格式。
有时数据集中的特定列包含一系列值，而不是单个标量值。建模的一种方法是使用数组数据类型。例如，假设有一个关于每天需要执行的任务的数据集。在这个数据集中，每一行代表每天的任务列表。每行由两列组成。一列包含日期，另一列包含任务列表。您可以使用与数组相关的集合函数轻松获取数组大小、检查值的存在或对数组进行排序。清单 4-42 包含了使用各种数组相关函数的例子。
// create an tasks DataFrame
val tasksDF = Seq(("Monday", Array("Pick Up John",
"Buy Milk", "Pay Bill")))
.toDF("day", "tasks")
// schema of tasksDF
tasksDF.printSchema
|-- day: string (nullable = true)
|-- tasks: array (nullable = true)
| |-- element: string (containsNull = true)
// get the size of the array, sort it, and check to see if a particular value exists in the array
tasksDF.select('day, size('tasks).as("size"),
sort_array('tasks).as("sorted_tasks"),
array_contains('tasks, "Pay Bill").as("payBill"))
.show(false)
+---------+-----+-----------------------------------+-----------+
| day | size| sorted_ta | payBill|
+---------+-----+-----------------------------------+-----------+
| Monday| 3 | [Buy Milk, Pay Bill, Pick Up John]| true |
+---------+-----+-----------------------------------+-----------+
// the explode function will create a new row for each element in the array
tasksDF.select('day, explode('tasks)).show
+----------+------------------+
| day| col|
+----------+------------------+
| Monday| Pick Up John|
| Monday| Buy Milk|
| Monday| Pay Bill|
+----------+------------------+
Listing 4-42Using Array Collection Functions to Manipulate a List of Tasks
许多非结构化数据集采用 JSON 的形式，这是一种流行的自描述数据格式。一个常见的例子是以 JSON 格式对 Kafka 消息有效负载进行编码。由于这种格式在大多数编程语言中得到广泛支持，所以用这些编程语言之一编写的 Kafka 消费者可以很容易地解码这些 Kafka 消息。JSON 相关的集合函数对于在 JSON 字符串和 struct 数据类型之间进行转换非常有用。主要功能有from_json和to_json。一旦 JSON 字符串被转换成 Spark struct 数据类型，就可以很容易地提取这些值。清单 4-43 显示了使用from_json和to_json功能的例子。
import org.apache.spark.sql.types._
// create a string that contains JSON string
val todos = """{"day": "Monday","tasks": ["Pick Up John",
"Buy Milk","Pay Bill"]}"""
val todoStrDF = Seq((todos)).toDF("todos_str")
// at this point, todoStrDF is DataFrame with one column with string data type
todoStrDF.printSchema
|-- todos_str: string (nullable = true)
// in order to convert a JSON string into a Spark struct data type, we need to describe its structure to Spark
val todoSchema = new StructType().add("day", StringType)
.add("tasks", ArrayType(StringType))
// use from_json to convert JSON string
val todosDF = todoStrDF.select(from_json('todos_str, todoSchema)
.as("todos"))
// todos is a struct data type that contains two fields: day and tasks
todosDF.printSchema
|-- todos: struct (nullable = true)
| |-- day: string (nullable = true)
| |-- tasks: array (nullable = true)
| | |-- element: string (containsNull = true)
// retrieving value out of struct data type using the getItem function of Column class
todosDF.select('todos.getItem("day"), 'todos.getItem("tasks"),
'todos.getItem("tasks").getItem(0).as("first_task"))
.show(false)
+-----------+-----------------------------------+-------------+
| todos.day| todos.tasks | first_task |
+-----------+-----------------------------------+-------------+
| Monday | [Pick Up John, Buy Milk, Pay Bill]| Pick Up John|
+-----------+-----------------------------------+-------------+
// to convert a Spark struct data type to JSON string, we can use to_json function
todosDF.select(to_json('todos)).show(false)
+---------------------------------------------------------------+
| structstojson(todos) |
+---------------------------------------------------------------+
|{"day":"Monday","tasks":["Pick Up John","Buy Milk","Pay Bill"]}|
+---------------------------------------------------------------+
Listing 4-43Examples of Using from_json and to_json Functions
使用杂项功能
杂项类别中的一些函数很有趣，在某些情况下会很有用。本节包括以下功能:monotonically_increasing_id、when、coalesce和lit。
有时需要为数据集中的每一行生成单调递增的唯一 id，但不是连续的 id。如果你花些时间思考一下，这是一个相当有趣的问题。例如，如果一个数据集有 2 亿行，并且分布在许多分区(机器)上，如何确保这些值是唯一的并且同时增加？这是monotonically_increasing_id函数的工作，它将 id 生成为 64 位整数。其算法的关键部分是将分区 ID 放在生成的 ID 的高 31 位。清单 4-44 显示了一个使用monotonically_increasing_id函数的例子。
// first generate a DataFrame with values from 1 to 10
// and spread them across 5 partitions
val numDF = spark.range(1,11,1,5)
// verify that there are 5 partitions
numDF.rdd.getNumPartitions
Int = 5
// now generate the monotonically increasing numbers
// and see which ones are in which partition
numDF.select('id, monotonically_increasing_id().as("m_ii"),
spark_partition_id().as("partition")).show
+----+--------------+-----------+
| id| m_ii| partition|
+----+--------------+-----------+
| 1| 0| 0|
| 2| 1| 0|
| 3| 8589934592| 1|
| 4| 8589934593| 1|
| 5| 17179869184| 2|
| 6| 17179869185| 2|
| 7| 25769803776| 3|
| 8| 25769803777| 3|
| 9| 34359738368| 4|
| 10| 34359738369| 4|
+----+--------------+-----------+
// the above table shows the values in m_ii columns have a different range in each partition.
Listing 4-44monotonically_increasing_id in Action
如果需要根据条件列表计算值并返回值，那么典型的解决方案是使用 switch 语句，这在大多数高级编程语言中都可用。当需要对 DataFrame 中的一列的值执行此操作时，您可以对这个用例使用when函数。清单 4-45 是使用when函数的一个例子。
// create a DataFrame with values from 1 to 7 to represent each day of the week
val dayOfWeekDF = spark.range(1,8,1)
// convert each numerical value to a string
dayOfWeekDF.select('id, when('id === 1, "Mon")
.when('id === 2, "Tue")
.when('id === 3, "Wed")
.when('id === 4, "Thu")
.when('id === 5, "Fri")
.when('id === 6, "Sat")
.when('id === 7, "Sun").as("dow"))
.show
+---+----+
| id| dow|
+---+----+
| 1| Mon|
| 2| Tue|
| 3| Wed|
| 4| Thu|
| 5| Fri|
| 6| Sat|
| 7| Sun|
+---+----+
// to handle the default case when we can use the otherwise function of the column class
dayOfWeekDF.select('id, when('id === 6, "Weekend")
.when('id === 7, "Weekend")
.otherwise("Weekday").as("day_type"))
.show
+---+--------+
| id|day_type|
+--+---------+
| 1| Weekday|
| 2| Weekday|
| 3| Weekday|
| 4| Weekday|
| 5| Weekday|
| 6| Weekend|
| 7| Weekend|
+------------+
Listing 4-45Use the when Function to Convert a Numeric Value to a String
处理数据时，正确处理空值非常重要。方法之一是将它们转换成在数据处理逻辑中表示 null 的其他值。借鉴 SQL 世界，Spark 提供了一个coalesce,它接受一个或多个列值，并返回第一个不为 null 的值。coalesce 中的每个参数都必须是 Column 类型，所以如果您想填充一个文字值，可以利用lit函数。这个函数之所以有效，是因为它接受一个文字值，并返回包装输入的Column类的一个实例。清单 4-46 是同时使用coalesce和lit功能的例子。
// create a movie with null title
case class Movie(actor_name:String, movie_title:String,
produced_year:Long)
val badMoviesDF = Seq( Movie(null, null, 2018L),
Movie("John Doe", "Awesome Movie", 2018L))
.toDF
// use coalesce function to handle null value in the title column
badMoviesDF.select(coalesce('actor_name,
lit("no_name")).as("new_title"))
.show
+-------------+
| new_title|
+-------------+
| no_name|
| John Doe|
+-------------+
Listing 4-46Using coalesce to Handle null Value in a Column
使用用户定义的函数(UDF)
尽管 Spark SQL 为最常见的用例提供了大量的内置函数，但总会出现这些函数都不能提供用例所需功能的情况。但是，不要绝望。Spark SQL 提供了一个简单的工具来编写用户定义函数(UDF ),并在 Spark 数据处理逻辑或应用程序中使用它们，就像使用内置函数一样。UDF 是扩展 Spark 功能以满足特定需求的有效方法之一。
我喜欢 Spark 的另一个原因是，UDF 可以用 Python、Java 或 Scala 编写，并且可以利用和集成任何必要的库。由于您可以使用自己最熟悉的编程语言来编写 UDF，因此开发和测试 UDF 非常容易和快速。
从概念上讲，UDF 只是常规函数，它接受一些输入并提供一个输出。尽管 UDF 可以用 Scala、Java 或 Python 编写，但是您必须意识到用 Python 编写 UDF 时的性能差异。UDF 在使用前必须向 Spark 注册，所以 Spark 知道把它们运送给 executors 来使用和执行。鉴于执行器是用 Scala 编写的 JVM 进程，它们可以在同一个进程中原生执行 Scala 或 Java UDFs。如果一个 UDF 是用 Python 编写的，那么一个执行器不能本地执行它，因此它必须生成一个单独的 Python 进程来执行 Python UDF。除了生成 Python 进程的成本之外，为数据集中的每一行来回序列化数据的成本也很高。
使用 UDF 涉及三个步骤。第一个是写一个函数并测试它。第二步是通过将函数名及其签名传递给 Spark 的udf函数，向 Spark 注册该函数。最后一步是在 DataFrame 代码中或发出 SQL 查询时使用 UDF。在 SQL 查询中使用 UDF 时，注册过程略有不同。清单 4-47 用一个简单的 UDF 演示了这三个步骤。
import org.apache.spark.sql.functions.udf
// create student grades DataFrame
case class Student(name:String, score:Int)
val studentDF = Seq(Student("Joe", 85), Student("Jane", 90), Student("Mary", 55)).toDF()
// register as a view
studentDF.createOrReplaceTempView("students")
// create a function to convert grade to a letter grade
def letterGrade(score:Int) : String = {
score match {
case score if score > 100 => "Cheating"
case score if score >= 90 => "A"
case score if score >= 80 => "B"
case score if score >= 70 => "C"
case _ => "F"
}
}
// register as an UDF
val letterGradeUDF = udf(letterGrade(_:Int):String)
// use the UDF to convert scores to letter grades
studentDF.select($"name",$"score",
letterGradeUDF($"score").as("grade")).show
+----+-----+-----+
|name|score|grade|
+----+-----+-----+
| Joe| 85| B|
|Jane| 90| A|
|Mary| 55| F|
+----+-----+-----+
// register as UDF to use in SQL
spark.sqlContext.udf.register("letterGrade",
letterGrade(_: Int): String)
spark.sql("select name, score, letterGrade(score) as grade from students").show
+----+-----+-----+
|name|score|grade|
+----+-----+-----+
| Joe| 85| B|
|Jane| 90| A|
|Mary| 55| F|
+----+-----+-----+
Listing 4-47A Simple UDF in Scala to Convert Numeric Grades to Letter Grades
高级分析功能
前面几节介绍了 Spark SQL 为基本分析需求提供的内置函数，如聚合、连接、透视和分组。所有这些函数从单个行中获取一个或多个值并产生一个输出值，或者获取一组行并返回一个输出。
本节介绍 Spark SQL 提供的高级分析功能。第一个是关于多维聚合，这对于涉及分层数据分析的用例非常有用。通常需要计算一组分组列的小计和总计。第二个功能是基于时间窗口执行聚合，这在处理时序数据(如来自物联网设备的交易或传感器值)时非常有用。第三个是在逻辑行分组(称为窗口)中执行聚合的能力。这种功能使您能够轻松地执行计算，例如移动平均值、累积和或每行的排名。
带有汇总和多维数据集的聚合
Rollups 和 cube 是多列分组的更高级版本，它们跨这些列的组合和排列生成小计和总计。所提供的一组列的顺序被视为分组的层次结构。
汇总
当处理分层数据(如跨不同部门和分部的收入数据)时，汇总可以很容易地计算它们之间的小计和总计。累计会考虑给定累计列集的给定层次结构，并始终从层次结构中的第一列开始累计过程。输出中列出了总数，其中所有列值都为 null。清单 4-48 展示了一个汇总是如何工作的。
// read in the flight summary data
val flight_summary = spark.read.format("csv")
.option("header", "true")
.option("inferSchema","true")
.load(<path>/chapter4/data/ flights/flight-summary.csv)
// filter data down to smaller size to make it easier to see the rollups result
val twoStatesSummary = flight_summary.select('origin_state,
'origin_city,'count)
.where('origin_state === "CA" || 'origin_state === "NY")
.where('count > 1 && 'count < 20)
.where('origin_city =!= "White Plains")
.where('origin_city =!= "Newburgh")
.where('origin_city =!= "Mammoth Lakes")
.where('origin_city =!= "Ontario")
// let's see what the data looks like
twoStatesSummary.orderBy('origin_state).show
+-------------+--------------+------+
| origin_state| origin_city| count|
+-------------+--------------+------+
| CA| San Diego | 18|
| CA| San Francisco| 5|
| CA| San Francisco| 14|
| CA| San Diego| 4|
| CA| San Francisco| 2|
| NY| New York| 4|
| NY| New York| 2|
| NY| Elmira| 15|
| NY| Albany| 5|
| NY| Albany| 3|
| NY| New York| 4|
| NY| Albany| 9|
| NY| New York| 10|
+-------------+--------------+------+
// perform the rollup by state, city,
// then calculate the sum of the count,and finally order by null last
twoStatesSummary.rollup('origin_state, 'origin_city)
.agg(sum("count") as "total")
.orderBy('origin_state.asc_nulls_last,
'origin_city.asc_nulls_last)
.show
+-------------+--------------+------+
| origin_state| origin_city| total|
+-------------+--------------+------+
| CA| San Diego| 22|
| CA| San Francisco| 21|
| CA| null| 43|
| NY| Albany| 17|
| NY| Elmira| 15|
| NY| New York| 20|
| NY| null| 52|
| null| null| 95|
+-------------+--------------+------+
Listing 4-48Performing Rollups with Flight Summary Data
该输出在第三行和第七行显示了每个州的小计。最后一行显示了在 original_state 和 origin_city 列中都为空值的合计。诀窍是使用 asc_nulls_last 选项进行排序，因此 Spark SQL order 空值排在最后。
立方体
多维数据集是汇总的更高级版本。它对分组列的所有组合执行聚合。因此，结果包括汇总提供的内容以及其他组合。在按 origin_state 和 origin_city 进行立方的示例中，结果包括每个原始城市的聚合。使用cube功能的方法类似于使用rollup功能的方法。
清单 4-49 就是一个例子。
// perform the cube across origin_state and origin_city
twoStatesSummary.cube('origin_state, 'origin_city)
.agg(sum("count") as "total")
.orderBy('origin_state.asc_nulls_last,
'origin_city.asc_nulls_last)
.show
+------------+-------------+-----+
|origin_state| origin_city|total|
+------------+-------------+-----+
| CA| San Diego| 22|
| CA|San Francisco| 21|
| CA| null| 43|
| NY| Albany| 17|
| NY| Elmira| 15|
| NY| New York| 20|
| NY| null| 52|
| null| Albany| 17|
| null| Elmira| 15|
| null| New York| 20|
| null| San Diego| 22|
| null|San Francisco| 21|
| null| null| 95|
+------------+-------------+-----+
Listing 4-49Performing a Cube Across the origin_state and origin_city Columns
在该表中，origin_state 列中具有空值的行表示一个州中所有城市的聚合。因此，多维数据集的结果总是比汇总的结果有更多的行。
带时间窗口的聚合
Spark 2.0 中引入了带时间窗口的聚合，以便于处理时序数据，时序数据由一系列按时间顺序排列的数据点组成。这种数据集在金融或电信等行业很常见。例如，股票市场交易数据集包含每个股票代码的交易日期、开盘价、收盘价、交易量和其他信息。时间窗口聚合可以帮助回答诸如苹果股票的周平均收盘价或苹果股票每周的月移动平均收盘价之类的问题。
窗口函数有几种版本，但它们都需要一个时间戳类型的列和一个窗口长度，以秒、分钟、小时、天或周为单位。窗口长度表示具有开始时间和结束时间的时间窗口，它决定了特定的时间序列数据应属于哪个时段。另一个版本接受滑动窗口大小的额外输入，它告诉在计算下一个时段时时间窗口应该滑动多少。这些版本的窗口功能是世界事件处理中翻滚窗口和滑动窗口概念的实现，它们在第六章中有更详细的描述。
下面的例子使用了苹果股票交易，可以在 Yahoo！ https://in.finance.yahoo.com/q/hp?s=AAPL 的财经网站。清单 4-50 根据一年的数据计算苹果股票的周均价。
val appleOneYearDF = spark.read.format("csv")
.option("header", "true")
.option("inferSchema","true")
.load("<path>/chapter5/data/stocks/aapl-2017.csv")
// display the schema, the first column is the transaction date
appleOneYearDF.printSchema
|-- Date: string (nullable = true)
|-- Open: double (nullable = true)
|-- High: double (nullable = true)
|-- Low: double (nullable = true)
|-- Close: double (nullable = true)
|-- Adj Close: double (nullable = true)
|-- Volume: integer (nullable = true)
// calculate the weekly average price using window function inside the groupBy transformation
// this is an example of the tumbling window, aka fixed window
val appleWeeklyAvgDF = appleOneYearDF.
groupBy(window('Date, "1 week"))
.agg(avg("Close"). as("weekly_avg"))
// the result schema has the window start and end time
appleWeeklyAvgDF.printSchema
|-- window: struct (nullable = false)
| |-- start: timestamp (nullable = true)
| |-- end: timestamp (nullable = true)
|-- weekly_avg: double (nullable = true)
// display the result with ordering by start time and
// round up to 2 decimal points
appleWeeklyAvgDF.orderBy("window.start")
.selectExpr("window.start",
"window.end","round(weekly_avg, 2) as
weekly_avg")
.show(5)
// notice the start time is inclusive and end time is exclusive
+--------------------+--------------------+---------------+
| start| end| weekly_avg|
+--------------------+--------------------+---------------+
| 2016-12-28 16:00:00| 2017-01-04 16:00:00| 116.08|
| 2017-01-04 16:00:00| 2017-01-11 16:00:00| 118.47|
| 2017-01-11 16:00:00| 2017-01-18 16:00:00| 119.57|
| 2017-01-18 16:00:00| 2017-01-25 16:00:00| 120.34|
| 2017-01-25 16:00:00| 2017-02-01 16:00:00| 123.12|
+--------------------+--------------------+---------------+
Listing 4-50Using the Time Window Function to Calculate the Average Closing Price of Apple Stock
清单 4-50 使用为期一周的滚动窗口，其中没有重叠。
因此，每笔交易只使用一次来计算移动平均线。清单 4-51 中的例子使用了滑动窗口。这意味着在计算月平均移动平均值时，一些交易会被多次使用。窗口大小为四周，在每个窗口中每次滑动一周。
// 4 weeks window length and slide by one week each time
val appleMonthlyAvgDF = appleOneYearDF.groupBy(
window('Date, "4 week", "1 week"))
.agg(avg("Close").as("monthly_avg"))
// display the results with order by start time
appleMonthlyAvgDF.orderBy("window.start")
.selectExpr("window.start", "window.end",
"round(monthly_avg, 2) as monthly_avg")
.show(5)
+--------------------+--------------------+------------+
| start| end| monthly_avg|
+--------------------+--------------------+------------+
| 2016-12-07 16:00:00| 2017-01-04 16:00:00| 116.08|
| 2016-12-14 16:00:00| 2017-01-11 16:00:00| 117.79|
| 2016-12-21 16:00:00| 2017-01-18 16:00:00| 118.44|
| 2016-12-28 16:00:00| 2017-01-25 16:00:00| 119.03|
| 2017-01-04 16:00:00| 2017-02-01 16:00:00| 120.42|
+--------------------+--------------------+------------+
Listing 4-51Use the Time Window Function to Calculate the Monthly Average Closing Price of Apple Stock
由于滑动窗口间隔是一周，前面的结果表显示两个连续行之间的开始时间差是一周。在两个连续的行之间，有大约三周的重叠交易，这意味着一个交易被多次用于计算移动平均。
窗口功能
您知道如何使用诸如concat或round之类的函数来计算单个行的一个或多个列值的输出，并利用诸如 max 或 sum 之类的聚合函数来计算每组行的输出。有时需要对一组行进行操作，并为每个输入行返回一个值。窗口函数提供了这种独特的功能，使得执行移动平均、累积和或每行的排名等计算变得容易。
使用窗口函数有两个主要步骤。第一个是定义一个窗口规范，该规范定义了一个称为框架的行逻辑分组，框架是评估每一行的上下文。第二步是应用一个适合你要解决的问题的窗口函数。在以下几节中，您将了解有关可用窗口功能的更多信息。
窗口规范定义了窗口函数使用的三个重要组件。第一个组件称为 partition by，您可以在这里指定一个或多个列来对行进行分组。第二个组件称为 order by，它定义了如何根据一个或多个列对行进行排序，以及排序应该是升序还是降序。在这三个组成部分中，最后一个更复杂，需要详细解释。最后一个组件叫做帧，它定义了当前行中窗口的边界。换句话说，“框架”限制了在计算当前行的值时要包括哪些行。可以使用行索引或 order by 表达式的实际值来指定要包含在窗口框架中的行的范围。最后一个组件适用于某些窗口函数，因此在某些情况下可能不是必需的。使用org.apache.spark.sql.expressions.Window类中定义的函数构建窗口规范。rowsBetween和rangeBetweeen函数分别通过行索引和实际值定义范围。
窗口函数可以分为三种不同的类型:排名函数、分析函数和聚集函数。排序和分析功能分别在表 4-4 和表 4-5 中描述。对于聚合函数，您可以将任何聚合函数用作窗口函数。您可以在 https://spark.apache.org/docs/latest/api/java/org/apache/spark/sql/functions.html 找到窗口功能的完整列表。
表 4-5
分析函数
|名字
|
描述
| | --- | --- | | cume _ dist | 返回一个框架中值的累积分布。换句话说，当前行下面的行的比例。 | | 滞后(列，偏移) | 返回当前行之前偏移行的列的值。 | | 引线(列，偏移) | 返回当前行之后偏移行的列的值。 |
表 4-4
排名功能
|名字
|
描述
| | --- | --- | | 等级 | 根据某种排序顺序返回框架中行的等级或顺序。 | | 密集 _ 秩 | 类似于等级，但是在有平局的情况下，等级之间没有空隙。 | | 分钟 _rank | 返回一个框架中行的相对等级。 | | 不完整的 | 返回有序窗口分区中的 ntile 组 ID。例如，如果 n 为 4，第一个四分之一的行的值为 1，第二个四分之一的行的值为 2，依此类推。 | | 行数 | 返回一个帧中从 1 开始的序列号。 |
让我们通过一个小样本数据集来演示窗口函数功能，从而将这些步骤放在一起。表 4-6 包含两个虚拟用户:John 和 Mary 的购物交易数据。
表 4-6
用户购物交易
|名称
|
日期
|
金额
| | --- | --- | --- | | 约翰 | 2017-07-02 | Thirteen point three five | | 约翰 | 2016-07-06 | Twenty-seven point three three | | 约翰 | 2016-07-04 | Twenty-one point seven two | | 玛丽 | 2017-07-07 | Sixty-nine point seven four | | 玛丽 | 2017-07-01 | Fifty-nine point four four | | 玛丽 | 2017-07-05 | Eighty point one four |
有了这些购物交易数据，让我们试着用窗口函数来回答
以下问题。
-
对于每个用户，最高的两笔交易金额是多少？
-
每个用户的交易金额和他们的最高交易金额相差多少？
-
每个用户的移动平均交易金额是多少？
-
每个用户的交易金额累计和是多少？
要回答第一个问题，您可以在一个窗口规范上应用rank window 函数，该窗口规范按用户对数据进行分区，并按降序对数据进行排序。等级窗口函数根据每一帧中每一行的排序顺序为每一行分配一个等级。清单 4-52 是解决第一个问题的实际代码。
// small shopping transaction dataset for two users
val txDataDF= Seq(("John", "2017-07-02", 13.35),
("John", "2017-07-06", 27.33),
("John", "2017-07-04", 21.72),
("Mary", "2017-07-07", 69.74),
("Mary", "2017-07-01", 59.44),
("Mary", "2017-07-05", 80.14))
.toDF("name", "tx_date", "amount")
// import the Window class
import org.apache.spark.sql.expressions.Window
// define window specification to partition by name
// and order by amount in descending amount
val forRankingWindow =
Window.partitionBy("name").orderBy(desc("amount"))
// add a new column to contain the rank of each row,
// apply the rank function to rank each row
val txDataWithRankDF =
txDataDF.withColumn("rank", rank().over(forRankingWindow))
// filter the rows down based on the rank to find
// the top 2 and display the result
txDataWithRankDF.where('rank < 3).show(10)
+------+-----------+-------+-----+
| name| tx_date| amount| rank|
+------+-----------+-------+-----+
| Mary| 2017-07-05| 80.14| 1|
| Mary| 2017-07-07| 69.74| 2|
| John| 2017-07-06| 27.33| 1|
| John| 2017-07-04| 21.72| 2|
+------+-----------+-------+-----+
Listing 4-52Apply the Rank Window Function to Find out the Top Two Transactions per User
解决第二个问题的方法包括对所有分区行的 amount 列应用max函数。除了按照用户名进行分区之外，还需要定义一个包含每个分区中所有行的框架边界。为此，您使用Window.rangeBetween函数，将Window. unboundedPreceding作为起始值，将Window.unboundedFollowing作为结束值。清单 4-53 根据之前定义的逻辑定义了一个窗口规范，并对其应用了max函数。
// use rangeBetween to define the frame boundary that includes
// all the rows in each frame
val forEntireRangeWindow =
Window.partitionBy("name").orderBy(desc("amount"))
.rangeBetween(Window.unboundedPreceding,
Window.unboundedFollowing)
// apply the max function over the amount column and then compute // the difference
val amountDifference =
max(txDataDF("amount")).over(forEntireRangeWindow) -
txDataDF("amount")
// add the amount_diff column using the logic defined above
val txDiffWithHighestDF =
txDataDF.withColumn("amount_diff", round(amountDifference, 3))
// display the result
txDiffWithHighestDF.show
+------+-----------+-------+-------------+
| name| tx_date| amount| amount_diff|
+------+-----------+-------+-------------+
| Mary| 2017-07-05| 80.14| 0.0|
| Mary| 2017-07-07| 69.74| 10.4|
| Mary| 2017-07-01| 59.44| 20.7|
| John| 2017-07-06| 27.33| 0.0|
| John| 2017-07-04| 21.72| 5.61|
| John| 2017-07-02| 13.35| 13.98|
+------+-----------+-------+-------------+
Listing 4-53Applying the max Window Function to Find the Difference of Each Row and the Highest Amount
为了按照交易日期的顺序计算每个用户的交易量移动平均值，您可以利用avg函数根据一个帧中的一组行来计算每行的平均交易量。对于本例，您希望每个框架包括三行:当前行加上它前面的一行和它后面的一行。根据特定的用例，帧可能在当前行之前和之后包含更多的行。与前面的示例一样，窗口规范按用户划分数据，但是每个框架中的行是按事务日期排序的。清单 4-54 展示了如何将avg函数应用到前面描述的窗口规范中。
// define the window specification
// a good practice is to specify the offset relative to
// Window.currentRow
val forMovingAvgWindow =
Window.partitionBy("name").orderBy("tx_date")
.rowsBetween(Window.currentRow-1,Window.currentRow+1)
// apply the average function over the amount column over the
// window specification
// also round the moving average amount to 2 decimals
val txMovingAvgDF = txDataDF.withColumn("moving_avg",
round(avg("amount").over(forMovingAvgWindow), 2))
// display the result
txMovingAvgDF.show
+------+-----------+-------+-----------+
| name| tx_date| amount| moving_avg|
+------+-----------+-------+-----------+
| Mary| 2017-07-01| 59.44| 69.79|
| Mary| 2017-07-05| 80.14| 69.77|
| Mary| 2017-07-07| 69.74| 74.94|
| John| 2017-07-02| 13.35| 17.54|
| John| 2017-07-04| 21.72| 20.8|
| John| 2017-07-06| 27.33| 24.53|
+------+-----------+-------+-----------+
Listing 4-54Applying the Average Window Function to Compute the Moving Average Transaction Amount
要计算每个用户的交易量的累积和，请对包含所有行直到当前行的帧应用sum函数。partition by 和 order by 子句与移动平均示例相同。清单 4-55 展示了如何将sum函数应用到前面描述的窗口规范中。
// define the window specification with each frame includes all
// the previous rows and the current row
val forCumulativeSumWindow =
Window.partitionBy("name").orderBy("tx_date")
.rowsBetween(Window.unbounded
Preceding,Window.currentRow)
// apply the sum function over the window specification
val txCumulativeSumDF =
txDataDF.withColumn("culm_sum",round(sum("amount")
.over(forCumulativeSumWindow),2))
// display the result
txCumulativeSumDF.show
+------+-----------+-------+---------+
| name| tx_date| amount| culm_sum|
+------+-----------+-------+---------+
| Mary| 2017-07-01| 59.44| 59.44|
| Mary| 2017-07-05| 80.14| 139.58|
| Mary| 2017-07-07| 69.74| 209.32|
| John| 2017-07-02| 13.35| 13.35|
| John| 2017-07-04| 21.72| 35.07|
| John| 2017-07-06| 27.33| 62.4|
+------+-----------+-------+---------+
Listing 4-55Applying the sum Window Function to Compute the Cumulative Sum of Transaction Amount
窗口规范的默认框架包括所有前面的行以及当前行。在清单 4-55 中，没有必要指定框架，所以你应该得到相同的结果。窗口函数示例是使用 DataFrame APIs 编写的。您可以使用带有PARTITION BY、ORDER BY、ROWS BETWEEN和RANGE BETWEEN关键字的 SQL 来实现相同的目标。
可以使用以下关键字来指定帧边界:UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW、、、、、FOLLOWING。清单 4-56 显示了在 SQL 中使用窗口函数的例子。
// register the txDataDF as a temporary view called tx_data
txDataDF.createOrReplaceTempView("tx_data")
// use RANK window function to find top two highest transaction amount
spark.sql("select name, tx_date, amount, rank from
(
select name, tx_date, amount,
RANK() OVER (PARTITION BY name ORDER BY amount DESC) as rank
from tx_data
) where rank < 3").show
// difference between maximum transaction amount
spark.sql("select name, tx_date, amount, round((max_amount -
amount),2) as amount_diff from
(
select name, tx_date, amount, MAX(amount) OVER
(PARTITION BY name ORDER BY amount DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as max_amount from tx_data)").show
// moving average
spark.sql("select name, tx_date, amount, round(moving_avg,2) as moving_avg from
(
select name, tx_date, amount, AVG(amount) OVER
(PARTITION BY name ORDER BY tx_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as moving_avg from tx_data)"
).show
// cumulative sum
spark.sql("select name, tx_date, amount, round(culm_sum,2) as moving_avg from
(
select name, tx_date, amount, SUM(amount) OVER
(PARTITION BY name ORDER BY tx_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as culm_sum from tx_data)"
).show
Listing 4-56Example of a Window Function in SQL
在 SQL 中使用窗口函数时，partition by、order by 和 frame
必须在单个语句中指定 window。
探索催化剂优化器
编写高效的数据处理应用程序的最简单方法是不要担心它，并自动优化您的数据处理应用程序。这是 Spark Catalyst 的承诺，它是一个查询优化器，是 Spark SQL 模块中的第二个主要组件。它在确保用 DataFrame APIs 或 SQL 编写的数据处理逻辑高效快速运行方面起着重要作用。它旨在最小化端到端的查询响应时间，并且是可扩展的，这样 Spark 用户就可以将用户代码注入到优化器中来执行定制优化。
在高层次上，Spark Catalyst 将用户编写的数据处理逻辑转换为逻辑计划，然后使用试探法对其进行优化，最后将逻辑计划转换为物理计划。最后一步是根据物理规划生成代码。图 4-6 提供了这些步骤的直观表示。
图 4-6
催化剂优化器
逻辑计划
Catalyst 优化过程的第一步是从一个DataFrame对象或解析的 SQL 查询的抽象语法树创建一个逻辑计划。逻辑计划是用户数据处理逻辑在操作符和表达式树中的内部表示。接下来，Catalyst 分析逻辑计划来解析引用，以确保它们是有效的。然后，它对逻辑计划应用一组基于规则和基于成本的优化。这两种类型的优化都遵循尽早删除不必要的数据和最小化每个操作符成本的原则。
基于规则的优化包括常量合并、项目修剪、谓词下推等。例如，在这个优化阶段，Catalyst 可能决定在执行连接之前移动过滤条件。出于好奇，基于规则的优化列表是在org.apache.spark.sql.catalyst.optimizer.Optimizer类中定义的。
Spark 2.2 中引入了基于成本的优化，使 Catalyst 能够更智能地根据正在处理的数据的统计信息选择正确的连接类型。基于成本的优化依赖于参与筛选或连接条件的列的详细统计信息，这就是引入统计信息收集框架的原因。统计数据的示例包括基数、不同值的数量、最大值/最小值以及平均长度/最大长度。
物理计划
一旦逻辑计划得到优化，Catalyst 就会使用与 Spark 执行引擎相匹配的物理操作符来生成物理计划。除了在逻辑规划阶段执行的优化之外，物理规划阶段还执行自己的基于规则的优化，包括将投影和过滤合并到单个操作中，并将投影或过滤谓词下推到支持此功能的数据源，即 Parquet。同样，这些优化遵循数据修剪原则。Catalyst 执行的最后一步是生成最便宜的物理计划的 Java 字节码。
催化剂在起作用
本节展示了如何使用DataFrame类的explain函数来显示逻辑和物理计划。
您可以将 explain 函数的扩展参数作为布尔值 true 来调用，以查看逻辑和物理计划。否则，此功能仅显示物理平面图。
这个小而有点傻的示例首先读取 Parquet 格式的电影数据，根据 produced_year 执行过滤，添加一个名为 produced_ decade 的列，并投影 movie_title 和 produced_decade 列，最后根据produced_decade过滤行。这里的目标是通过向explain函数传递一个布尔true值来检查生成的逻辑和物理计划，从而证明 Catalyst 执行了谓词下推和过滤条件优化。在输出中，您可以看到四个部分:解析的逻辑计划、分析的逻辑计划、优化的逻辑计划和物理计划。清单 4-57 展示了如何生成逻辑和物理计划。
// read movies data in Parquet format
val moviesDF =
spark.read.load("<path>/book/chapter4/data/movies/movies.
parquet")
// perform two filtering conditions
val newMoviesDF = moviesDF.filter('produced_year > 1970)
.withColumn("produced_decade",
'produced_year + 'produced_year % 10)
val latestMoviesDF = newMoviesDF.select('movie_title,
'produced_decade)
.where('produced_decade > 2010)
// display both logical and physical plans
latestMoviesDF.explain(true)
== Parsed Logical Plan ==
'Filter ('produced_decade > 2010)
+- Project [movie_title#673, produced_decade#678L]
+- Project [actor_name#672, movie_title#673, produced_year#674L, (produced_year#674L + (produced_year#674L % cast(10 as bigint))) AS produced_decade#678L]
+- Filter (produced_year#674L > cast(1970 as bigint))
+- Relation[actor_name#672,movie_title#673,produced_year#674L] parquet
== Analyzed Logical Plan ==
movie_title: string, produced_decade: bigint
Filter (produced_decade#678L > cast(2010 as bigint))
+- Project [movie_title#673, produced_decade#678L]
+- Project [actor_name#672, movie_title#673, produced_year#674L, (produced_year#674L + (produced_year#674L % cast(10 as bigint))) AS produced_decade#678L]
+- Filter (produced_year#674L > cast(1970 as bigint))
+- Relation[actor_name#672,movie_title#673,produced_year#674L] parquet
== Optimized Logical Plan ==
Project [movie_title#673, (produced_year#674L + (produced_year#674L % 10)) AS produced_decade#678L]
+- Filter ((isnotnull(produced_year#674L) AND (produced_year#674L > 1970)) AND ((produced_year#674L + (produced_year#674L % 10)) > 2010))
+- Relation[actor_name#672,movie_title#673,produced_year#674L] parquet
== Physical Plan ==
*(1) Project [movie_title#673, (produced_year#674L + (produced_year#674L % 10)) AS produced_decade#678L]
+- *(1) Filter ((isnotnull(produced_year#674L) AND (produced_year#674L > 1970)) AND ((produced_year#674L + (produced_year#674L % 10)) > 2010))
+- *(1) ColumnarToRow
+- FileScan parquet [movie_title#673,produced_year#674L] Batched: true, DataFilters: [isnotnull(produced_year#674L), (produced_year#674L > 1970), ((produced_year#674L + (produced_yea..., Format: Parquet, Location: InMemoryFileIndex[file:<path>/chapter4/data/movies/..., PartitionFilters: [], PushedFilters: [IsNotNull(produced_year), GreaterThan(produced_year,1970)], ReadSchema: struct<movie_title:string,produced_year:bigint>
Listing 4-57Using the explain Function to Generate the Logical and Physical Plans
如果您仔细分析优化的逻辑计划，您会看到它将两个过滤条件组合成一个过滤器。物理规划显示 Catalyst 在 FileScan 步骤中同时下推 produced_year 的过滤并执行预测修剪，以最佳方式仅读入所需的数据。
在 Spark 3.0 中，引入了explain函数的新变体。它接受一个字符串形式的输入，允许你指定在输出中看到五种模式中的哪一种(见表 4-7 )。
表 4-7
输出格式的各种模式
|方式
|
描述
| | --- | --- | | 简单的 | 仅打印物理计划。 | | 延长 | 打印逻辑和物理计划。 | | codegen(代码基因) | 打印物理计划和生成的代码(如果它们可用)。 | | 费用 | 打印逻辑计划和统计数据(如果有)。 | | 格式化 | 将解释输出分成两部分；物理计划大纲和细节。 |
最后三个选项生成新信息。检查生成的 Scala 代码并把它作为一个练习留给你是很有趣的。formatted选项的输出可读性更好，也更容易理解。清单 4-58 显示了如何在formatted模式下使用explain功能。
latestMoviesDF.explain("formatted")
== Physical Plan ==
* Project (4)
+- * Filter (3)
+- * ColumnarToRow (2)
+- Scan parquet (1)
(1) Scan parquet
Output [2]: [movie_title#673, produced_year#674L]
Batched: true
Location: InMemoryFileIndex [file:<path>/chapter4/data/movies/movies.parquet]
PushedFilters: [IsNotNull(produced_year), GreaterThan(produced_year,1970)]
ReadSchema: struct<movie_title:string,produced_year:bigint>
(2) ColumnarToRow [codegen id : 1]
Input [2]: [movie_title#673, produced_year#674L]
(3) Filter [codegen id : 1]
Input [2]: [movie_title#673, produced_year#674L]
Condition : ((isnotnull(produced_year#674L) AND (produced_year#674L > 1970)) AND ((produced_year#674L + (produced_year#674L % 10)) > 2010))
(4) Project [codegen id : 1]
Output [2]: [movie_title#673, (produced_year#674L + (produced_year#674L % 10)) AS produced_decade#678L]
Input [2]: [movie_title#673, produced_year#674L]
Listing 4-58Using the explain Function with formatted Mode
输出清楚地显示了 Spark 计算 latestMoviesDF 的四个步骤:扫描或读取输入的 parquet 文件，将列格式的数据转换为行，根据两个指定的条件进行筛选，最后投影标题并生成 decade 列。
钨项目
从 2015 年开始，Spark 设计师发现 Spark 工作负载越来越多地受到 CPU 和内存的瓶颈，而不是 I/O 和网络通信。这有点违反直觉，但并不令人惊讶，因为硬件方面的进步，如 10Gbps 网络链接和高速 SSD。钨计划旨在提高 Spark 应用程序中内存和 CPU 的使用效率，并将性能推向现代硬件的极限。钨项目有三项举措。
-
通过使用堆外管理技术来明确地管理内存，以消除 JVM 对象模型的开销并最小化垃圾收集。
-
使用智能缓存感知算法和数据结构来利用内存层次。
-
通过将多个运算符组合成一个函数，使用全阶段代码生成来最大限度地减少虚函数调用。
钨项目中艰苦而有趣的工作极大地改进了 Spark 2.0 以来的 Spark 执行引擎。钨项目中的大部分工作发生在执行引擎的幕后。下面的例子通过检查物理计划，展示了对整个阶段代码生成计划的一点了解。在下面的输出中，每当一个星号(*)出现在一个运算符之前，就意味着启用了全阶段代码生成。清单 4-59 显示了对数据帧中的整数进行过滤和求和的物理计划。
spark.range(1000).filter("id > 100")
.selectExpr("sum(id)").explain("formatted")
== Physical Plan ==
* HashAggregate (5)
+- Exchange (4)
+- * HashAggregate (3)
+- * Filter (2)
+- * Range (1)
(1) Range [codegen id : 1]
Output [1]: [id#719L]
Arguments: Range (0, 1000, step=1, splits=Some(12))
(2) Filter [codegen id : 1]
Input [1]: [id#719L]
Condition : (id#719L > 100)
(3) HashAggregate [codegen id : 1]
Input [1]: [id#719L]
Keys: []
Functions [1]: [partial_sum(id#719L)]
Aggregate Attributes [1]: [sum#726L]
Results [1]: [sum#727L]
(4) Exchange
Input [1]: [sum#727L]
Arguments: SinglePartition, ENSURE_REQUIREMENTS, [id=#307]
(5) HashAggregate [codegen id : 2]
Input [1]: [sum#727L]
Keys: []
Functions [1]: [sum(id#719L)]
Aggregate Attributes [1]: [sum(id#719L)#723L]
Results [1]: [sum(id#719L)#723L AS sum(id)#724L]
Listing 4-59Demonstrating the Whole-Stage Code Generation by Looking at the Physical Plan
全阶段代码生成将过滤和对整数求和的逻辑结合到一个函数中。
摘要
本章介绍了 Spark SQL 模块中许多有用且强大的特性。
-
聚合是大数据分析领域最常用的功能之一。Spark SQL 提供了许多常用的聚合函数，比如
sum、count和avg。旋转聚合提供了一种很好的汇总数据以及将列转置为行的方式。 -
执行任何复杂且有意义的数据分析或处理通常需要连接两个或更多数据集。Spark SQL 支持 SQL 世界中存在的许多标准连接类型。
-
Spark SQL 附带了一组丰富的内置函数，应该涵盖了处理字符串、数学、日期和时间等的大多数常见需求。如果没有一个满足用例的特定需求，那么很容易编写一个用户定义的函数，它可以与 DataFrame APIs 和 SQL 查询一起使用。
-
窗口函数是强大的高级分析函数，因为它们可以计算输入组中每一行的值。它们对于计算移动平均值、累计和或每行的排名特别有用。
-
Catalyst optimizer 使您能够编写高效的数据处理应用程序。Spark 2.2 中引入了基于成本的优化器，使 Catalyst 能够更智能地根据收集到的处理数据的统计信息选择正确的连接实现。
-
钨项目是幕后的主力，它通过采用一些先进的技术来提高内存和 CPU 的使用效率，从而加速数据处理应用程序的执行。
