大数据-229 离线数仓ODS 层实战：Hive 外部表分区加载与 JSON 解析（get_json_object/json_tuple）

武子康
76 阅读7分钟

TL;DR

  • 场景：离线数仓 ODS 层落地，Hive 外部表承接日志，按天分区管理，JSON 字段按需解析与展开
  • 结论：ODS 保持“贴源 + 分区 + 外部表”，JSON 解析优先 json_tuple 批量取字段，展开用 explode + lateral view
  • 产出：ODS 建表模板、分区增删 SQL、按天装载脚本骨架、JSON 单值/数组/展开查询范式

大数据-229 离线数仓ODS 层实战：Hive 外部表分区加载与 JSON 解析（get_json_object/json_tuple）

离线数仓架构图

整体结构

离线数仓 数据仓库架构

ODS层

ODS的定义

ODS是一种面向操作层的数据存储，专注于支持企业的日常运营。它通常整合了来自不同数据源（如ERP、CRM、销售系统等）的数据，通过清洗、转换后存储，以便快速查询和使用。ODS不以历史数据分析为主要目的，而是以操作和事务处理为核心。

ODS的特性

实时性或近实时性

ODS中的数据通常是实时或近实时的，能够快速反映业务系统中的最新状态，支持企业的及时决策。

数据整合

ODS整合来自多个源系统的数据，提供统一视图。它会通过ETL（提取、转换、加载）工具对数据进行处理。

非历史性

ODS数据的生命周期相对较短，通常只保存当前或最近的数据，不会长期存储历史数据。

面向操作

ODS的设计重点在于支持操作和事务处理，例如快速查询当前的客户订单状态，而不是复杂的分析或建模。

ODS的架构

ODS通常包含以下主要部分：

数据输入层数据输入层

从事务系统中提取原始数据（可能来自不同的数据源）。 使用ETL工具对数据进行初步清洗和转换。

数据存储层

存储经过整合的数据，通常以面向主题的形式组织。 数据存储可以是关系型数据库（如MySQL、PostgreSQL）或其他支持快速查询的数据库。

数据访问层

为操作型应用提供快速查询接口。 可能支持API、SQL查询或直接集成到企业应用中。

创建ODS层

我们启动Hive，进行建立： ODS层的数据与源数据的格式基本相同，创建ODS层表：

use ods;
create external table ods.ods_start_log(
  `str` string
) comment '用户启动日志信息'
partitioned by (`dt` string)
location '/user/data/logs/start';

执行结果如下图所示： 离线数仓 Hive 建表 加载数据（测试用，可以不做测试）

-- 加载数据（测试时使用）
-- 加分区
alter table ods.ods_start_log add partition(dt='2020-08-02');
-- 删除分区
alter table ods.ods_start_log drop partition(dt='2020-08-02');

执行结果如下图所示： 离线数仓 Hive 修改表格式 这里可以看到使用的是：external 外部表的模式

加载ODS层数据

mkdir /opt/wzk/hive
vim /opt/wzk/hive/ods_load_startlog.sh

传参数确定日志，没有参数则取昨天日期

#！/bin/bash
APP=ODS
source /etc/profile
# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
# 定义要执行的SQL
sql="
alter table "$APP".ods_start_log add partition(dt='$do_date');
"
hive -e "$sql"

写入的内容如下图所示： 离线数仓 Hive 脚本运行

JSON数据处理

数据文件中每行必须要是一个完整的JSON字符串，一个JSON串不能穿越多行，Hive处理JSON数据总体来说有三个办法：

  • 使用内建的函数get_json_obejct，json_tuple
  • 使用自定义的UDF
  • 第三方的SerDe

内建函数

get_json_object(string json_string, string path)

返回值：String 说明：解析JSON字符串json_string，返回path指定的内容，如果输入的JSON字符串无效，那么返回NULL，函数每次只返回一个数据项。

json_tuple(jsonStr, k1, k2...)

返回值：所有的输入参数，输出参数都是String 说明：参数为一组 k1,k2等等和JSON的字符串，返回值的元组，该方法比get_json_object高效，因此可以在一次调用中输入多个键

explode

使用explode将Hive一行中复杂的array或map结构拆分成多行。

测试数据

我们写入数据：

vim /opt/wzk/weibo.json

写入内容如下所示：

user1;18;male;{"id": 1,"ids": [101,102,103],"total_number": 3}
user2;20;female;{"id": 2,"ids":[201,202,203,204],"total_number": 4}
user3;23;male;{"id": 3,"ids":[301,302,303,304,305],"total_number": 5}
user4;17;male;{"id": 4,"ids": [401,402,403,304],"total_number":5}
user5;35;female;{"id": 5,"ids": [501,502,503],"total_number":3}

写入的结果如下图所示： 离线数仓Hive数据

建表加载数据

启动Hive

hive

随后我们执行：

CREATE TABLE IF NOT EXISTS jsont1(
  username string,
  age int,
  sex string,
  json string
)
row format delimited fields terminated by ';';

load data local inpath '/opt/wzk/weibo.json' overwrite into table jsont1;

导入数据的结果如下图所示： 离线数仓 启动Hive 加载数据

JSON数据的处理

-- get 单层值
SELECT 
  username, age, sex, 
  get_json_object(json, "$.id") id, 
  get_json_object(json, "$.ids") ids, 
  get_json_object(json, "$.total_number") num
FROM jsont1;

对应的查询结果如下图所示： 离线数仓 查询Hive数据

