hive posexplode 函数的使用举例
问题1、在不借助其它表的情况下输出结果如下所示:
1
2
3
4
5
...
49
50
解题思路:首先借助 space(49) 函数生成49个空格,然后用split去切分为49个空白数组,这样依赖,可以使用侧写函数对其进行处理陈带有序号的空白,这里的序号是我们要用到的
答案
select id + start_num is_id, * from (select 1 start_num, 50 end_num) t lateral view posexplode(split(space(50 - 1), '')) t as id, val;
另一种形式是:
select row_number() over () as id from (select split(replace(space(49), '', 'A'), ' ') as x) t lateral view explode(x) t as val
问题2:获取JSON 数组的key
[{"title":"笑傲江湖","author":"金庸"},{"title":"流星蝴蝶剑","author":"古龙"}]
有这样一个JSON,需要获取key
实现:
1、首先将 json 数组转换为 字符串 ;这里使用 字符串替换函数 translate
select translate('[{"title":"笑傲江湖","author":"金庸"},{"title":"流星蝴蝶剑","author":"古龙"}]', '[]{}""', '') json_str;
输出
title:笑傲江湖,author:金庸,title:流星蝴蝶剑,author:古龙
2、使用 regexp_replace 将中间的 , 替换成 ;
select regexp_replace(translate('[{"title":"笑傲江湖","author":"金庸"},{"title":"流星蝴蝶剑","author":"古龙"}]', '[]{}""', ''), '\,', '\:') json_str;
输出
title:笑傲江湖:author:金庸:title:流星蝴蝶剑:author:古龙
这个时候就可以使用 posexplode 函数,侧写出 带有序号的数据了
select * from (select regexp_replace(translate('[{"title":"笑傲江湖","author":"金庸"},{"title":"流星蝴蝶剑","author":"古龙"}]', '{}[]', ''), '\,', '\:') json) t lateral view posexplode(split(json, ':')) t1 as id, val;
结果
"""title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 0 """title""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 1 """笑傲江湖""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 2 """author""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 3 """金庸""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 4 """title""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 5 """流星蝴蝶剑""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 6 """author""" """title"":""笑傲江湖"":""author"":""金庸"":""title"":""流星蝴蝶剑"":""author"":""古龙""" 7 """古龙"""
此时,我们只要对数据过滤一下即可
select val from (select regexp_replace(translate('[{"title":"笑傲江湖","author":"金庸"},{"title":"流星蝴蝶剑","author":"古龙"}]', '{}[]', ''), '\,', '\:') json) t lateral view posexplode(split(json, ':')) t1 as id, val where id % 2 = 0 group by val;
最终结果:
"""author"""
"""title"""
问题3:
有如下一组数据
令狐冲,依琳,田伯光,东方 华山,衡山,黄沙,黑木崖
方正,依琳,田伯光,东方 少林,衡山,黄沙,黑木崖
希望得到一下结果:
令狐冲 华山
依琳 衡山
田伯光 黄沙
东方 黑木崖
方正 少林
依琳 衡山
田伯光 黄沙
东方 黑木崖
思路:使用两次 posexplode 函数进行侧写,两次结果需要限制序号相等
select name_1,part_1 from (select "令狐冲,依琳,田伯光,东方" name, '华山,衡山,黄沙,黑木崖' party union all select "方正,依琳,田伯光,东方", '少林,衡山,黄沙,黑木崖') t lateral view posexplode(split(name, ',')) t1 as id_1, name_1 lateral view posexplode(split(party, ',')) t2 as id_2, part_1 where id_1 = id_2;