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

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

业务背景

不同业务下有着不同的业务逻辑，这里以一个抽象的业务背景为例子，帮助大家理解行转列的逻辑。现有hive数据表my_db.book_test，数据如下
book_idbook_type1[科技,数学]2[小说,散文]3[历史,政治]
需求是想要将书的类型变成属性，主键为book_id，字段如下：
book_idis_novelis_historyis_technois_econo

思路分析

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

SELECT  book_id,
        book_type_ele
FROM    my_db.book_test
LATERAL VIEW
        EXPLODE(book_type) t1 AS book_type_ele

转换后的数据
book_idbook_type_ele1科技1数学2小说2散文3历史3政治
第二步转换，将书籍属性专为key，value默认为1，代码如下：

SELECT  book_id,
        str_to_map(
                concat_ws(",",
                    COLLECT_SET(
                        concat_ws(':',
                            book_type_ele,1))))as book_type_map
FROM    第一步转换的结果
groupby
    book_id

转换后结果
book_idbook_type_map1{‘科技’:1,‘数学’:1}2{‘小说’:1,‘散文’:1}3{‘历史’:1,‘政治’:1}
最后可以按照取map的key的模式把属性字段取出来

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

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