离线数据仓库之用户活跃度分析
文章学习笔记内容来源：拉勾教育大数据开发高薪训练营。
记录一下数据仓库学习笔记，第一次接触数据仓库，重点不在于数据仓库项目实现，而是要理解明白怎么一步步构建数据仓库从需求分析、数据采集、建立表、数据分层、数据一层层转换得到最终所需要的数据。下面的项目基于Centos7.2、Hadoop、Hive、MySQL、Flume，采用的MR计算引擎。
需求分析
统计计算App用户活跃度
计算指标：
新增用户：每日新增用户数
活跃用户：每日，每周，每月的活跃用户数
用户留存：1日，2日，3日用户留存数、1日，2日，3日用户留存率
指标口径业务逻辑：
用户：以设备为判断标准，每个独立设备认为是一个用户。Android系统通常根据IMEI号，IOS系统通常根据OpenUDID 来标识一个独立用户，每部移动设备是一个用户；
活跃用户：打开应用的用户即为活跃用户，暂不考虑用户的实际使用情况。一台设备每天多次打开计算为一个活跃用户。在自然周内启动过应用的用户为周活跃用户，同理还有月活跃用户；
用户活跃率：一天内活跃用户数与总用户数的比率是日活跃率；还有周活跃率（自然周）、月活跃率（自然月）；
新增用户：第一次使用应用的用户，定义为新增用户；卸载再次安装的设备，不会被算作一次新增。新增用户包括日新增用户、周（自然周）新增用户、月（自然月）新增用户；
留存用户与留存率：某段时间的新增用户，经过一段时间后，仍继续使用应用认为是留存用户；这部分用户占当时新增用户的比例为留存率。
已知条件：
- 1、明确了需求
- 2、输入：启动日志
- 3、输出：新增用户、活跃用户、留存用户
- 4、日志文件、ODS、DWD、DWS、ADS（输出）
日志数据采集
数据采集：日志文件 => Flume => HDFS => ODS
数据准备：这里只展示了一条数据 包含了需要的JSON串
2020-09-01 11:56:08.211 [main] INFO com.lagou.ecommerce.AppStart - {"app_active":{"name":"app_active","json":{"entry":"2","action":"0","error_code":"0"},"time":1598965103000},"attr":{"area":"三门峡","uid":"2F10092A1","app_v":"1.1.0","event_type":"common","device_id":"1FB872-9A1001","os_type":"0.97","channel":"WM","language":"chinese","brand":"xiaomi-3"}}
准备7天数据每天10条数据方便查看
Flume自定义拦截器
public class LogTypeInterceptor implements Interceptor{
@Override
public void initialize() {
}
private String strToDate(String timeStr) {
if(timeStr == null || "".equals(timeStr)) {
return "Unknown";
}
//5、将时间戳转换为字符串 "yyyy-MM-dd"
long timestamp = Long.parseLong(timeStr);
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
Instant instant = Instant.ofEpochMilli(timestamp);
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
String date = formatter.format(localDateTime);
//6、将转换后的字符串放置header中
return date;
}
// 逐条处理
@Override
public Event intercept(Event event) {
//1、获取 event 的 body
String eventBody = new String(event.getBody(),Charsets.UTF_8);
//2、获取 event 的 header
Map<String, String> headers = event.getHeaders();
headers.put("logtime", "Unknown");
//3、解析body获取json串
String[] bodyArr = eventBody.split("\\s+"); // 空格切分
if(bodyArr.length < 7) return event;
String jsonStr = bodyArr[6];
int len = jsonStr.length();
boolean flag = len > 2 && jsonStr.charAt(0) == '{' && jsonStr.charAt(len - 1) == '}';
if(!flag) return event;
try {
//4、解析json串获取时间戳
JSONObject jsonObject = JSON.parseObject(jsonStr);
if(jsonObject != null) {
String logtype = headers.get("logtype");
// 启动日志的时间戳
if("start".equals(logtype)) {
String timeStr = jsonObject.getJSONObject("app_active").getString("time");
headers.put("logtime", this.strToDate(timeStr));
}
// 事件日志的时间戳
if("event".equals(logtype)) {
JSONArray jsonArray = jsonObject.getJSONArray("lagou_event");
if(jsonArray.size() > 0){
String timeStr = jsonArray.getJSONObject(0).getString("time");
headers.put("logtime", this.strToDate(timeStr));
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
//7、返回event
return event;
}
@Override
public List<Event> intercept(List<Event> events) {
for(Event event : events) {
this.intercept(event);
}
return events;
}
@Override
public void close() {
}
public static class Builder implements Interceptor.Builder {
@Override
public void configure(Context context) {
}
@Override
public Interceptor build() {
return new LogTypeInterceptor();
}
}
}
Flume 采集日志脚本
a1.sources = r1
a1.sinks = k1
a1.channels = c1
# taildir source
a1.sources.r1.type = TAILDIR
# 记录每个文件最新消费位置
a1.sources.r1.positionFile = /data/conf/startlog_position.json
# filegroups指定filegroups，可以有多个，以空格分隔（taildir source可同时监控多个目录中的文件）
# headers.<filegroupName>.<headerKey>给event增加header key。不同的filegroup，可配置不同的value自定义拦截器
a1.sources.r1.filegroups = f1 f2
a1.sources.r1.filegroups.f1 = /data/logs/start/.*log
a1.sources.r1.headers.f1.logtype = start
a1.sources.r1.filegroups.f2 = /data/logs/event/.*log
a1.sources.r1.headers.f2.logtype = event
# 自定义拦截器
a1.sources.r1.interceptors = i1
a1.sources.r1.interceptors.i1.type = cn.dw.flume.interceptor.LogTypeInterceptor$Builder
# memorychannel
a1.channels.c1.type = memory
a1.channels.c1.capacity = 1000
a1.channels.c1.transactionCapacity = 500
# hdfs sink
a1.sinks.k1.type = hdfs
# 根据自定义目录 写在hdfs上面
a1.sinks.k1.hdfs.path = /user/data/logs/%{logtype}/dt=%{logtime}/
a1.sinks.k1.hdfs.filePrefix = startlog
# 配置文件滚动方式（文件大小2M）
a1.sinks.k1.hdfs.rollSize = 2096152
a1.sinks.k1.hdfs.rollCount = 0
a1.sinks.k1.hdfs.rollInterval = 0
a1.sinks.k1.hdfs.idleTimeout = 0
a1.sinks.k1.hdfs.minBlockReplicas = 1
# 向hdfs上刷新的event的个数
a1.sinks.k1.hdfs.batchSize = 500
# 使用本地时间
# a1.sinks.k1.hdfs.useLocalTimeStamp = true
#指定hdfs文件格式 默认是SequenceFile
a1.sinks.k1.hdfs.fileType = DataStream
# Bind the source and sink to the channel
a1.sources.r1.channels = c1
a1.sinks.k1.channel = c1
先把自定义拦截器打包好上传服务器 放在$FLUME_HOME/lib目录下 启动Flume脚本
flume-ng agent --conf /opt/servers/flume-1.9.0/conf --conf-file /data/conf/flume-log2hdfs3.conf -name a1 -Dflume.root.logger=INFO,console
执行该脚本把准备好的日志拷贝到 配置的目录 /data/logs/start/.*log 下面 到HDFS观察采集日志情况
用户活跃表总体情况
用户活跃情况所用表的层级结构如下面所示
ODS层数据加载
创建ods层表[ods_start_log]并把7天的日志加载到ods表数据
use ods;
create external table ods.ods_start_log(`str` string) comment '用户启动日志信息'
partitioned by (`dt` string)
location '/user/data/logs/start';
-- 进行加载数据
alter table ods.ods_start_log add partition(dt='2020-09-01');
查看ods层表数据 70条测试数据
DWD层数据加载
创建表[dwd_start_log], 设备ID 根据这个字段进行区分用户
设备ID 根据这个字段进行区分用户
CREATE TABLE dwd.dwd_start_log(
`device_id` string,
`area` string,`uid` string,`app_v` string,`event_type` string,`os_type` string,
`channel` string,`language` string,`brand` string,`entry` string,
`action` string,`error_code` string
)PARTITIONED BY (dt string) STORED AS parquet;
对ods层数据进行解析加载到dwd表[准备的7天数据]，这里采用 get_json_object 函数进行解析
with tmp as(select split(str, ' ')[7] line from ods.ods_start_log where dt='2020-09-01')
insert overwrite table dwd.dwd_start_log partition(dt='2020-09-01')
select get_json_object(line, '$.attr.device_id'),
get_json_object(line, '$.attr.area'),
get_json_object(line, '$.attr.uid'),
get_json_object(line, '$.attr.app_v'),
get_json_object(line, '$.attr.event_type'),
get_json_object(line, '$.attr.os_type'),
get_json_object(line, '$.attr.channel'),
get_json_object(line, '$.attr.language'),
get_json_object(line, '$.attr.brand'),
get_json_object(line, '$.app_active.json.entry'),
get_json_object(line, '$.app_active.json.action'),
get_json_object(line, '$.app_active.json.error_code')
from tmp;
验证数据 结果70条
select count(*) from dwd.dwd_start_log t where t.dt>='2020-09-01' and t.dt<='2020-09-07' limit 0,10;
查看数据
select * from dwd.dwd_start_log t where t.dt='2020-09-01';
活跃用户
活跃用户：打开应用的用户即为活跃用户，暂不考虑用户的实际使用情况。一台设备每天多次打开计算为一个活跃用户。在自然周内启动过应用的用户为周活跃用户，同理还有月活跃用户；
dwd：用户的每日启动信息明细（用户都是活跃用户；某个用户可能会出现多次）
dws：每日活跃用户信息(关键)、每周活跃用户信息、每月活跃用户信息
处理过程：
1、建表（每日、每周、每月活跃用户信息）
2、每日启动明细 ===> 每日活跃用户
3、每日活跃用户 => 每周活跃用户；每日活跃用户 => 每月活跃用户
4、汇总生成ads层的数据
加载DWS层数据
创建dws表
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;
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;
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层数据，数据从前面的dwd层dwd.dwd_start_log加载
当前日期 减一天
select date_add(‘2020-07-08’,-1);
当前时间开始的下周星期一[mo]日期
select next_day(‘2020-08-29’,‘mo’);
当月1号
select date_format(‘2020-08-29’, ‘yyyy-MM-01’);
Hive中collect相关的函数有collect_list和collect_set它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重
每日活跃用户明细
insert overwrite table dws.dws_member_start_day partition(dt='2020-09-01')
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='2020-09-01' group by device_id;
汇总得到每周活跃用户明细
insert overwrite table dws.dws_member_start_week partition(dt='2020-09-01')
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('2020-09-01', 'mo'), -7)
from dws.dws_member_start_day
where dt >= date_add(next_day('2020-09-01', 'mo'), -7) and dt <= '2020-09-01' group by device_id;
汇总得到每月活跃用户
insert overwrite table dws.dws_member_start_month partition(dt='2020-09-01')
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('2020-09-01', 'yyyy-MM')
from dws.dws_member_start_day
where dt >= date_format('2020-09-01', 'yyyy-MM-01')
and dt <= '2020-09-01'
group by device_id;
加载ADS层数据[日、周、月]
创建ads层表，计算当天、当周、当月活跃用户数量
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层表
insert overwrite table ads.ads_member_active_count partition(dt='2020-09-01')
select daycnt, weekcnt, monthcnt
from (select dt, count(*) daycnt from dws.dws_member_start_day where dt='2020-09-01' group by dt
) day join (select dt, count(*) weekcnt from dws.dws_member_start_week where dt='2020-09-01' group by dt
) week on day.dt = week.dt join
(select dt, count(*) monthcnt from dws.dws_member_start_month where dt='2020-09-01' group by dt
) month on day.dt=month.dt;
加载ADS层数据[最近7天连续活跃3天]
创建表并加载数据
进行创建表
create table ads.(
`start_day` string COMMENT '活跃开始日期',
`end_day` string COMMENT '活跃结束日期',
`dt_count` int COMMENT '最近七天连续三天活跃用户数',
`device_id` string COMMENT '用户ID'
) COMMENT '最近七天连续三天活跃用户数'
partitioned by(dt string) row format delimited fields terminated by ',';
进行加载数据
with tmp as(select t.device_id,t.dt,
进行排名操作
date_sub(dt,row_number() over(partition by t.device_id order by dt)) as rdt
from dws.dws_member_start_day t where t.dt>='2020-09-01' and t.dt<='2020-09-07')
insert overwrite table ads.ads_member_three_seven_count partition(dt='2020-09-07')
进行分组统计过滤
select min(dt) as start_day,max(dt) as end_day,count(1) as dt_count,device_id
from tmp group by device_id,rdt having dt_count >= 3 order by start_day;
数据验证结果如下：select * from ads.ads_member_three_seven_count t;
新增用户
用一张表来存储所有用户，在所有用户信息表中增加时间列，表示这个用户是哪一天成为新增用
表中包含用户ID 和 成为新增用户时间
判断新增用户：每日活跃用户表的数据判断不是在所有用户里面的数据，则该用户则是新增用户，可以left join实现 判断用户ID为空则是新增用户
加载DWS层数据
创建dws层表并加载数据
进行表创建
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;
加载每日新增用户
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,t1.dt
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='2020-09-01' and t2.device_id is null;
加载ADS层数据
创建表并加重数据
创建表
create table ads.ads_new_member_cnt(`cnt` string)
partitioned by(dt string) row format delimited fields terminated by ',';
加载数据
insert overwrite table ads.ads_new_member_cnt partition (dt='2020-09-01')
select count(1) from dws.dws_member_add_day where dt = '2020-09-01'
留存数据
留存用户与留存率：某段时间的新增用户，经过一段时间后，仍继续使用应用认为是留存用户,这部分用户占当时新增用户的比例为留存率。
1号40人登陆了，2号40人登录了，3号50人登录， 重叠部分是相同的人
2020-09-02[1日留存率]：1号新增40人，在2号有20人登录了，那么留存率 20 / 40 =0.5, 50%
2020-09-03[1日留存率]：2号新增20人，全部在3号登陆了，那么留存率则是20 / 20 = 1, 100%
2020-09-03[2日留存率]：1号新增40人，在3号登陆了10，那么留存率则是10 / 40 = 0.25, 25%
加载DWS层数据
创建dws层表并加装数据 这里主要是判断当天等于 与要计算留存率的那一天的新增用户 看看有多少是那一天新增
这里说明一下dws_member_start_day每天活跃详情，dws_member_add_day 每天新增详情，date_add(‘2020-09-07’, -1) 计算一条前的日期from dws.dws_member_start_day t1 join dws.dws_member_add_day t2 on t1.device_id=t2.device_id where t2.dt=date_add(‘2020-09-07’, -1) and t1.dt=‘2020-09-07’ 这里计算 2020-09-07 这一天活跃用户有多少是2020-09-06新增的，计算在2020-07-07这一天，一日留存用户详情
create table dws.dws_member_retention_day(`device_id` string,`uid` string,`app_v` string,
`os_type` string,`language` string,`channel` string,`area` string,
`brand` string,`add_date` string comment '用户新增时间',`retention_date` int comment '留存天数'
)COMMENT '每日用户留存明细' PARTITIONED BY (`dt` string) stored as parquet;
进行数据加载
insert overwrite table dws.dws_member_retention_day partition(dt='2020-09-07')
select device_id,uid,app_v,os_type,language,channel,area,brand,add_date,c_num from(
一日留存详情
select t2.device_id, t2.uid, t2.app_v,t2.os_type,t2.language,t2.channel,
t2.area,t2.brand,t2.dt as add_date, 1 as c_num
from dws.dws_member_start_day t1 join dws.dws_member_add_day t2
on t1.device_id=t2.device_id where t2.dt=date_add('2020-09-07', -1) and t1.dt='2020-09-07'
union all
两日留存详情
select t2.device_id,t2.uid, t2.app_v,t2.os_type,t2.language,t2.channel,
t2.area,t2.brand,t2.dt as add_date,2 as c_num
from dws.dws_member_start_day t1 join dws.dws_member_add_day t2
on t1.device_id=t2.device_id where t2.dt=date_add('2020-09-07', -2) and t1.dt= '2020-09-07'
union all
三日留存详情
select t2.device_id,t2.uid,t2.app_v,t2.os_type,t2.language,t2.channel,
t2.area,t2.brand,t2.dt as add_date,3 as c_num
from dws.dws_member_start_day t1 join dws.dws_member_add_day t2
on t1.device_id=t2.device_id where t2.dt=date_add('2020-09-07', -3) and t1.dt= '2020-09-07'
) tmp;
加载ADS层数据
创建表并加装数据：这里的 add_date 是前面计算 留存人数 取的当然新增日期，就是计算2020-07-07[1日留存率]，add_date =2020-07-06
用户留存数
create table ads.ads_member_retention_count(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数'
) COMMENT '用户留存数'
partitioned by(dt string) row format delimited fields terminated by ',';
用户留存率
create table ads.ads_member_retention_rate(
`add_date` string comment '新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数',
`new_mid_count` bigint comment '当日用户新增数',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '用户留存率'
partitioned by(dt string) row format delimited fields terminated by ',';
加载留存人数
insert overwrite table ads.ads_member_retention_count partition (dt='2020-09-07')
select add_date, retention_date, count(*) retention_count
from dws.dws_member_retention_day
where dt='2020-09-07' group by add_date, retention_date;
加载留存率
insert overwrite table ads.ads_member_retention_rate partition (dt='2020-09-07')
select t1.add_date, t1.retention_day,t1.retention_count,t2.cnt,t1.retention_count/t2.cnt*100
from ads.ads_member_retention_count t1 join ads.ads_new_member_cnt t2 on t1.add_date=t2.dt
where t1.dt='2020-09-07';
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
