《大型综合项目-基于大数据平台的数据仓库》学习笔记（10）：埋点日志报表篇1_游戏数仓都是日志表么

本项目教程笔记源自多易教育《Titan综合数据仓库与数据运营系统》，在CSDN学院有相关视频教程购买链接，大数据企业级项目实战–Titan大型数据运营系统
本项目课程是一门极具综合性和完整性的大型大数据项目实战课程，课程项目的业务背景源自各类互联网公司对海量用户浏览行为数据和业务数据分析的需求及企业数据管理、数据运营需求。
学完本课程，你将很容易就拿到大数据数仓建设或用户画像建设等岗位的OFFER

本课程项目涵盖数据采集与预处理、数据仓库体系建设、用户画像系统建设、数据治理（元数据管理、数据质量管理）、任务调度系统、数据服务层建设、OLAP即席分析系统建设等大量模块，力求原汁原味重现一个完备的企业级大型数据运营系统。

跟随项目课程，历经接近100+小时的时间，从需求分析开始，到数据埋点采集，到预处理程序代码编写，到数仓体系搭建…逐渐展开整个项目的宏大视图，构建起整个项目的摩天大厦。

---

多易教育，专注大数据培训； 课程引领市场，就业乘风破浪
多易教育官网地址
https://www.51doit.cn
多易教育在线学习平台
https://v.51doit.cn

一、【数据加载】ODS层数据加载

1、数据建模

        ODS层：操作数据层（贴源数据层）
        这一层的建模原则是：模型结构应该跟导入数据的结构保持一致
        而在具体流程中，我们的“原始数据”指的就是经过spark程序预处理完成之后的数据
        （app端埋点日志数据、web端埋点日志数据、微信小程序埋点日志数据）

但是，
        1）考虑到ods层对接的数据是hive数仓外部的数据（spark预处理程序所输出的数据）
我们应该把ods层的表定义为外部表！
        2）考虑到流量数据是会每天新增的，为了将来的计算效率，ods层的流量数据表还应该定义为分区表！

2、模型，表结构

CREATE EXTERNAL TABLE ODS_APP_LOG (
  eventid String
  ,`timestamp` DOUBLE
  ,event Map < String,String >
  ,uid String
  ,phoneNbr String
  ,sessionId String
  ,imei String
  ,mac String
  ,imsi String
  ,osName String
  ,osVer String
  ,androidId String
  ,resolution String
  ,deviceType String
  ,deviceId String
  ,uuid String
  ,appid String
  ,appVer String
  ,release_ch String
  ,promotion_ch String
  ,longtitude DOUBLE
  ,latitude DOUBLE
  ,carrier String
  ,netType String
  ,cid_sn String
  ,ip String
  ,province String
  ,city String
  ,district String
  ,dateStr String
  ,timeStr String
  ) partitioned BY (dt string) stored AS parquet;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

3、加载数据

小提示：
HiveServer启动时可以选择启动为前台模式，也可以启动为后台模式：
如何将一个程序启动为后台模式？
示例如下：
nohup  bin/hiveserver2  1>/dev/null  2>&1  &
1
2
3
4
5

步骤：

1） 启动hiveserver2

2） 启动hiveserver的客户端程序beeline，来连接hive服务
[root@c701 hive-3.1.1]# bin/beeline -u jdbc:hive2://localhost:10000 -n root

3） 新建一个数仓的库titan
0: jdbc:hive2://localhost:10000> create database titan;
0: jdbc:hive2://localhost:10000> use titan;

4) 创建ods层的流量数据表（调用上一节建模中的建表语句）

5）加载预处理后的日志到表： ods_traffic_log 
a) 在hdfs上创建预处理结果数据目录
[root@c703 ~]# hdfs dfs -mkdir -p /washlog/2019-06-13/
	
b）从windows上将预处理测试结果文件上传到linux（alt+p打开sftp，或者rz，上传即可）
	
