关注 spark技术分享,
撸spark源码 玩spark最佳实践

案例|使用 spark Pivot 处理复杂的数据统计需求

Pivot 算子是 spark 1.6 版本开始引入的,在 spark2.4版本中功能做了增强,还是比较强大的,做过数据清洗ETL工作的都知道,行列转换是一个常见的数据整理需求。spark 中的Pivot 可以根据枢轴点(Pivot Point) 把多行的值归并到一行数据的不同列,这个估计不太好理解,我们下面使用例子说明,看看pivot 这个算子在处理复杂数据时候的威力。

关键词:spark pivot用法 spark dataframe转置 spark行转列 spark pivot scala spark列转行

使用Pivot 来统计天气走势

下面是西雅图的天气数据表,每行代表一天的天气最高值:

Date Temp (°F)
07-22-2018 86
07-23-2018 90
07-24-2018 91
07-25-2018 92
07-26-2018 92
07-27-2018 88
07-28-2018 85
07-29-2018 94
07-30-2018 89

如果我们想看下最近几年的天气走势,如果这样一天一行数据,是很难看出趋势来的,最直观的方式是 按照年来分行,然后每一列代表一个月的平均天气,这样一行数据,就可以看到这一年12个月的一个天气走势,下面我们使用 pivot 来构造这样一个查询结果:

案例|使用 spark Pivot 处理复杂的数据统计需求

结果如下图:

案例|使用 spark Pivot 处理复杂的数据统计需求

 

是不是很直观,第一行就代表 2018 年,从1月到12月的平均天气,能看出一年的天气走势。

关键词:spark pivot用法 spark dataframe转置 spark行转列 spark pivot scala spark列转行

这个SQL应该怎么理解

我们来看下这个 sql 是怎么玩的,首先是一个 子查询语句,我们最终关心的是 年份,月份,和最高天气值,所以先使用子查询对原始数据进行处理,从日期里面抽取出来年份和月份。

下面在子查询的结果上使用 pivot 语句,pivot 第一个参数是一个聚合语句,这个代表聚合出来一个月30天的一个平均气温,第二个参数是 FOR month,这个是指定以哪个列为枢轴列,第三个 In 子语句指定我们需要进行行列转换的具体的 枢轴点(Pivot Point)的值,上面的例子中 1到12月份都包含了,而且给了一个别名,如果只指定 1到6月份,结果就如下了:

案例|使用 spark Pivot 处理复杂的数据统计需求

上面sql语句里面有个特别的点需要注意, 就是聚合的时候有个隐含的维度字段,就是 年份,按理来讲,我们没有写  group-by year, 为啥结果表里面不同年份区分在了不同的行,原因是,FORM 子查询出来的每行有 3个列, year,month,tmp,如果一个列既不出现在进行聚合计算的列中(temp 是聚合计算的列), 也不作为枢轴列 , 就会作为聚合的时候一个隐含的维度。我们的例子中算平均值聚合操作的维度是 (year, month),一个是隐含维度,一个是 枢轴列维度, 这一点一定要注意,如果不需要按照 year 来区分,FORM 查询的时候就不要加上这个列。

关键词:spark pivot用法 spark dataframe转置 spark行转列 spark pivot scala spark列转行

指定多个聚合语句

上文中只有一个聚合语句,就是计算平均天气,其实是可以加多个聚合语句的,比如我们需要看到 7,8,9 月份每个月的最大气温和平均气温,就可以用以下SQL语句

 

案例|使用 spark Pivot 处理复杂的数据统计需求

上文中指定了两个聚合语句,查询后, 枢轴点(Pivot Point)  和 聚合语句 的笛卡尔积作为结果的不同列,也就是  <value>_<aggExpr>, 看下图:

 

案例|使用 spark Pivot 处理复杂的数据统计需求

聚合列(Grouping Columns)和 枢轴列(Pivot Columns)的不同之处

现在假如我们有西雅图每天的最低温数据,我们需要把最高温和最低温放在同一张表里面看对比着看

Date Temp (°F)
08-01-2018 59
08-02-2018 58
08-03-2018 59
08-04-2018 58
08-05-2018 59
08-06-2018 59

 

我们使用  UNION ALL 把两张表做一个合并:

案例|使用 spark Pivot 处理复杂的数据统计需求

 

现在使用 pivot 来进行处理:

案例|使用 spark Pivot 处理复杂的数据统计需求

我们统计了 4年中  7,8,9 月份最低温和最高温的平均值,这里要注意的是,我们把 year 和 一个最低最高的标记(H/L)都作为隐含维度,不然算出来的就是最低最高温度在一起的平均值了。结果如下图:

案例|使用 spark Pivot 处理复杂的数据统计需求

上面的查询中,我们把最低最高的标记(H/L)也作为了一个隐含维度,group-by 的维度就变成了 (year, H/L, month), 但是year 和 H/L 体现在了不同行上面,month 体现在了不同的列上面,这就是  聚合列(Grouping Columns)和 枢轴列(Pivot Columns)的不同之处。

再接再厉,我们把 H/L 作为  枢轴列(Pivot Columns) 进行查询:

案例|使用 spark Pivot 处理复杂的数据统计需求

 

结果的展现方式就和上面的不同了,虽然每个单元格值都是相同的,但是把  H/L 和 month 笛卡尔积作为列,H/L 维度体现在了列上面了:

案例|使用 spark Pivot 处理复杂的数据统计需求

关键词:spark pivot用法 spark dataframe转置 spark行转列 spark pivot scala spark列转行

大家都在看

spark sql源码系列:

是时候学习真正的spark技术了从0到1认识 spark sqlspark sql 源码剖析 PushDownPredicatespark sql 源码剖析 OptimizeIn 篇

structured streaming 系列:

structured streaming 原理剖析structured streaming 碰上kafkastructured streaming 是如何搞定乱序时间的

spark streaming 系列:

spark streaming 读取kafka各种姿势详解spark streaming流式计算中的困境与解决之道

spark core 系列:

彻底搞懂spark shuffle过程(1)彻底搞懂spark shuffle过程(2)spark内存管理-Tungsten框架探秘

spark 机器学习系列

关注【spark技术分享】

一起撸spark源码,一起玩spark最佳实践

案例|使用 spark Pivot 处理复杂的数据统计需求

原文始发于微信公众号(spark技术分享):案例|使用 spark Pivot 处理复杂的数据统计需求

赞(0) 打赏
未经允许不得转载:spark技术分享 » 案例|使用 spark Pivot 处理复杂的数据统计需求
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

关注公众号:spark技术分享

联系我们联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