【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一段一段执行下看看结果是什么

classatrribute_keyvalue
Ay_code30
Ay_weight60
An_code20
An_weight70

 

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月)

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

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

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spark是一个开源的大数据处理框架,它提供了丰富的API和工具,用于处理大规模数据集。在Spark中,行列转换是指将数据从行格式转换为列格式,或者从列格式转换为行格式。下面是一个示例代码,用于实现Spark中的行列转换: ```scala import org.apache.spark.sql.{SparkSession, DataFrame} // 创建SparkSession val spark = SparkSession.builder() .appName("RowColumnTransformation") .master("local") .getOrCreate() // 创建示例数据集 val data = Seq( (1, "Alice", 25), (2, "Bob", 30), (3, "Charlie", 35) ) // 将数据集转换为DataFrame val df = spark.createDataFrame(data).toDF("id", "name", "age") // 行转列 val columnNames = df.columns val rowToColumnDF = df.selectExpr(s"stack(${columnNames.length}, ${columnNames.mkString(",")}) as (column, value)") .groupBy("column") .pivot("column") .agg(first("value")) // 列转行 val columnToRowDF = rowToColumnDF.selectExpr(s"stack(${columnNames.length}, ${columnNames.map(c => s"'$c', `$c`").mkString(",")}) as (column, value)") .groupBy("column") .pivot("column") .agg(first("value")) // 打印结果 println("行转列结果:") rowToColumnDF.show() println("列转行结果:") columnToRowDF.show() ``` 这段代码首先创建了一个SparkSession对象,然后创建了一个示例数据集。接下来,使用`createDataFrame`方法将数据集转换为DataFrame,并指定列名。然后,通过使用`selectExpr`、`groupBy`和`pivot`等方法实现了行转列和列转行的操作。最后,使用`show`方法打印结果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值