hive:explode和lateral view函数实现行转列

需求：假设某超市的订单表order如下，现想统计用户经常购买的Top100商品。

思路：为了便于统计Top100商品，需要从订单表order的info字段提取商品字段goods，并行转列。下面利用hive中的explode和lateral view函数得到以下表结构。

• 第一步：利用explode函数将info字段进行拆分及行转列。
  regexp_replace(string A, string B, string C)：正则表达式替换，将字符串A中的符合正则表达式B的部分替换为C；
  split(string A, string B):按照B字符串分割A，会返回分割后的字符串数组；
  explode函数：explode可以将一列复杂的array或者map结构拆分成多行。

select
    explode(split(regexp_replace(regexp_replace(info,'\[\{',''),'}]',''),'},\{')) as info
from order

• **第二步：**利用get_json_object函数解析json，得到goods和amount字段。因为经过第一步得到的info字段并不是严格意义上的json格式，因此先用concat()在首尾拼接{}，然后再用get_json_object解析。

select
    t1.info,
    get_json_object(concat('{',t1.info,'}'),'$.goods') as goods,
    get_json_object(concat('{',t1.info,'}'),'$.amount') as amount
from
(select
    explode(split(regexp_replace(regexp_replace(info,'\[\{',''),'}]',''),'},\{')) as info
from order
limit 10) t1

• 第三步：结合lateral view函数查询多个字段。explode()只支持一个字段，故前两步仅对info字段进行了处理，但是最终想提取id、goods字段，用于聚合出Top100商品，此时可以结合lateral view一起使用。

select
    t1.id as id,
    t1.info as info,
   	get_json_object(concat('{',info,'}'),'$.goods') as goods,
    get_json_object(concat('{',info,'}'),'$.amount') as amount
from
	(select
	    id,
	    info
	from order
	)t1
lateral view explode(split(regexp_replace(regexp_replace(t1.info,'\[\{',''),'}]',''),'},\{')) table_tmp as info

其中，利用lateral view explode()形成虚拟表table_tmp，可以与t1笛卡尔关联。

• 第四步：按goods字段分组聚合。

select
	tab.goods,
	count(distinct id)
from
(select
    t1.id as id,
    t1.info as info,
   	get_json_object(concat('{',info,'}'),'$.goods') as goods,
    get_json_object(concat('{',info,'}'),'$.amount') as amount
from
	(select
	    id,
	    info
	from order
	)t1
lateral view explode(split(regexp_replace(regexp_replace(t1.info,'\[\{',''),'}]',''),'},\{')) table_tmp as info) tab
group by tab.goods

ps：初衷是通过撰写博文记录自己所学所用，实现知识的梳理与积累；将其分享，希望能够帮到面临同样困惑的小伙伴儿。如发现博文中存在问题，欢迎随时交流~~