Hive行转列

行转列

把Hive中,一行的数据转换成多条数据

test表数据:

1
2
3
4
5
6
7
8
9
a	aaa
b bbb
c ccc
d ddd
e eee
f fff
g ggg
h hhh
i iii

构建使用分隔符分割 (“,”) 的数据
1
SELECT CONCAT_WS(',', '1', '2', '3', '4', '5', '6', '7', '8', '9') FROM test;

数据为:

1
1,2,3,4,5,6,7,8,9

使用分割函数 split
1
SELECT split(CONCAT_WS(',', '1', '2', '3', '4', '5', '6', '7', '8', '9'), ",") FROM test;

数据为:

1
["1","2","3","4","5","6","7","8","9"]

使用行专列函数 explode
1
select explode(split(concat_ws(',', '1', '2', '3', '4', '5', '6', '7', '8', '9'), ',')) from test;

数据为:

1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

使用lateral view 把其他字段显示出来
1
select a.*, b from test a lateral view explode(split(concat_ws(',', '1', '2', '3', '4', '5', '6', '7', '8', '9'), ',')) t as b;

数据为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
a	aaa	1
a aaa 2
a aaa 3
a aaa 4
a aaa 5
a aaa 6
a aaa 7
a aaa 8
a aaa 9
b bbb 1
b bbb 2
b bbb 3
b bbb 4
b bbb 5
b bbb 6
b bbb 7
b bbb 8
b bbb 9
…………