Hive之列转行,行转列

By timebusker on February 20, 2018

数据准备

104399    1715131
104399    2105395
104399    1758844
104399    981085
104399    2444143
104399    1458638
104399    968412
104400    1609001
104400    2986088
104400    1795054

create table cookie4 (id string,value bigint) row format delimited fields terminated by "\t";
load data local inpath '/root/data/cookie4' overwrite into table cookie4;
select * from cookie4;

列转行

把相同id的value按照逗号转为一行

collect_list 不去重,collect_set 去重。 column的数据类型要求是string

select id,
concat_ws(',',collect_list(value)) as values 
from cookie4
group by id;

+---------+--------------------------------------------------------+--+
|   id    |                         values                         |
+---------+--------------------------------------------------------+--+
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  |
| 104400  | 1609001,2986088,1795054                                |
+---------+--------------------------------------------------------+--+

行转列

create table cookie41 as
select id,
concat_ws(',',collect_list(value)) as values 
from cookie4
group by id;

select * from cookie41;
+---------+--------------------------------------------------------+--+
|   id    |                         values                         |
+---------+--------------------------------------------------------+--+
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  |
| 104400  | 1609001,2986088,1795054                                |
+---------+--------------------------------------------------------+--+

# 列转行
drop table if exists cookie42;
create table cookie42 as
select id,values,value
from cookie41
lateral view explode(split(values,',')) as value;

+---------+--------------------------------------------------------+----------+--+
|   id    |                         values                         |  value   |
+---------+--------------------------------------------------------+----------+--+
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 1715131  |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 2105395  |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 1758844  |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 981085   |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 2444143  |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 1458638  |
| 104399  | 1715131,2105395,1758844,981085,2444143,1458638,968412  | 968412   |
| 104400  | 1609001,2986088,1795054                                | 1609001  |
| 104400  | 1609001,2986088,1795054                                | 2986088  |
| 104400  | 1609001,2986088,1795054                                | 1795054  |
+---------+--------------------------------------------------------+----------+--+