Hive与Presto中的列转行区别
Hive与Presto列转行的区别
- 1、背景描述
- 2、Hive/Spark列转行
- 3、Presto列转行
1、背景描述
在处理数据时，我们经常会遇到一个字段存储多个值，这时需要把一行数据转换为多行数据，形成标准的结构化数据
例如，将下面的两列数据并列转换为三行，使得code和name一一对应
| id | code | name |
|---|---|---|
| 1 | a、b、c | A、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的执行结果如下：
| id | sub_code | sub_name |
|---|---|---|
| 1 | a | A |
| 1 | b | B |
| 1 | c | C |
值得注意的是，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() rnfrom tempcross join unnest(split(code, '、')) as t (sub_code)where id='1'
),
t2 as (select id,sub_name,row_number() over() rnfrom tempcross join unnest(split(name, '、')) as t (sub_name)where id='1'
)
select t1.id, t1.sub_code, t2.sub_name
from t1
left join t2
on t1.rn = t2.rn
order by t1.rn
PrestoSQL的执行结果如下：
| id | sub_code | sub_name |
|---|---|---|
| 1 | b | B |
| 1 | a | A |
| 1 | c | C |
需要注意的是，cross join unnest不会自动过滤被转换列和转换列字段值为空的数据，因此此方式数据不会丢失
例如，当转换列字段值存在空值时：
| id | code | name |
|---|---|---|
| 1 | a、b、c | A、B |
cross join unnest列转行的结果为
| id | sub_code | sub_name |
|---|---|---|
| 1 | a | A |
| 1 | c | NULL |
| 1 | b | B |
当被转换列字段值存在空值时：
| id | code | name |
|---|---|---|
| 1 | a、b、c | NULL |
cross join unnest列转行的结果为
| id | sub_code | sub_name |
|---|---|---|
| 1 | b | NULL |
| 1 | a | NULL |
| 1 | c | NULL |