一个小知识点-Hive行转列实现Pivot

前言

传统关系型数据库中，无论是Oracle（11g之后）还是SQLserver（2005之后），都自带了Pivot函数实现行转列功能行转列，本文主要讲述在Hive中实现行转列的两种方式。

传统数据库方式

这种方式是借鉴在Oracle或者SQLserver在支持Pivot函数之前实现行转列的方式，实际上语法没有什么变化，只是换成了Hive。

with testtable(
select 1 id,'k1' key,123 value
  union all
select 1,'k2' key,124 value
  union all
select 2,'k1',234 value
  )
select id,
       max(case when key='k1' then value else null end) k1,
       max(case when key='k2' then value else null end) k2
  from testtable
 group by id

Map方式

这种方式的思路是把需要转列的字段及其值字段拼接成一个Hive上的key-value的map数据，具体Sql如下：

with testtable(
select 1 id,'k1' key,123 value
  union all
select 1,'k2' key,124 value
  union all
select 2,'k1',234 value
  )
select id,kv['k1'],kv['k2']
  from (
select id,str_to_map(concat_ws(',', collect_set(concat(key, '-', value))),',','-') kv
  from testtable
 group by id)t