+----------+----------------------+--+
| a.id | a.tim |
+----------+----------------------+--+
| a,b,c,d | 2:00,3:00,4:00,5:00 |
| f,b,c,d | 1:10,2:20,3:30,4:40 |
+----------+----------------------+--+
explode 炸裂函数,一列变多行
select id,tim,single_tim
from atlasdemo.a
lateral view explode(split(tim,',')) t as single_tim
+----------+----------------------+-------------+--+
| id | tim | single_tim |
+----------+----------------------+-------------+--+
| a,b,c,d | 2:00,3:00,4:00,5:00 | 2:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 3:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 4:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 5:00 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 1:10 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 2:20 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 3:30 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 4:40 |
+----------+----------------------+-------------+--+
posexplode炸裂除了会炸开数组/map,还会对应生成索引下标。
select id,tim,single_id_index,single_id
from atlasdemo.a
lateral view posexplode(split(id,',')) t as single_id_index, single_id;
+----------+----------------------+------------------+------------+--+
| id | tim | single_id_index | single_id |
+----------+----------------------+------------------+------------+--+
| a,b,c,d | 2:00,3:00,4:00,5:00 | 0 | a |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 1 | b |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 2 | c |
| a,b,c,d | 2:00,3:00,4:00,5:00 | 3 | d |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 0 | f |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 1 | b |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 2 | c |
| f,b,c,d | 1:10,2:20,3:30,4:40 | 3 | d |
+----------+----------------------+------------------+------------+--+
如果想实现对两列同时进行多行转换,那么用explode()函数就不能实现了,
但可以用posexplode()函数,因为该函数可以将index和数据都取出来,
使用两次posexplode并令两次取到的index相等就行了。
select id,tim,single_id,single_tim from atlasdemo.a
lateral view posexplode(split(id,',')) t as single_id_index, single_id
lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim
where single_id_index = single_yim_index;
+----------+----------------------+------------+-------------+--+
| id | tim | single_id | single_tim |
+----------+----------------------+------------+-------------+--+
| a,b,c,d | 2:00,3:00,4:00,5:00 | a | 2:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | b | 3:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | c | 4:00 |
| a,b,c,d | 2:00,3:00,4:00,5:00 | d | 5:00 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | f | 1:10 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | b | 2:20 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | c | 3:30 |
| f,b,c,d | 1:10,2:20,3:30,4:40 | d | 4:40 |
+----------+----------------------+------------+-------------+--+