大数据-231 离线数仓 Hive实战：DWD到DWS（日/周/月活跃会员）ADS 指标落地脚本与坑点Hive 离线数仓

TL;DR

场景：离线数仓统计活跃会员（日活/周活/月活），从启动明细汇总到指标输出
结论：DWS 先做去重汇总（Day/Week/Month），ADS 再做轻量聚合，结构清晰且易扩展
产出：DWS 三张分区表 + ADS 一张分区指标表 + 2 套可运行 Hive 装载脚本

大数据-231 离线数仓 Hive实战：DWD到DWS（日/周/月活跃会员）ADS 指标落地脚本与坑点

离线数仓架构图

活跃会员

活跃会员：打开应用的会员即为活跃会员
新增会员：第一次使用英勇的会员，定义为新增会员
留存会员：某段时间新增会员，经过一段时间后，仍继续使用应用认为是留存会员
活跃会员的指标需求：每日、每周、每月的活跃会员数

DWD：会员的每日启动信息明细（会员都是活跃会员，某个会员可能会出现多次） DWS：每日活跃会员信息（关键）、每周活跃会员信息、每月活跃会员信息每日活跃会员信息 => 每周活跃会员信息每日活跃会员信息 => 每月活跃会员信息 ADS：每日、每周、每月活跃会员数（输出）

ADS表结构：daycnt weekcnt monthcnt dt

备注：周、月为自然周、自然月

处理过程：

建表（每日、每周、每月活跃会员信息）
每日启动明细 => 每日活跃会员
每日活跃会员 => 每周活跃会员；每日活跃会员 => 每月活跃会员
汇总生成ADS层的数据

创建DWS层表

DWS作用

统一数据模型

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

支持业务场景

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

数据细化与分类

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

数据准确性与一致性

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

编写脚本

启动Hive，进行执行：

use dws;
drop table if exists dws.dws_member_start_day;
create table dws.dws_member_start_day
(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string
) COMMENT '会员日启动汇总'
partitioned by(dt string)
stored as parquet;
drop table if exists dws.dws_member_start_week;
create table dws.dws_member_start_week(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string,
  `week` string
) COMMENT '会员周启动汇总'
PARTITIONED BY (`dt` string)
stored as parquet;
drop table if exists dws.dws_member_start_month;
create table dws.dws_member_start_month(
  `device_id` string,
  `uid` string,
  `app_v` string,
  `os_type` string,
  `language` string,
  `channel` string,
  `area` string,
  `brand` string,
  `month` string
) COMMENT '会员月启动汇总'
PARTITIONED BY (`dt` string)
stored as parquet;

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

加载DWS层数据

vim /opt/wzk/hive/dws_load_member_start.sh

写入的内容如下所示：

#！/bin/bash
source /etc/profile
# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
# 定义要执行的SQL
# 汇总得到每日活跃会员信息；每日数据汇总得到每周、每月数据
sql="
insert overwrite table dws.dws_member_start_day
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand))
from dwd.dwd_start_log
where dt='$do_date'
group by device_id;
-- 汇总得到每周活跃会员
insert overwrite table dws.dws_member_start_week
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand)),
date_add(next_day('$do_date', 'mo'), -7)
from dws.dws_member_start_day
where dt >= date_add(next_day('$do_date', 'mo'), -7)
and dt <= '$do_date'
group by device_id;
-- 汇总得到每月活跃会员
insert overwrite table dws.dws_member_start_month
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand)),
date_format('$do_date', 'yyyy-MM')
from dws.dws_member_start_day
where dt >= date_format('$do_date', 'yyyy-MM-01')
and dt <= '$do_date'
group by device_id;
"
hive -e "$sql"

注意Shell的引号。写入的内容如下图所示：离线数仓 Hive 加载DWS ODS => DWD => DWS（每日、每周、每月活跃会员的汇总表）

创建ADS层表

ADS 作用

聚合和简化数据

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

面向业务应用

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

数据分发与集成

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

轻量化与高性能

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

计算当天、当周、当月活跃会员数量

drop table if exists ads.ads_member_active_count;
create table ads.ads_member_active_count(
  `day_count` int COMMENT '当日会员数量',
  `week_count` int COMMENT '当周会员数量',
  `month_count` int COMMENT '当月会员数量'
) COMMENT '活跃会员数'
partitioned by(dt string)
row format delimited fields terminated by ',';

执行结果如下图所示：离线数仓创建 DWS 表

