4——Hive数仓项目完整流程（在线教育）

@—笨小孩—@

已于 2023-01-21 18:51:57 修改

阅读量438

点赞数

分类专栏： python大数据数仓实战文章标签： hive hadoop 数据仓库

于 2023-01-15 23:57:13 首次发布

本文链接：https://blog.youkuaiyun.com/m0_57588393/article/details/128699345

版权

在线教育(4)

在线教育（四）

在线教育(4)

1. 意向用户主题看板__全量流程

1.1 需求分析

需求一：计期内，新增意向客户（包含自己录入的意向客户）总数

涉及维度:	时间维度：年、季度、月、天、小时、		线上线下
涉及指标:	意向量
涉及表:	customer_relationship(客户意向表)
涉及字段:	create_date_time、customer_id
缺失字段: 线上线下

需求二：统计指定时间段内，新增的意向客户，所在城市区域人数热力图

涉及维度:	时间维度、地区维度、新增、线上线下
涉及指标:	意向量
涉及表:	customer_relationship(客户意向表)、customer(客户表)
	连接条件:	客户表.id = 客户意向表.customer_id
涉及字段:	create_date_time、customer_id、area

注意:区域热力图，对数据进行排序

需求三：统计指定时间段内，新增的意向客户中，意向学科人数排行榜。学科名称要关联查询出来

涉及维度:	时间维度、学科维度、新增、线上线下
涉及指标:	意向量
涉及表:	customer_relationship(客户意向表)、customer_clue(客户线索表)、itcast_subject(学科表)
	连接条件:客户意向表.itcast_subject_id = 学科表.id
			客户线索表.customer_relationship_id = 客户意向表.id
涉及字段:	
	客户线索表.create_date_time、
	客户意向表.itcast_subject_id、
	客户意向表.customer_id、
	客户意向表.origin_type('NETSERVICE','PRESIGNUP')、
	学科表.name
	客户线索表.clue_state='VALID_NEW_CLUES'(新客户新线索)
清洗操作: 
	客户线索表.deleted = false 数据保留下, 为true清洗掉

需求四：统计指定时间段内，新增的意向客户中，意向校区人数排行榜

涉及维度:	时间维度、线上线下、校区维度、线上线下
涉及指标:	意向量
涉及表:	customer_clue(客户线索表)、customer_relationship(客户意向表)、itcast_school(校区表)
	连接条件: 客户意向表.itcast_school_id=校区表.id
			 客户意向表.id = 客户线索表.customer_relationship_id
涉及字段:
		时间维度	客户线索表.create_date_time
		线上线下	客户意向表.origin_type('NETSERVICE','PRESIGNUP')(排除线下)
		校区维度	客户意向表.itcast_school_id	，校区表.name
		指标字段	客户意向表.customer_id
		客户线索表.clue_state('VALID_NEW_CLUES')新客户新线索

需求五：统计指定时间段内，新增的意向客户中，不同来源渠道的意向客户占比

涉及维度:	时间维度、来源渠道、新增
涉及指标:	意向量
涉及表:	customer_relationship(客户意向表)、customer_clue(客户线索表)
	连接条件:客户意向表.id = 客户线索表.customer_relationship_id
涉及字段:
	时间维度	客户意向表.create_date_time	
	来源渠道	客户意向表.origin_type('NETSERVICE','PRESIGNUP')(排除线下)
	新增		客户线索表.clue_state('VALID_NEW_CLUES')
	指标字段	客户意向表.customer_id
清洗操作: 
	线索表.deleted = false

需求六：统计指定时间段内，新增的意向客户中，各咨询中心产生的意向客户数占比情况

过滤条件:	新增
涉及维度:	时间维度、咨询中心、线上线下
涉及指标:	意向量
涉及表:	客户意向表、employee(员工表)、scrm_department(部门表)
	连接条件:客户意向表.creator = 员工表.id
			员工表.tdepart_id = 部门表.id
涉及字段:	时间维度	客户意向表.create_data_time
		   咨询中心		员工表.tdepart_id、部门表.name
		   新增		客户线索表.clue_state('VALID_NEW_CLUES')
		   排除线下		客户意向表.origin_type('NETSERVICE','PRESIGNUP')

1.2 需求分析总结

涉及维度:
	固有维度:	时间维度、线上线下
	产品属性维度:	地区维度、学科、校区、来源渠道、咨询中心
	过滤条件:	新增
涉及指标:	意向量
涉及表:
	事实表:	客户意向表
	维度表:	客户线索表、客户表、学科表、校区表、员工表、部门表
	连接条件:
		客户表.id = 客户意向表.customer_id
		客户意向表.itcast_subject_id = 学科表.id
		客户线索表.customer_relationship_id = 客户意向表.id
		客户意向表.itcast_school_id=校区表.id
		客户意向表.creator = 员工表.id
		员工表.tdepart_id = 部门表.id
涉及字段:
	意向量	customer_id
	时间维度	create_date_time
	线上线下	客户意向表.origin_type('NETSERVICE','PRESIGNUP')
	新增		客户线索表.clue_state('VALID_NEW_CLUES')
	地区维度	area
	学科			客户意向表.itcast_subject_id、学科表.name
	校区		客户意向表.itcast_school_id	，校区表.name
	来源渠道	客户意向表.origin_type('NETSERVICE','PRESIGNUP')
	咨询中心	员工表.tdepart_id、部门表.name
