解析Hive中的嵌套JSON数组：方法与实践

村口蹲点的阿三

于 2024-10-10 11:45:28 发布

阅读量1.2k

点赞数 4

分类专栏： hive-sql语法文章标签： hive json hadoop 数据仓库大数据

本文链接：https://blog.youkuaiyun.com/liuya19921123/article/details/142817562

版权

hive-sql语法专栏收录该内容

16 篇文章

订阅专栏

解析Hive中的嵌套JSON数组：方法与实践

在大数据处理中，Hive作为一款高效的数据仓库工具，经常面临着解析复杂数据格式的需求，尤其是JSON格式。JSON作为一种轻量级的数据交换格式，因其格式灵活、易于人阅读而广泛使用。本文将详细介绍如何在Hive中解析存储为字符串格式的JSON数组，特别是那些包含嵌套数组的复杂JSON结构。

常规JSON解析方法

在Hive中，解析JSON通常可以使用内置函数如get_json_object和json_tuple。这些函数配合lateral view可以有效解析标准的JSON结构。例如，考虑以下JSON数组：

[
    {
        "member_no": "A0001",
        "home_address": "上海市-非正常人研究所"
    },
    {
        "member_no": "A0002",
        "home_address": "深圳市-研究非正常人所"
    }
]

这种简单结构的JSON可以通过以下Hive SQL轻松解析：

-- 示例表
WITH tab AS (
    SELECT '[{"member_no": "A0001", "home_address": "上海市-非正常人研究所"}, {"member_no": "A0002", "home_address": "深圳市-研究非正常人所"}]' AS cl_name
)
-- 使用get_json_object解析JSON字段
SELECT get_json_object(cl_name, '$.member_no') AS member_no,
       get_json_object(cl_name, '$.home_address') AS home_address
FROM tab
-- 使用explode展开JSON数组
LATERAL VIEW explode(split(regexp_replace(regexp_replace(cl_name, '\\}\\,\\{', '\\}\\;\\{'), '\\[|\\]', ''), '\\;')) cl_name AS cl_name;

处理复杂的嵌套JSON数组

当遇到包含嵌套数组的JSON时，单纯使用get_json_object可能不足以解决问题。考虑以下更复杂的JSON结构：

[
    {
        "member_no": "A0001",
        "home_address": [
            {
                "city_name": "上海市",
                "apartment_name": "非正常人研究所"
            },
            {
                "city_name": "北京市",
                "apartment_name": "正常非人研究所"
            }
        ]
    },
    {
        "member_no": "A0002",
        "home_address": [
            {
                "city_name": "深圳市",
                "apartment_name": "研究非正常人所"
            },
            {
                "city_name": "杭州市",
                "apartment_name": "正常研究非人所"
            }
        ]
    }
]

方法一：使用正则表达式和特殊字符处理

此方法首先使用regexp_extract和regexp_replace处理字符串，将其转换为可操作的格式，然后再进行解析。

WITH tab AS (
    -- 示例表
    SELECT '[{"member_no": "A0001","home_address":[{"city_name": "上海市","apartment_name": "非正常人研究所"},{"city_name": "北京市","apartment_name": "正常非人研究所"}]},{"member_no": "A0002","home_address":[{"city_name": "深圳市","apartment_name": "研究非正常人所"},{"city_name": "杭州市","apartment_name": "正常研究非人所"}]}]' AS cl_name
)
-- 使用regexp_extract提取JSON内容
SELECT get_json_object(mem_json, '$.member_no') AS member_no,
       get_json_object(ads_json, '$.city_name') AS city_name,
       get_json_object(ads_json, '$.apartment_name') AS apartment_name
FROM tab
-- 使用explode展开外层JSON对象
LATERAL VIEW explode(split(regexp_replace(regexp_replace(regexp_extract(cl_name, '^\\[(.+)\\]$', 1), ',"home_address":\\[.*?\\]', ''), '\\},\\{', '\\}\\;\\{'), '\\;')) mem_objs AS mem_json
-- 使用explode展开内层JSON对象
LATERAL VIEW explode(split(regexp_replace(regexp_extract(cl_name, '"home_address":\\[(.*?)\\]', 1), '\\},\\{', '\\}\\;\\{'), '\\;')) ads_objs AS ads_json;

方法二：使用内置解析器JsonSerde

JsonSerde允许将JSON字符串直接转换为Hive理解的结构化数据，简化解析过程。

WITH tab AS (
    -- 示例表
    SELECT '[{"member_no": "A0001","home_address":[{"city_name": "上海市","apartment_name": "非正常人研究所"},{"city_name": "北京市","apartment_name": "正常非人研究所"}]},{"member_no": "A0002","home_address":[{"city_name": "深圳市","apartment_name": "研究非正常人所"},{"city_name": "杭州市","apartment_name": "正常研究非人所"}]}]' AS cl_name
)
-- 使用from_json将JSON字符串转换为结构化数据
SELECT member_no,
       home_address,
       get_json_object(to_json(home_address), '$.city_name') AS city_name,
       get_json_object(to_json(home_address), '$.apartment_name') AS apartment_name
FROM (
    SELECT cl_name,
           -- 定义JSON结构
           from_json(cl_name, 'array<struct<member_no:string, home_address:array<struct<city_name:string, apartment_name:string>>>>') AS new_cl_name
    FROM tab
) tt
-- 使用explode展开成员和地址信息
LATERAL VIEW explode(new_cl_name.member_no) t1 AS member_no
LATERAL VIEW explode(new_cl_name.home_address[0]) t2 AS home_address;