加载ADS层数据

vim /opt/wzk/hive/ads_load_memeber_active.sh

写入的内容如下：

#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
with tmp as(
  select 'day' datelabel, count(*) cnt, dt
  from dws.dws_member_start_day
  where dt='$do_date'
  group by dt
  union all
  select 'week' datelabel, count(*) cnt, dt
  from dws.dws_member_start_week
  where dt='$do_date'
  group by dt
  union all
  select 'month' datelabel, count(*) cnt, dt
  from dws.dws_member_start_month
  where dt='$do_date'
  group by dt
)
insert overwrite table ads.ads_member_active_count
partition(dt='$do_date')
select sum(case when datelabel='day' then cnt end) as
day_count,
sum(case when datelabel='week' then cnt end) as
week_count,
sum(case when datelabel='month' then cnt end) as
month_count
from tmp
group by dt;
"
hive -e "$sql"

写入内容如下图所示：离线数仓创建ADS层这里有一个同样功能的脚本，可以参考对比以下：

vim /opt/wzk/hive/ads_load_memeber_active2.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_member_active_count
partition(dt='$do_date')
select daycnt, weekcnt, monthcnt
from (select dt, count(*) daycnt
      from dws.dws_member_start_day
      where dt='$do_date'
      group by dt
     ) day join
(select dt, count(*) weekcnt
 from dws.dws_member_start_week
 where dt='$do_date'
 group by dt
) week on day.dt=week.dt
join
(select dt, count(*) monthcnt
 from dws.dws_member_start_month
 where dt='$do_date'
 group by dt
) month on day.dt=month.dt;
"
hive -e "$sql"

写入内容如下图所示：离线数仓 Hive ADS写入数据

第一个脚本：通过构建临时表（WITH tmp AS (...)）将不同维度的数据（天、周、月）汇总到一个临时表中，再通过 SUM 计算出最终的统计结果。这种方式的灵活性较高，便于扩展。
第二个脚本：直接通过 JOIN 不同的子查询，将天、周、月三个维度的数据联结在一起，最后插入目标表。这种方式在性能上可能更高效，但扩展性稍差。

错误速查

症状	根因定位	修复
Shell脚本执行报“bad interpreter”或直接不跑	shebang写成了`#！/bin/bash`（全角感叹号）或脚本含Windows换行	`head -1 script.sh`；`cat -A script.sh`看`^M`；改为`#!/bin/bash`；`sed -i 's/\r$//' script.sh`
DWS日表里uid/app_v等变成`abc`长串，后续不好分析/耗资源	用`collect_set`拼接字符串是“存展示字段”，不是“分析字段”	避免直接使用`collect_set`拼接长字符串，改用分析型字段存储方案
周活跃口径不稳定：同一自然周每天跑批结果变化/难对账	周表`partition(dt=跑批日)`，但week范围用“周一到do_date”的滚动窗口	周汇总建议按`week`分区/主键输出（例如`dt=week_start`）；或ADS用固定周区间统计（整周结束后出数）
`next_day('$do_date','mo')`在部分环境报错或结果不符合预期	`next_day`的weekday参数依赖实现/语言环境（`mo/mon/Monday`等）	统一用明确写法（如`next_day(date_sub('$do_date',7),'MO')`并验证）；或改用`date_format+pmod(datediff...)`自算周一
ADS表结构描述与建表不一致	文中写`daycnt weekcnt monthcnt dt`，建表实际是`day_count week_count month_count`且`dt`是分区字段	对照“备注/表结构说明”与DDL；统一命名与说明；表结构描述要与DDL一致
ADS产出为NULL或0，但DWS明明有数据	`dt`分区值不一致（`do_date`与DWS `dt`不一致）、跑批日无数据、或`group by dt`后`join`丢行	`show partitions ...`；`select count(*) where dt=...`；确保ODS/DWD/DWS同一`do_date`；必要时对空数据兜底
Hive执行报解析错误，定位到`--`注释附近	多语句拼接在同一个`hive -e`中，分号/换行/注释位置不规范导致截断	打印最终SQL字符串；复制到hive cli单句执行；用`/* */`注释或保证每句以`;`结束并换行
DWS周/月汇总慢、数据量大	周/月是从day表回扫多天并`group by device_id`，且每天全量覆盖写入	周/月改为增量（按日追加、按周/月周期重算）；或用中间层保存`device_id`去重集合

其他系列

🚀 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案例详解 🔗 大数据模块直达链接