hive中的行列转换问题(知识点+案例解析)

一.多行转单列
1.涉及到的函数

• List item

  数据收集函数
  collect_set:把多行数据收集为一行,返回set集合,去重无序
  collect_list: 把多行数据收集为一行,返回list集合,不去重有序
  字符串拼接函数
  concat: 直接拼接字符串
  concat_ws: 指定分隔符拼接
  举例:
  select concat(‘a’,‘b’,‘c’)
  select concat_ws(‘,’,‘a’,‘b’)

2.案例
–原表
±---------------±---------------±---------------±-+
| word1 |word2 | num |
±---------------±---------------±---------------±-+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
±---------------±---------------±---------------±-+

–目标表
±------±------±-------±-+
| word1 | word2 | num |
±------±------±-------±-+
| a | b | 1-2-3 |
| c | d | 4-5-6 |
±------±------±-------±-+

• – 建表

create table table2(
word1 string,
word2 string,
num string
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’;

• – 导入数据

load data local inpath ‘/data/test1.txt’ into table table2;
truncate table table2;
select * from table2;

• – 多行转单列 数据处理

select word1,word2,
concat_ws(‘,’,collect_list(num)) as num
from table2
group by word1,word2;

二.单列转多行

• 1.涉及到的函数

explode + later view
2.案例
–原表
±------±------±-------±-+
| zimu1 | zimu2 | shuzi |
±------±------±-------±-+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
±------±------±-------±-+

–目标表
±---------------±---------------±---------------±-+
| zimu1 | zimu2 | shuzi |
±---------------±---------------±---------------±-+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
±---------------±---------------±---------------±-+

• – 建表

create table table1(
zimu1 string,
zimu2 string,
shuzi string
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’;

• – 导入数据

load data local inpath “/data/test.txt” into table table1;
truncate table test.table1;
select * from table1;

• –数据处理
• List item

select
w.zimu1,w.zimu2,
r.shuzi
from table1 w
lateral view explode(split(w.shuzi,‘,’)) r as shuzi;