c）将测试结果文件上传到hdfs
 	[root@c703 ~]# hdfs dfs -put part-00000-3afd3170-0129-401e-8351-b5144306a73c-c000.snappy.parquet /washlog/2019-06-13/
	
d） load数据到hive表
	两种方式皆可：
load data inpath '/washlog/2019-06-13' into table ods_traffic_log partition(dt='2019-06-13');
或者： 
alter table ods_traffic_log add partition(dt='2019-06-13') location '/washlog/2019-06-13';

e）加载进去之后，可以用如下语法来查询表中是否有新增的分区：
show partitions ods_app_log;
select * from ods_app_log limit 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

二、【明细构建】DWD层建模及开发

DWD层：数仓明细层！
DWD层的特点是：数据粒度跟ODS保持一致的！
DWD相对于ODS来说，区别主要有：字段规范化，数据清洗，字段补充（常用维度信息）
DWD还有一个考虑：可以根据实际情况，将ODS的数据按主题来切分

1、数据建模

维度补充
1)将日期字段，关联时间维表，得到更丰富的时间信息：
年，月，日，旬，周，季度，双十一期间，周年庆

2)将pgid（页面id // url）等这类id字段，关联相关字典维表，得到：
页面标题，页面所属栏目，所属tab页，所属频道

字段补充
ods层的埋点数据中，用户的标识没有得到统一，可以在DWD层中生成用户标识；
如果一条日志中有uid，则用uid作为统一标识的值
如果一条日志没有uid，则按如下优先级挑选一个标识作为统一标识的值：
imei --> mac --> imsi --> androidid --> uuid --> deviceId

主题划分： 
可以根据经验或者需求划分主题，比如：
1)流量分析主题明细表
2)交互事件分析主题表
3)广告分析主题表等；
1
2
3
4
5
6
7
8
9
10
11

2、ETL计算逻辑

一共要生成4张表：
DWD：全局数据明细表 DWD_APL_GLB_DTL
– 相对ODS来说，主要增加了时间维度信息等公共维度信息

DWD：流量分析主题明细表 DWD_APL_TFC_DTL
– 相对于ODS来说，只包含页面浏览事件数据，增加页面描述相关维度信息（栏目、频道，类别）

DWD：交互事件分析主题明细表 DWD_APL_ITR_DTL
–相对于ODS来说，只包含各类交互事件数据（点赞、收藏、分享、评论…）

DWD：广告分析主题明细表 DWD_APL_ADV_DTL
–相对于ODS来说，只包含各类广告事件，增加页面维度信息、广告维度

app日志全局明细表
关键逻辑：关联公共维度，生成用户统一标识字段
数据中如果uid为空，则从imei --> mac --> imsi --> androidid --> uuid --> deviceId中取一个值

app日志流量分析明细表
关键逻辑：主题抽取，所有appviewEvent、pgviewEvent事件

app日志交互事件分析明细表表
关键逻辑：主题抽取，所有点赞、收藏、进入tab页、举报、加购等交互行为事件

app日志广告分析明细表
关键逻辑：主题抽取，所有广告曝光（ad_show）、广告点击事件(ad_click)

3、Spark / Hive 任务实现

用到的几个函数：
select unix_timestamp(‘2019-10-24 08:20:30’)   -->  1572113655  日期字符串转时间戳
select from_unixtime(1572113655)    --> ‘2019-10-24 08:20:30’  时间戳转日期字符串

regexp_extract(字符串，正则表达式，输出索引)
select regexp_extract('http://www.doitedu.cn/asdgf/pg1302','http://www.doitedu.cn/(.*?)/(.*?)',1);


HQL（hive query language）开发

1
2
3
4
5
6
7
8
9
10

三、【流量分析】流量概况分析-不考虑维度

流量：衡量网站/app被访问的人数、次数、时间等数据的统称！
ADS_FLW_OVW报表统计需求：

1、DWS层建模及开发

本层共开发两个汇总表：
        按会话session进行汇总的流量数据表
        按用户进行汇总的流量数据表

1)、流量会话聚合表建模

DWS_FLW_AGG_S：流量会话聚合表（一次会话一条数据）

