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 date 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,        hhfrom     (select id,collect_set(value)as a from temp.lateral_test group by id)tlateral view explode(a)k as hh

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

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