本文转载自博客：https://blog.csdn.net/bsf5521/article/details/76576180
一、行转列的使用
１、问题
hive如何将
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
变为：
a b 1,2,3
c d 4,5,6
２、数据
test.txt
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
３、答案
1.建表
drop table tmp_bsf_test;
create table tmp_bsf_test
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by ‘\t’
stored as textfile;
load data local inpath ‘/home/jiangzl/shell/test.txt’ into table tmp_bsf_test;
2.处理
select col1,col2,concat_ws(’,’,collect_set(col3))
from tmp_bsf_test
group by col1,col2;
二、列转行
１、问题
hive如何将
a b 1,2,3
c d 4,5,6
变为：
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
2、答案
1.建表
drop table tmp_bsf_test;
create table tmp_bsf_test
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by ‘\t’
stored as textfile;
处理：
select col1, col2, col5
from tmp_bsf_test a
lateral view explode(split(col3,’,’)) b AS col5