Hive数组转成多行
2021-03-09 05:31
标签：sql src split 多行 regexp 数据 col json get 原始数据如下： 转换后效果： 实现方式： Hive数组转成多行 标签：sql src split 多行 regexp 数据 col json get 原文地址：https://www.cnblogs.com/kopao/p/14182859.html
-- 原数据
SELECT ‘[{"slot_seq":"1","status":"0","battery_sn":"K212EV14C3"},{"slot_seq":"10","status":"0","battery_sn":"K482GE13RT"},{"slot_seq":"3","status":"0","battery_sn":"E513GJ11XT"},{"slot_seq":"4","status":"0","battery_sn":"K377FH1BSK"},{"slot_seq":"5","status":"0","battery_sn":"E046EH142Y"},{"slot_seq":"7","status":"0","battery_sn":"L154GVA0CP"}]‘ AS json_column
-- hive sql 实现
select get_json_object(j_column,‘$.slot_seq‘) as slot_seq,
get_json_object(j_column,‘$.status‘) as status,
get_json_object(j_column,‘$.battery_sn‘) as battery_sn
from
(SELECT split(regexp_replace(regexp_extract(t.json_column, ‘(\\[)(.*?)(\\])‘, 2), ‘\\},\\{‘, ‘\\}|\\{‘), ‘\\|‘) AS json_column
FROM (
SELECT ‘[{"slot_seq":"1","status":"0","battery_sn":"K212EV14C3"},{"slot_seq":"10","status":"0","battery_sn":"K482GE13RT"},{"slot_seq":"3","status":"0","battery_sn":"E513GJ11XT"},{"slot_seq":"4","status":"0","battery_sn":"K377FH1BSK"},{"slot_seq":"5","status":"0","battery_sn":"E046EH142Y"},{"slot_seq":"7","status":"0","battery_sn":"L154GVA0CP"}]‘ AS json_column
) t
) a
lateral view explode(json_column) b as j_column
上一篇：python之元组、字典、字符串
下一篇：Vue CLI 去除gojs水印