hive行列转换函数使用示例

hive 行列转换函数

行转列 多行合并为一列

使用函数：concat_ws(‘,’,collect_set(column))

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

示例:

构建测试数据

vim  row_to_col.txt
a	b	1
a	b	2
a	b	3
c	d	4
c	d	5
c	d	6		

建表

create table tabname
(
col1 String,
col2 String,
col3 stirng
)
row format delimited fields terminated by '\t'
stored as textfile;

加载数据

load data local inpath '/root/row_to_col.txt' into table tabname;

执行行转换

select col1, col2,concat_ws(',',collect_set(col3))as bian
from tabname 
group  by  col1,col2;

列转行 对某列拆分 一列拆多行

使用函数：lateral view explode(split(column, ‘,’)) num

构建测试数据

vim tabfile.TXT
a	b	1,2,3
c	d	4,5,6

建表

create table  tabname 
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by '\t'
stored as textfile;

加载数据

load data local inpath '/roottabfile.TXT' into table tabname;

执行转换

select col1, col2, col3_new 
from tabname a 
lateral  view explode(split(col3,',')) b AS col3_new;