大数据-232 离线数仓Hive 离线数仓新增与留存计算：DWS 明细 + ADS 汇总一套跑通

武子康
60 阅读7分钟

TL;DR

  • 场景：离线数仓按天计算“新增会员”，并为后续“会员留存”提供口径一致的数据底座
  • 结论：用“全量会员表(含首日dt)”做去重锚点，DWS 产新增明细，ADS 产新增计数，脚本串行即可
  • 产出：可复用的 Hive SQL/脚本模板：新增识别（left join is null）+ 会员全量表增量维护 + 每日新增指标表

大数据-232 离线数仓Hive 离线数仓新增会员与留存计算：DWS 明细 + ADS 汇总一套跑通

离线数仓架构图

新增会员

  • 留存会员：某段时间的新增会员，经过一段时间后，仍然使用应用认为是留存的会员。
  • 新增会员：第一次使用应用的用户，定义为新增会员，卸载再次安装的设备，不会被算作是新增用户

新增会员先计算 => 计算会员留存

需求描述

每日新增会员数 08-02：DWD，会员每日启动明细（95-110）；所有会员信息（1-100）

  • 新增会员 101-110
  • 新增会员数据+旧的会员的信息 = 新的所有会员信息（1-110）

08-03：DWD，会员每日启动明细（100-120）；所有会员的信息（1-110）

  • 新增会员：111-120
  • 新增会员数据 + 旧的所有会员的信息 = 新的所有会员的信息（1-120）

计算步骤：

  • 计算新增会员
  • 更新所有会员信息

改进后方法：

  • 在所有会员信息中增加时间列，表示这个会员是哪一天成为新增会员
  • 只需要一张表：所有会员的信息（id，dt）
  • 将新增会员插入所有会员表中

案例：如何计算新增会员

-- t1.dat 的数据如下
4,2020-08-02
5,2020-08-02
6,2020-08-02
7,2020-08-02
8,2020-08-02
9,2020-08-02

-- 日启动表 => DWS
drop table t1;
create table t1(id int, dt string) row format delimited fields terminated by ',';
load data local inpath '/opt/wzk/hive/data/t1.dat' into table t1;

执行如下图所示： 离线数仓Hive 创建表

继续执行

-- t2.dat 的数据如下
1,2020-08-01
2,2020-08-01
3,2020-08-01
4,2020-08-01
5,2020-08-01
6,2020-08-01

-- 全量数据 => DWS
drop table t2;
create table t2(id int, dt string)
row format delimited fields terminated by ',';
load data local inpath '/opt/wzk/hive/data/t2.dat' into table t2;

执行结果如下图所示： 离线数仓Hive丢弃表

继续执行：

-- 找出 2020-08-02 的新用户
select t1.id, t1.dt, t2.id, t2.dt
from t1 left join t2 on t1.id=t2.id
where t1.dt="2020-08-02";
select t1.id, t1.dt
from t1 left join t2 on t1.id=t2.id
where t1.dt="2020-08-02"
and t2.id is null;

离线数仓Hive 查询表 继续运行：

-- 将找到 2020-08-02 新用户数据插入t2表中
insert into table t2
select t1.id, t1.dt
from t1 left join t2 on t1.id=t2.id
where t1.dt="2020-08-02"
and t2.id is null;

执行结果如下图所示： 离线数仓Hive 插入数据到表格 目前t2的数据有：

hive (default)> select * from t2;
OK
t2.id   t2.dt
7       2020-08-02
8       2020-08-02
9       2020-08-02
1       2020-08-01
2       2020-08-01
3       2020-08-01
4       2020-08-01
5       2020-08-01
6       2020-08-01
Time taken: 0.137 seconds, Fetched: 9 row(s)
hive (default)>

我们继续加载新的数据进去，整体的思路如下：

-- t3.dat的数据，t1 加载 2020-08-03 的数据
14,2020-08-03
15,2020-08-03
16,2020-08-03
17,2020-08-03
18,2020-08-03
19,2020-08-03

load data local inpath '/opt/wzk/hive/data/t3.dat' into table t1;

-- 同样的思路
-- 将找到 2020-08-03 新用户数据插入t2表中
insert into table t2
select t1.id, t1.dt
from t1 left join t2 on t1.id=t2.id
where t1.dt="2020-08-03"
and t2.id is null;
-- 检查结果
select * from t2;

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

DWS作用

统一数据模型

将原始数据（ODS层）按照一定的逻辑模型进行整合、清洗、加工，形成标准化的数据结构。 支持对数据的多维度、多粒度分析。

