知识点01:课程回顾
-
项目中有哪些主题域?
-
服务域:工单主题、安装主题
- 客户域:客户事主题
- 仓储域:物料主题
- 运营域:工时主题
- 市场域:工单主题
-
项目中有哪些核心维度?
- 时间维度
- 地区维度
- 油站维度
- 服务站点维度
- 组织机构维度
- 物流维度
- 仓库维度
- ……
-
行政地区维度中有哪些核心字段?
省份id 省份名称 城市id 城市名称 县区id 县区名称 乡镇id 乡镇名称
-
时间维度中有哪些核心字段?
年 季度 月 周 日 年的第几天 周的第几天 工作日 节假日 周日
- 每一年提前将下一年的时间维度信息生成,增量放入数据仓库中
-
服务网点维度中有哪些核心字段?
服务网点id 编码 名称 省份 城市 县区 组织机构id 组织机构名称
-
油站维度中有哪些核心字段?
油站id 油站编码 油站名称 省份 城市 县区 乡镇 客户id 客户名称 公司id 公司名称
-
组织机构维度中有哪些核心字段?
工程师id 工程师名称 岗位id 岗位名称 部门id 部门名称
-
集中问题
- DG连接不上:YARN的进程故障,导致ThriftServer无法运行
- Hadoop:NameNode、DataNode、ResourceManager、NodeManager
- Hive:Metastore、Hiveserver2
- Spark:ThriftServer
- 异常:ProtocolBuffer 不匹配:dim_date
- 数据文件与表的定义是不匹配的
- step1:检查建表语法
- step2:文件:上传时候文件是不对的
- 语法 + 函数 + 数据关系
知识点02:课程目标
-
整体目标:构建数仓中的DWB:主题事务事实表
-
核心的主题事实的构建:SQL实现 + 主题的指标
-
原始事务事实数据【DWD】:订单数据
o001 userid1 2021-01-01 200.00
-
主题事务事实数据【DWB】:订单主题
o001 userid1 2021-01-01 订单总金额:200 订单总个数:1
-
主题周期快照事实表:数据应用层【ST:维度【DWS】 + 事实指标【DWB】】
2021-01-01 订单总金额:xxxx 订单总个数:xxxx
-
重点内容:SQL以及数据关系
知识点03:分层回顾
-
ODS层 :原始数据层:101张表:AVRO
-
DWD层:明细数据层:101张表:ORC
-
DWS层:维度数据层:维度表
-
DWB层:轻度汇总层:Join + 构建基础指标
-
小结
知识点04:DWB层的设计
-
目标:掌握DWB层的设计
-
路径
- step1:功能
- step2:来源
- step3:需求
-
实施
-
功能:存储每个事实主题需要的事务事实数据以及轻度聚合的结果,供ST层基于DWS层进行统计聚合得到最终每个主题的指标
- 关联:将事实主题需要的字段进行关联合并到一张事实表中,构建基于主题的事实
- 聚合:对常用的基础指标基于细粒度实现轻度聚合
-
来源:对DWD层的数据进行关联或者轻度聚合
-
需求:按照一站制造的业务主题的划分需求,构建每个主题的DWB层的数据
-
小结
知识点05:事实主题指标划分
知识点06:呼叫中心事实指标需求分析
-
目标:掌握DWB层呼叫中心事实指标表的需求
-
路径
-
实施
-
目标需求:基于基础的时间、受理方式、来电类型等事实维度统计工单数量、电话数量、回访数量、投诉数量等
-
数据来源
-
ciss_service_callaccept:客服中心来电详情表
- **eos_dict_type**:字典状态类别表,记录所有需要使用字典标记的表
```sql
select * from eos_dict_type where dicttypename = '来电类型';
select * from eos_dict_type where dicttypename = '来电受理单--处理方式';
```
- **eos_dict_entry**:字典状态明细表,记录所有具体的状态或者类别信息
```sql
select * from eos_dict_entry where dicttypeid = 'BUSS_CALL_TYPE';
select * from eos_dict_entry where dicttypeid = 'BUSS_PROCESS_WAY';
```
- **ciss_service_workorder**:工单状态明细表
```sql
select callaccept_id,status from ciss_service_workorder;
-- 查看每个状态的含义
select * from eos_dict_type where dicttypename = '派工单状态';
select * from eos_dict_entry where dicttypeid = 'BUSS_WORKORDER_STATUS';
```
知识点07:呼叫中心事实指标构建
知识点08:油站事实指标需求分析
-
目标:掌握DWB层油站事实指标表的需求分析
-
路径
-
实施
-
目标需求:基于油站信息及设备数据构建油站主题事实的油站个数、停用个数、新增个数、设备个数等
- 油站数量:1个油站就是一条数据,这个值默认就为1
- 已停用油站数量:停用状态,判断油站的状态是什么状态
- 有效油站数量:使用状态,判断油站的状态是什么状态
- 当日新增油站:判断之前有没有这个油站
- 历史记录表:oil_history:记录了当前所有油站的信息
- id、name
- 今日新数据:oil_current:记录了今天所有油站的信息
- id、name
- left join
```
oil_current a left join oil_history b on a.id = b.id
where b.id is null
```
- 当日停用油站:判断当日状态
- 油站设备数量:得到这个油站的所有设备信息,按照油站id分组统计设备个数
知识点09:油站事实指标构建
-
目标:实现DWB层油站事实指标表的构建
-
实施
-
建表
-- 创建油站事实表
drop table if exists one_make_dwb.fact_oil_station;
create table if not exists one_make_dwb.fact_oil_station(
os_id string comment '油站id'
, os_name string comment '油站名称'
, os_code string comment '油站编码'
, province_id string comment '省份id'
, city_id string comment '城市id'
, county_id string comment '县id'
, status_id int comment '状态id'
, cstm_type_id int comment '客户分类id'
, os_num int comment '油站数量 默认为1'
, invalid_os_num int comment '已停用油站数量(状态为已停用为1,否则为0)'
, valid_os_num int comment '有效油站数量(状态为启用为1,否则为0)'
, current_new_os_num int comment '当日新增油站(新增油站为1,老油站为0)'
, current_invalid_os_num int comment '当日停用油站(当天停用的油站数量)'
, device_num int comment '油站设备数量'
)
comment "油站事实表"
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_oil_station';
-
抽取
insert overwrite table one_make_dwb.fact_oil_station partition(dt = '20210101')
select
oil.id os_id --油站id
, name os_name --油站名称
, code os_code --油站编码
, province province_id --油站省份
, city city_id --油站城市
, region county_id --油站区域
, status status_id --油站状态
, customer_classify cstm_type_id --客户分类id
, 1 os_num --油站数量:默认为1
, case when status = 2 then 1 else 0 end invalid_os_num --停用油站数量:1-停用,0-启用
, case when status = 1 then 1 else 0 end valid_os_num --有效油站数量:1-有效,0-无效
, current_new_os_num --当日新增油站数量,1-新增,0-老油站
, case when current_invalid_os_num is null then 0 else current_invalid_os_num end current_invalid_os_num --当日停用油站数量
, device_num --油站设备数量
--油站信息表
from one_make_dwd.ciss_base_oilstation oil
left join (
--关联历史油站表,判断是否为新增油站
select
oil.id
, case when oil.id = his.id then 0 else 1 end current_new_os_num
from one_make_dwd.ciss_base_oilstation oil
left outer join one_make_dwd.ciss_base_oilstation_history his
on oil.id = his.id where oil.dt = '20210101'
) oilnewhis on oil.id = oilnewhis.id
left join (
--关联停用油站数据,统计今日停用油站个数
select
oil.id, count(oil.id) current_invalid_os_num
from one_make_dwd.ciss_base_oilstation oil
where oil.dt = '20210101' and oil.status = 2 group by oil.id
) invalidos on oil.id = invalidos.id
left join (
--关联油站设备信息表,统计油站设备个数
select
oil.id, count(dev.id) device_num from one_make_dwd.ciss_base_oilstation oil
left join one_make_dwd.ciss_base_device_detail dev on oil.id = dev.oilstation_id
where oil.dt = '20210101'
group by oil.id
) devinfo on oil.id = devinfo.id;
-
小结
知识点10:工单事实指标需求分析
知识点11:工单事实指标构建
-
目标:实现DWB层工单事实指标表的构建
-
实施
-
建表
drop table if exists one_make_dwb.fact_worker_order;
create table if not exists one_make_dwb.fact_worker_order(
wo_id string comment '工单id'
, callaccept_id string comment '来电受理单id'
, oil_station_id string comment '油站id'
, userids string comment '服务该工单用户id(注意:可能会有多个,以逗号分隔)'
, wo_num bigint comment '工单单据数量'
, back_num bigint comment '退回工单数量,默认为0'
, abolished_num bigint comment '已作废工单数量'
, wait_dispatch_num bigint comment '待派工数量'
, wait_departure_num bigint comment '待出发数量'
, alread_complete_num bigint comment '已完工工单数量(已完工、已回访)'
, processing_num bigint comment '正在处理工单数量(待离站、待完工)'
, people_num int comment '工单人数数量(一个工单由多人完成)'
, service_total_duration int comment '服务总时长(按小时),(leave_time - start_time)'
, repair_service_duration int comment '报修响应时长(按小时),(start_time-submit_time)'
, customer_repair_num bigint comment '客户报修工单数量'
, charg_num bigint comment '收费工单数量'
, repair_device_num bigint comment '维修设备数量'
, install_device_num bigint comment '安装设备数据量'
, install_num bigint comment '安装单数量'
, repair_num bigint comment '维修单数量'
, remould_num bigint comment '改造单数量'
, inspection_num bigint comment '巡检单数量'
, workorder_trvl_exp decimal(20,1) comment '工单差旅费'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_worker_order'
;
-
抽取
insert overwrite table one_make_dwb.fact_worker_order partition(dt = '20210101')
select
--工单id
wo.id wo_id
--来电受理单id
, max(callaccept_id) callaccept_id
--油站id
, max(oil_station_id) oil_station_id
--工程师id
, max(case when wo.service_userids is not null then concat_ws(',', wo.service_userid, wo.service_userids) else wo.service_userid end) userids
--工单单据数量:安装单、维修单……
, count(wo.id) wo_num
--退回工单数量
, count(wob.id) back_num
--已作废工单数量
, sum(case when status = '-1' then 1 else 0 end) abolished_num
--待派发工单数量
, sum(case when status = '4' then 1 else 0 end) wait_dispatch_num
--待出发工单数量
, sum(case when status = '2' then 1 else 0 end) wait_departure_num
--已完工工单数量
, sum(case when status = '5' then 1 when status = '6' then 1 else 0 end) alread_complete_num
--处理中工单数量
, sum(case when status = '3' then 1 when status = '4' then 1 else 0 end) processing_num
--工单人数
, case when count(usr.id) = 0 then 1 else count(usr.id) end people_num
--服务总时长
, max((wo.leave_time - wo.start_time) / 3600000) service_total_duration
--报修响应时长
, max((wo.start_time - wo.submit_time) / 3600000) repair_service_duration
--客户报修工单数量
, sum(case when wo.is_customer_repairs = '2' then 1 else 0 end) customer_repairs
--收费工单数量
, sum(case when wo.is_charg = '1' then 1 else 0 end) charg_num
--维修设备数量
, max(case when sod.repair_device_num = 0 then 1 when sod.repair_device_num is null then 0 else sod.repair_device_num end) repair_device_num
--安装设备数量
, max(case when sod2.install_device_num = 0 then 1 when sod2.install_device_num is null then 0 else sod2.install_device_num end) install_device_num
--安装单数量
, sum(case when sertype.installid is not null then 1 else 0 end) install_num
--维修单数量
, sum(case when sertype.repairid is not null then 1 else 0 end) repair_num
--改造单数量
, sum(case when sertype.remouldid is not null then 1 else 0 end) remould_num
--巡检单数量
, sum(case when sertype.inspectionid is not null then 1 else 0 end) inspection_num
--工单差旅费
, max(case when ed.submoney5 is null then 0.0 else ed.submoney5 end) workorder_trvl_exp
-- 工单信息表
from one_make_dwd.ciss_service_workorder wo
--关联回退工单:回退工单个数
left join one_make_dwd.ciss_service_workorder_back wob on wo.id = wob.workorder_id
--关联工程师信息表:工程师人数
left join one_make_dwd.ciss_service_workorder_user usr on wo.id = usr.workorder_id
--关联差旅费用信息表:工单差旅费用
left join one_make_dwd.ciss_service_trvl_exp_dtl ed on wo.id = ed.work_order_id
--关联维修设备个数信息
left join (
--统计每个工单的维修设备个数
select
so.workorder_id, count(sod.id) repair_device_num
--服务单表关联设备表:每个工单对应的设备id
from one_make_dwd.ciss_service_order so
left join one_make_dwd.ciss_service_order_device sod
on so.id = sod.service_order_id
where so.type = '2' and so.dt='20210101'
group by so.workorder_id
) sod on wo.id = sod.workorder_id
--关联安装设备个数信息
left join (
--统计每个工单的安装设备个数
select
so.workorder_id, count(sod.id) install_device_num
from one_make_dwd.ciss_service_order so
left join one_make_dwd.ciss_service_order_device sod
on so.id = sod.service_order_id
--过滤服务单的类型为安装类型的服务单
where so.type = '1' and so.dt='20210101'
group by so.workorder_id
) sod2 on wo.id = sod2.workorder_id
--工单类型合并表:安装、维修、改造、巡检单id
left join (
select
so.id, so.workorder_id, install.id installid, repair.id repairid, remould.id remouldid, inspection.id inspectionid
from one_make_dwd.ciss_service_order so
left join one_make_dwd.ciss_service_install install on so.id = install.service_id
left join one_make_dwd.ciss_service_repair repair on so.id = repair.service_id
left join one_make_dwd.ciss_service_remould remould on so.id = remould.service_id
left join one_make_dwd.ciss_service_inspection inspection on so.id = inspection.service_id
where so.dt = '20210101'
) sertype on wo.id = sertype.workorder_id
where wo.dt='20210101'
group by wo.id
;
-
小结
知识点12:安装事实指标需求分析
-
目标:掌握DWB层安装事实指标表的需求分析
-
路径
-
实施
-
目标需求:基于设备安装信息统计安装设备个数、收费安装个数、审核安装个数等指标
- 全新安装数量:install_type = 1
- 联调安装数量:install_way = 2
- 产生维修数量:is_repair = 1
- 额外收费数量:is_pay = 1
- 安装设备数量:与服务单关联,统计设备的id个数
- 安装费用:通过工单id从报销单信息中关联得到报销金额
- 审核完成工单个数:
-
数据来源
-
ciss_service_install:安装单信息表
select
id,--安装单id
code,--安装单号
install_way, --安装方式
service_id --服务单id
from ciss_service_install;
-
ciss_service_workorder:工单详情事实表
select
service_userid,--工程师id
service_station_id,--服务站点id
oil_station_id,--油站id
create_time --创建时间
from ciss_service_workorder;
-
ciss_service_order:服务单信息表
select
id, --服务单id
workorder_id, --工单id
type --工单类型,1-安装,2-维修,3-巡检
from ciss_service_order;
-
ciss_service_order_device:服务单设备信息表
select
id, --设备id
service_order_id --服务单id
from ciss_service_order_device;
-
ciss_s_install_exp_rep_02_dtl:报销单明细表
select
id, --报销ID
workorder_id,--工单id
money5 --报销金额
from ciss_s_install_exp_rep_02_dtl;
-
ciss_service_install_validate:设备安装审核信息表
select
id, --审核ID
workorder_id,--工单id
has_validate --审核状态,1-已审核,0-未审核
from ciss_service_install_validate;
-
小结
知识点13:安装事实指标构建
-
目标:实现DWB层安装事实指标表的构建
-
实施
-
建表
-- 创建安装单事实表
drop table if exists one_make_dwb.fact_srv_install;
create table if not exists one_make_dwb.fact_srv_install(
inst_id string comment '安装单id'
, inst_code string comment '安装单编码'
, inst_type_id string comment '安装方式id'
, srv_user_id string comment '服务人员用户id'
, ss_id string comment '服务网点id'
, os_id string comment '油站id'
, date_id string comment '日期id'
, new_inst_num int comment '全新安装数量'
, debug_inst_num int comment '设备联调安装数量'
, repair_num int comment '产生维修安装单数量'
, ext_exp_num int comment '额外收费安装单数量'
, inst_device_num int comment '安装设备数量'
, exp_device_money int comment '安装费用'
, validated_inst_num int comment '审核安装单数量'
) comment '安装单事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_install';
-
抽取
insert overwrite table one_make_dwb.fact_srv_install partition(dt = '20210101')
select
sinstall.id inst_id --安装单id
, sinstall.code inst_code --安装单号
, sinstall.install_way inst_type_id --安装方式
, swo.service_userid srv_user_id --工程师id
, swo.service_station_id ss_id --服务网点id
, swo.oil_station_id os_id --油站id
, swo.create_time date_id --创建时间
, new_inst_num --全新安装数量
, debug_inst_num --设备联调安装数量
, repair_num --产生维修安装数量
, ext_exp_num --额外收费安装数量
, inst_device_num --安装设备数量
, exp_device_money --安装费用
, validated_inst_num --已审核安装单数量
--安装信息表
from one_make_dwd.ciss_service_install sinstall
--服务单表
left join one_make_dwd.ciss_service_order sorder on sinstall.service_id = sorder.id
--工单表
left join one_make_dwd.ciss_service_workorder swo on sorder.workorder_id = swo.id
--获取全新、联调、维度、收费的安装数量
left join (
select
id,
case when install_type = 1 then 1 else 0 end new_inst_num,
case when install_way = 2 then 1 else 0 end debug_inst_num,
case when is_repair = 1 then 1 else 0 end repair_num,
case when is_pay = 1 then 1 else 0 end ext_exp_num
from one_make_dwd.ciss_service_install
) installtype on sinstall.id = installtype.id
--获取每个服务单的安装设备数量
left join (
select
sorder.id, count(sodevice.id) inst_device_num
from one_make_dwd.ciss_service_order sorder
left join one_make_dwd.ciss_service_order_device sodevice on sorder.id = sodevice.service_order_id
group by sorder.id
) sodev on sorder.id = sodev.id
--获取每个工单的报销总金额
left join (
select
swo.id, sum(dtl.money5) exp_device_money
from one_make_dwd.ciss_service_workorder swo
left join one_make_dwd.ciss_s_install_exp_rep_02_dtl dtl on swo.id = dtl.workorder_id
where dtl.dt = '20210101' and dtl.money5 is not null
group by swo.id
) dtl on swo.id = dtl.id
--获取每个安装工单的审核状态
left join (
select
swo.id, case when ivalida.has_validate = 1 then 1 else 0 end validated_inst_num
from one_make_dwd.ciss_service_workorder swo
left join one_make_dwd.ciss_service_install_validate ivalida on swo.id = ivalida.workorder_id
) validate on swo.id = validate.id where swo.service_userid is not null and sinstall.dt = '20210101';
-
小结
知识点14:维修事实指标需求分析
-
目标:掌握DWB层维修事实指标表的构建需求分析
-
路径
-
实施
-
目标需求:基于维修信息数据统计维修设备个数、维修、更换、升级配件数量、工时费用、配件费用等指标
-
数据来源
-
ciss_service_repair:维修信息表
select
id,--维修单id
code,--维修单号
service_id,--服务单id
is_pay,--是否收费 1-收费,0-免费
hour_charge,--工时费用
parts_charge,--配件费用
fares_charge --车船费用
from ciss_service_repair;
-
ciss_service_order:服务单信息表
select
id, --服务单id
workorder_id, --工单id
type --工单类型,1-安装,2-维修,3-巡检
from ciss_service_order;
-
ciss_service_workorder:工单详情事实表
select
id,--工单id
service_userid,--工程师id
service_station_id,--服务站点id
oil_station_id,--油站id
create_time --创建时间
from ciss_service_workorder;
-
ciss_service_order_device:服务单设备信息表
select
id, --设备id
service_order_id --服务单id
from ciss_service_order_device;
-
ciss_service_fault_dtl:设备故障信息表
select
serviceorder_device_id,--服务单设备id
solution_id,--解决方案id,1-维修,2-更换,3-升级
fault_type_id --故障分类id
from ciss_service_fault_dtl;
-
小结
知识点15:维修事实指标构建
-
目标:实现DWB层维修事实指标表的构建
-
实施
-
建表
drop table if exists one_make_dwb.fact_srv_repair;
create table if not exists one_make_dwb.fact_srv_repair(
rpr_id string comment '维修单id'
, rpr_code string comment '维修单编码'
, srv_user_id string comment '服务人员用户id'
, ss_id string comment '服务网点id'
, os_id string comment '油站id'
, date_id string comment '日期id'
, exp_rpr_num string comment '收费维修数量'
, hour_money int comment '工时费用'
, parts_money int comment '配件费用'
, fars_money int comment '车船费用'
, rpr_device_num int comment '维修设备数量'
, rpr_mtrl_num int comment '维修配件数量'
, exchg_parts_num int comment '更换配件数量'
, upgrade_parts_num int comment '升级配件数量'
, fault_type_ids string comment '故障类型id集合'
) comment '维修单事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_repair';
-
抽取
insert overwrite table one_make_dwb.fact_srv_repair partition(dt = '20210101')
select
repair.id rpr_id --维修单id
, repair.code rpr_code --维修单号
, swo.service_userid srv_user_id --工程师id
, swo.service_station_id ss_id --服务网点id
, swo.oil_station_id os_id --油站id
, swo.create_time date_id --创建时间
, case when repair.is_pay = 1 then 1 else 0 end exp_rpr_num --收费维修数量
, repair.hour_charge hour_money --工时费用
, repair.parts_charge parts_money --配件费用
, repair.fares_charge fars_money --车船费用
, rpr_device_num --维修设备数量
, rpr_mtrl_num --维修配件数量
, exchg_parts_num --更换配件数量
, upgrade_parts_num --升级配件数量
, fault_type_ids --故障类型id集合
--维修信息表
from one_make_dwd.ciss_service_repair repair
--服务单信息表
left join one_make_dwd.ciss_service_order sorder on repair.service_id = sorder.id
--工单信息表
left join one_make_dwd.ciss_service_workorder swo on sorder.workorder_id = swo.id
--获取维修设备数量
left join (
select
rep.id, count(rep.id) rpr_device_num
from one_make_dwd.ciss_service_repair rep
left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
group by rep.id
) repairdvc on repair.id = repairdvc.id
--获取维修、更换、升级配件数量
left join (
select
rep.id,
sum(case when sfd.solution_id = 1 then 1 else 0 end) rpr_mtrl_num,
sum(case when sfd.solution_id = 2 then 1 else 0 end) exchg_parts_num,
sum(case when sfd.solution_id = 3 then 1 else 0 end) upgrade_parts_num
from one_make_dwd.ciss_service_repair rep
left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
group by dev.id,rep.id
) dvcnum on repair.id = dvcnum.id
--获取故障类型ID
left join (
select
rep.id, concat_ws(',', collect_set(sfd.fault_type_id)) fault_type_ids
from one_make_dwd.ciss_service_repair rep
left join one_make_dwd.ciss_service_order sod on rep.service_id = sod.id
left join one_make_dwd.ciss_service_order_device dev on sod.id = dev.service_order_id
left join one_make_dwd.ciss_service_fault_dtl sfd on dev.id = sfd.serviceorder_device_id
where sfd.fault_type_id is not null
group by rep.id
) faulttype on repair.id = faulttype.id
where repair.dt = '20210101'
;
-
小结
知识点16:客户回访事实指标需求分析
-
目标:掌握DWB层客户回访事实指标表的需求分析
-
路径
-
实施
-
目标需求:基于客户回访数据统计工单满意数量、不满意数量、返修数量等指标
-
数据来源
-
ciss_service_return_visit:回访信息表
select
id,--回访id
code,--回访编号
workorder_id,--工单id
create_userid, --回访人员id
service_attitude,--服务态度
response_speed,--响应速度
repair_level,--服务维修水平
is_repair --是否返修
from ciss_service_return_visit;
-
ciss_service_workorder:服务工单信息表
select
id,--工单id
service_userid,--工程师id
service_station_id,--服务站点id
oil_station_id --油站id
from ciss_service_workorder;
-
小结
知识点17:客户回访事实指标
-
目标:实现DWB层客户回访事实指标表的构建
-
实施
-
建表
-- 创建客户回访实时表
drop table if exists one_make_dwb.fact_srv_rtn_visit;
create table if not exists one_make_dwb.fact_srv_rtn_visit(
vst_id string comment '回访id'
, vst_code string comment '回访编号'
, wrkodr_id string comment '工单id'
, srv_user_id string comment '服务人员用户id'
, os_id string comment '油站id'
, ss_id string comment '服务网点id'
, vst_user_id string comment '回访人员id'
, satisfied_num int comment '满意数量'
, unsatisfied_num int comment '不满意数量'
, srv_atu_num int comment '服务态度满意数量'
, srv_bad_atu_num int comment '服务态度不满意数量'
, srv_rpr_prof_num int comment '服务维修水平满意数量'
, srv_rpr_unprof_num int comment '服务维修水平不满意数量'
, srv_high_res_num int comment '服务响应速度满意数量'
, srv_low_res_num int comment '服务响应速度不满意数量'
, rtn_rpr_num int comment '返修数量'
) comment '客户回访事实表'
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_rtn_visit';
-
抽取
insert overwrite table one_make_dwb.fact_srv_rtn_visit partition(dt = '20210101')
select
visit.id vst_id --回访id
, visit.code vst_code --回访编号
, visit.workorder_id wrkodr_id --工单id
, swo.service_userid srv_user_id --工程师id
, swo.oil_station_id os_id --油站id
, swo.service_station_id ss_id --服务网点id
, visit.create_userid vst_user_id --回访人员id
, satisfied_num --满意数量
, unsatisfied_num --不满意数量
, srv_atu_num --服务态度满意数量
, srv_bad_atu_num --服务态度不满意数量
, srv_rpr_prof_num --服务水平满意数量
, srv_rpr_unprof_num --服务水平不满意数量
, srv_high_res_num --服务响应速度满意数量
, srv_low_res_num --服务响应速度不满意数量
, rtn_rpr_num --返修数量
--回访信息表
from one_make_dwd.ciss_service_return_visit visit
--工单信息表
left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id
--获取满意与不满意个数
left join (
select visit.workorder_id,
sum(case when visit.service_attitude = 1 and visit.response_speed = 1 and visit.repair_level = 1 then 1 else 0 end) satisfied_num,
sum(case when visit.service_attitude = 0 then 1 when visit.response_speed = 0 then 1 when visit.repair_level = 0 then 1 when visit.yawp_problem_type = 0 then 1 else 0 end) unsatisfied_num,
sum(case when visit.service_attitude = 1 then 1 else 0 end) srv_atu_num,
sum(case when visit.service_attitude = 0 then 1 else 0 end) srv_bad_atu_num,
sum(case when visit.repair_level = 1 then 1 else 0 end) srv_rpr_prof_num,
sum(case when visit.repair_level = 0 then 1 else 0 end) srv_rpr_unprof_num,
sum(case when visit.response_speed = 1 then 1 else 0 end) srv_high_res_num,
sum(case when visit.response_speed = 0 then 1 else 0 end) srv_low_res_num,
sum(case when visit.is_repair = 1 then 1 else 0 end) rtn_rpr_num
from one_make_dwd.ciss_service_return_visit visit
left join one_make_dwd.ciss_service_workorder swo on visit.workorder_id = swo.id
where visit.dt = '20210101'
group by visit.workorder_id
) vstswo on visit.workorder_id = vstswo.workorder_id
where visit.dt = '20210101'
;
-
小结
知识点18:费用事实指标分析及实现
-
目标:实现DWB层费用报销事实指标表的构建
-
路径
- step1:目标需求
- step2:数据来源
- step3:目标实现
-
实施
-
数据来源
-
ciss_service_expense_report:费用信息表
select
id,--报销单id
create_user_id,--创建人id
submoney5, --报销金额
create_org_id --创建部门id
from ciss_service_expense_report;
-
ciss_base_servicestation:服务网点信息表
select
id,--服务网点id
org_id --部门id
from ciss_base_servicestation;
-
ciss_service_exp_report_dtl:费用明细表
select
exp_report_id,--报销单id
submoney5,--项目报销实际金额
item_id --费用项目id
from ciss_service_exp_report_dtl;
-
tmp_dict:数据字典表
select
dictid, --项目id
dictname --项目名称
from one_make_dwb.tmp_dict where dicttypename = '费用报销项目';
-
目标实现
-
建表
drop table if exists one_make_dwb.fact_regular_exp;
create table if not exists one_make_dwb.fact_regular_exp(
exp_id string comment '费用报销id'
, ss_id string comment '服务网点id'
, srv_user_id string comment '服务人员id'
, actual_exp_money decimal(20,1) comment '费用实际报销金额'
, exp_item string comment '费用项目id'
, exp_item_name string comment '费用项目名称'
, exp_item_money decimal(20,1) comment '费用项目实际金额'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_regular_exp';
-
抽取
insert overwrite table one_make_dwb.fact_regular_exp partition(dt = '20210101')
select
/*+repartitions(1) */
exp.id as exp_id --费用报销id
, ss.id as ss_id --服务网点id
, exp.create_user_id as srv_user_id --创建人id
, exp.submoney5 as actual_exp_money --实际报销金额
, dict.dictid as exp_item --费用项目id
, dict.dictname as exp_item_name --费用项目名称
, exp_dtl.submoney5 as exp_item_money --费用项目金额
from
--费用信息表
(
select
*
from one_make_dwd.ciss_service_expense_report
where dt = '20210101' and status = 9 --只取制证会计已审状态
) exp
--服务网点信息表
left join one_make_dwd.ciss_base_servicestation ss
on ss.dt = '20210101' and ss.org_id = exp.create_org_id
--报销明细表
left join one_make_dwd.ciss_service_exp_report_dtl exp_dtl
on exp_dtl.dt = '20210101' and exp.id = exp_dtl.exp_report_id
--数据字典表
left join one_make_dwb.tmp_dict dict
on dict.dicttypename = '费用报销项目' and dict.dictid = exp_dtl.item_id
;
-
小结
知识点19:差旅事实指标分析及实现
-
目标:实现DWB层差旅报销事实指标表的构建
-
路径
- step1:目标需求
- step2:数据来源
- step3:目标实现
-
实施
-
目标需求:基于差率报销信息统计交通费用、住宿费用、油费金额等报销费用指标
-
数据来源
-
ciss_service_trvl_exp_sum:差旅报销汇总信息表
select
id,--汇总报销单id
user_id,--报销人id【工程师id】
status,--汇总单状态:15表示审核通过
submoney5 --应收报销总金额
from ciss_service_trvl_exp_sum;
-
ciss_s_exp_report_wo_payment:汇总报销单与工单费用单对照表
select
exp_report_id,--汇总报销单id
workorder_travel_exp_id --工单费用单id
from ciss_s_exp_report_wo_payment;
-
ciss_service_travel_expense:差旅报销单信息表
select
id,--差旅报销单id
work_order_id --工单id
from ciss_service_travel_expense;
-
ciss_service_workorder:工单信息表
select
id,--工单id
service_station_id --服务网点id
from ciss_service_workorder;
-
ciss_service_trvl_exp_dtl:差旅费用明细表
select
travel_expense_id,--费用单id
item,--费用项目名称
submoney5 --费用金额
from ciss_service_trvl_exp_dtl;
-
目标实现
-
建表
drop table if exists one_make_dwb.fact_trvl_exp;
create table if not exists one_make_dwb.fact_trvl_exp(
trvl_exp_id string comment '差旅报销单id'
, ss_id string comment '服务网点id'
, srv_user_id string comment '服务人员id'
, biz_trip_money decimal(20,1) comment '外出差旅费用金额总计'
, in_city_traffic_money decimal(20,1) comment '市内交通费用金额总计'
, hotel_money decimal(20,1) comment '住宿费费用金额总计'
, fars_money decimal(20,1) comment '车船费用金额总计'
, subsidy_money decimal(20,1) comment '补助费用金额总计'
, road_toll_money decimal(20,1) comment '过桥过路费用金额总计'
, oil_money decimal(20,1) comment '油费金额总计'
, secondary_money decimal(20,1) comment '二单补助费用总计'
, third_money decimal(20,1) comment '三单补助费用总计'
, actual_total_money decimal(20,1) comment '费用报销总计'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_trvl_exp';
-
抽取
insert overwrite table one_make_dwb.fact_trvl_exp partition(dt = '20210101')
select
--差旅费汇总单id
exp_sum.id as trvl_exp_id
--服务网点id
, wrk_odr.service_station_id as ss_id
--服务人员id
, exp_sum.user_id as srv_user_id
--外出差旅费用金额总计
, sum(case when trvl_dtl_sum.item = 1 then trvl_dtl_sum.item_money else 0 end) as biz_trip_money
--市内交通费用金额总计
, sum(case when trvl_dtl_sum.item = 2 then trvl_dtl_sum.item_money else 0 end) as in_city_traffic_money
--住宿费费用金额总计
, sum(case when trvl_dtl_sum.item = 3 then trvl_dtl_sum.item_money else 0 end) as hotel_money
--车船费用金额总计
, sum(case when trvl_dtl_sum.item = 4 then trvl_dtl_sum.item_money else 0 end) as fars_money
--补助费用金额总计
, sum(case when trvl_dtl_sum.item = 5 then trvl_dtl_sum.item_money else 0 end) as subsidy_money
--过桥过路费用金额总计
, sum(case when trvl_dtl_sum.item = 6 then trvl_dtl_sum.item_money else 0 end) as road_toll_money
--油费金额总计
, sum(case when trvl_dtl_sum.item = 7 then trvl_dtl_sum.item_money else 0 end) as oil_money
--二单补助费用总计
, sum(case when trvl_dtl_sum.item = 8 then trvl_dtl_sum.item_money else 0 end) as secondary_money
--三单补助费用总计
, sum(case when trvl_dtl_sum.item = 9 then trvl_dtl_sum.item_money else 0 end) as third_money
--费用报销总计
, max(exp_sum.submoney5) as actual_total_money
--差旅报销汇总单
from one_make_dwd.ciss_service_trvl_exp_sum exp_sum
--汇总报销单与工单费用单对照表
inner join one_make_dwd.ciss_s_exp_report_wo_payment r on exp_sum.dt = '20210101' and r.dt = '20210101' and exp_sum.id = r.exp_report_id and exp_sum.status = 15
--差旅报销单信息表
inner join one_make_dwd.ciss_service_travel_expense exp on exp.dt = '20210101' and exp.id = r.workorder_travel_exp_id
--工单信息表
inner join one_make_dwd.ciss_service_workorder wrk_odr on wrk_odr.dt = '20210101' and wrk_odr.id = exp.work_order_id
--获取每种费用项目总金额
inner join (
select
travel_expense_id, item, sum(submoney5) as item_money
from one_make_dwd.ciss_service_trvl_exp_dtl
where dt = '20210101'
group by travel_expense_id, item
) as trvl_dtl_sum
on trvl_dtl_sum.travel_expense_id = exp.id
group by exp_sum.id, wrk_odr.service_station_id, exp_sum.user_id
;
-
小结
知识点20:网点物料事实指标分析及实现
-
目标:实现DWB层网点物料事实指标表的构建
-
路径
- step1:目标需求
- step2:数据来源
- step3:目标实现
-
实施
-
目标需求:基于物料申请单的信息统计物料申请数量、物料申请金额等指标
-
数据来源
-
ciss_material_wdwl_sqd:物料申请信息表
select
id,--申请单id
code,--申请单编号
service_station_code,--网点编号
logistics_type,--物流公司类型
logistics_company,--物流公司名称
warehouse_code --仓库id
from ciss_material_wdwl_sqd;
-
ciss_base_servicestation:服务网点信息表
select
id,--服务网点id
code --服务网点编号
from ciss_base_servicestation;
-
ciss_material_wdwl_sqd_dtl:物料申请明细表
select
wdwl_sqd_id,--申请单id
application_reason,--申请理由
count_approve,--审核数量
price,--单价
count --个数
from ciss_material_wdwl_sqd_dtl;
-
目标实现
-
建表
create table if not exists one_make_dwb.fact_srv_stn_ma(
ma_id string comment '申请单id'
, ma_code string comment '申请单编码'
, ss_id string comment '服务网点id'
, logi_id string comment '物流类型id'
, logi_cmp_id string comment '物流公司id'
, warehouse_id string comment '仓库id'
, total_m_num decimal(10,0) comment '申请物料总数量'
, total_m_money decimal(10,1) comment '申请物料总金额'
, ma_form_num decimal(10,0) comment '申请单数量'
, inst_m_num decimal(10,0) comment '安装申请物料数量'
, inst_m_money decimal(10,1) comment '安装申请物料金额'
, bn_m_num decimal(10,0) comment '保内申请物料数量'
, bn_m_money decimal(10,1) comment '保内申请物料金额'
, rmd_m_num decimal(10,0) comment '改造申请物料数量'
, rmd_m_money decimal(10,1) comment '改造申请物料金额'
, rpr_m_num decimal(10,0) comment '维修申请物料数量'
, rpr_m_money decimal(10,1) comment '维修申请物料金额'
, sales_m_num decimal(10,0) comment '销售申请物料数量'
, sales_m_money decimal(10,1) comment '销售申请物料金额'
, insp_m_num decimal(10,0) comment '巡检申请物料数量'
, insp_m_money decimal(10,1) comment '巡检申请物料金额'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_stn_ma';
-
抽取
insert overwrite table one_make_dwb.fact_srv_stn_ma partition(dt = '20210101')
select
/*+repartition(1) */
ma.id as ma_id, --物料申请单id
ma.code as ma_code, --申请单编号
stn.id as ss_id, --服务网点id
ma.logistics_type as logi_id, --物流类型id
ma.logistics_company as logi_cmp_id, --物流公司id
ma.warehouse_code as warehouse_id, --仓库id
sum(m_smry.cnt) as total_m_num , --申请物料总数量
sum(m_smry.money) as total_m_money, --申请物料总金额
count(1) as ma_form_num, --申请单数量
sum(case when m_smry.ma_rsn = 1 then m_smry.cnt else 0 end) as inst_m_num, --安装申请物料数量
sum(case when m_smry.ma_rsn = 1 then m_smry.money else 0 end) as inst_m_money, --安装申请物料金额
sum(case when m_smry.ma_rsn = 2 then m_smry.cnt else 0 end) as bn_m_num, --保内申请物料数量
sum(case when m_smry.ma_rsn = 2 then m_smry.money else 0 end) as bn_m_money, --保内申请物料金额
sum(case when m_smry.ma_rsn = 3 then m_smry.cnt else 0 end) as rmd_m_num, --改造申请物料数量
sum(case when m_smry.ma_rsn = 3 then m_smry.money else 0 end) as rmd_m_money, --改造申请物料金额
sum(case when m_smry.ma_rsn = 4 then m_smry.cnt else 0 end) as rpr_m_num, --维修申请物料数量
sum(case when m_smry.ma_rsn = 4 then m_smry.money else 0 end) as rpr_m_money, --维修申请物料金额
sum(case when m_smry.ma_rsn = 5 then m_smry.cnt else 0 end) as sales_m_num, --销售申请物料数量
sum(case when m_smry.ma_rsn = 5 then m_smry.money else 0 end) as sales_m_money, --销售申请物料金额
sum(case when m_smry.ma_rsn = 6 then m_smry.cnt else 0 end) as insp_m_num, --巡检申请物料数量
sum(case when m_smry.ma_rsn = 6 then m_smry.money else 0 end) as insp_m_money --巡检申请物料金额
--物料申请信息表:8为审核通过
from (
select *
from one_make_dwd.ciss_material_wdwl_sqd
where dt = '20210101' and status = 8
) ma
--关联站点信息表,获取站点id
left join one_make_dwd.ciss_base_servicestation stn
on stn.dt = '20210101' and ma.service_station_code = stn.code
--关联物料申请费用明细
left join (
select
dtl.wdwl_sqd_id as wdwl_sqd_id,
dtl.application_reason as ma_rsn,
sum(dtl.count_approve) as cnt,
sum(dtl.price * dtl.count) as money
from one_make_dwd.ciss_material_wdwl_sqd_dtl dtl
where dtl.dt = '20210101'
group by dtl.wdwl_sqd_id, dtl.application_reason
) m_smry on m_smry.wdwl_sqd_id = ma.id
group by ma.id, ma.code, stn.id, ma.logistics_type, ma.logistics_company, ma.warehouse_code
;
-
小结
附录一:在线教育项目回顾
01:在线教育项目需求
-
目标:掌握在线教育项目需求
-
实施
-
常规的需求:通过对数据进行数据分析处理,得到一些指标,来反映一些事实,支撑运营决策
-
行业:在线教育行业
- 产品:课程
-
需求:提高学员报名的转换率,实现可持续化的运营发展
- 需求1:分析学员从访问到报名每个环节的留存率和流失率,发现每个环节存在的问题,解决问题,提高报名率
- 访问分析
- 咨询分析
- 意向分析
- 报名分析
- 通过各个环节的分析,来发现每个环节流失原因,解决问题,实现提高每一步转化率
- 需求2:持续化发展需要构建良好的产品口碑,把控学员学习质量:通过对考试、考勤、作业做管理和把控
-
小结
-
面试:项目介绍
02:需求主题划分
-
目标:掌握在线教育中需求主题的划分
-
实施
-
数据仓库的数据管理划分
- 数据仓库【DW】:存储了整个公司所有数据
- 数据集市/主题域【DM】:按照一定的业务需求进行划分:部门、业务、需求
-
在线教育中的需求主题
- 数据仓库:业务系统数据【客服系统、CRM系统、学员管理系统】
- 数据集市/主题域
- 运营管理集市/运营域
- 销售管理集市/销售域
- 学员管理集市/用户域
- 产品管理集市/产品域
- 广告域
- ……
- 数据主题
- 来源分析主题、访问分析主题、咨询分析主题
- 销售分析主题、线索分析主题、意向分析主题、报名分析主题
- 考勤分析主题、考试分析主题、作业分析主题
- 产品访问主题、产品销售主题、产品付费主题
- 表名:层 _ 【域】 _ 主题 _ 维度表
-
小结
- 掌握在线教育中需求主题的划分
- 面试:项目中划分了哪些主题域以及有哪些主题?
03:数据来源
-
目标:掌握在线教育平台的数据来源
-
实施
-
访问分析主题、咨询分析主题
-
客服系统:客服系统数据库
- 需求:统计不同维度下的访问用户数、咨询用户数
- 指标:UV、PV、IP、Session、跳出率、二跳率
- 维度:时间、地区、来源渠道、搜索来源、来源页面
- web_chat_ems
- web_chat_text_ems
-
线索分析主题、意向分析主题、报名分析主题
-
CRM系统:营销系统数据库
- 需求:统计不同维度下意向用户个数、报名用户个数、有效线索个数
- 维度:时间、地区、来源渠道、线上线下、新老学员、校区、学科、销售部门
- customer_relationship:意向与报名信息表
- customer_clue:线索信息表
- customer:学员信息表
- itcast_school:校区信息表
- itcast_subject:学科信息表
- employee:员工信息表
- scrm_deparment:部门信息表
- itcast_clazz:报名班级信息表
-
考勤分析主题
- 数据来源:学员管理系统
- 需求:统计不同维度下学员考勤指标:出勤人数、出勤率、迟到、请假、旷课
- tbh_student_signin_record:学员打卡信息表
- student_leave_apply:学员请假信息表
- tbh_class_time_table:班级作息时间表
- course_table_upload_detail:班级排课表
- class_studying_student_count:班级总人数表
-
小结
04:数仓设计
-
目标:掌握业务分析主题中每个主题数仓的实现流程
-
实施
-
访问分析主题
- ODS:web_chat_ems、web_chat_text_ems
- DWD:将两张表进行合并,并且实现ETL
- DWS:基于不同维度统计所有访问数据的用户个数、会话个数、Ip个数
-
咨询分析主题
- ODS:web_chat_ems、web_chat_text_ems
- DWD:直接复用了访问分析的DWD
- DWS:基于不同维度统计所有咨询【msg_count > 0】数据的用户个数、会话个数、Ip个数
-
意向分析主题
- ODS:customer_relationship、customer_clue
- DIM:customer、employee、scrm_department、itcast_shcool、itcast_subject
- DWD:对customer_relationship实现ETL
- DWM:实现所有表的关联,将所有维度和事实字段放在一张表中
- DWS:实现基于不同维度的聚合得到意向人数
-
报名分析主题
- ODS:customer_relationship
- DIM:customer、employee、scrm_department、itcast_clazz
- DWD:对customer_relationship实现ETL并且过滤报名数据
- DWM:实现四张表的关联,将所有维度和事实字段放在一张表中
- DWS:基于小时维度对其他组合维度进行聚合得到指标
- APP:基于小时的结果累加得到天、月、年维度下的事实的结果
-
考勤管理主题
- ODS:tbh_student_signin_record、student_leave_apply
- DIM:tbh_class_time_table、course_table_upload_detail、class_studying_student_count
- DWD:没有
- DWM
- 学员出勤状态表:基于学员打卡信息表
- 班级出勤状态表:基于学员出勤状态表
- 班级请假信息表:基于请假信息表得到的
- 班级旷课信息表:总人数 - 出勤人数 - 请假人数
- DWS:基于天构建天+班级维度下的出勤指标:24个
- APP:基于人次进行Sum累加重新计算月、年的出勤指标
-
小结
- 掌握业务分析主题中每个主题数仓的实现流程
- 面试:分层怎么设计的?
- ODS:原始数据层:存储原始数据
- DWD:明细数据层:ETL以后的明细数据
- DWM:轻度汇总层:对主题的事务事实进行构建,关联所有事实表获取主题事实,构建一些基础指标
- DWS:汇总数据层: 构建整个主题域的事实和维度的宽表
- APP:拆分每个主题不同维度的子表
- DIM :维度数据层:所有维度表
05:技术架构
06:项目优化
-
目标:掌握Hive的常见优化
-
实施
-
属性优化
-
本地模式
hive.exec.mode.local.auto=true;
-
JVM重用
mapreduce.job.jvm.numtasks=10
-
推测执行
mapreduce.map.speculative=true
mapreduce.reduce.speculative=true
hive.mapred.reduce.tasks.speculative.execution=true
-
Fetch抓取
hive.fetch.task.conversion=more
-
并行执行
hive.exec.parallel=true
hive.exec.parallel.thread.number=16
-
压缩
hive.exec.compress.intermediate=true
hive.exec.orc.compression.strategy=COMPRESSION
mapreduce.map.output.compress=true
mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.DefaultCodec
-
矢量化查询
hive.vectorized.execution.enabled = true;
hive.vectorized.execution.reduce.enabled = true;
-
零拷贝
hive.exec.orc.zerocopy=true;
-
关联优化
hive.optimize.correlation=true;
-
CBO优化器
hive.cbo.enable=true;
hive.compute.query.using.stats=true;
hive.stats.fetch.column.stats=true;
hive.stats.fetch.partition.stats=true;
-
小文件处理
#设置Hive中底层MapReduce读取数据的输入类:将所有文件合并为一个大文件作为输入
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
#如果hive的程序,只有maptask,将MapTask产生的所有小文件进行合并
hive.merge.mapfiles=true;
hive.merge.mapredfiles=true;
hive.merge.size.per.task=256000000;
hive.merge.smallfiles.avgsize=16000000;
-
索引优化
hive.optimize.index.filter=true
-
谓词下推PPD
hive.optimize.ppd=true;
- Inner Join和Full outer Join,条件写在on后面,还是where后面,性能上面没有区别
- Left outer Join时 ,右侧的表写在on后面,左侧的表写在where后面,性能上有提高
- Right outer Join时,左侧的表写在on后面、右侧的表写在where后面,性能上有提高
- 如果SQL语句中出现不确定结果的函数,也无法实现下推
- Map Join
```properties
hive.auto.convert.join=true
hive.auto.convert.join.noconditionaltask.size=512000000
```
- Bucket Join
```properties
hive.optimize.bucketmapjoin = true;
hive.auto.convert.sortmerge.join=true;
hive.optimize.bucketmapjoin.sortedmerge = true;
hive.auto.convert.sortmerge.join.noconditionaltask=true;
```
- Task内存
```properties
mapreduce.map.java.opts=-Xmx6000m;
mapreduce.map.memory.mb=6096;
mapreduce.reduce.java.opts=-Xmx6000m;
mapreduce.reduce.memory.mb=6096;
```
- 缓冲区大小
```properties
mapreduce.task.io.sort.mb=100
```
- Spill阈值
```properties
mapreduce.map.sort.spill.percent=0.8
```
- Merge线程
```properties
mapreduce.task.io.sort.factor=10
```
- Reduce拉取并行度
```properties
mapreduce.reduce.shuffle.parallelcopies=8
mapreduce.reduce.shuffle.read.timeout=180000
```
-
SQL优化
-
核心思想:先过滤后处理
- where和having使用
- join中on和where使用
- 将大表过滤成为小表再join
-
设计优化
-
分区表:减少了MapReduce输入,避免不需要的过滤
-
分桶表:减少了比较次数,实现数据分类,大数据拆分,构建Map Join
-
文件存储:优先选用列式存储:parquet、orc
-
小结
- 熟练掌握Hive中的优化
- 面试:项目中做了哪些优化?Hive做了哪些优化?
07:项目问题
- 堆内存不足:给Task进程分配更多的内存
```
mapreduce.map.java.opts=-Xmx6000m;
mapreduce.map.memory.mb=6096;
mapreduce.reduce.java.opts=-Xmx6000m;
mapreduce.reduce.memory.mb=6096;
```
- 物理内存不足
- 允许NodeManager使用更多的内存
- 硬件资源可以扩充:扩充物理内存
- 调整代码:基于分区处理、避免Map Join
- 虚拟内存不足:调整虚拟内存的比例,默认为2.1
-
数据倾斜问题:程序运行时间长,一直卡在99%或者100%
- **现象**
- 运行一个程序,这个程序的某一个Task一直在运行,其他的Task都运行结束了,进度卡在99%或者100%
- **基本原因**
- 基本原因:这个ReduceTask的负载要比其他Task的负载要高
- ReduceTask的数据分配不均衡
- **根本原因**:分区的规则
- 默认分区:根据K2的Hash值取余reduce的个数
- 优点:相同的K2会由同一个reduce处理
- 缺点:可能导致数据倾斜
- **数据倾斜的场景**
- group by / count(distinct)
- join
- **解决方案**
- group by / count(distinct)
- 开启Combiner
```
hive.map.aggr=true
```
- 随机分区
- 方式一:开启参数
```
hive.groupby.skewindata=true
```
- 开启这个参数以后,底层会自动走两个MapReduce
- 第一个MapReduce自动实现随机分区
- 第二个MapReduce做最终的聚合
- 方式二:手动指定
```
distribute by rand():将数据写入随机的分区中
```
```
distribute by 1 :将数据都写入一个分区
```
- join
- 方案一:尽量避免走Reduce Join
- Map Join:尽量将不需要参加Join的数据过滤,将大表转换为小表
- 构建分桶Bucket Map Join
- 方案二:skewjoin:避免数据倾斜的Reduce Join过程
```sql
--开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
--如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
--在编译时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
--不合并,提升性能
set hive.optimize.union.remove=true;
--如果Hive的底层走的是MapReduce,必须开启这个属性,才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
```
-
小结
- 掌握Hive中常见的内存溢出及数据倾斜问题
- 面试:数据倾斜怎么解决?
- 调大分区个数:重分区
- Join时候, 可以将小的数据实现广播
- 自定义分区规则:RDD五大特性:对于二元组类型的RDD,可以指定分区器
- reduceByKey(partitionClass = HashPartition)
-
技术面试:理论为主
附录二:一站制造项目回顾
01:项目需求
-
目标:掌握项目业务需求
- 这个项目属于哪个行业?
- 为什么要做这个项目?
- 这个项目的目的是什么?
-
实施
-
小结
02:业务流程
-
目标:掌握加油站设备维护的主要业务流程
-
实施
- step1:加油站服务商联系呼叫中心,申请服务:安装/巡检/维修/改造加油机
- step2:呼叫中心联系对应服务站点,分派工单:联系站点主管,站点主管分配服务人员
- step3:服务人员确认工单和加油站点信息
- step4:服务人员在指定日期到达加油站,进行设备检修
- step5:如果为安装或者巡检服务,安装或者巡检成功,则服务完成
- step6:如果为维修或者改造服务,需要向服务站点申请物料,物料到达,实施结束,则服务完成
- step7:服务完成,与加油站站点服务商确认服务结束,完成订单核验
- step8:工程师报销过程中产生的费用
- step9:呼叫中心会定期对该工单中的工程师的服务做回访
-
小结
- 掌握加油站设备维护的主要业务流程
- 工单分析、费用分析、物料分析、回访分析
03:技术选型
-
目标:掌握加油站服务商数据运营平台的技术选型
-
实施
-
数据生成:业务数据库系统
- Oracle:工单数据、物料数据、服务商数据、报销数据等
- ERP:企业资源管理系统
- 呼叫中心:所有来电系统业务系统
- CISS:业务系统:客户、工单
-
数据采集
-
数据存储
-
数据计算
-
SparkSQL:类HiveSQL开发方式【面向表】
- 对数据仓库中的结构化数据做处理分析
- 场景:统计分析
- 开发方式
- DSL:使用函数【DSL函数 + RDD函数】
- SQL:使用SQL语句对表的进行处理
- 功能:离线计算 + 实时计算
- 注意:SparkSQL可以解决所有场景的分布式计算,离线计算的选型不仅仅是SparkSQL
- 使用方式
- Python/Jar:spark-submit
-
ThriftServer:SparkSQL用于接收SQL请求的服务端,类似于Hive的Hiveserver2
- PyHive :Python连接SparkSQL的服务端,提交SQL语句
- JDBC:Java连接SparkSQL的服务端,提交SQL语句
- spark-sql -f :运行SQL文件,类似于hive -f
- beeline:交互式命令行,一般用于测试
-
数据应用
- MySQL:结果存储
- Grafana:数据可视化工具
-
监控工具
-
调度工具
-
技术架构
04:分层整体设计
-
ODS:原始数据层:最接近于原始数据的层次,直接采集写入层次:原始事务事实表
-
DWD:明细数据层:对ODS层的数据根据业务需求实现ETL以后的结果:ETL以后事务事实表
-
DWB:轻度汇总层:类似于以前讲解的DWM,轻度聚合
- 关联:将主题事实的表进行关联,所有与这个主题相关的字段合并到一张表
- 聚合:基于主题的事务事实构建基础指标
- 主题事务事实表
-
ST:数据应用层:类似于以前讲解的APP,存储每个主题基于维度分析聚合的结果:周期快照事实表
-
DM:数据集市:按照不同部门的数据需求,将暂时没有实际主题需求的数据存储
-
DWS:维度数据层:类似于以前讲解的DIM:存储维度数据表
-
数据仓库设计方案
- 从上到下:在线教育:先明确需求和主题,然后基于主题的需求采集数据,处理数据
-
上下到上:一站制造:将整个公司所有数据统一化在数据仓库中存储准备,根据以后的需求,动态直接获取数据
-
小结
- 掌握油站分析项目中的分层整体设计
- ODS:原始数据层
- DWD:明细数据层
- DWB:轻度汇总层
- ST:数据应用层
- DM:数据集市层
- DWS:维度数据层
05:分层具体功能
-
目标:掌握油站分析的每层的具体功能
-
实施
-
ODS
- 数据内容:存储所有原始业务数据,基本与Oracle数据库中的业务数据保持一致
- 数据来源:使用Sqoop从Oracle中同步采集
- 存储设计:Hive分区表,avro文件格式存储,保留3个月
-
DWD
- 数据内容:存储所有业务数据的明细数据
- 数据来源:对ODS层的数据进行ETL扁平化处理得到
- 存储设计:Hive分区表,orc文件格式存储,保留所有数据
-
DWB
- 数据内容:存储所有事实与维度的基本关联、基本事实指标等数据
- 数据来源:对DWD层的数据进行清洗过滤、轻度聚合以后的数据
- 存储设计:Hive分区表,orc文件格式存储,保留所有数据
-
ST
- 数据内容:存储所有报表分析的事实数据
- 数据来源:基于DWB和DWS层,通过对不同维度的统计聚合得到所有报表事实的指标
-
DM
- 数据内容:存储不同部门所需要的不同主题的数据
- 数据来源:对DW层的数据进行聚合统计按照不同部门划分
-
DWS
- 数据内容:存储所有业务的维度数据:日期、地区、油站、呼叫中心、仓库等维度表
- 数据来源:对DWD的明细数据中抽取维度数据
- 存储设计:Hive普通表,orc文件 + Snappy压缩
- 特点:数量小、很少发生变化、全量采集
-
小结
06:业务系统结构
-
目标:了解一站制造中的业务系统结构
-
实施
-
数据来源
-
业务流程
-
油站站点联系呼叫中心,申请工单
-
工程师完成工单
-
ERP系统:企业资源管理系统,存储整个公司所有资源的信息
- 所有的工程师、物品、设备产品供应链、生产、销售、财务的信息都在ERP系统中
-
CISS系统:客户服务管理系统,存储所有用户、运营数据
-
呼叫中心系统:负责实现所有客户的需求申请、调度、回访等
-
组织结构
- 运营部(编制人数300人)
- 负责服务策略制定和实施,对服务网络运营过程管理。部门职能包括物料管理、技术支持、服务效率管理、服务质量控制、服务标准化和可视化实施等工作。承担公司基础服务管理方面具体目标责任
- 综合管理部(编制人数280人)
- 下属部门有呼叫中心、信息运维、人事行政、绩效考核与培训、企划部等部门。负责公司市场部、运营部、财务部等专业业务以外的所有职能类工作,包括行政后勤管理、劳动关系、绩效考核与培训、企划宣传、采购需求管理、信息建设及数据分析、公司整体目标和绩效管理等工作。
- 市场部(编制人数50人)
- 负责客户需求开发、服务产品开发、市场拓展与销售管理工作,执行销售策略、承担公司市场、销售方面具体目标责任。
- 财务部(编制人数10人)
- 负责服务公司财务收支、费用报销、报表统计、财务分析等财务管理工作
- 市场销售服务中心(编制人数4000人)
- 负责服务产品销售,设备的安装、维护、修理、改造等工作,严格按照公司管理标准实施日常服务工作
-
业务流程
-
小结
07:业务系统数据
-
目标:熟悉业务系统核心数据表
-
实施
-
切换查看数据库
-
查看数据表
- CISS_BASE:基础数据表
- 报销项目核算、地区信息、服务商信息、设备信息、故障分类、出差补助信息、油站基础信息等
- CISS_SERVICE、CISS_S:服务数据表
- 来电受理单信息、改派记录信息、故障更换材料明细信息、综合报销信息、服务单信息、安装单、维修单、改造单信息
- CISS_MATERIAL、CISS_M:仓储物料表
- ORG:组织机构数据
- EOS:字典信息表
-
核心数据表
- 运营分析
- 工单分析、安装分析、维修分析、巡检分析、改造分析、来电受理分析
- 提高服务质量
- 回访分析
- 运营成本核算
- 收入、支持分析
08:一站制造业务主题划分
-
目标:掌握一站制造的主题域及主题的划分
-
实施
-
来源
- 主题域划分:业务或者部门划分
- 业务:客户域、广告域、运营域……
- 部门:运维域、财务域、销售域……
- 数据需求来划分主题
- 运营域:访问分析报表、转化分析报表、用户属性分析报表、订单分析报表
-
服务域
- 安装主题:安装方式、支付费用、安装类型
- 工单主题:派工方式、工单总数、派工类型、完工总数、
- 维修主题:支付费用、零部件费用、故障类型
- 派单主题:派单数、派单平均值、派单响应时间
- 费用主题:差旅费、安装费、报销人员统计
- 回访主题:回访人员数、回访工单状态
- 油站主题:油站总数量、油站新增数量
-
客户域
-
仓储域
- 保内良品核销主题:核销数量、配件金额
- 保内不良品核销主题:核销配件数、核销配件金额
- 送修主题:送修申请、送修物料数量、送修类型
- 调拨主题:调拨状态、调拨数量、调拨设备类型
- 消耗品核销:核销总数、核销设备类型
-
服务商域
- 工单主题:派工方式、工单总数、工单类型、客户类型
- 服务商油站主题:油站数量、油站新增数量
-
运营域
- 运营主题:服务人员工时、维修站分析、平均工单、网点分布
-
市场域
-
小结
09:一站制造业务维度设计
-
目标:掌握一站制造业务维度设计
-
实施
-
日期时间维度
- 年维度、季度维度、月维度、周维度、日维度
- 日环比、周环比、月环比、日同比、周同比、月同比
- 环比:同一个周期内的比较
- 同比:上个个周期的比较
-
行政地区维度
- 地区级别:国家维度、省份维度、城市维度、县区维度、乡镇维度
-
服务网点维度
-
油站维度
- 油站类型、油站名称、油站编号、客户编号、客户名称、省份、城市、县区、油站状态、所属公司
-
组织机构维度
- 人员编号、人员名称、岗位编号、岗位名称、部门编号、部门名称
-
服务类型维度
-
设备维度
- 设备类型、设备编号、设备名称、油枪数量、泵类型、软件类型
-
故障类型维度
- 一级故障编号、一级故障名称、二级故障编号、二级故障名称
-
物流公司维度
- ……
-
小结
10:一站制造业务主题维度矩阵
编制人数280人)
- 下属部门有呼叫中心、信息运维、人事行政、绩效考核与培训、企划部等部门。负责公司市场部、运营部、财务部等专业业务以外的所有职能类工作,包括行政后勤管理、劳动关系、绩效考核与培训、企划宣传、采购需求管理、信息建设及数据分析、公司整体目标和绩效管理等工作。
- 市场部(编制人数50人)
- 负责客户需求开发、服务产品开发、市场拓展与销售管理工作,执行销售策略、承担公司市场、销售方面具体目标责任。
- 财务部(编制人数10人)
- 负责服务公司财务收支、费用报销、报表统计、财务分析等财务管理工作
- 市场销售服务中心(编制人数4000人)
- 负责服务产品销售,设备的安装、维护、修理、改造等工作,严格按照公司管理标准实施日常服务工作
07:业务系统数据
08:一站制造业务主题划分
-
目标:掌握一站制造的主题域及主题的划分
-
实施
-
来源
- 主题域划分:业务或者部门划分
- 业务:客户域、广告域、运营域……
- 部门:运维域、财务域、销售域……
- 数据需求来划分主题
- 运营域:访问分析报表、转化分析报表、用户属性分析报表、订单分析报表
-
服务域
- 安装主题:安装方式、支付费用、安装类型
- 工单主题:派工方式、工单总数、派工类型、完工总数、
- 维修主题:支付费用、零部件费用、故障类型
- 派单主题:派单数、派单平均值、派单响应时间
- 费用主题:差旅费、安装费、报销人员统计
- 回访主题:回访人员数、回访工单状态
- 油站主题:油站总数量、油站新增数量
-
客户域
-
仓储域
- 保内良品核销主题:核销数量、配件金额
- 保内不良品核销主题:核销配件数、核销配件金额
- 送修主题:送修申请、送修物料数量、送修类型
- 调拨主题:调拨状态、调拨数量、调拨设备类型
- 消耗品核销:核销总数、核销设备类型
-
服务商域
- 工单主题:派工方式、工单总数、工单类型、客户类型
- 服务商油站主题:油站数量、油站新增数量
-
运营域
- 运营主题:服务人员工时、维修站分析、平均工单、网点分布
-
市场域
-
小结
09:一站制造业务维度设计
-
目标:掌握一站制造业务维度设计
-
实施
-
日期时间维度
- 年维度、季度维度、月维度、周维度、日维度
- 日环比、周环比、月环比、日同比、周同比、月同比
- 环比:同一个周期内的比较
- 同比:上个个周期的比较
-
行政地区维度
- 地区级别:国家维度、省份维度、城市维度、县区维度、乡镇维度
-
服务网点维度
-
油站维度
- 油站类型、油站名称、油站编号、客户编号、客户名称、省份、城市、县区、油站状态、所属公司
-
组织机构维度
- 人员编号、人员名称、岗位编号、岗位名称、部门编号、部门名称
-
服务类型维度
-
设备维度
- 设备类型、设备编号、设备名称、油枪数量、泵类型、软件类型
-
故障类型维度
- 一级故障编号、一级故障名称、二级故障编号、二级故障名称
-
物流公司维度
- ……
-
小结
10:一站制造业务主题维度矩阵