hive宽表窄表互转

背景

在工作中经常会遇到高表转宽表，宽表转窄表的场景，在此做一些梳理。

宽表转窄表

传统思路

使用sql代码作分析的时候，几次遇到需要将长格式数据转换成宽格式数据，一般使用left join或者case when实现，代码看起来冗长，探索一下，可以使用更简单的方式实现长格式数据转换成宽格式数据。

select year,
max(case when month=1 then money else 0 end) as M1,
max(case when month=2 then money else 0 end) as M2,
max(case when month=3 then money else 0 end) as M3,
max(case when month=4 then money else 0 end) as M4 
from sale group by year;

需求描述

某电商数据库中存在一张客户信息表user_info，记录着客户属性数据和消费数据，需要将左边长格式数据转化成右边宽格式数据。 需求实现

涉及函数：str_to_map, concat_ws, collect_set, sort_array

实现思路：步骤一：将客户信息转化成map格式的数据。collect_set形成的集合是无序的，若想得到有序集合，可以使用sort_array对集合元素进行排序。步骤二：将map格式数据中的key与value提取出来，key就是每一列变量名，value就是变量值

select 
    user_no,
    message1['name'] name,
    message1['sex'] sex,
    message1['age'] age,
    message1['education'] education,
    message1['regtime'] regtime,
    message1['first_buytime'] first_buytime
from 
  (select
      user_no,
      str_to_map(concat_ws(',',sort_array(collect_set(concat_ws(':', message, detail))))) message1
      from user_info
      group by user_no
      order by user_no
   ) a

窄表转宽表

长宽格式数据之间相互转换使用到的函数，可以叫做表格生成函数

需求描述

某电商数据库中存在表user_info1，以宽格式数据记录着客户属性数据和消费数据，需要将左边user_info1宽格式数据转化成右边长格式数据。 需求实现

步骤一：将宽格式客户信息转化成map格式的数据。步骤二：使用explode函数将 map格式数据中的元素拆分成多行显示

select user_no, explode(message1)
    from 
    (select user_no, 
        map('name',name, 'sex',sex, 'age',age, 'education',education, 'regtime',regtime, 'first_buytime',first_buytime) message1
        from user_info1
    ) a

总结

不管是将长格式数据转换成宽格式数据还是将宽格式数据转换成长格式数据，都是先将数据转换成map格式数据。长格式数据转换成宽格式数据：先将长格式数据转换成map格式数据，然后使用列名['key']得到每一个key的value；宽格式数据转换成长格式数据：先将宽格式数据转换成map格式数据，然后使用explode函数将 map格式数据中的元素拆分成多行显示。顺便说一句，R语言中也是通过类似的方法实现长宽格式之间相互转换的。