hive将多行查询成一个json字符串

今天在用hive做一个离线统计的任务，既然是统计，那就是某个ID可能在某个维度有多个值，比如某个部门男女人数分别是多少。

部门ID	性别	人数
0001	0	1200
0001	1	600
002	0	200
002	1	30

统计出来的结果可能是类似于上述的结果格式，这样业务方没有办法对数据直接使用，因为业务方需要的直接的一条数据，直接是json格式的，类似于：
0001 {"0":"1200","1":"600"}
0002 {"0":"200","1":"30"}
也就是说一个ID就是一条记录，这样就方便直接使用，那hive怎么来实现呢？

1、统计结果格式数据

show1.png

2、转换处理
1）步骤1：将值转成带“：”的

select id,concat(concat('\"',sex,'\"'),':',concat('\"',count,'\"')) kv from result_sex limit 6；

show2.png

2）步骤2：根据ID做聚合

select id,sort_array(collect_set(kv)) gg
from (select id,concat(concat('\"',sex,'\"'),':',concat('\"',count,'\"')) kv from result_sex limit 6) x group by id;

show3.png

3）步骤3：将聚合的数据转成字符串

select id,concat_ws(',',sort_array(collect_set(kv))) gg
from (select id,concat(concat('\"',sex,'\"'),':',concat('\"',count,'\"')) kv from result_sex limit 9) x group by id;

show4.png

4）步骤4：添加json的花括号

select id,concat('{',concat_ws(',',sort_array(collect_set(kv))),'}') gg
from (select id,concat(concat('\"',sex,'\"'),':',concat('\"',count,'\"')) kv from result_sex limit 9) x group by id;

show5.png

这样我们就实现了我们的需求了。。

欢迎大家留言讨论