行转列是指多行数据转换为一个列的字段。
列转行是值某一个字段转换成多行显示。
行转列
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
建表
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;