需清洗内容:
	过滤字段:客户意向表.deleted = false
需转换内容:
	日期字段:create_date_time转换为yearinfo......
	新增:客户线索表.clue_state值为VALID_NEW_CLUES为新用户
	线上线下:客户意向表.origin_type('NETSERVICE','PRESIGNUP')表示线上		转化为线下（0）、线上（1）
	校区和学科id转换:需要将客户意向表中, 校区id 和 学科id 如果为 0或者 null 转换为 -1

1.3 业务数据准备（工作环境无）

将数据导入MySQL中，首先建库之后导入数据

create database scrm default character set utf8mb4 collate utf8mb4_unicode_ci;

1.4 建模分析

ODS 层：源数据层

作用: 存储事实表和少量维度表
建表字段: 与源数据中一致即可
	注意: 分区字段为时间字段，用于标记抽取数据到 ODS层 的时间
	表: 客户意向表	外加	客户线索表
		注意: 意向表 和 线索表 存在数据变更操作，需采用缓慢渐变维方式解决

DIM 层：维度层

作用:	存储维度表
表 :  客户表、学科表、员工表、部门表、校区表
建表字段:  与表中字段一致 + 抽取时间

DW 层：数据仓库层
- DWD 层：明细层

作用: 清洗转换、少量维度退化
	清洗操作:
		过滤字段:客户意向表.deleted = false
	转换操作:
		日期字段:create_date_time转换为yearinfo......
		新增:客户线索表.clue_state值为VALID_NEW_CLUES为新用户
		线上线下:客户意向表.origin_type('NETSERVICE','PRESIGNUP')表示线上		转化为线下（0）、线上（1）
		校区和学科id转换:需要将客户意向表中, 校区id 和 学科id 如果为 0或者 null 转换为 -1
	
建表字段: 必须字段(只可为事实表字段) + 清洗字段 + 转换字段 + join字段
(因为没有做维度退化，所以只有事实表字段)(join字段只写与事实表相关的)
	customer_relationship(意向表)字段:
		时间维度:	create_date_time
		线上线下:origin_type --> origin_type_stat线下（0）、线上（1）
		新增	 :	  origin_type
		校区维度:	itcast_school_id
		学科维度:	itcast_subject_id
		来源渠道:	origin_type
		join字段:	  customer_id、id
		
最终字段:	
id	customer_id,create_date_time,origin_type,itcast_subject_id,itcast_school_id,origin_type_stat,
	creator,deleted,yearinfo,monthinfo,dayinfo,hourinfo

DWM 层：中间层

作用: 维度退化，提前聚合
因为后续的意向量指标字段存在去重操作，所以无维度退化操作
join字段:	
		客户表.id = 客户意向表.customer_id
		客户意向表.itcast_subject_id = 学科表.id
		客户线索表.customer_relationship_id = 客户意向表.id
		客户意向表.itcast_school_id=校区表.id
		客户意向表.creator = 员工表.id
		员工表.tdepart_id = 部门表.id
建表字段: 指标字段 + 各表维度相关字段
id,customer_id,create_date_time,origin_type,
area,
itcast_subject_id,itcast_subject_name,
itcast_school_id,itcast_school_name,
origin_type_stat,
tdepart_id,tdepart_name
	creator,deleted,yearinfo,monthinfo,dayinfo,hourinfo
	需要7表关联操作

DWS 层：数据业务层

作用: 细化维度统计操作
建表字段:  统计字段 + 各维度字段 + 三个用于查询的字段
字段: 
	指标字段:	customerid_total
	时间维度:	yearinfo,monthinfo,dayinfo,hourinfo
	新	老:	  clue_state_stat
	线上线下:	origin_type_stat
	来源渠道:	origin_type
	地 	区:	  area
	学科维度:	itcast_subject_id,itcast_subject_name
	校区维度:	itcast_schoool_id,itcast_school_name
	咨询中心:	tdepart_id,tdepart_name
	经验字段:	group_type,time_type,time_str

1.5 建模操作

ODS 层：

set hive.exec.orc.compression.strategy=COMPRESSION;
-- 客户意向表(内部表 分区表 分桶表, 拉链表)
create table if not exists itcast_ods.'customer_relationship'(
`id` int COMMENT '客户关系id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int COMMENT '是否被删除（禁用）',
  `customer_id` int COMMENT '所属客户id',
  `first_id` int COMMENT '第一条客户关系id',
  `belonger` int COMMENT '归属人',
  `belonger_name` STRING COMMENT '归属人姓名',
  `initial_belonger` int COMMENT '初始归属人',
  `distribution_handler` int COMMENT '分配处理人',
  `business_scrm_department_id` int COMMENT '归属部门',
  `last_visit_time` STRING COMMENT '最后回访时间',
  `next_visit_time` STRING COMMENT '下次回访时间',
  `origin_type` STRING COMMENT '数据来源',
  `itcast_school_id` int COMMENT '校区Id',
  `itcast_subject_id` int COMMENT '学科Id',
  `intention_study_type` STRING COMMENT '意向学习方式',
  `anticipat_signup_date` STRING COMMENT '预计报名时间',
  `level` STRING COMMENT '客户级别',
  `creator` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type