大数据-237 离线数仓 - Hive 广告业务实战：ODS→DWD 事件解析、广告明细与转化分析落地

TL;DR

• 场景： 基于埋点日志中的广告事件，完成离线数仓从 ODS 到 DWD 的解析、清洗与明细建模。
• 结论： 核心链路可落地，但当前脚本与字段设计存在字段命名、UDF 调用、类型定义不一致等风险点。
• 产出： 给出适合技术博客发布的标题、SEO 摘要、版本矩阵，以及上线前可直接排查的错误速查卡。

广告业务

基本介绍在上节已经完成，本节我们继续处理这块业务。

需求分析

事件日志数据样例：

{
  "wzk_event": [{
    "name": "goods_detail_loading",
    "json": {
      "entry": "3",
      "goodsid": "0",
      "loading_time": "80",
      "action": "4",
      "staytime": "68",
      "showtype": "4"
    },
    "time": 1596225273755
  }, {
    "name": "loading",
    "json": {
      "loading_time": "18",
      "action": "1",
      "loading_type": "2",
      "type": "3"
    },
    "time": 1596231657803
  }, ...

采集的信息包括：

• 商品详情页加载：goods_detail_loading
• 商品列表：loading
• 消息通知：notification
• 商品评论：comment
• 收藏：favorites
• 点赞：praise
• 广告：ad

在广告的字段中，收集到的数据有：

• action 用户行为 0曝光 1曝光后点击 2购买
• duration 停留时长
• shop_id 商家id
• event_type “ad”
• ad_type 1JPG 2PNG 3GIF 4SWF
• show_style 0静态图 1动态图
• product_id 产品id
• place 广告位置 1首页 2左侧 3右侧 4列表页
• sort 排序位置

需求指标

点击次数统计（分时统计）

• 曝光次数、不同用户ID数、不同用户数
• 点击次数、不同用户ID数、不同用户数
• 购买次数、不同用户ID数、不同用户数

转化率-漏斗分析

• 点击率 = 点击次数/曝光次数
• 购买率 = 购买次数/点击次数

活动曝光效果评估 行为（曝光、点击、购买）、时间段、广告位、产品、统计对应的次数 时间段、广告位、商品，曝光次数最多的前N个

ODS层

建立新表

Hive启动之后，切换到 ods层，然后我们继续创建外部表，将数据映射到Hive中

use ods;
drop table if exists ods.ods_log_event;
CREATE EXTERNAL TABLE ods.ods_log_event(
  `str` string
) PARTITIONED BY (`dt` string)
STORED AS TEXTFILE
LOCATION '/user/data/logs/event';

执行结果如下图所示：

编写脚本

vim /opt/wzk/hive/ods_load_event_log.sh

编写脚本，写入内容如下：

#!/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
alter table ods.ods_log_event add partition (dt='$do_date');
"
hive -e "$sql"

写入的内容如下所示：

DWD层

ODS：分区，事件的主要信息在JSON串中（JSON数组），公共信息在另外一个JSON串中 ODS：解析JSON，从JSON串中，提取JSONArray数据，将公共信息从JSON串中解析出来，所有事件的明细

所有事件的明细，包括：

• 分区
• 事件（JSON串）
• 公共信息字段

所有事件的明细 => 广告JSON串即系 => 广告事件的明细 广告事件的明细：

• 分区
• 广告信息字段
• 公共信息字段

建立新表

Hive启动之后，切换到 dwd 层，然后我们继续创建外部表，将数据映射到Hive中： 所有事件的明细表：

use dwd;
-- 所有事件明细
drop table if exists dwd.dwd_event_log;
CREATE EXTERNAL TABLE dwd.dwd_event_log(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `event_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string,
  `name` string,
  `event_json` string,
  `report_time` string)
PARTITIONED BY (`dt` string)
stored as parquet;

运行结果如下图所示： 与广告点击明细：

use dwd;
-- 与广告点击明细
drop table if exists dwd.dwd_ad;
CREATE TABLE dwd.dwd_ad(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `event_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string,
  `report_time` string,
  `duration` int,
  `ad_action` int,
  `shop_id` int,
  `ad_type` int,
  `show_style` smallint,
  `product_id` int,
  `place` string,
  `sort` int,
  `hour` string
)
PARTITIONED BY (`dt` string)
stored as parquet;

运行结果如下图所示：

UDF

UDF 是用户根据具体需求编写的自定义函数，用于处理 SQL 语言无法直接完成的复杂逻辑。数据仓库系统（如 Hive、Spark SQL、ClickHouse 等）内置了一些通用的函数，但当内置函数无法满足需求时，可以通过 UDF 实现自定义扩展。

UDF 的作用

实现复杂逻辑

通过 UDF，可以将复杂的业务逻辑封装成函数，以简化 SQL 代码。例如，根据自定义规则处理字符串、日期计算等。

提高代码复用性

将重复使用的逻辑封装成 UDF，便于在多个查询中调用，减少代码冗余。

扩展 SQL 的功能

内置函数的功能有限，通过自定义函数可以实现更复杂的计算，如机器学习模型的调用、特殊格式解析等。

优化性能

在某些场景下，使用 UDF 可以减少 SQL 中复杂逻辑的嵌套，从而优化查询性能。

JSON串解析

内建函数、UDF、SerDe（JSON是所有的信息） 详细内容参见 会员活跃度章节-JSON数据处理-UDF（处理JSONArray）

public class ParseJsonArray extends UDF {
    public ArrayList<String> evaluate(String jsonStr) {
        if (Strings.isNullOrEmpty(jsonStr)) {
            return null;
        }
        try{
            // 获取jsonArray
            JSONArray jsonArray = JSON.parseArray(jsonStr);
            ArrayList<String> lst = new ArrayList<>();
            for(Object o: jsonArray) {
                lst.add(o.toString());
            }
            return lst;
        }catch (JSONException e){
            return null;
        }
    }
}

编写脚本

vim /opt/wzk/hive/dwd_load_event_log.sh

编写的脚本内容如下：

USE dwd;
add jar /opt/wzk/hive-parse-json-array-1.0-SNAPSHOT-jar-with-dependencies.jar;
CREATE temporary function wzk_json_array AS 'icu.wzk.ParseJsonArray';

WITH tmp_start AS (
  SELECT SPLIT(str, ' ')[7] AS line
  FROM ods.ods_log_event
  WHERE dt='$do_date'
)

-- 插入数据到目标表 dwd_event_log
INSERT OVERWRITE TABLE dwd.dwd_event_log
PARTITION (dt='$do_date')
SELECT 
  device_id, 
  uid,
  app_v,
  os_type,
  event_type,
  language,
  channel,
  area,
  brand,
  get_json_object(k, '$.name') AS name,
  get_json_object(k, '$.json') AS json,
  get_json_object(k, '$.time') AS time
FROM (
  SELECT
    get_json_object(line, '$.attr.device_id') AS device_id,
    get_json_object(line, '$.attr.uid') AS uid,
    get_json_object(line, '$.attr.app_v') AS app_v,
    get_json_object(line, '$.attr.os_type') AS os_type,
    get_json_object(line, '$.attr.event_type') AS event_type,
    get_json_object(line, '$.attr.language') AS language,
    get_json_object(line, '$.attr.channel') AS channel,
    get_json_object(line, '$.attr.area') AS area,
    get_json_object(line, '$.attr.brand') AS brand,
    get_json_object(line, '$.wzk_event') AS wzk_event,
    line
  FROM tmp_start
) A 
LATERAL VIEW EXPLODE(wzk_json_array(line, 'wzk_event')) B AS k;
"

# 执行 Hive SQL
hive -e "$sql"

对应的截图如下所示： 从全部的事件日志中获取广告点击事件：

vim /opt/wzk/hive/dwd_load_ad_log.sh

写入内容如下所示：

#!/bin/bash

# 加载系统环境变量
source /etc/profile

# 确定操作日期，默认为前一天
if [ -n "$1" ]; then
  do_date=$1
else
  do_date=$(date -d "-1 day" +%F)
fi

# Hive SQL语句
sql="
INSERT OVERWRITE TABLE dwd.dwd_ad
PARTITION (dt='$do_date')
SELECT
  device_id,
  uid,
  app_v,
  os_type,
  event_type,
  language,
  channel,
  area,
  brand,
  report_time,
  get_json_object(event_json, '$.duration') AS duration,
  get_json_object(event_json, '$.ad_action') AS ad_action,
  get_json_object(event_json, '$.shop_id') AS shop_id,
  get_json_object(event_json, '$.ad_type') AS ad_type,
  get_json_object(event_json, '$.show_style') AS show_style,
  get_json_object(event_json, '$.product_id') AS product_id,
  get_json_object(event_json, '$.place') AS place,
  get_json_object(event_json, '$.sort') AS sort,
  from_unixtime(ceil(report_time/1000), 'HH') AS report_hour
FROM dwd.dwd_event_log
WHERE dt='$do_date' AND name='ad';
"

# 执行 Hive SQL
hive -e "$sql"

对应的截图如下所示： 日志 => Flume => ODS => 清洗、转换 => 广告事件详细信息。

错误速查

症状	根因定位	修复
UDF 调用直接报参数不匹配	Java UDF evaluate(String jsonStr) 只接收 1 个参数，但 SQL 中写成 wzk_json_array(line, 'wzk_event')	检查 UDF 类签名与 Hive SQL 调用参数个数改为先取出 wzk_event 字段，再调用单参函数，如 EXPLODE(wzk_json_array(wzk_event))
广告字段解析后大量为空	需求里广告行为字段是 action，脚本里却取 $.ad_action	对照原始埋点 JSON 与 get_json_object 路径将 $.ad_action 改为 $.action，并统一表字段命名
place、action 等字段语义正常但类型混乱	表结构与原始埋点字段类型设计不一致，如 place 建成 string，语义却是枚举数字	检查 DWD 建表字段定义与埋点字典统一数值型字段类型，枚举字段优先使用 int/smallint
小时字段无法正确生成或隐式转换不稳定	report_time 在 DWD 事件表中是 string，却直接参与时间计算	检查 from_unixtime(ceil(report_time/1000), 'HH') 显式转 bigint：from_unixtime(cast(report_time as bigint)/1000, 'HH')
事件拆分结果异常或丢数据	SPLIT(str, ' ')[7] 过度依赖原始日志固定空格位置，日志格式一变即失效	查看原始 str 内容结构改为更稳妥的正则提取或直接按 JSON 原文入湖，避免硬编码索引
dwd_event_log 中字段含义混淆	SELECT 中把 get_json_object(k, '$.json') AS json、... '$.time' AS time，但目标表字段名是 event_json、report_time	对照目标表字段顺序与 SELECT 别名显式使用与表结构一致的别名，降低维护成本
读者照抄后难以复现	文中缺少 Hive/Flume/JDK/依赖版本、表分区执行前置条件	查看全文是否存在版本说明与运行顺序说明补充环境版本、JAR 构建方式、脚本执行顺序与依赖前置条件

其他系列

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

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

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

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

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

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