Hive当中实现pivot操作

0.背景

实际工作当中遇到这样一个问题，数据如下，但是我想通过SQL将数据转换成按列展示的形式，即常用的pivot透视操作。

# 原始数据
id item value
1  a    10 
2  b    21
1  b    15
2  a    20

# 次级目标格式（SQL）
id itemValue
1  a-10,b-15
2  a-20,b-21

# 目标格式
id item_a item_b（SQL + Python）
1  10     15
2  20     21

在Pandas中有pivot以及pivot_table可以实现相关的功能。但是如果原始数据是在服务器或者集群上，将原始数据pull到本地有时是一个代价非常高的事情。并且如果可以通过sql实现pivot操作，在我们多表join的时候也会非常方便。

1.操作

需要用到的hive操作有，concat，concat_ws，collect_set，group by。

1.1 首先使用concat将item和value连接起来，concat(item, '-', value)。

a-10
b-20
b-15
a-20

1.2 利用collect_set进行去重（因为我们是通过group by进行的数据聚合，其他字段可能可以区分这些重复的数据，如下），并且将同属于一个id的值形成一个set类型，方便concat_ws连接。

# 我们使用id进行group by，连接item和value会有重复，collect_set可以去掉这些重复的。
# 但是这个需要谨慎，如果other字段也是重要的区分字段，把other也加入到group by的字段里。
id item value other
1  a    10    x
1  a    10    y

1.3 使用concat_ws拼接，拼接的字符一般要和concat拼接那个不一样。注意group by分组，这里是group by id。也可以用多个字段进行group by。

数据结果如下：

id itemValue
1  a-10,b-15
2  a-20,b-21

2.SQL

SELECT
    id,
    concat_ws(',', collect_set(concat(item, '-', value)))
FROM test_table
GROUP BY id

3.最终格式

如果想要处理成pivot table那种形式，可以拉到本地进行一些处理。这样做的主要好处是方便和其他表进行join。

4.Reference

1.https://blog.csdn.net/waiwai3/article/details/79071544