Hive ODS层实战：外部表分区加载与JSON数据解析技巧

场景：构建离线数据仓库的 ODS（Operational Data Store）层，使用 Hive 外部表承接原始日志数据，按天分区管理，并对 JSON 格式字段进行高效解析与展开。
结论：ODS 层应坚持“贴源、分区、外部表”原则；JSON 字段优先使用 json_tuple 批量提取，数组结构通过 explode + lateral view 展开。
产出：提供可复用的建表模板、分区管理 SQL、按天装载脚本骨架，以及 JSON 单值/数组/展开查询的标准范式。

1.1 什么是 ODS？

ODS（Operational Data Store）是数据仓库体系中的贴源层，用于存储未经深度加工的原始业务数据。它不承担复杂计算或历史分析任务，而是作为后续 DWD/DWS 层的数据入口。

1.2 ODS 的关键特性

✅ 最佳实践：ODS 不等于“脏数据池”，仍需保证格式规范（如每行一个完整 JSON）。

-- 创建 ODS 库（若未存在）
CREATE DATABASE IF NOT EXISTS ods;

USE ods;

-- 创建外部表：原始日志以字符串形式存储
CREATE EXTERNAL TABLE ods.ods_start_log (
    `str` STRING COMMENT '原始日志 JSON 字符串'
)
COMMENT '用户启动日志 ODS 表'
PARTITIONED BY (`dt` STRING COMMENT '日期分区，格式：yyyy-MM-dd')
STORED AS TEXTFILE
LOCATION '/user/data/logs/start';

🔍 关键点：

• EXTERNAL：删除表时仅删元数据，HDFS 文件保留；
• PARTITIONED BY (dt)：支持按天增量处理；
• LOCATION：指向 HDFS 日志目录，需提前创建并授权。

-- 添加分区（需确保 HDFS 对应目录已有数据）
ALTER TABLE ods.ods_start_log ADD PARTITION (dt='2025-08-02');

-- 删除分区（仅删元数据，HDFS 文件仍保留）
ALTER TABLE ods.ods_start_log DROP PARTITION (dt='2025-08-02');

-- 查看所有分区
SHOW PARTITIONS ods.ods_start_log;

⚠️ 常见错误：ADD PARTITION 后查不到数据？
原因：HDFS 分区目录为空或路径不匹配。
解决：先确认 /user/data/logs/start/dt=2025-08-02/ 下有文件。

#!/bin/bash
# 文件：ods_load_startlog.sh
# 功能：自动为昨日日志添加 Hive 分区

APP=ods
source /etc/profile

# 参数处理：支持手动传入日期，否则默认昨天
if [ -n "$1" ]; then
    do_date=$1
else
    do_date=$(date -d "-1 day" +%F)
fi

echo "Adding partition for date: $do_date"

# 执行 Hive SQL
hive -e "
ALTER TABLE ${APP}.ods_start_log ADD PARTITION (dt='${do_date}');
"

✅ 运行方式：

chmod +x ods_load_startlog.sh
./ods_load_startlog.sh          # 处理昨天
./ods_load_startlog.sh 2025-08-02  # 指定日期

🔧 权限问题：确保 Hive 用户对 HDFS 目录有读权限，必要时执行：

hdfs dfs -mkdir -p /user/data/logs/start
hdfs dfs -chown hive:hadoop /user/data/logs/start

假设原始日志每行为合法 JSON，例如：

{"uid":"u1001","event":"start","ts":1717020800,"props":{"os":"Android","version":"3.2.1","ids":[101,102,103]}}

5.1 单值字段提取 → get_json_object（简单场景）

SELECT
  get_json_object(str, '$.uid') AS uid,
  get_json_object(str, '$.event') AS event,
  get_json_object(str, '$.props.os') AS os
FROM ods.ods_start_log
WHERE dt = '2025-08-02';

❌ 缺点：每次只能取一个字段，效率低。

5.2 批量提取顶层字段 → json_tuple（推荐！）

SELECT
  t.uid,
  t.event,
  t.ts,
  t.props
FROM ods.ods_start_log
LATERAL VIEW json_tuple(str, 'uid', 'event', 'ts', 'props') t
AS uid, event, ts, props
WHERE dt = '2025-08-02';

✅ 优势：一次调用解析多个字段，性能优于多次 get_json_object。

5.3 数组展开 → explode + lateral view

目标：将 props.ids 数组 [101,102,103] 拆成多行。

WITH parsed AS (
  SELECT
    uid,
    event,
    ts,
    get_json_object(props, '$.ids') AS ids_str  -- 得到 "[101,102,103]"
  FROM (
    SELECT
      t.uid,
      t.event,
      t.ts,
      t.props
    FROM ods.ods_start_log
    LATERAL VIEW json_tuple(str, 'uid', 'event', 'ts', 'props') t
    AS uid, event, ts, props
  ) tmp
  WHERE dt = '2025-08-02'
)
SELECT
  uid,
  event,
  ts,
  id_item
FROM parsed
LATERAL VIEW explode(
  split(regexp_replace(ids_str, '\$$|\$$', ''), ',')
) t2 AS id_item
WHERE id_item != '';  -- 过滤空值

🔑 关键步骤：

1. regexp_replace(ids_str, '\$$|\$$', '') → 去掉 [ 和 ]
2. split(..., ',') → 转为 Array
3. explode + lateral view → 展开为多行

⚠️ 注意：若 JSON 中 ids 本身是空数组 []，需额外处理避免空字符串。

✅ 使用 外部表（EXTERNAL TABLE）
✅ 按 天分区（dt string）
✅ LOCATION 指向 HDFS 日志目录
✅ 原始数据保持 一行一 JSON
✅ JSON 解析优先 json_tuple
✅ 数组展开用 explode + lateral view
✅ 装载脚本支持 参数化日期 + 幂等性

📌 记住：ODS 是数仓的“第一道防线”——不求快，但求稳、准、全。