1.全量流程
需求分析
需求一: 计期内，新增意向客户（包含自己录入的意向客户）总数。
需求二: 统计指定时间段内，新增的意向客户，所在城市区域人数热力图。
需求三: 统计指定时间段内，新增的意向客户中，意向学科人数排行榜。学科名称要关联查询出来。
需求四: 统计指定时间段内，新增的意向客户中，意向校区人数排行榜.
需求五: 统计指定时间段内，新增的意向客户中，不同来源渠道的意向客户占比。
需求六: 统计指定时间段内，新增的意向客户中，各咨询中心产生的意向客户数占比情况。
根据需求分析表结构:
涉及表:
customer_relationship(客户意向表) (事实表)
employee(员工表) (维度表)
scrm_department(部门表) (维度表)
customer_clue(线索表) (维度表)
itcast_school(校区表) (维度表)
itcast_subject(学科表) (维度表)
customer (客户表) (维度表)表与表关系:
客户意向表.creator = 员工表.id
员工表.tdepart_id = 部门表.id
线索表.customer_relationship_id = 客户意向表.id
客户意向表.itcast_school_id = 校区表.id
客户意向表.itcast_subject_id = 学科表.id
客户意向表.customer_id = 客户表.id涉及字段:
时间维度: 客户意向表.create_date_time
线上线下: 客户意向表.origin_type --> origin_type_stat
新老维度: 线索表.clue_state --> clue_state_stat
地区维度: 客户表.area
校区维度: 客户意向表.itcast_school_id 和 校区表.name
学科维度: 客户意向表.itcast_subject_id 和 学科表.name
来源渠道: 客户意向表.origin_type
各咨询中心: 员工表.tdepart_id 和 部门表.name
指标字段: 客户意向表.customer_id
清洗字段: 线索表.deleted
需要清洗的内容: 将删除标记为true的数据删除
过滤出: 客户意向表.deleted = false需要转换的内容:
1) 日期: 客户意向表.create_date_time
需要转换为: yearinfo monthinfo dayinfo hourinfo
2) 新老维度: 线索表.clue_state
说明: 当字段的值为 'VALID_NEW_CLUES' 为新用户
暂定: 其他的值都是老用户
需要转换为一个新的字段: clue_state_stat
此字段只有二个值: 0(老) 1(新)
3) 线上线下: 客户意向表.origin_type
说明: 当字段的值为 'NETSERVICE' 或者 'PRESIGNUP' 表示为线上
暂定: 其他值都为线下
需要转换为一个新的字段: origin_type_stat
此字段只有二个值 0(线下) 1(线上)
4) 校区和学科的id转换
需要将客户意向表中, 校区id 和 学科id 如果为 0或者 null 转换为 -1
数据准备
mysql:create database scrm default character set utf8mb4 collate utf8mb4_unicode_ci;
导入数据
建模
ODS:
放置事实表即可:
customer_relationship(意向表 ) -- 本次主题的事实表
customer_clue(线索表) -- 本次主题的维度表, 下次主题的事实表
DIM层: 数据维度层
customer(客户表) --- 维度表
itcast_subject(学科表) --- 维度表
itcast_school(校区表) --- 维度表
employee(员工表) --- 维度表
scrm_department(部门表) --- 维度表
DW层: 数据仓库层
DWD
作用: 1) 清洗转换处理工作 2) 少量维度退化
需要转换内容:
1) 将create_date_time 转换为 yearinfo monthinfo dayinfo hourinfo
2) 将origin_type 转换为 origin_type_state (用于统计线上线下)
转换逻辑: origin_type的值为: NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下
3) 将clue_state 转换为 clue_state_stat (用于统计新老维度)
转换逻辑:clue_state的值为 VALID_NEW_CLUES 为新客户 其余暂定为老客户
4) 将校区和学科的 id字段, 如果为 0 或者 null 转换为 -1
eg:
customer_relationship(意向表 ) --- 事实表
时间维度: create_date_time
线上线下: origin_type --> origin_type_stat
来源渠道: origin_type
校区维度: itcast_school_id
学科维度: itcast_subject_id
指标字段: customer_id,
关联条件的字段: creator,id
表字段的组成:
customer_id, create_date_time,origin_type,itcast_school_id,itcast_subject_id,creator,id
deleted,origin_type_stat,yearinfo monthinfo dayinfo hourinfo
DWM
表字段:eg
customer_id,
create_date_time, yearinfo monthinfo dayinfo hourinfo
deleted (意义不大)
clue_state_stat(此字段需要转换)
origin_type_stat
area,
itcast_subject_id,itcast_subject_name
itcast_school_id,itcast_school_name
origin_type
tdepart_id,tdepart_name
DWS
表字段:eg:
customerid_total,
yearinfo,monthinfo,dayinfo,hourinfo
clue_state_stat,
origin_type_stat,
area
itcast_subject_id,itcast_subject_name
itcast_school_id,itcast_school_name
origin_type
tdepart_id,tdepart_name
group_type
time_type
time_str
DA:此时不做
2.分桶表优化方案
什么是分桶表?
主要是用于分文件的, 在建表的时候, 指定按照那些字段执行分桶操作, 并可以设置需要分多少个桶, 当插入数据的时候, 执行MR的分区的操作, 将数据分散各个分区(hive分桶)中, 默认分发方案: hash 取模
如何向分桶表添加数据?
标准格式:
1) 创建一张与分桶表一样的临时表,唯一区别这个表不是一个分桶表
2) 将数据加载到这个临时表中
3) 通过 insert into + select 语句将数据导入到分桶表中
分桶表有什么作用呢?
1) 进行数据采样
案例1: 数据质量校验工作(一般会先判断各个字段数据的结构信息是否完整)
案例2: 在进行数据分析的时候, 一天需要编写N多条SQL, 但是每编写一条SQL后, 都需要对SQL做一个校验, 如果直接面对完整的数据集做校验, 会导致校验时间过长, 影响开发进度, 此时可以先采样出一部分数据
案例3: 在计算一些比率值,或者 在计算相对指标的时候, 也会基于采样数据来计算相对指标
比如: 计算当前月的销售额相对上个月 环比增长了百分之多少?
可以选择当前月和上个月抽取出百分之30的数据, 基于这个数据来计算
2) 提升查询的效率(单表|多表)
数据采样
分桶表采样函数: tablesample(bucket x out of y on column)
描述:
x: 从第几个桶开始进行采样
y: 抽样比例(总桶数/y=分多少个桶)
column: 分桶的字段, 可以省略的注意:
x 不能大于 y
y 必须是表的分桶数量的倍数或者因子
放置位置: 紧紧放置表的后面 如果表有别名 必须放置别名的前面
提升查询的效率
* 小表和大表:
* 采用 map join的方案
在进行join的时候, 将小表的数据放置到每一个读取大表的mapTask的内存中, 让mapTask每读取一次大表的数据都和内存中小表的数据进行join操作, 将join上的结果输出到reduce端即可, 从而实现在map端完成join的操作```sql
如何开启map Join
set hive.auto.convert.join=true; -- 是否开启map Join
set hive.auto.convert.join.noconditionaltask.size=512000000; -- 设置小表最大的阈值(设置block cache 缓存大小)
map Join 不限制任何表
* 中型表和大表:
* 中型表: 与小表相比 大约是小表3~10倍左右
* 解决方案:
* 1) 能提前过滤就提前过滤掉(一旦提前过滤后, 会导致中型表的数据量会下降, 有可能达到小表阈值)
2) 如果join的字段值有大量的null, 可以尝试添加随机数(保证各个reduce接收数据量差不多的, 减少数据倾斜问题)
3) 基于分桶表的: bucket map joinbucket map join的生效条件:
1） set hive.optimize.bucketmapjoin = true; --开启bucket map join 支持
2） 一个表的bucket数是另一个表bucket数的整数倍
3） bucket列 == join列
4） 必须是应用在map join的场景中
注意：如果表不是bucket的，则只是做普通join。