Hive之行转列和列转行

行转列是指多行数据转换为一个列的字段。

列转行是值某一个字段转换成多行显示。

行转列

Hive行转列用到的函数：
concat(str1,str2,...) --字段或字符串拼接
concat_ws(sep, str1,str2) --以分隔符拼接每个字符串
collect_set(col) --将某字段的值进行去重汇总，产生array类型字段

测试数据（来源：oracle自带数据集emp）

deptno  |  ename  
+---------+---------+--+
20        SMITH   
30        ALLEN   
30        WARD    
20        JONES   
30        MARTIN  
30        BLAKE   
10        CLARK   
20        SCOTT   
10        KING    
30        TURNER  
20        ADAMS   
30        JAMES   
20        FORD    
10        MILLER

View Code

建表

create table emp(
deptno int,
ename string
) row format delimited fields terminated by '	';

插入数据：

load data local inpath "/opt/data/emp.txt" into table emp;

行转列，COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;

Hive列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

测试数据

10    CLARK|KING|MILLER
20    SMITH|JONES|SCOTT|ADAMS|FORD
30    ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

建表

create table emp3(
deptno int,
names array<string>)
row format delimited fields terminated by '	'
collection items terminated by '|';

插入数据

load data local inpath "/opt/data/emp3.txt" into table emp3;

列转行

select deptno,name from emp3 lateral view explode(names) tmp_tbl as name;