HIVESQL的列转行和行转列使用总结

在日常数仓的数据开发中，业务数据需求会遇到很多行转列和列转行的需求，这里需要做一次总结，也可以把经验分享给大家

业务背景

不同业务下有着不同的业务逻辑，这里以一个抽象的业务背景为例子，帮助大家理解行转列的逻辑。现有hive数据表my_db.book_test，数据如下

需求是想要将书的类型变成属性，主键为book_id，字段如下：

思路分析

通过数据可以看出，一个书籍具有多个类型，但是枚举值是无法穷举的。从数据形式来看，是要将一列转为多列。这里可以先将原表列转行，然后行转列。
第一步转换代码

SELECT  book_id,
        book_type_ele
FROM    my_db.book_test
LATERAL VIEW
        EXPLODE(book_type) t1 AS book_type_ele
1
2
3
4
5

转换后的数据

第二步转换，将书籍属性专为key，value默认为1，代码如下：

SELECT  book_id,
        str_to_map(
                concat_ws(
                    ",",
                    COLLECT_SET(
                        concat_ws(
                            ':',
                            book_type_ele,
                            1
                        )
                    )
                )
            ) as book_type_map
FROM    第一步转换的结果
group by
	book_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

转换后结果

最后可以按照取map的key的模式把属性字段取出来

select 
	book_id,
	nvl(map['科技'], 0) as is_tecno
from 第二步转换的结果
1
2
3
4

这样做的好处是如果书籍类型枚举值新增，只需要按照key取出即可。