Hive通过函数对数据的拆分

记录一个数据拆分案例

select id,userid,
from_unixtime(cast(adddate/1000 as bigint),'yyyy-MM-dd') as adddate,
from_unixtime(cast(updatedate/1000 as bigint),'yyyy-MM-dd') as updatedate,
get_json_object(tag1,'$.id') as setting_id,
if(get_json_object(tag1,'$.enable') = 'true', 1 , 0) as enable 
from (
select id,tag1,userid,adddate,updatedate
from (select id,userid,adddate,updatedate,REPLACE(cast(substr(setting,2,length(setting)-2) as String),'},','}#') as aaa from 库名.表名 ) ta
lateral view explode(split(ta.aaa, '#')) r1 as tag1
)acc;

之前数据

拆分之后的数据

21021-07-02更新我的草稿本来了

数组里面的json拆分

insert overwrite table 库名.表名
select
tmp01.companyName,
get_json_object(ccc,'$.position') as position,
get_json_object(ccc,'$.personName') as personName
from(
select
get_json_object(companyBaseInfo,'$.companyName') as companyName,
REPLACE(REPLACE(REPLACE(zhuYaoChengYuan,'[',''),']',''),'},','}###') as tmp
from 库名.表名
)tmp01
lateral view explode(split(tmp01.tmp,'###')) rr as ccc;

原始数据
餐饮管理有限公司 [{“position”: “总经理”, “personName”: “陈1”}, {“position”: “监事”, “personName”: “谢11”}]

拆分之后

companyName position personName

餐饮管理有限公司 执行董事兼总经理 陈1
餐饮管理有限公司 监事 谢11

部分函数应用得当，可拆解正常遇到的多数数据；