如何查询包含嵌套ARRAY与STRUCT的Hive JSON SerDe表？

开发者阿泽

2026-8-12

Hive嵌套结构查询与数据拆分实战

1. 原外部表DDL（精简后）

假设原JSON包含用户基本信息和嵌套的apps数组，以下是经json2Hive生成并移除冗余字段后的Hive外部表定义：

CREATE EXTERNAL TABLE user_apps (
    user_id string,
    user_name string,
    apps array<struct<
        app_id: string,
        app_name: string,
        app_version: string
    >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/user/hive/warehouse/user_apps';

2. 示例JSON数据

{
    "user_id": "U001",
    "user_name": "Alice",
    "apps": [
        {"app_id": "A001", "app_name": "微信", "app_version": "8.0.40"},
        {"app_id": "A002", "app_name": "支付宝", "app_version": "10.5.36"},
        {"app_id": "A003", "app_name": "抖音", "app_version": "29.0.0"}
    ]
}

3. 嵌套结构查询通用方法

访问单个嵌套字段：通过.访问STRUCT字段，数组用下标定位元素，比如提取第一个app的ID：
```
SELECT user_id, apps[0].app_id FROM user_apps;
```
展开数组所有元素：用LATERAL VIEW explode(apps)将数组拆分为多行，每个数组元素对应一行数据：
```
SELECT user_id, app_info.app_id, app_info.app_name
FROM user_apps
LATERAL VIEW explode(apps) exploded_apps AS app_info;
```

4. 提取app子项并导入拆分新表

新表DDL（字段拆分后）

CREATE TABLE user_app_details (
    user_id string,
    app_id string,
    app_name string,
    app_version string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

数据插入SQL

用INSERT OVERWRITE（或INSERT INTO）结合LATERAL VIEW explode完成数据迁移：

INSERT OVERWRITE TABLE user_app_details
SELECT 
    user_id,
    app_info.app_id,
    app_info.app_name,
    app_info.app_version
FROM user_apps
LATERAL VIEW explode(apps) exploded_apps AS app_info;