大数据-231 离线数仓 - DWS 层、ADS 层的创建 Hive 执行脚本

点一下关注吧！！！非常感谢！！持续更新！！！

Java篇开始了！

目前开始更新 MyBatis，一起深入浅出！

目前已经更新到了：

• Hadoop（已更完）
• HDFS（已更完）
• MapReduce（已更完）
• Hive（已更完）
• Flume（已更完）
• Sqoop（已更完）
• Zookeeper（已更完）
• HBase（已更完）
• Redis （已更完）
• Kafka（已更完）
• Spark（已更完）
• Flink（已更完）
• ClickHouse（已更完）
• Kudu（已更完）
• Druid（已更完）
• Kylin（已更完）
• Elasticsearch（已更完）
• DataX（已更完）
• Tez（已更完）
• 数据挖掘（已更完）
• Prometheus（已更完）
• Grafana（已更完）
• 离线数仓（正在更新…）

章节内容

上节我们完成了如下的内容：

• ODS层的构建 Hive处理
• UDF 处理
• SerDe 处理
• 当前总结

活跃会员

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

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;

执行结果如下图所示：

加载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的引号。
写入的内容如下图所示：

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 ',';

执行结果如下图所示：

加载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, dtfrom dws.dws_member_start_daywhere dt='$do_date'group by dtunion allselect 'week' datelabel, count(*) cnt, dtfrom dws.dws_member_start_weekwhere dt='$do_date'group by dtunion allselect 'month' datelabel, count(*) cnt, dtfrom dws.dws_member_start_monthwhere 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"

写入内容如下图所示：

这里有一个同样功能的脚本，可以参考对比以下：

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(*) daycntfrom dws.dws_member_start_daywhere dt='$do_date'group by dt) day join
(select dt, count(*) weekcntfrom dws.dws_member_start_weekwhere dt='$do_date'group by dt
) week on day.dt=week.dt
join
(select dt, count(*) monthcntfrom dws.dws_member_start_monthwhere dt='$do_date'group by dt
) month on day.dt=month.dt;
"
hive -e "$sql"

写入内容如下图所示：

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