Hive与Presto列转行的区别
1、背景描述
在处理数据时,我们经常会遇到一个字段存储多个值,这时需要把一行数据转换为多行数据,形成标准的结构化数据
例如,将下面的两列数据并列转换为三行,使得
code
和
name
一一对应
idcodename1a、b、cA、B、C
Hive、Spark和Presto都提供了这种实现,但有所不同。下面通过这个案例介绍三者之间的区别及注意事项
2、Hive/Spark列转行
Hive和Spark都可以使用
lateral view posexplode
实现:
select id, pos1, sub_code, pos2, sub_name from tmp
lateral view posexplode(split(code,'、')) v1 as pos1, sub_code
lateral view posexplode(split(name,'、')) v2 as pos2, sub_name
where id='1'and pos1=pos2
Hive On MapReduce与Hive On Spark的执行结果如下:
idsub_codesub_name1aA1bB1cC
值得注意的是,
lateral view posexplode
会自动过滤被转换列字段值为空的数据,进而导致数据丢失
优化方案是将
lateral view
修改为
lateral view outer
后尝试
更多关于
lateral view UDTF
的使用见文章:传送门
3、Presto列转行
使用PrestoSQL的交叉连接
cross join unnest
实现:
with t1 as(select id,sub_code,row_number()over() rn
fromtempcrossjoin unnest(split(code,'、'))as t (sub_code)where id='1'),
t2 as(select id,sub_name,row_number()over() rn
fromtempcrossjoin unnest(split(name,'、'))as t (sub_name)where id='1')select t1.id, t1.sub_code, t2.sub_name
from t1
leftjoin t2
on t1.rn = t2.rn
orderby t1.rn
PrestoSQL的执行结果如下:
idsub_codesub_name1bB1aA1cC
需要注意的是,
cross join unnest
不会自动过滤被转换列和转换列字段值为空的数据,因此此方式数据不会丢失
例如,当转换列字段值存在空值时:
idcodename1a、b、cA、B
cross join unnest
列转行的结果为
idsub_codesub_name1aA1cNULL1bB
当被转换列字段值存在空值时:
idcodename1a、b、cNULL
cross join unnest
列转行的结果为
idsub_codesub_name1bNULL1aNULL1cNULL
版权归原作者 对许 所有, 如有侵权,请联系我们删除。