2)、计算逻辑

计算流程：
        源表：dwd_traffic_dtl
        目标表：DWS_TF_AG_S
计算逻辑：
        分组聚合！
        uid ： 作为分组key
        sessionid： 作为分组key
        起始时间： min(commit_time)
        结束时间： max(commit_time)
        pv个数： count(1)

3)、流量_用户聚合表建模

DWS_FLW_AGG_U：流量用户聚合表（一个用户一条数据）
DWS_FLW_AGG_S：流量会话聚合表（一次会话一条数据）

访问次数：会话个数！
访问总时长：每个会话的时长之和！

4)、计算逻辑

计算流程：
        源表：dws_traffic_session_agg
        目标表：DWS_TF_AG_U
计算逻辑：
        分组聚合！
        uid ： 作为分组key
        访问次数： count
        访问总时长：sum(end_time-start_time)
        pv总数： sum(pv_cnts)
        省、市、区、手机型号、操作系统、系统版本都是维度，直接在每组中取一个值 max()

2、ADS层报表开发

1)、流量概况统计报表建模

报表：ADS_FLW_OVW

2)、计算逻辑

《详见项目代码》

四、【流量分析】流量概况分析-不考虑维度

明细表：

流量会话聚合表：

流量用户聚合表：

流量概况报表：
       pv数
       uv数
       访问次数
       次均时长
       人均次数
       人均pv
       人均时长
       回头客占比
       省
       市
       区
       手机型号
       操作系统
       系统版本
       下载渠道
       推广渠道

可能的维度组合统计结果示例：
       省 pv总数 uv总数 回头客总数 …
       市 pv总数 uv总数 回头客总数 …
       省 市 pv总数 uv总数 回头客占比 …
       省 市 区 pv总数 uv总数 回头客占比 …
       省 设备类型 PV总数 UV总数 回头客占比 …

为避免创建n多的维度组合汇总结果表，可以创建一张多维数据模型（cube）表：

此表，将涵盖所有维度组合下的流量聚合指标！

五、【用户分析】日新日活分析

日新： 每日新增用户（之前没来过的用户，“今天”来了）
日活： 每日活跃用户（当天访问过的用户）

1、需求可视化示例

产品端有如下数据报表可视化需求：
图表1

图表2

图表3

图表4

2、需求分析

以上需求中，经过提炼，发现，所涉及的指标有：
        新用户数
        活跃用户数
        以及占比

剩下的，就是通过各种维度来进行聚合分析
所以，这一堆的可视化需求，都可以通过底层的两个数据立方体来支撑：
        ADS日新数cube
        ADS日活数cube
发现，报表中有事实指标：新增用户数 活跃用户数 累计总用户数
进而思考：新增用户是什么？怎么判定一个用户是新增用户？

新增用户定义： 计算日之前从没访问过，计算日出现了！
怎么去判别？应该有一个历史用户记录表！

总结：
前台产品提出的用户分析相关需求，主要涉及3大事实：
        新用户数
        活跃用户数
        用户留存日

3、DWS层模型设计

1)、日活记录表：dws_apl_dau_rec

报表中，关于日活的事实指标，主要就是一个日活数
所以，本日活表中，主要记录：什么时间，什么人出现过，加上各种维度！

日活表

2)、日新记录表：dws_apl_dnu_rec

表结构与日活表完全相同，只是表中的数据不同（日新中只有新增用户）

日新表

计算逻辑： 将当日的 日活记录表 LEFT JOIN 昨日的历史登录记录表
然后过滤出右表为null的就是新用户

3)、历史访问记录表：dws_apl_hsu_rec

如果光考虑日新、日活的计算，那么，历史用户记录表只需要记用户id即可
但考虑到留存、用户画像首次登录标签等的计算，可以设计如下模型（留存明细表）：

用户登录历史记录表

有了这个留存明细表：
        既可以作为历史累计用户记录表用！
        又方便地计算各种时间段的留存数据！

4、DWS层ETL开发

1)、DWS日活表计算逻辑

