hivesql 将json格式字符串转为数组
完整过程SQL在文末
json 格式字符串
本案例 json 字符串参考格式，请勿使用本数据
{
"data": [
{
"province": 11,
"id_card": "110182198903224674",
"name": "闾丘饱乾"
},
{
"province": 21,
"id_card": "210182198903224674",
"name": "贺巧"
}
]
}
测试数据
本案例测试数据，复制保存后请勿格式化
{"data":[{"province":11,"id_card":"110182198903224674","name":"闾丘饱乾"},{"province":21,"id_card":"210182198903224674","name":"贺巧"},{"province":31,"id_card":"310182198903224674","name":"方加牡"},{"province":41,"id_card":"410182198903224674","name":"邱赣"},{"province":42,"id_card":"420182198903224674","name":"郝郑惭"},{"province":52,"id_card":"520182198903224674","name":"余烂"},{"province":62,"id_card":"620182198903224674","name":"宇文酚"},{"province":81,"id_card":"810182198903224674","name":"赖队瞻"}]}
创建测试数据库
create database test;
use test;
创建数据表
本案例为数仓分层设计
创建ods层原始数据表创建dwd层维度数据表ETL转换ods层数据插入到dwd层
创建ods层原始数据表
create table people_ods(
info string
);
加载测试数据
load data local inpath "/root/people.json" overwrite into table people_ods;
创建dwd层维度数据表
create table people_dwd(
id_card string,
name string,
province string
);
ETL转换ods层数据插入到dwd层
insert overwrite table people_dwd (
select json_tuple(people,'id_card','name','province') as (id_card,name,province) from (
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t1 as people) t2);
查询测试
select * from people_dwd;
ETL 解析
查询原始数据
select info from people_ods;
获取json格式数组字符串
使用 get_json_object 函数获取 data 属性
select get_json_object(info,'$.data') from people_ods;
将字符串两端的 [] 去掉
使用 regexp_replace 函数将 开头的 [ 和结尾的 ] 替换为 空字， 注意：由于hive使用java语言编写所以需要使用转义字符
select regexp_replace(get_json_object(info,'$.data'),'\\[|\\]','') from people_ods;
清洗后的格式
{"province":11,"id_card":"110182198903224674","name":"闾丘饱乾"},{"province":12,"id_card":"120182198903224674","name":"慕容芋岛"}
将字符串中 },{ 转为 };{ 对象之间使用分号间隔
json格式字符串对象分隔符和属性分隔符都使用的是逗号 使用split函数切分的时候无法区分对象和属性 故而将对象分隔符替换为分号便于split函数切分
select regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{') from people_ods;
清洗后的格式
{"province":11,"id_card":"110182198903224674","name":"闾丘饱乾"};{"province":12,"id_card":"120182198903224674","name":"慕容芋岛"}
两次清洗后结果对比
转为 字符串数组
select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),';') from people_ods;
列转行
使用 UDTF 裂函数 将单行数据转换为多行数据
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t as people;
json 解析
使用 get_json_object 或者 json_tuple 函数 对json对象进行解析 本案例使用 json_tuple 函数
select json_tuple(people,'id_card','name','province') as (id_card,name,province) from (
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t1 as people) t2;
完成过程SQL
-- 创建测试数据库
create database test;
-- 使用测试数据库
use test;
-- 创建ods层原始数据表
create table people_ods(
info string
);
-- 加载数据
load data local inpath "/root/people.json" overwrite into table people_ods;
-- 查询ods层袁术数据
select info from people_ods;
-- 获取json格式数组字符串
select get_json_object(info,'$.data') from people_ods;
-- 将字符串两端的 [] 去掉
select regexp_replace(get_json_object(info,'$.data'),'\\[|\\]','') from people_ods;
-- 将字符串中 },{ 转为 };{ 对象之间使用分号间隔
select regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{') from people_ods;
-- 转为 字符串数组
select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),';') from people_ods;
-- 列转行
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t as people;
-- 转json对象后解析
select json_tuple(people,'id_card','name','province') as (id_card,name,province) from (
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t1 as people) t2;
-- 创建dwd层维度数据表
create table people_dwd(
id_card string,
name string,
province string
);
-- ETL转换ods层数据插入到dwd层
insert overwrite table people_dwd (
select json_tuple(people,'id_card','name','province') as (id_card,name,province) from (
select people from (select split(regexp_replace(regexp_replace(get_json_object(info,'$.data'),'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),";") people_list from people_ods) ods
lateral view explode(people_list) t1 as people) t2);
select * from people_dwd;
文章来源
您阅读本篇文章共花了：
发表评论