1、背景描述

在處理數(shù)據(jù)時，我們經(jīng)常會遇到一個字段存儲多個值，這時需要把一行數(shù)據(jù)轉(zhuǎn)換為多行數(shù)據(jù)，形成標(biāo)準(zhǔn)的結(jié)構(gòu)化數(shù)據(jù)

例如，將下面的兩列數(shù)據(jù)并列轉(zhuǎn)換為三行，使得code和name一一對應(yīng)

id	code	name
1	a、b、c	A、B、C

Hive、Spark和Presto都提供了這種實現(xiàn)，但有所不同。下面通過這個案例介紹三者之間的區(qū)別及注意事項

2、Hive/Spark列轉(zhuǎn)行

Hive和Spark都可以使用lateral view posexplode實現(xiàn)：

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的執(zhí)行結(jié)果如下：

id	sub_code	sub_name
1	a	A
1	b	B
1	c	C

值得注意的是，lateral view posexplode會自動過濾被轉(zhuǎn)換列字段值為空的數(shù)據(jù)，進(jìn)而導(dǎo)致數(shù)據(jù)丟失

優(yōu)化方案是將lateral view修改為lateral view outer后嘗試

更多關(guān)于lateral view UDTF的使用見文章：傳送門

3、Presto列轉(zhuǎn)行

使用PrestoSQL的交叉連接cross join unnest實現(xiàn)：

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的執(zhí)行結(jié)果如下：

id	sub_code	sub_name
1	b	B
1	a	A
1	c	C

需要注意的是，cross join unnest不會自動過濾被轉(zhuǎn)換列和轉(zhuǎn)換列字段值為空的數(shù)據(jù)，因此此方式數(shù)據(jù)不會丟失

例如，當(dāng)轉(zhuǎn)換列字段值存在空值時：

id	code	name
1	a、b、c	A、B

cross join unnest列轉(zhuǎn)行的結(jié)果為

id	sub_code	sub_name
1	a	A
1	c	NULL
1	b	B

當(dāng)被轉(zhuǎn)換列字段值存在空值時：

id	code	name
1	a、b、c	NULL

cross join unnest列轉(zhuǎn)行的結(jié)果為

id	sub_code	sub_name
1	b	NULL
1	a	NULL
1	c	NULL