hive collect_set,lateral view,explode 实现行列转换

1、建表

create table if not exists temp.lateral_test
(id    string,  
 value string
)  
ROW format delimited FIELDS TERMINATED BY ',' ;

2、插入数据

load data local inpath 'latearl.txt' overwrite into table temp.lateral_test;

select * from lateral_test;

数据如下：

3.1、collect_set：去重、变数组(列传行）

select id,collect_set(value) as a from temp.lateral_test group by id

3.2、数组前加序号访问对应元素，从0开始

select id,
        a[0] a0,
        a[1] a1 
from 
    (select id,collect_set(value) as a from temp.lateral_test group by id) b

4、利用lateral view  explode 对3.1的数据实现行转列（k、hh别名不可少）

select id,
        hh
from 
    (select id,collect_set(value)as a from temp.lateral_test group by id)t
lateral view explode(a)k as hh

PS:explode 可以把单行数组类型数据转为列形式：

select explode(split(concat_ws(',','1','2','3','4'),','))