Hive实现unpivot
--写在前面:--------------------------------------
原表数据
'A1','A2','A3',5,6,1,4
实现结果:(截图:2)
A1 A2 A3 X1 5
A1 A2 A3 X2 6
A1 A2 A3 X3 1
截图:1
截图:2
--开始-------------------------------------------
step0.创建表
create temporary table test_hive_unpivot (
c1 string, c2 string, c3 string, X1 int, X2 int, X3 int, X4 int
)
stored as parquet
tblproperties('transactional' = 'false')
;
step1.插入数据
insert into test_hive_unpivot
select 'A1','A2','A3',5,6,1,4 ;
step2.使用 explode 实现 unpivot
select c1, c2, c3, t2.key, t2.value
from test_hive_unpivot
lateral view explode (map(
"X1",X1,
"X2",X2,
"X3",X3
)) t2 as key,value;