題目:
假設現有一張Hive表,資料格式為:
col1 | col2 |
---|---|
a,b,c | 1:00,2:00,3:00 |
d,e,f | 4:00,5:00,6:00 |
… | … |
表中兩個字段均為String類型,現在需要将行資料拆分轉為多列如下
col1 | col2 |
---|---|
a | 1:00 |
b | 2:00 |
c | 3:00 |
d | 4:00 |
e | 5:00 |
… | … |
該如何實作
解決方法:
select
split(col1,",")[idx] as col1 ,type as col2
from test t
lateral view posexplode(split(col2,",")) t1 as idx, type -- idx為list(split(col2,","))中對應的每個索引,另外一個list根據該索引取值,就能做到一一比對