hive explode 使用

hive explode 使用

https://zhuanlan.zhihu.com/p/115918587

例子一：
将json array 结构 打散成多行形式：（适用于数组长度未知）

[{"text":"内蒙古自治区","type":"province"},{"text":"呼和浩特市","type":"city"},{"text":"上秃亥乡","type":"township"},{"text":"刘家村","type":"village"}]

实现代码

select
*,
city_info
from ***  a
lateral view explode(split(substr(a.structaddr, 2, length(a.structaddr) - 2),
                  concat('(?<=','\\','})',',','(?=\\','{)'))) tag AS city_info
where dt='20220227'

二、利用get_json_object 提取jsonArray（适用于JSoN结构已知且固定）

[{"name":"王二狗","sex":"男","age":"25"},{"name":"李狗嗨","sex":"男","age":"47"}]
取出第一个json对象，那么hive sql为：

SELECT get_json_object(xjson,"$.[0]") FROM person;
 结果是：

{"name":"王二狗","sex":"男","age":"25"}
取出第一个json的age字段的值：

SELECT get_json_object(xjson,"$.[0].age") FROM person;
结果
25


三、将数据行数复制2倍
with t as (
  select 1 as id,'xiao' as name
  union all
  select 2 as id,'bai' as name
)

select * 
from t
lateral view explode(split('0,1',',')) red as col1

结果为

ID name col1
2 bai 0
2 2 bai 1
3 1 xiao 0
4 1 xiao 1

主要 如果split 切分的结果为null 。那其附加到其他字段中这一行的数据不显示。
例如下列例子中 查询结果小白一行不显示：
with tmp_good_info as(
select ‘小红’ as name, ‘100,50,30’ as chengji
union all
select ‘小白’ as name, null as chengji
union all
select ‘小蓝’ as name, ‘100’ as chengji
union all
select ‘小黄’ as name, ‘100’ as chengji
union all
select ‘小美’ as name, ‘100,50,30’ as chengji
)

select
name,
chengji,
fen
from tmp_good_info t
lateral view explode(split(t.chengji,‘,’)) tag as fen

通过在split 加入coalesce 对null 负值为 “”，空字符串，即小白一行显示。
如下：

select
name,
chengji,
fen
from tmp_good_info t
lateral view explode(split(coalesce(t.chengji,‘’),‘,’)) tag as fen

行转列两种通用的写法：
第一种

with test as(
  select 1 as age ,'xiao,hong' as name
  union all
  select 1 as age ,'xiao,lv' as name
  union all
  select 1 as age ,'xiao,lna' as name
  union all
  select 1 as age ,'xiao,b' as name
  union all
  select 1 as age ,'xiao,sas' as name
  union all
  select 1 as age ,'xiao,xihao' as name

)
select  
age,
name,
name_str
from test
lateral view explode(split(name,','))  t as name_str

第二种

with test as(
  select 1 as age ,'xiao,hong' as name
  union all
  select 1 as age ,'xiao,lv' as name
  union all
  select 1 as age ,'xiao,lna' as name
  union all
  select 1 as age ,'xiao,b' as name
  union all
  select 1 as age ,'xiao,sas' as name
  union all
  select 1 as age ,'xiao,xihao' as name

)
select  
age,
name,
name_str
from test
cross join unnest(split(name,',')) as t(name_str)