-- get 数组
SELECT
  username, age, sex, 
  get_json_object(json, "$.id") id,
  get_json_object(json, "$.ids[0]") ids0,
  get_json_object(json, "$.ids[1]") ids1,
  get_json_object(json, "$.ids[2]") ids2,
  get_json_object(json, "$.ids[3]") ids3,
  get_json_object(json, "$.total_number") num
FROM jsont1;

执行的结果如下图所示： 离线数仓 查询数据数组

-- 使用 json_tuple 一次处理多个字段
SELECT
  json_tuple(json, 'id', 'ids', 'total_number')
FROM jsont1;

执行的结果如下图所示： 离线数仓 查询Hive数据 json_tuple

-- 使用 explode + lateral view
-- 在上一步的基础上，再将数据展开
-- 第一步，将 [101,102,103] 中的 [ ] 替换掉
-- select "[101,102,103]"
-- select "101,102,103"
SELECT regexp_replace("[101,102,103]", "\\[|\\]", "");

-- 第二步，将上一步的字符串变为数组
SELECT split(regexp_replace("[101,102,103]", "\\[|\\]", ""), ",");

-- 第三步，使用explode + lateral view 将数据展开
SELECT username, age, sex, id, ids, num
FROM jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1
AS id, ids, num;

with tmp AS (
SELECT username, age, sex, id, ids, num
FROM jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1 AS id, ids, num)
SELECT username, age, sex, id, ids1, num
FROM tmp
lateral view explode(split(regexp_replace(ids, "\\[|\\]", ""), ",")) t1 AS ids1;

最终的执行结果如下图所示： 离线数仓 json_tuple json_tuple 优点是一次可以解析多个JSON字段，对嵌套结果的解析操作复杂。

错误速查

症状根因定位修复
分区加了但查不到数据分区目录不存在或目录下无数据；只 add partition 不等于导入数据show partitions ods_start_log; 查看分区；检查 HDFS：分区目录是否有文件先把数据落到对应分区目录（或用 load/insert 覆盖）；保证 location 与分区目录一致
LOCATION '/user/data/logs/start' 下没有数据或权限报错HDFS 路径未创建/权限不足；写入用户与 Hive 执行用户不一致hdfs dfs -ls /user/data/logs/start；看报错里的 User/Permission创建目录并授权：hdfs dfs -mkdir -p ...；hdfs dfs -chmod/-chown ...
外部表删表后数据还在/以为会删数据external table 元数据与数据分离desc formatted 看 Table Type: EXTERNAL_TABLE明确外部表语义：删表不删 HDFS 文件；需要清理时单独删目录
ods_load_startlog.sh 运行报 “bad interpreter” 或直接不执行shebang 写成了全角字符 #！/bin/bash（非 #!/bin/bash）或文件无执行权限head -1 ods_load_startlog.sh；file ods_load_startlog.sh；ls -l改为 #!/bin/bash 半角；chmod +x ods_load_startlog.sh；用 bash ods_load_startlog.sh 复核
脚本里 APP=ODS 执行失败（库不存在/表找不到）Hive 库名大小写或命名不一致；正文建库使用 use ods;，脚本用 ODSshow databases;、show tables in ods;统一：APP=ods 或直接写死 ods.ods_start_log
alter table ... add partition 报已存在/重复同一天分区重复添加show partitions 看是否已有 dt=...改为幂等：先 drop partition 或在脚本里判断（查询 metastore/分区列表）
JSON 解析结果全是 NULLJSON 字符串不合法；路径写错；JSON 字段含前后脏字符抽样 select json from jsont1 limit 5;；对比路径 $.id保证每行是完整 JSON 串且无多余字符；必要时先清洗再解析
get_json_object(json, "$.ids") 得到带 [] 的字符串，explode 不生效ids 仍是字符串，不是 array 类型；直接 explode 需要 arrayselect typeof(get_json_object(...))（若支持）或观察返回用 regexp_replace + split 转 array，再 explode；或改用支持 array 的 SerDe/UDF
json_tuple 解析嵌套字段很痛苦json_tuple 主要针对顶层键；嵌套需二次解析或改方案观察嵌套结构（对象内对象/数组对象）嵌套多时改用 SerDe（如 JSON SerDe）或自定义 UDF；减少在 SQL 层堆正则
文本中函数名写错导致读者复制失败“get_json_obejct” 拼写错误（应为 get_json_object）搜索正文关键字保留原文不改时，至少在文中显式标注正确函数名用于复制运行

其他系列

🚀 AI篇持续更新中（长期更新）

AI炼丹日志-29 - 字节跳动 DeerFlow 深度研究框斜体样式架 私有部署 测试上手 架构研究，持续打造实用AI工具指南！ AI研究-132 Java 生态前沿 2025：Spring、Quarkus、GraalVM、CRaC 与云原生落地

💻 Java篇持续更新中（长期更新）

Java-218 RocketMQ Java API 实战：同步/异步 Producer 与 Pull/Push Consumer MyBatis 已完结，Spring 已完结，Nginx已完结，Tomcat已完结，分布式服务已完结，Dubbo已完结，MySQL已完结，MongoDB已完结，Neo4j已完结，FastDFS 已完结，OSS已完结，GuavaCache已完结，EVCache已完结，RabbitMQ已完结，RocketMQ正在更新... 深入浅出助你打牢基础！

📊 大数据板块已完成多项干货更新（300篇）：

包括 Hadoop、Hive、Kafka、Flink、ClickHouse、Elasticsearch 等二十余项核心组件，覆盖离线+实时数仓全栈！ 大数据-278 Spark MLib - 基础介绍 机器学习算法 梯度提升树 GBDT案例 详解

avatar
文章
阅读
粉丝