Hive：行列转换

行转列

多行转多列

假设数据表row2col：

col1   col2    col3
a      c       1
a      d       2
a      e       3  
b      c       4
b      d       5
b      e       6

现在要将其转化为：

col1   c      d      e
a      1      2      3
b      4      5      6

此时需要使用到max(case … when … then … else 0 end)，仅限于转化的字段为数值类型，且为正值的情况。

HQL语句为：

select col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from row2col
group by col1;

多行转单列

假设数据表row2col：

col1    col2    col3
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6

现在要将其转化为：

col1    col2    col3
a       b       1,2,3
c       d       4,5,6

此时需要用到内置的UDF：

1. concat_ws(separator, str1, str2, …)：把多个字符串用分隔符进行拼接
2. collect_set()：把列聚合成为数据，去重
3. collect_list()：把列聚合成为数组，不去重

HQL语句为：

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

注意：由于使用concat_ws()函数，collect_set()中的字段必须为string类型，如果是其他类型可使用cast(col3 as string)将其转换为string类型。

列转行

多列转多行

假设有数据表col2row：

col1   c      d      e
a      1      2      3
b      4      5      6

现要将其转化为：

col1   col2    col3
a      c       1
a      d       2
a      e       3
b      c       4
b      d       5
b      e       6

这里需要使用union进行拼接。

HQL语句为：

select col1, 'c' as col2, c as col3 from col2row
UNION ALL
select col1, 'd' as col2, d as col3 from col2row
UNION ALL
select col1, 'e' as col2, e as col3 from col2row
order by col1, col2;

单列转多行

假设有数据表col2row：

col1    col2    col3
a       b       1,2,3
c       d       4,5,6

现要将其转化为：

col1    col2    col3
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6

这里需要使用UDTF（表生成函数）explode()，该函数接受array类型的参数，其作用恰好与collect_set相反，实现将array类型数据行转列。explode配合lateral view实现将某列数据拆分成多行。

HQL语句为：

select col1, col2, lv.col3 as col3
from col2row 
lateral view explode(split(col3, ',')) lv as col3;

下面看下行转列使用的函数：

lateral view explode(split表达式) tableName as columnName

• tableName 表示虚拟表的名称。
• columnName 表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

explode 函数：处理数组结构的字段，将数组转换成多行。

Lateral View：其实explode是一个UDTF函数（一行输入多行输出），这个时候如果要select除了explode得到的字段以外的多个字段，需要创建虚拟表

Lateral View 用于和UDTF函数【explode,split】结合来使用。
首先通过 UDTF 函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
主要解决在 select 使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)