【spark】行列互转的思考

本文基于一个具体的案例，介绍spark中case when，stack，lateral view explode，pivot的用法，并做一些性能上的比较

一、背景

群里有个小伙伴出了一道题目是行转列（多行数据转成多列数据）

如图，把上表的数据通过sql变成下表的数据

当然实现方法还是比较多的，我也想了几种方式，并随便搞了2.5亿条数据测试下性能（表格式是text，每个文件块大小都很均匀）

二、实现方法

不同引擎里，会有不同的函数或者语法做行列的转换，我这里就针对spark引擎来说，我的测试环境是spark-3.1.2，我想到了几种方法来解这道题，包括最简单的CASE WHEN，STACK，LATERAL VIEW EXPLODE，包括在2.4左右的版本才添加的PIVOT(类似excel的透视方法)，下面我来说说我的三种想法和实现

数据初始化：

CREATE TABLE `t`(
  `class` string,
  `people` string,
  `code` int,
  `weight` int);

insert into t select 'A','y',30,60 
union all select 'A','n',20,70;

1、CASE WHEN

这个语法我应该不用过多阐述，非常基础的行转列操作

SELECT class ,
    sum(CASE people WHEN 'y' THEN code ELSE 0 END ) as y_code,
    sum(CASE people WHEN 'y' THEN weight ELSE 0 END ) as y_weight,
    sum(CASE people WHEN 'n' THEN code ELSE 0 END ) as n_code,
    sum(CASE people WHEN 'n' THEN weight ELSE 0 END ) as n_weight
FROM t
GROUP BY class;

处理时长：1.3min

2、LATERAL VIEW EXPLODE + PIVOT

pivot语法，是将如下格式转换为最终的结果，所以要思考怎么将数据处理成如下格式，在方案二里用的是字段拼接(concat)把code和weight的key和值都拼到一起，再加上LATERAL VIEW EXPLODE把数据拆成多行

具体处理过程，大家可以把sql一段一段执行下看看结果是什么

class	atrribute_key	value
A	y_code	30
A	y_weight	60
A	n_code	20
A	n_weight	70

 

select * from 
(select class,
concat(people,'_',split(value1,'_')[0]) as atrribute_key,
cast(split(value1,'_')[1] as bigint) as value from
(select class,people,value1
from 
(select class,people,concat('code_',code,':','weight_',weight) agg from t)tmp 
lateral view explode(split(agg,':')) tmp as value1)tmp) models
pivot(
sum(value)
for atrribute_key in ('y_code','y_weight','n_code','n_weight'));

处理时长：10min 

3、STACK + PIVOT

最后一种是和第二种其实差不多，核心都是用pivot做最后一步处理，只是将数据转成pivot能处理的数据的方式不同，使用的是stack

select * from 
(select class,
stack(2, concat(people,'_','code'),`code`,concat(people,'_','weight'),`weight`) as (`atrribute_key`,`value`)
from t) t
pivot(
sum(value)
for atrribute_key in ('y_code','y_weight','n_code','n_weight'));

处理时长：5min（我多跑了几次，有的是4.9min，截图是5.2min） 

三、介绍下函数

LATERAL VIEW EXPLODE和CASE WHEN我想大家应该都很常见了

主要说说PIVOT和STACK这两个比较少见的

1、PIVOT

我想官方文档其实介绍的比较清楚了，大家看下面官方文档的链接就可以了

sum(value)是每个单元格填的值，for后面是把atrribute_key字段中每一种可能都作为单独的一列

当然sql写的时候，需要把所有的列的值都写出来，如果用spark的DSL的语法写的话，不需要全部列出来，对于值不确定，不清楚会有几列的场景，就比较友好了~

df.groupBy("class").pivot("atrribute_key").agg(sum("value")).show()

PIVOT Clause - Spark 3.2.1 Documentation

2、STACK

这个函数其实官方手册里没有写的很明白到底怎么用

我来解释下，stack方法，会使得一行数据变多行，并且该函数会返回多列

我总共传了5个参数，第一个参数是个数字，说明要把一行数据拆成几行

后面传入的参数依次是，第一行的第一列填入什么内容，第一行的第二列填入什么内容，在我的case中，第一行的第一列填入的是people字段的值和“_”和“code”拼接，会变成“y_code”，然后第一行的第二列填入code的值

再接着后面两个参数分别是：第二行的第一列填入什么内容，第二行的第二列填入什么内容

所以原来的表，就会转换成如下的样子

Spark SQL, Built-in Functions

四、总结

其实用了几个比较少见的花里胡哨的函数或者语法，这些函数还不一定每个计算引擎/数据库里都有，反而会带来切换引擎时的有缝切换，其实这并不是我们想看到的，因为建议大家在写sql的时候，尽量还是写些通用的语法，减少切换引擎的工作量，并且我们看到在这个例子中，CASE WHEN的性能是最好的，1.3min就跑完了，其次是STACK，最差的是LATERAL VIEW EXPLODE

所以如果列数确定，直接case when就得了，不确定的情况下，又有spark较新的版本，可以使用DSL语法实现pivot与stack结合使用

太久太久没写文章了（前两篇文章分别是22年1月和21年7月）

但是其实我的写作并没有停止，只是没写在这里罢了

对未来着实是迷茫，不知道自己的职业发展会走向何方，虽然现在每天压力也很大，也学了很多东西，但是缺乏技术的深度，我蛮担心能力会越来越钝，变得离不开现在的环境，慢慢失去竞争力，哎不知如何是好~