项目流程
step1: 需求分析(知道有哪些主题,哪些指标,哪些维度
step2: 建模分析(是否要压缩,行/列存储,索引,分区,分桶
step3: 建模操作,建库建表
step4: 数据采集(MySQL业务数据库 -> Hive(ODS层
step5: 数据清洗转换(空值过滤,空值转换,列值裁剪,切割
step6: 数据分析操作,结合主题,维度指标完成各种需求
step7: 数据导出操作,导出分析后的结果数据到mysql中,之后对接FineBI进行可视化展示
需求分析
提出需求
这一步就是根据需求把涉及到的表, 维度, 指标, 以及具体的维度字段, 维度字段提取出来
需求1- 总访问客户量
- 统计指定时间段内，访问客户的总数量。能够下钻到小时数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 维度:
时间维度: 年 季度 月 天 小时
指标:
总访问客户量
涉及到表:
web_chat_ems_年_月 (事实表)
涉及到字段:
时间维度: create_time
特点: 一个字段中涵盖了多个字段的数据
转换操作: 需要将 create_time 转换为 yearinfo, quarterInfo, monthInfo, dayinfo,hourinfo
指标字段: sid、session_id、ip
注意: 先去重在count统计
|
需求2- 地区独立访客热力图
- 统计指定时间段内，访问客户中各区域人数热力图。能够下钻到小时数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 涉及到维度:
时间维度: 年 季度 月 天 小时
区域维度
涉及到指标:
访问客户量
涉及到表:
web_chat_ems_年_月 (事实表)
涉及到字段:
时间维度字段: create_time
转换操作: 需要将 create_time 转换为 yearinfo, quarterInfo, monthInfo, dayinfo,hourinfo
区域维度: area, country, province,city
指标字段: sid、session_id、ip
先去重, 后统计
|
需求3- 访客咨询率趋势
- 统计指定时间段内，不同地区（省、市）访问的客户中发起咨询的人数占比；
- 咨询率=发起咨询的人数/访问客户量；客户与网咨有说一句话的称为有效咨询。
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
| 涉及到维度:
时间维度: 年 季度 月 天
地区维度: 省、市、区域
涉及到指标:
咨询客户量, 访问客户量
细点:
当遇到需要计算比率的相关的指标的时候, 我们前期只需要计算其分子和分母即可, 最后比率在APP层计算即可
涉及表:
web_chat_ems_年_月 (事实表)
涉及到字段:
时间维度: create_time
转换操作: 需要将 create_time 转换为 yearinfo, quarterInfo, monthInfo, dayinfo,hourinfo
地区维度: area,province,city
指标维度:
访问客户量: sid、session_id、ip
去重统计
咨询客户量: sid、session_id、ip
去重统计
判断依据: 必须保证 msg_count >= 1
发现:
访问量在上述的SQL中, 统计的更加细化, 已经涵盖了当前这个需求内容, 所以此处的访问量无需在计算了
此处只需要算咨询量:
在计算咨询量的时候, 既然访问量算的更加细化了, 建议咨询量也以此为标准, 后续可以应用更多需求
|
需求4- 客户访问量和访客咨询率双轴趋势
- 统计指定时间段内，每日客户访问量/咨询率双轴趋势图。能够下钻到小时数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 涉及维度:
时间维度: 年 季度 月 天 小时
涉及指标:
访问量: 不需要计算了, 直接复用需求一的内容
咨询量:
涉及表:
web_chat_ems_年_月 (事实表)
涉及字段:
时间维度: create_time
转换操作: 需要将 create_time 转换为 yearinfo, quarterInfo, monthInfo, dayinfo,hourinfo
咨询量指标: sid、session_id、ip
去重统计, 统计的时候, 需要进行判断: 必须保证 msg_count >= 1
|
需求5- 时间段访问客户量趋势 (不需要关心了, 前序涵盖)
需求6- 来源渠道访问量占比
- 统计指定时间段内，不同来源渠道的访问客户量占比。能够下钻到小时数据。
- 占比: 总访问量与各来源渠道的占比情况
- 占比: 各来源渠道中,访问量的占比 (依次为标准)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 涉及维度:
时间维度: 年 季度 月 天 小时
来源渠道
涉及指标:
访问客户量
咨询客户量
涉及表:
web_chat_ems_年_月 (事实表)
涉及字段:
时间维度: create_time
来源渠道: origin_channel
访问量和咨询量指标字段: sid、session_id、ip
访问量和咨询量区别:
咨询量多个一个判断: msg_count >= 1
|
需求7- 搜索来源访问量占比
- 统计指定时间段内，不同搜索来源的访问客户量占比。能够下钻到小时数据。
- 占比: 总访问量与各搜索来源的占比情况 (依次为标准)
- 占比: 各搜索来源中,访问量的占比
1
2
3
4
5
6
7
8
9
10
11
12
13
| 涉及维度:
时间维度: 年 季度 月 天 小时
搜索来源
涉及指标:
访问量
涉及表:
web_chat_ems_年_月 (事实表)
涉及字段:
时间字段: create_time
搜索来源: seo_source
指标字段: sid、session_id、ip
|
需求8- 活跃页面排行榜
- 统计指定时间段内，产生访问客户量最多的页面排行榜TOPN。能够下钻到小时数据。
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
| 涉及维度:
时间维度: 年 季度 月 天 小时
受访页面
涉及到指标:
访问客户量
涉及表:
web_chat_text_ems_年_月 (事实表)
web_chat_ems(事实表)
涉及字段:
时间维度: create_time
受访页面维度: from_url
指标字段: sid、session_id、ip
表关联条件:
id = id
说明:
当发现有些字段无法确定的时候, 此时需要通过查看表的数据结构, 从当中找相关的字段, 如果找不到, 思考怎么获得这个字段
以当前这个为例, 发现 web_chat_text_ems 和 web_chat_ems 是一种一对一的关系, 简单说, 本质就是一个表, 所以可以直接使用 web_chat_ems中相关的维度字段来处理
维度退化操作: 降维度
将两个表合并为一个表
|
需求汇总
将上述需求设计到的表和字段进行汇总.
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
| 访问客户量: 共计有 25个需求
涉及到维度:
常驻维度: 每一个需求都需要计算的维度
时间维度: 年 季度 月 天 小时
产品属性维度: 有这个维度, 但是并不是每个需求都有的
地区维度
来源渠道维度
搜索来源维度
受访页面
总访问量
咨询客户量: 共计有 15个需求
涉及维度:
常驻维度:
时间维度: 年 季度 月 天 小时
产品属性维度:
地区维度
来源渠道
总咨询量
涉及表:
web_chat_text_ems_年_月 (事实表) wcte
web_chat_ems(事实表) wce
涉及字段:
时间维度: wce.create_time
地区维度: wce.area , wce.country, wce.province,wce.city
来源渠道维度: wce.origin_channel
搜索来源维度: wce.seo_source
受访页面: wcte.from_url
指标字段:
sid、ip、session_id
两个表关联条件:
wcte.id = wce.id
需要转换内容:
时间维度需要转换: 需要将create_time 转换为 年 季度 月 天 小时 (拉宽操作)
合并降维操作: 将两个表合并为一个表
是否需要进行拉链呢? 数据只有新增操作, 所以不需要进行拉链操作
同步方式: 仅新增同步
|
建模分析
建模分析主要的作用: 用于分析当前这个主题需要进行那些分层操作, 每个层次结构中需要有那些表, 每个表中需要有那些字段
ODS层: 数据源层(贴源层)
1
2
3
4
5
6
7
8
9
10
| 作用: 对接数据源, 保留最完整的原始数据。用于将数据源中数据完整的拷贝到ODS层, 一般来说数据源中有哪些表, 那么会在ODS层构建有那些表, 与之一一对应
字段和原始数据一致，需要加一个分区字段：抽取数据的日期
建表:
需要构建两张表: web_chat_ems 和 web_chat_text_ems
每个表的字段有那些: 字段内容与业务库的字段一致 + 分区字段(dt)
是否需要分区操作: 仅新增同步需要构建的, 同步周期为 天
是否需要分桶操作: 不需要, 这样采集数据更加方便
选择什么存储格式: ORC, SNAPPY
选择什么压缩格式: SNAPPY
|
DWD层: 明细层
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
| 作用: 提供高质量的数据
1- 清洗操作
2- 转换操作
3- 拉链实现
说明:
DWD层的表的数量与ODS层表的数量是一致的, 表的字段基本上都是涵盖(除非这些字段以后完全用不上)
表字段不受清洗操作的影响，因为清洗操作只过滤行数据，不影响字段列；
受到转换操作的影响，转换出来的新字段，需要加到DWD表中。
当前主题, 没有拉链操作, 有清洗和转换操作, 需要将其中一个表中create_time字段转换为 年 季度 月 天 小时
建表:
需要构建 web_chat_ems_dwd和 web_chat_text_ems表
表字段:
将所有原有字段全部保留 + 扩展出来新字段 + 分区字段(dt)
是否需要分区表: 需要的, dt 天分区
是否需要分桶表: 建分桶表(走SMB优化)
建表:
1- 两个表必须都是分桶表
2- 分桶字段必须是JOIN字段,同时还需要进行排序
3- 桶的数量必须一致
选择什么存储格式: ORC
选择什么压缩格式: SNAPPY
思考: 还可以走什么优化手段
|
DWB层: 中间层
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 作用:
进行维度退化操作, 形成宽表操作(多表合并, 进行Join)
建表:
需要构建为一张表: web_chat_ems_dwb
此表需要将DWD层两个表合并在一起, 合并后, 所有字段都要全部保留
是否需要分区表: 需要的, dt 天分区
是否需要分桶表: 1- 是否需要采样(1)指标统计是否是相对指标 2) 数据量是否很庞大, 测试环境无法运行, 此时需要采样) 2- 是否需要提升查询效率(不需要了)
不需要构建分桶表
选择什么存储格式: ORC
选择什么压缩格式: SNAPPY
思考: 还可以走什么优化手段:
目前在计算咨询量的时候, 需要 msg_count >=1 此时可以尝试使用索引优化(row group index 索引要求, bloom filter index)
满足了 row group index 索引要求
|
DWS层: 提前聚合(业务层)
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
| 作用:
提前聚合操作
注意：当涉及到去重操作时，不要提前聚合。
比如说:
按照 年 月 日 统计, 此时可以在DWS层先按照日进行统计, 后续在日统计结果基础上, 统计 月 和 年
当下主题的指标统计: 先去重, 然后才能统计 一般不能进行提前聚合, 因为一旦聚合后, 导致上卷统计结果不准确
注意:
当下主题, 无法进行提前聚合操作, 所以 当前主题不需要有DWS层
举个例子:
2022年 3月 1号 访问量100条, 其中重复的有 30个
2022年 3月 2号 访问量 100条, 其中重复的50个
注意: 同一个用户有可能在 1号 和 2号都访问了
先按照天统计:
3月1号访问量为 70个
3月2号访问量为 50个
统计3月份访问量:
70 + 50 = 120个
实际上, 如果直接对原有数据统计, 共计只有100个, 其中有20 个 1号和2号都来了
|
DM层:
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
| 作用:
细化统计分析操作. 基于DWS层结果, 进行上卷统计处理
指标字段 + 维度字段 + 两个经验字段
访问量: 共计有 25个需求
涉及到维度:
固有维度: 每一个需求都需要计算的维度
时间维度: 年 季度 月 天 小时
产品属性维度: 有这个维度, 但是并不是每个需求都有的
地区维度
来源渠道维度
搜索来源维度
受访页面
总访问量
咨询量: 共计有 15个需求
涉及维度:
固有维度:
时间维度: 年 季度 月 天 小时
产品属性维度:
地区维度:
来源渠道
总咨询量
visit_consult_dm表需要有那些字段呢?
指标字段 + 维度字段 + 两个经验字段
visit_sid, visit_session_id, visit_ip, consult_sid, consult_session_id, consult_ip,
yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area,origin_channel,seo_source,from_url,
time_type,group_type
表是否需要构建分区表: 需要的 分区字段就是我们的时间字段
可以将 yearinfo,quarterinfo,monthinfo,dayinfo 作为分区字段
表是否为分桶表: 不需要了
选择什么存储格式: ORC
选择什么压缩格式: SNAPPY
思考可以走什么优化?
可以考虑有可能出现group by的数据倾斜问题, 如何解决
|
RPT层(ADS,APP层) : 数据应用层
1
2
3
4
| 作用:
对接外部应用, 应用方需要什么数据, 从DM层提取出来, 放置到DM层中
对接最终应用: 此层就根据实际需求, 建表存储即可
|
建模操作
根据上面分析到的数据建库建表.
ODS层表:
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
46
47
48
49
50
51
|
drop database if exists edu_ods;
create database if not exists edu_ods;
create table if not exists edu_ods.web_chat_ems(
id INT comment '主键' ,
create_date_time STRING comment '数据创建时间' ,
session_id STRING comment '七陌SESsionId' ,
sid STRING comment '访客id' ,
create_time STRING comment '会话创建时间' ,
seo_source STRING comment '搜索来源' ,
seo_keywords STRING comment '关键字' ,
ip STRING comment 'IP地址' ,
AREA STRING comment '地域' ,
country STRING comment '所在国家' ,
province STRING comment '省' ,
city STRING comment '城市' ,
origin_channel STRING comment '投放渠道' ,
`user` STRING comment '所属坐席' ,
manual_time STRING comment '人工开始时间' ,
begin_time STRING comment '坐席领取时间' ,
end_time STRING comment '会话结束时间' ,
last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
reply_msg_count INT comment '客服回复消息数' ,
msg_count INT comment '客户发送消息数' ,
browser_name STRING comment '浏览器名称' ,
os_info STRING comment '系统名称'
)
partitioned by(dt string)
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES('orc.compress'='SNAPPY' );
create table if not exists edu_ods.web_chat_text_ems(
id INT comment'主键',
referrer string comment'上级来源页面',
from_url string comment'会话来源页面',
landing_page_url string comment'访客着陆页面',
url_title string comment'咨询页面title',
platform_description string comment'客户平台信息',
other_params string comment'扩展字段中数据',
history string comment'历史访问记录'
)
partitioned by(dt string)
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES('orc.compress'='SNAPPY' );
|
DWD层建表操作
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
drop database if exists edu_dwd;
create database if not exists edu_dwd;
drop table if exists edu_dwd.web_chat_ems_dwd;
create table if not exists edu_dwd.web_chat_ems_dwd(
id INT comment '主键' ,
create_date_time STRING comment '数据创建时间' ,
session_id STRING comment '七陌SESsionId' ,
sid STRING comment '访客id' ,
create_time STRING comment '会话创建时间' ,
seo_source STRING comment '搜索来源' ,
seo_keywords STRING comment '关键字' ,
ip STRING comment 'IP地址' ,
AREA STRING comment '地域' ,
country STRING comment '所在国家' ,
province STRING comment '省' ,
city STRING comment '城市' ,
origin_channel STRING comment '投放渠道' ,
`user` STRING comment '所属坐席' ,
manual_time STRING comment '人工开始时间' ,
begin_time STRING comment '坐席领取时间' ,
end_time STRING comment '会话结束时间' ,
last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
reply_msg_count INT comment '客服回复消息数' ,
msg_count INT comment '客户发送消息数' ,
browser_name STRING comment '浏览器名称' ,
os_info STRING comment '系统名称' ,
hourinfo string comment '小时'
)
partitioned by(yearinfo string,quarterinfo string,monthinfo string,dayinfo string)
clustered by(id) sorted by (id asc) into 10 buckets
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES(
'orc.compress'='SNAPPY' ,
"orc.bloom.filter.columns"="id"
);
drop table if exists edu_dwd.web_chat_text_ems_dwd;
create table if not exists edu_dwd.web_chat_text_ems_dwd(
id INT comment'主键',
referrer string comment'上级来源页面',
from_url string comment'会话来源页面',
landing_page_url string comment'访客着陆页面',
url_title string comment'咨询页面title',
platform_description string comment'客户平台信息',
other_params string comment'扩展字段中数据',
history string comment'历史访问记录'
)
partitioned by(dt string)
clustered by(id) sorted by (id asc) into 10 buckets
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES(
'orc.compress'='SNAPPY',
"orc.bloom.filter.columns"="id"
);
|
DWB层:
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
46
|
drop database if exists edu_dwb;
create database if not exists edu_dwb;
create table if not exists edu_dwb.web_chat_ems_dwb(
id INT comment '主键' ,
create_date_time STRING comment '数据创建时间' ,
session_id STRING comment '七陌SESsionId' ,
sid STRING comment '访客id' ,
create_time STRING comment '会话创建时间' ,
seo_source STRING comment '搜索来源' ,
seo_keywords STRING comment '关键字' ,
ip STRING comment 'IP地址' ,
AREA STRING comment '地域' ,
country STRING comment '所在国家' ,
province STRING comment '省' ,
city STRING comment '城市' ,
origin_channel STRING comment '投放渠道' ,
`user` STRING comment '所属坐席' ,
manual_time STRING comment '人工开始时间' ,
begin_time STRING comment '坐席领取时间' ,
end_time STRING comment '会话结束时间' ,
last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
reply_msg_count INT comment '客服回复消息数' ,
msg_count INT comment '客户发送消息数' ,
browser_name STRING comment '浏览器名称' ,
os_info STRING comment '系统名称' ,
hourinfo string comment '小时',
referrer string comment'上级来源页面',
from_url string comment'会话来源页面',
landing_page_url string comment'访客着陆页面',
url_title string comment'咨询页面title',
platform_description string comment'客户平台信息',
other_params string comment'扩展字段中数据',
history string comment'历史访问记录'
)
partitioned by(yearinfo string,quarterinfo string,monthinfo string,dayinfo string)
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES(
'orc.compress'='SNAPPY' ,
'orc.create.index'='true'
);
|
DM层:
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
|
drop database if exists edu_dm;
create database if not exists edu_dm;
create table if not exists edu_dm.visit_consult_dm(
visit_sid bigint comment 'sid访问量',
visit_session_id bigint comment 'session_id访问量',
visit_ip bigint comment 'ip访问量',
consult_sid bigint comment 'sid咨询量',
consult_session_id bigint comment 'session_id咨询量',
consult_ip bigint comment 'ip咨询量',
hourinfo string comment '小时',
area string comment '地区维度',
origin_channel string comment '来源渠道维度',
seo_source string comment '搜索来源维度',
from_url string comment '受访url',
time_type string comment '时间标记: 1 小时 2 天 3月 4 季度 5 年',
group_type string comment '产品属性维度标记: 1 地区 2 来源渠道 3 搜索来源 4 受访url 5总访问量'
)
partitioned by(yearinfo string,quarterinfo string,monthinfo string,dayinfo string)
row format delimited fields terminated by '\t'
stored as orc
TBLPROPERTIES(
'orc.compress'='SNAPPY'
);
|
RPT建表:
1
| 暂时不构建, 后续根据应用方需要, 动态建表即可
|
数据采集
Sqoop命令,个人感觉都写麻了.
数据清洗转换
数据分析操作
数据导出操作
微信
支付宝