直接从“DWD_APL_GLB_DTL”抽所需要的字段即可

/*
用户主题dwd层：活跃用户明细表：dws_usr_dau
日期  uid	  省	市	 区	  手机型号  osname  osver

@Author HUNTER
@Date 2019-07-26

@源表：dws_FLW_agg_U
@目标：dws_usr_dau
@计算逻辑：
	抽数据
*/
1
2
3
4
5
6
7
8
9
10
11
12

2)、DWS日新表计算逻辑

用 [当日日活表] left join [昨日登录历史记录表] 可得

《详见项目代码》

3)、访问历史记录表计算逻辑

用 [日活表] full join [登录历史记录表]

《详见项目代码》

5、ADS层报表开发

1)、需求分析

ADS_USR_DAU_CUBE ：日活数多维报表

ADS_USR_DNU_CUBE ：日新数多维报表

2)、ADS日新多维分析报表计算

/*
用户主题dws层：日新数cube表：dws_apl_dnu_cube
省	市	 区	  手机型号  osname  osver  user_new_cnts

@Author HUNTER
@Date 2019-07-26

@源表：dwd_user_new_d
@目标：dws_user_new_cube

@计算逻辑：
	根据各种维度来构建数据立方体
*/

-- 建表
create table dws_apl_dnu_cube(
province string,
city string,
district string,
manufacture string,
osname string,
osver string,
user_new_cnts int
)
partitioned by (dt string,dim string)
stored as parquet
;


-- etl计算
insert into table dws_user_new_cube partition(dt='2019-06-15',dim)
select
province,
city,
district,
manufacture,
osname,
osver,
count(1) as user_new_cnts,
grouping__id as dim
from dwd_user_new_d
where dt='2019-06-15'
group by province,city,district,manufacture,osname,osver
with cube
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

3)、ADS日活多维分析报表计算

/*
用户主题dws层：日新数cube表：dws_user_active_cube
省	市	 区	  手机型号  osname  osver  user_active_cnts

@Author HUNTER
@Date 2019-07-26

@源表：dwd_apl_dau_rec
@目标：dws_apl_dau_cube

@计算逻辑：
	根据各种维度来构建数据立方体
*/

-- 建表
drop table if exists dws_user_active_cube;
create table dws_user_active_cube(
province string,
city string,
district string,
manufacture string,
osname string,
osver string,
user_active_cnts int
)
partitioned by (dt string,dim string)
stored as parquet
;

-- etl 计算
insert into table dws_user_active_cube partition(dt='2019-06-15',dim)
select
province,
city,
district,
manufacture,
osname,
osver,
count(1) as user_active_cnts,
grouping__id as dim
from dwd_user_active_d
where dt='2019-06-15'
group by province,city,district,manufacture,osname,osver
with cube
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

---

多易教育，专注大数据培训； 课程引领市场，就业乘风破浪
多易教育官网地址
https://www.51doit.cn
多易教育在线学习平台
https://v.51doit.cn

本项目教程笔记源自多易教育《Titan综合数据仓库与数据运营系统》，在CSDN学院有相关视频教程购买链接，大数据企业级项目实战–Titan大型数据运营系统
本项目课程是一门极具综合性和完整性的大型大数据项目实战课程，课程项目的业务背景源自各类互联网公司对海量用户浏览行为数据和业务数据分析的需求及企业数据管理、数据运营需求。
学完本课程，你将很容易就拿到大数据数仓建设或用户画像建设等岗位的OFFER

本课程项目涵盖数据采集与预处理、数据仓库体系建设、用户画像系统建设、数据治理（元数据管理、数据质量管理）、任务调度系统、数据服务层建设、OLAP即席分析系统建设等大量模块，力求原汁原味重现一个完备的企业级大型数据运营系统。

跟随项目课程，历经接近100+小时的时间，从需求分析开始，到数据埋点采集，到预处理程序代码编写，到数仓体系搭建…逐渐展开整个项目的宏大视图，构建起整个项目的摩天大厦。