支持业务场景

满足企业对历史数据的查询和分析需求。 支持 OLAP（在线分析处理）操作，如聚合查询、钻取和切片。

数据细化与分类

将数据按照主题域（如销售、财务、库存等）分类，便于管理和查询。 通常保持较高的细节粒度，便于灵活扩展。

数据准确性与一致性

经过处理的数据经过校验，确保逻辑关系正确，能够为下游提供准确的一致性数据。

创建DWS层表

use dws;
drop table if exists dws.dws_member_add_day;
create table dws.dws_member_add_day
(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string,
  `dt` string
) COMMENT '每日新增会员明细'
stored as parquet;

执行结果如下图所示： 离线数仓 DWS 层

加载DWS层数据

我们编写脚本：

vim /opt/wzk/hive/dws_load_member_add_day.sh

写入内容如下：

#!/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert into table dws.dws_member_add_day
select t1.device_id,
t1.uid,
t1.app_v,
t1.os_type,
t1.language,
t1.channel,
t1.area,
t1.brand,
'$do_date'
from dws.dws_member_start_day t1 left join
dws.dws_member_add_day t2
on t1.device_id=t2.device_id
where t1.dt='$do_date'
and t2.device_id is null;
"
hive -e "$sql"

写入的内容如下图所示： 离线数仓加载 DWS 层

ADS 作用

聚合和简化数据

将 DWS 层中多表、多主题域的数据聚合成简单易用的表或视图。 直接输出满足业务需求的数据结果。

面向业务应用

通过设计宽表或高性能视图，直接支持具体的业务场景和报表需求。 响应快速查询需求，如实时数据的展示。

数据分发与集成

为前端的 BI 工具、报表系统或 API 服务提供高效的查询接口。 能够通过缓存机制或物化视图加速查询性能。

轻量化与高性能

尽量减少数据量，保留业务最关心的关键指标。 采用预聚合、预计算等技术提升查询效率。

创建ADS层表

drop table if exists ads.ads_new_member_cnt;
create table ads.ads_new_member_cnt(
  `cnt` string
)
partitioned by(dt string)
row format delimited fields terminated by ',';

执行结果如下图所示： 离线数仓 Hive DWS 层

加载ADS层数据

编写数据加载的脚本：

vim /opt/wzk/hive/ads_load_member_add.sh

编写对应的脚本如下所示：

#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table ads.ads_new_member_cnt
partition (dt='$do_date')
select count(1)
from dws.dws_member_add_day
where dt = '$do_date'
"
hive -e "$sql"

暂时小结

离线数仓 ADS 层 调用脚本的次序是：

dws_load_member_add_day.sh
ads_load_member_add.sh

错误速查

症状根因定位修复
当日新增数忽高忽低新增锚点不稳定（device_id 会变/uid 会变）对比同日 uid 与 device_id 去重后的基数差异；明确新增口径：优先选稳定主键；必要时做 device_id↔uid 绑定表
新增明细重复出现同一用户多天全量会员表未成功增量写入或写入失败回滚检查 t2/全量表中是否存在该 id 的历史 dt；先保证“插入全量表”成功再产出下游；给插入加幂等约束（分区/去重）
新增明细为 0，但启动明细有数据join 条件/字段类型不一致导致全匹配抽样检查 t1.id 与 t2.id 的类型、是否有空格/格式差；统一字段类型与清洗规则；必要时 trim/cast 后再 join
ADS 分区覆盖错误或数据跑到错误日期do_date 传参/默认日期取值不符合调度时间打印脚本 do_date 与 SQL where dt；统一调度时间基准；强制调度传参，避免默认 -1 day 误差
ADS 结果累加而非覆盖insert into 用错，或分区未指定导致追加检查是否使用 insert overwrite + partition(dt=)；指标表用 insert overwrite 覆盖当日分区；明细表按需求决定追加
新增口径与“卸载重装不算新增”不一致仅靠 device_id 无法识别“同设备卸载重装”边界统计同 device_id 的首次/多次出现模式；以 device_id 为主键维护首次 dt；若设备重置导致变化，需风控/埋点补强
DWS/ADS 串行执行仍出现空结果DWS 表 dt 字段写入与 where dt 不一致核对 DWS 插入时 dt 取值是否为 ‘$do_date’；保证 DWS 写入 dt 与查询 dt 同源；避免混用 t1.dt 与脚本 dt

其他系列

🚀 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
文章
阅读
粉丝