知识点01：回顾
-
拉链表是什么？如何构建拉链表？
- 拉链表：通过时间来标记变化数据的不同状态
- startTime：状态开始时间
- endTime：状态结束时间，最新状态：9999-12-31
- 步骤
- step1：增量采集到更新表：ODS
- step2：更新表与拉链表合并临时表：TMP
- 修改原有拉链表中的数据：数据发生更新的，并且之前是拉链表最新的状态的数据，endTime修改为当前最新的startTime-1
- step3：将最新的拉链数据覆盖到拉链表：DW
- 拉链表：通过时间来标记变化数据的不同状态
-
报名业务的需求有哪些维度与指标？
-
指标：报名人数
-
维度：时间、线上线下、来源渠道、校区、学科、部门
-
-
报名数仓的设计分为几层，每层的功能是什么？
-
ODS：存储原始事务事实表
- customer_relationship
-
DIM：维度数据层
- itcast_clazz：班级信息表
- 校区、学科
- employee和scrm_deparment
- 部门
- itcast_clazz：班级信息表
-
DWD：ETL
- step1：过滤过期的数据，过滤报名数据
- step2：维度退化，支付时间退化年、月、天、小时
-
DWM：实现了事务事实表与所有维度表的关联
- 实现4张表的关联
-
DWS：基于小时维度实现各种组合维度的聚合
-
APP：基于更大时间维度对小时维度的结果直接聚合
-
-
Hive是否支持索引？如何实现的？有什么问题？
-
支持索引机制：0.7 ~ 3.0
-
通过MapReduce将索引数据存储在一张索引表中
- 列的值与文件、在文件中的偏移量映射关系
-
问题：Hive中索引不会自动根据原表的数据进行更新，必须强制手动更新
-
-
ORC文件索引分为几种，各自的应用和特点是什么？
-
row group index：范围匹配、存储列的最大值和最小值
-
bloom filter index：等值匹配、存储列的值
-
-
Hive中小文件如何处理？有哪些特殊的属性优化？
- 小文件处理
- 避免小文件产生：将结果进行小文件合并
- 如果处理小文件：提前对小文件进行聚合再处理
- 特殊属性优化
- 矢量化查询
- 零拷贝
- 关联优化器
- 小文件处理
知识点02：目标
- 学员考勤管理
- 业务需求
- 为什么要做考勤管理的分析？
- 指标与维度分别是什么？
- 数据来源
- 有哪些表，哪些字段？
-
数据仓库设计
- 如何对原始的数据进行处理，得到可以使用的的数据
- 对转换后的数据再进行聚合
- 数据仓库实现
- 业务需求
- Hive中的优化器的选择
- RBO
- CBO
- 练习：有效线索主题
- 基于不同维度统计有效线索的个数
知识点03：学员考勤业务需求
-
目标：掌握学员考勤业务需求
-
路径
- step1：需求
- step2：指标与维度
- step3：数据来源
-
实施
-
需求
- 通过学员的考勤数据来实现基于时间或者班级校区等不同维度下的考勤指标统计
- 发现教学管理中的问题，提升学员学习质量
-
指标与维度
- 指标
- 出勤
- 出勤人数：上课的人数
- 出勤率：上课人数 / 总人数
- 迟到：迟到人数、迟到率
- 请假：请假人数、请假率
- 旷课：旷课人数、旷课率
- 出勤
- 维度
- 时间、班级、校区、学科
- 指标
-
数据来源
- 学员管理系统
- 分析需要的数据
- 打卡数据表：班级id、学员id、打卡时间
- 正常出勤和迟到出勤
- 请假数据表：班级id、学员id、请假开始时间、请假结束时间
- 班级总人数表
- 判断是否是一个有效的出勤或者请假状态：
- 今天不上课
- 打卡时间不是上课时间
- 请假没有被批准
- ……
- 打卡数据表：班级id、学员id、打卡时间
-
需求
-
小结
- 掌握学员考勤业务需求
知识点04：学员考勤管理数据
4.1 学生打卡信息表
- tbh_student_signin_record：学员打卡信息表
- class_id：班级id
- student_id：学员id
- signin_time：打卡时间
- signin_date：打卡日期
- 判断：是否是一个有效打卡，如果是有效的，出勤状态是什么？
- time_table_id：作息时间的id，关联作息时间表
- share_state：共屏状态
- 作用：构建每个学生的出勤状态：正常出勤、迟到出勤
| COLUMN_NAME | COLUMN_COMMENT |
|---|---|
| id | 主键id |
| normal_class_flag | 是否正课 1 正课 2 自习 |
| time_table_id | 作息时间id 关联 tbh_school_time_table 或者 tbh_class_time_table |
| class_id | 班级id |
| student_id | 学员id |
| signin_time | 签到时间 |
| signin_date | 签到日期 |
| inner_flag | 内外网标志 0 外网 1 内网 |
| signin_type | 签到类型 1 心跳打卡 2 老师补卡 |
| share_state | 共享屏幕状态 0 否 1是，在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 |
| inner_ip | 内网ip地址 |
4.2 班级作息时间表
- tbh_class_time_table：班级作息时间表
- 每个班级每天上午、下午、晚上的上课时间和下课时间
- 作用：判断学员的打卡是否是一个有效的打卡
| COLUMN_NAME | COLUMN_COMMENT |
|---|---|
| id | 主键id |
| class_id | 班级id |
| morning_template_id | 上午出勤模板id |
| morning_begin_time | 上午开始时间 |
| morning_end_time | 上午结束时间 |
| afternoon_template_id | 下午出勤模板id |
| afternoon_begin_time | 下午开始时间 |
| afternoon_end_time | 下午结束时间 |
| evening_template_id | 晚上出勤模板id |
| evening_begin_time | 晚上开始时间 |
| evening_end_time | 晚上结束时间 |
| use_begin_date | 使用开始日期 |
| use_end_date | 使用结束日期 |
| create_time | 创建时间 |
| create_person | 创建人 |
| remark | 备注 |
4.3 班级课表
-
course_table_upload_detail：班级排课信息表
-
class_id：班级id
-
class_date：班级上课的日期
-
content：班级上课的内容
- 如果内容为null，或者今天上课的内容是开班典礼
-
作用：用于判断当前的打卡是否是一个有效的打卡、或者一个请假是否是一个有效的请假
-
| COLUMN_NAME | COLUMN_COMMENT |
|---|---|
| id | id |
| base_id | 课程主表id |
| class_id | 班级id |
| class_date | 上课日期 |
| content | 课程内容 |
| teacher_id | 老师id |
| teacher_name | 老师名字 |
| job_number | 工号 |
| classroom_id | 教室id |
| classroom_name | 教室名称 |
| is_outline | 是否大纲 0 否 1 是 |
| class_mode | 上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播 |
| is_stage_exam | 是否阶段考试（0：否 1：是） |
| is_pay | 代课费（0：无 1：有） |
| tutor_teacher_id | 晚自习辅导老师id |
| tutor_teacher_name | 辅导老师姓名 |
| tutor_job_number | 晚自习辅导老师工号 |
| is_subsidy | 晚自习补贴（0：无 1：有） |
| answer_teacher_id | 答疑老师id |
| answer_teacher_name | 答疑老师姓名 |
| answer_job_number | 答疑老师工号 |
| remark | 备注 |
| create_time | 创建时间 |
4.4 在读学员人数信息表
- class_studying_student_count：班级学员人数表
- 记录每个班级的总人数
- class_id：班级id
- studying_student_count：班级的总人数
| COLUMN_NAME | COLUMN_COMMENT |
|---|---|
| id | |
| school_id | 校区id |
| subject_id | 学科id |
| class_id | 班级id |
| studying_student_count | 在读班级人数 |
| studying_date | 在读日期 |
4.5 学生请假申请表
-
student_leave_apply：学员请假信息表
-
class_id：班级id
-
student_id：学员id
-
audit_state：是否批准
-
begin_time：请假开始时间
-
end_time：请假结束时间
-
作用：用于统计每个班级的请假人数
- 对请假的合法性做判断
-
| COLUMN_NAME | COLUMN_COMMENT |
|---|---|
| id | 序列id |
| class_id | 班级id |
| student_id | 学员id |
| audit_state | 审核状态 0 待审核 1 通过 2 不通过 |
| audit_person | 审核人 |
| audit_time | 审核时间 |
| audit_remark | 审核备注 |
| leave_type | 请假类型 1 请假 2 销假 |
| leave_reason | 请假原因 1 事假 2 病假 |
| begin_time | 请假开始时间 |
| begin_time_type | 1：上午 2：下午 |
| end_time | 请假结束时间 |
| end_time_type | 1：上午 2：下午 |
| days | 请假/已休天数 |
| cancel_state | 撤销状态 0 未撤销 1 已撤销 |
| cancel_time | 撤销时间 |
| old_leave_id | 原请假id，只有leave_type =2 销假的时候才有 |
| leave_remark | 请假/销假说明 |
| valid_state | 是否有效（0：无效 1：有效） |
| create_time | 创建时间 |
知识点05：数仓设计：ODS与DIM层
-
目标：了解考勤管理数仓中ODS与DIM层的设计
-
实施
-
ODS：事务事实表
-
学员打卡信息表：记录了所有打卡信息，用于描述出勤事实
- 正常出勤、迟到出勤
- 出勤人数：正常出勤 + 迟到出勤
- 迟到人数
-
学员请假信息表：记录所有请假信息，用于描述请假事实
- 请假人数
-
学员打卡信息表：记录了所有打卡信息，用于描述出勤事实
-
DIM：维度信息表
-
班级作息时间表：判断打卡或者请假是否合法的
-
班级排课信息表：判断打卡或者请假是否合法的
-
班级人数信息表：记录每个班级的总人数
-
用于计算旷课人数
-
用于计算出勤状态的比例
-
-
-
-
小结
- 了解考勤管理数仓中ODS与DIM层的设计
知识点06：数仓设计：DWD与DWM层
-
目标：了解考勤管理数仓中DWD与DWM层的设计
-
实施
-
DWD：明细数据层
- 无具体ETL需求，不做DWD处理
-
DWM：中间数据层
- 最终目标
-
时间 班级 上午出勤 上午出勤率 上午迟到人数 上午迟到率 上午请假人数 上午请假率 上午旷课人数 上午旷课率
5-14 001 98 98% 10 10% 2 2% 0
时间 班级 上午出勤人数 上午迟到人数 上午请假人数 上午旷课人数
5-14 001 98 10 2 0
- 打卡信息表
- 请假信息表
|
- 班级出勤状态表
时间 班级 上午出勤人数 上午迟到人数
5-14 001 98 10
- 班级请假状态表
时间 班级 上午请假人数
5-14 001 2
-
班级旷课状态表
- 总人数表 - 出勤状态表 - 请假状态表 时间 班级 上午旷课人数 5-14 001 0-
目标：构建班级出勤状态表、班级请假状态表、班级旷课状态表
-
班级出勤状态表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到- 班级请假状态表
时间 班级id 上午请假 下午请假 晚上请假 ``` - 班级旷课状态表 ``` 时间 班级id 上午旷课 下午旷课 晚上旷课 ```
-
班级出勤状态表分析
-
数据：学员打卡信息表
班级id 学员id 打卡时间 001 1 2020-01-01 12:30:00 001 1 2020-01-01 15:30:00 001 1 2020-01-01 19:00:00 -
目标：班级出勤状态表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到 2020-01-01 001 30 10 30 2 30 1 -
步骤
-
step1：先将学员打卡信息转换为学员出勤状态信息
班级id 学员id date 上午 下午 晚上 001 1 正常 迟到 未出勤 001 2 迟到 正常 正常
-
-
-
select
case 打卡时间 when 上课前40分钟到下课之间 then 出勤
when 上课后10分钟到下课之间 then 迟到
else 其他
from ods_student_signin
-
出勤：上课前40分钟到下课之间
-
迟到：上课后10分钟到下课之间
-
step2：基于学员出勤状态信息统计班级出勤人数表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到 2020-01-01 001 30 10 30 2 30 1select count(case when 上午状态 = 正常 or 迟到 then 学员id else null end) as 上午出勤人数 count(case when 上午状态 = 迟到 then 学员id else null end) as 上午迟到人数 …… from table group by class_id ,date
-
-
班级请假状态表分析
-
ODS：学员请假信息表
班级id 学员id 请假开始时间 请假结束时间 是否合法 001 1 12:00:00 18:00:00 0 001 2 12:00:00 18:00:00 1 -
DWM：班级请假人数报表
时间 班级id 上午请假人数 下午请假人数 晚上请假人数 2020-01-01 001 3 2 4 -
实现
select class_id, date, count(disintct 学员id) as 上午请假人数 from table where 请假时间 < 上午上课时间 and 请假时间 > 上午下课时间 group by class_id,date; join select class_id, date, count(disintct 学员id) as 下午请假人数 from table where 请假时间 < 下午上课时间 and 请假时间 > 下午下课时间 group by class_id,date; join select class_id, date, count(disintct 学员id) as 晚上请假人数 from table where 请假时间 < 晚上上课时间 and 请假时间 > 晚上下课时间 group by class_id,date; on classId and date -
-
班级旷课状态表分析
-
DWM
-
班级出勤状态表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到 2020-01-01 001 25 5 25 2 25 4 -
班级请假人数表
时间 班级id 上午请假人数 下午请假人数 晚上请假人数 2020-01-01 001 3 2 4 -
DIM：班级总人数表
班级id 总人数 001 30
-
-
DWM：班级旷课信息表
时间 班级 上午旷课人数 下午旷课人数 晚上旷课人数 2020-01-01 001 2 3 1
-
-
-
小结
- 了解考勤管理数仓中DWD与DWM层的设计
知识点07：数仓设计：DWS与APP层
-
目标：了解考勤管理数仓中DWS与APP层的设计
-
实施
-
DWM
- 出勤状态
时间 班级 出勤人数 迟到人数- 请假状态
时间 班级 请假人数- 旷课装填
时间 班级 旷课人数 -
DWS：汇总数据层
-
关联
时间 班级 出勤人数 迟到人数 请假人数 旷课人数 -
按照天维度做基础聚合，得到天维度下的结果
时间【天】 班级 上午指标…… 下午指标…… 晚上指标……
-
-
APP：应用数据层
-
最终得到每个时间维度下对应每个班级的汇总指标
时间【天、月、年】 班级 上午指标…… 下午指标…… 晚上指标……
-
-
-
小结
- 了解考勤管理数仓中DWS与APP层的设计
知识点08：Hive分析优化器与CBO引擎
-
目标：掌握Hive分析优化器与CBO引擎的使用
-
路径
- step1：优化器
- step2：CBO
- step3：分析优化器Analyze
-
实施
-
优化器
-
问题：关于底层优化器的选择的问题
-
RBO：基于规则的优化引擎，几乎所有数据库工具默认的优化引擎
- 按照规定的规则，源码中定义所有的规则，只要符合条件，就按照固定的规则来处理
-
CBO：基于代价的优化引擎
- 按照代价的判断的性能来具体选择一种代价最小的方式来处理数据
-
举个栗子
select * from table where age = 20- 条件：age这一列有索引，整张表有100条数据，age=20的数据有90条
- 结果：返回这90条数据内容
- RBO：按照固定的规则，来实现处理，走了索引，通过索引查询到这90条数据对应的位置，再去读这90条数据
- CBO：根据代价实现判断，发现全表扫描比走索引更快，所以CBO引擎会选择走全表扫描来得到这90条数据
-
-
-
CBO
-
功能：基于各种方式所要付出的代价来衡量，使用代价相对较小的方式来实现底层的计算
-
要求：要想用CBO，必须提前准备这些数据的一些元数据信息，列的元数据信息【哪些列，每种列的值有多少个】
-
属性
set hive.cbo.enable=true; set hive.compute.query.using.stats=true; set hive.stats.fetch.column.stats=true; set hive.stats.fetch.partition.stats=true;
-
-
分析优化器Analyze
-
功能：通过底层执行一个MapReduce，提前构建这张表中的列、分区等数据的元数据，搭配CBO引擎来使用
-
使用
ANALYZE TABLE tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)] COMPUTE STATISTICS [noscan]; ANALYZE TABLE tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)] COMPUTE STATISTICS FOR COLUMNS ( columns name1, columns name2...) [noscan];- noscan：不读取文件，如果不读取文件，只能获取到一些基本的信息
-
-
-
小结
- 什么是CBO？如何使用CBO？
- CBO：基于代价的优化器引擎，根据所有方案需要付出的代价选择最小的代价的方案来实现执行
- 使用
- step1：先通过分析优化器anlayze构建元数据
- step2：开启属性
- 什么是CBO？如何使用CBO？
知识点09：数仓实现：ODS层与DIM层
-
目标：实现ODS层与DIM层的构建
-
实施
-
ODS
-
分析
- 学员打卡信息表：出勤和迟到事实
- 学员请假信息表：请假事实
-
实现
-
学生打卡记录表
drop table itcast_ods.student_signin_ods; CREATE TABLE IF NOT EXISTS itcast_ods.student_signin_ods ( id int, normal_class_flag int comment '是否正课 1 正课 2 自习 3 休息', time_table_id int comment '作息时间id normal_class_flag=2 关联tbh_school_time_table 或者 normal_class_flag=1 关联 tbh_class_time_table', class_id int comment '班级id', student_id int comment '学员id', signin_time String comment '签到时间', signin_date String comment '签到日期', inner_flag int comment '内外网标志 0 外网 1 内网', signin_type int comment '签到类型 1 心跳打卡 2 老师补卡 3 直播打卡', share_state int comment '共享屏幕状态 0 否 1是 在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 (暂不用)', inner_ip String comment '内网ip地址', create_time String comment '创建时间') comment '学生打卡记录表' PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_ods.db/student_signin_ods' TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='time_table_id,class_id,signin_date,share_state');sqoop import \ --connect jdbc:mysql://node3:3306/teach \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d") as dt from tbh_student_signin_record where $CONDITIONS' \ --hcatalog-database itcast_ods \ --hcatalog-table student_signin_ods \ --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' \ -m 1 -
学生请假记录表
drop table itcast_ods.student_leave_apply_ods; CREATE TABLE IF NOT EXISTS itcast_ods.student_leave_apply_ods ( id int, class_id int comment '班级id', student_id int comment '学员id', audit_state int comment '审核状态 0 待审核 1 通过 2 不通过', audit_person int comment '审核人', audit_time String comment '审核时间', audit_remark String comment '审核备注', leave_type int comment '请假类型 1 请假 2 销假 （查询是否请假不用过滤此类型，通过有效状态来判断）', leave_reason int comment '请假原因 1 事假 2 病假', begin_time String comment '请假开始时间', begin_time_type int comment '1：上午 2：下午 3：晚自习', end_time String comment '请假结束时间', end_time_type int comment '1：上午 2：下午 3：晚自习', days float comment '请假/已休天数', cancel_state int comment '撤销状态 0 未撤销 1 已撤销', cancel_time String comment '撤销时间', old_leave_id int comment '原请假id，只有leave_type =2 销假的时候才有', leave_remark String comment '请假/销假说明', valid_state int comment '是否有效（0：无效 1：有效）', create_time String comment '创建时间') comment '学生请假申请表' PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_ods.db/student_leave_apply_ods' TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='class_id,audit_state,cancel_state,valid_state');sqoop import \ --connect jdbc:mysql://node3:3306/teach \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d") as dt from student_leave_apply where $CONDITIONS' \ --hcatalog-database itcast_ods \ --hcatalog-table student_leave_apply_ods \ --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' \ -m 1
-
-
-
DIM
-
分析
- 班级作息时间表
-
班级排课信息表
-
-
班级总人数表
-
实现
-
在读学员人数表
set hive.exec.orc.compression.strategy=COMPRESSION; drop table itcast_dimen.class_studying_student_count_dimen; CREATE TABLE IF NOT EXISTS itcast_dimen.class_studying_student_count_dimen ( id int, school_id int comment '校区id', subject_id int comment '学科id', class_id int comment '班级id', studying_student_count int comment '在读班级人数', studying_date STRING comment '在读日期') comment '在读班级的每天在读学员人数' PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as orc
location ‘/user/hive/warehouse/itcast_dimen.db/class_studying_student_count_dimen’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’,‘orc.bloom.filter.columns’=‘studying_student_count,studying_date’);```shell sqoop import \ --connect jdbc:mysql://node3:3306/teach \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --query 'select id, school_id, subject_id, class_id, ifnull(studying_student_count,0) studying_student_count, studying_date, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d") as dt from class_studying_student_count where $CONDITIONS' \ --hcatalog-database itcast_dimen \ --hcatalog-table class_studying_student_count_dimen \ --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' \ -m 1-
班级排课表
drop table itcast_dimen.course_table_upload_detail_dimen; CREATE TABLE IF NOT EXISTS itcast_dimen.course_table_upload_detail_dimen ( id int comment 'id', base_id int comment '课程主表id', class_id int comment '班级id', class_date STRING comment '上课日期', content STRING comment '课程内容', teacher_id int comment '老师id', teacher_name STRING comment '老师名字', job_number STRING comment '工号', classroom_id int comment '教室id', classroom_name STRING comment '教室名称', is_outline int comment '是否大纲 0 否 1 是', class_mode int comment '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播', is_stage_exam int comment '是否阶段考试（0：否 1：是）', is_pay int comment '代课费（0：无 1：有）', tutor_teacher_id int comment '晚自习辅导老师id', tutor_teacher_name STRING comment '辅导老师姓名', tutor_job_number STRING comment '晚自习辅导老师工号', is_subsidy int comment '晚自习补贴（0：无 1：有）', answer_teacher_id int comment '答疑老师id', answer_teacher_name STRING comment '答疑老师姓名', answer_job_number STRING comment '答疑老师工号', remark STRING comment '备注', create_time STRING comment '创建时间') comment '班级课表明细表' PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as orc
location ‘/user/hive/warehouse/itcast_dimen.db/course_table_upload_detail_dimen’
TBLPROPERTIES (‘orc.compress’=‘SNAPPY’,‘orc.bloom.filter.columns’=‘class_id,class_date’);```shell sqoop import \ --connect jdbc:mysql://node3:3306/teach \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --query 'select id, base_id, class_id, class_date, content, teacher_id, teacher_name, job_number, classroom_id, classroom_name, is_outline, class_mode, is_stage_exam, is_pay, tutor_teacher_id, tutor_teacher_name, tutor_job_number, is_subsidy, answer_teacher_id, answer_teacher_name, answer_job_number, remark, create_time, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d") as dt from course_table_upload_detail where $CONDITIONS' \ --hcatalog-database itcast_dimen \ --hcatalog-table course_table_upload_detail_dimen \ --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' \ -m 1- 班级作息时间表
-
-
-
drop table itcast_dimen.class_time_dimen;
CREATE TABLE IF NOT EXISTS itcast_dimen.class_time_dimen (
id int,
class_id int comment '班级id',
morning_template_id int comment '上午出勤模板id',
morning_begin_time STRING comment '上午开始时间',
morning_end_time STRING comment '上午结束时间',
afternoon_template_id int comment '下午出勤模板id',
afternoon_begin_time STRING comment '下午开始时间',
afternoon_end_time STRING comment '下午结束时间',
evening_template_id int comment '晚上出勤模板id',
evening_begin_time STRING comment '晚上开始时间',
evening_end_time STRING comment '晚上结束时间',
use_begin_date STRING comment '使用开始日期',
use_end_date STRING comment '使用结束日期',
create_time STRING comment '创建时间',
create_person int comment '创建人',
remark STRING comment '备注')
comment '班级作息时间表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_dimen.db/class_time_dimen'
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='id,class_id');
sqoop import \
--connect jdbc:mysql://node3:3306/teach \
--username root \
--password 123456 \
--driver com.mysql.jdbc.Driver \
--query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d") as dt from tbh_class_time_table where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table class_time_dimen \
--hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' \
-m 1
-
小结
- 实现ODS层与DIM层的构建
知识点10：数仓实现：DWM层出勤
-
目标：实现DWM层出勤状态表的构建
-
实施
-
分析
-
ODS：学员打卡信息表
班级id 学员id 打卡时间 打卡日期 001 1 2020-01-01 12:30:00 2020-01-01 -
DWM
-
step1：先将学员打卡信息转换为学员出勤状态信息
班级id 学员id 上午 下午 晚上 001 1 正常 迟到 未出勤 001 2 迟到 正常 正常 -
step2：基于学员出勤状态信息统计班级出勤人数表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到 2020-01-01 001 30 10 30 2 30 1
-
-
SQL实现：判断：0.正常出勤、1.迟到、2.其他
- 判断打卡时间是否在上课时间范围内
- 出勤时间范围：上课前40分钟 ~ 下课时间
- 迟到时间范围：上课十分钟后 ~ 下课时间
- 关联班级作息时间表
- 出勤时间范围：上课前40分钟 ~ 下课时间
- 判断打卡是否是一个合法的打卡
- 今天是否是正课
- 关联班级排课表
- 判断打卡时间是否在上课时间范围内
-
-
实现学员出勤状态表
-
建表：学员出勤状态表
drop table itcast_dwm.student_attendance_dwm; CREATE TABLE IF NOT EXISTS itcast_dwm.student_attendance_dwm ( dateinfo String comment '日期', class_id int comment '班级id', student_id int comment '学员id', morning_att String comment '上午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）', afternoon_att String comment '下午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）', evening_att String comment '晚自习出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）') comment '学生出勤(正常出勤和迟到)数据' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_dwm.db/student_attendance_dwm'; --内存不足，执行以下语句 drop table itcast_dwm.student_attendance_dwm; CREATE TABLE IF NOT EXISTS itcast_dwm.student_attendance_dwm ( dateinfo String comment '日期', class_id int comment '班级id', student_id int comment '学员id', morning_att String comment '上午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）', afternoon_att String comment '下午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）', evening_att String comment '晚自习出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）') comment '学生出勤(正常出勤和迟到)数据' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/user/hive/warehouse/itcast_dwm.db/student_attendance_dwm'; -
开启属性
--分区 SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.max.dynamic.partitions.pernode=10000; set hive.exec.max.dynamic.partitions=100000; set hive.exec.max.created.files=150000; --hive压缩 set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true; --写入时压缩生效 set hive.exec.orc.compression.strategy=COMPRESSION; --分桶 set hive.enforce.bucketing=true; set hive.enforce.sorting=true; set hive.optimize.bucketmapjoin = true; set hive.auto.convert.sortmerge.join=true; set hive.auto.convert.sortmerge.join.noconditionaltask=true; --并行执行 set hive.exec.parallel=true; set hive.exec.parallel.thread.number=8; --矢量化查询 set hive.vectorized.execution.enabled=true; --关联优化器 set hive.optimize.correlation=true; --读取零拷贝 set hive.exec.orc.zerocopy=true; --join数据倾斜 set hive.optimize.skewjoin=true; set hive.optimize.skewjoin.compiletime=true; -- group倾斜 set hive.groupby.skewindata=true; -
实现学员出勤状态表
-
分析的SQL
-- 在上课前40分钟~上课时间点10分钟之内，为正常打卡0；在上课后10分钟~放学时间之内，为迟到1；否则为2. --上午的状态 if( --第一个参数是判断条件，判断是否出勤了 sum( if( ( cast( --上午上课时间 (unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') - --签到时间 unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS')) / 60 as INT ) <= 40 ) and ( --签到时间 unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') < --上午结束时间 unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_end_time), 'yyyy-MM-dd HH:mm:ss') ), --如果满足这个条件就返回1，不满足返回0 1, 0) --返回1结果大于0，条件成立，执行true的结果 --返回0结果，条件不成立，执行false的结果，返回2 ) > 0, --if层的第二个参数，true的结果，又是一个if判断，用于判断是正常还是迟到，正常：0，迟到：1 if( sum( if( ( cast( ( --上午上课时间 unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') - --签到时间 unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') ) / 60 as INT ) <= 40 ) and ( cast( ( --签到时间 unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') - --上午上课时间 unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') ) / 60 as INT --小于10分钟 ) <= 10 ), --如果满足以上条件，返回1, --如果不满足返回0 1, 0) --如果上一个条件满足，大于0，返回0，正常出勤 --如果不满足上一条条件，返回1，迟到出勤 ) > 0, 0, 1 ), --第三个参数，false的结果，非法打卡 2 ) as morning_att, -
实现的SQL
INSERT overwrite table itcast_dwm.student_attendance_dwm PARTITION(yearinfo, monthinfo, dayinfo) SELECT course.class_date,--上课日期 course.class_id,--班级id so.student_id,--学生id -- 在上课前40分钟~上课时间点10分钟之内，为正常打卡0；在上课后10分钟~放学时间之内，为迟到1；否则为2. --上午的状态 if( sum( if( ( cast( (unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS')) / 60 as INT ) <= 40 ) and ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') < unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_end_time), 'yyyy-MM-dd HH:mm:ss') ), 1, 0) ) > 0, if( sum( if( ( cast( ( unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') ) / 60 as INT ) <= 40 ) and ( cast( ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') - unix_timestamp(concat_ws(' ', so.signin_date, ti.morning_begin_time), 'yyyy-MM-dd HH:mm:ss') ) / 60 as INT ) <= 10 ), 1, 0) ) > 0, 0, 1 ), 2 ) as morning_att, -- 在上课前40分钟~上课时间点10分钟之内，为正常打卡0；在上课后10分钟~放学时间之内，为迟到1；否则为2. --下午的转态 if( --判断是否正常出勤(包含迟到的) sum( if( ( cast( (unix_timestamp(concat_ws(' ', so.signin_date, ti.afternoon_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS')) / 60 as INT ) <= 40 ) and ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') < unix_timestamp(concat_ws(' ', so.signin_date, ti.afternoon_end_time), 'yyyy-MM-dd HH:mm:ss') ), 1, 0) ) > 0, --正常出勤，但迟到了，判断并标记 if( sum( if( ( cast( ( unix_timestamp(concat_ws(' ', so.signin_date, ti.afternoon_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') ) / 60 as INT ) <= 40 ) and ( cast( ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') - unix_timestamp(concat_ws(' ', so.signin_date, ti.afternoon_begin_time), 'yyyy-MM-dd HH:mm:ss') ) / 60 as INT ) <= 10 ), 1, 0) ) > 0, 0, 1 ), 2 ) as afternoon_att, -- 在上课前40分钟~上课时间点10分钟之内，为正常打卡0；在上课后10分钟~放学时间之内，为迟到1；否则为2. --晚上的状态 if( --判断是否正常出勤(包含迟到的) sum( if( ( cast( (unix_timestamp(concat_ws(' ', so.signin_date, ti.evening_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS')) / 60 as INT ) <= 40 ) and ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') < unix_timestamp(concat_ws(' ', so.signin_date, ti.evening_end_time), 'yyyy-MM-dd HH:mm:ss') ), 1, 0) ) > 0, --正常出勤，但迟到了，判断并标记 if( sum( if( ( cast( ( unix_timestamp(concat_ws(' ', so.signin_date, ti.evening_begin_time), 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') ) / 60 as INT ) <= 40 ) and ( cast( ( unix_timestamp(so.signin_time, 'yyyy-MM-dd HH:mm:ss.SSS') - unix_timestamp(concat_ws(' ', so.signin_date, ti.evening_begin_time), 'yyyy-MM-dd HH:mm:ss') ) / 60 as INT ) <= 10 ), 1, 0) ) > 0, 0, 1 ), 2 ) as evening_att, substr(course.class_date, 1, 4) as yearinfo, substr(course.class_date, 6, 2) monthinfo, substr(course.class_date, 9, 2) dayinfo from itcast_ods.student_signin_ods so --班级课表关联学生签到表 --通过班级id和对应日期进行关联，并且学生正常开启共屏 LEFT JOIN itcast_dimen.course_table_upload_detail_dimen course on course.class_id = so.class_id and so.signin_date=course.class_date AND so.share_state=1 --再关联班级上课时间表 LEFT JOIN itcast_dimen.class_time_dimen ti on so.time_table_id=ti.id --课表中课程内容不为 空并且不是开班典礼 WHERE course.content IS NOT NULL AND course.content != '开班典礼' --按照班级和日期以及学生进行分组 GROUP BY course.class_date, course.class_id, so.student_id; -
-
-
实现班级出勤状态表
-
count(case morning_att when 0 or 1 then student_id else null end) as 上午出勤人数
count(case morning_att when 1 then student_id else null end) as 上午迟到人数
- 建表
drop table itcast_dwm.class_attendance_dwm;
CREATE TABLE IF NOT EXISTS itcast_dwm.class_attendance_dwm (
dateinfo String comment '日期',
class_id int comment '班级id',
morning_att_count String comment '上午出勤人数',
afternoon_att_count String comment '下午出勤人数',
evening_att_count String comment '晚自习出勤人数',
morning_late_count String comment '上午迟到人数',
afternoon_late_count String comment '下午迟到人数',
evening_late_count String comment '晚自习迟到人数')
comment '学生出勤(正常出勤和迟到)数据'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_dwm.db/class_attendance_dwm'
TBLPROPERTIES ('orc.compress'='SNAPPY');
--内存不足，执行以下命令
drop table itcast_dwm.class_attendance_dwm;
CREATE TABLE IF NOT EXISTS itcast_dwm.class_attendance_dwm (
dateinfo String comment '日期',
class_id int comment '班级id',
morning_att_count String comment '上午出勤人数',
afternoon_att_count String comment '下午出勤人数',
evening_att_count String comment '晚自习出勤人数',
morning_late_count String comment '上午迟到人数',
afternoon_late_count String comment '下午迟到人数',
evening_late_count String comment '晚自习迟到人数')
comment '学生出勤(正常出勤和迟到)数据'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
location '/user/hive/warehouse/itcast_dwm.db/class_attendance_dwm';
- 属性配置
--本地模式
set hive.exec.mode.local.auto=true;
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=false;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
--join数据倾斜
set hive.optimize.skewjoin=false;
-- set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=false;
-- group倾斜
set hive.groupby.skewindata=false;
--分析优化器
ANALYZE TABLE itcast_dwm.student_attendance_dwm partition(yearinfo, monthinfo, dayinfo) COMPUTE STATISTICS;
ANALYZE TABLE itcast_dwm.student_attendance_dwm partition(yearinfo, monthinfo, dayinfo) COMPUTE STATISTICS FOR COLUMNS;
set hive.cbo.enable=true;
set hive.stats.autogather=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
-
实现班级状态信息表
INSERT INTO itcast_dwm.class_attendance_dwm PARTITION (yearinfo, monthinfo, dayinfo) SELECT stu.dateinfo, stu.class_id, count(DISTINCT case WHEN (stu.morning_att='0' or stu.morning_att='1') THEN stu.student_id ELSE NULL END ) morning_att_count, count(DISTINCT case WHEN (stu.afternoon_att ='0' or stu.afternoon_att='1') THEN stu.student_id ELSE NULL END ) afternoon_att_count, count(DISTINCT case WHEN (stu.evening_att ='0' or stu.evening_att='1') THEN stu.student_id ELSE NULL END ) evening_att_count, count(DISTINCT case WHEN (stu.morning_att='1') THEN stu.student_id ELSE NULL END ) morning_late_count, count(DISTINCT case WHEN (stu.afternoon_att='1') THEN stu.student_id ELSE NULL END ) afternoon_late_count, count(DISTINCT case WHEN (stu.evening_att='1') THEN stu.student_id ELSE NULL END ) evening_late_count, stu.yearinfo,stu.monthinfo,stu.dayinfo from itcast_dwm.student_attendance_dwm stu where stu.student_id is not null GROUP BY stu.class_id,stu.yearinfo,stu.monthinfo,stu.dayinfo,stu.dateinfo; -
小结
- 实现DWM层出勤状态表的构建
知识点11：数仓实现：DWM层请假
-
目标：实现DWM层请假状态表的构建
-
实施
-
分析
-
ODS：学员请假信息表
班级id 学员id 请假开始时间 请假结束时间 是否合法 001 1 12:00:00 18:00:00 0 001 2 12:00:00 18:00:00 1 -
DWM：班级请假人数报表
时间 班级id 上午请假人数 下午请假人数 晚上请假人数- 每个时间段请假人数
- 请假开始时间早于上课时间
- 请假结束时间晚于下课时间
- 每个时间段请假人数
-
-
实现
-
建表
drop table itcast_dwm.class_leave_dwm; CREATE TABLE IF NOT EXISTS itcast_dwm.class_leave_dwm ( dateinfo String comment '日期', class_id int comment '班级id', morning_leave_count String comment '上午请假人数', afternoon_leave_count String comment '下午请假人数', evening_leave_count String comment '晚自习请假人数') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_dwm.db/class_leave_dwm' TBLPROPERTIES ('orc.compress'='SNAPPY'); -
实现
INSERT INTO itcast_dwm.class_leave_dwm PARTITION (yearinfo, monthinfo, dayinfo) SELECT morning.dateinfo, morning.class_id, morning.morning_leave_count, afternoon.afternoon_leave_count, evening.evening_leave_count, substr(morning.dateinfo, 1, 4) yearinfo, substr(morning.dateinfo, 6, 2) monthinfo, substr(morning.dateinfo, 9, 2) dayinfo from --统计上午请假的人数 ( SELECT cource.class_date as dateinfo,--上课日期 cource.class_id,--班级id count(DISTINCT leave.student_id) as morning_leave_count--请假的人数 FROM itcast_dimen.course_table_upload_detail_dimen cource,--排课表 itcast_ods.student_leave_apply_ods leave,--请假表 itcast_dimen.class_time_dimen ti--作息时间 --过滤出所有合法的请假的信息 WHERE cource.class_id=leave.class_id and cource.class_id=ti.class_id --这天的课程非空并且不是开班典礼 and cource.content is NOT NULL and cource.content != '开班典礼' --保证是有效的作息时间 AND ti.use_begin_date <= cource.class_date AND ti.use_end_date >= cource.class_date -- 请假开始时间 <= 上课开始时间 <= 请假结束时间 AND leave.begin_time <= concat(cource.class_date, ' ', ti.morning_begin_time) AND concat(cource.class_date, ' ', ti.morning_begin_time) <= leave.end_time --请假是否有效 AND leave.audit_state=1 --审批通过 AND leave.cancel_state=0 --没有撤销 AND leave.valid_state=1 --有效请假 GROUP BY cource.class_id, cource.class_date ) morning FULL JOIN ( SELECT cource.class_date as dateinfo, cource.class_id, count(DISTINCT leave.student_id) as afternoon_leave_count FROM itcast_dimen.course_table_upload_detail_dimen cource, itcast_ods.student_leave_apply_ods leave, itcast_dimen.class_time_dimen ti WHERE cource.class_id=leave.class_id and cource.class_id=ti.class_id and cource.content is NOT NULL and cource.content != '开班典礼' --作息时间匹配 AND ti.use_begin_date <= cource.class_date AND ti.use_end_date >= cource.class_date -- 请假开始时间 <= 上课开始时间 <= 请假结束时间 AND leave.begin_time <= concat(cource.class_date, ' ', ti.afternoon_begin_time) AND concat(cource.class_date, ' ', ti.afternoon_begin_time) <= leave.end_time --请假是否有效 AND leave.audit_state=1 AND leave.cancel_state=0 AND leave.valid_state=1 GROUP BY cource.class_id, cource.class_date ) afternoon on morning.class_id=afternoon.class_id AND morning.dateinfo=afternoon.dateinfo FULL JOIN ( SELECT cource.class_date as dateinfo, cource.class_id, count(DISTINCT leave.student_id) as evening_leave_count FROM itcast_dimen.course_table_upload_detail_dimen cource, itcast_ods.student_leave_apply_ods leave, itcast_dimen.class_time_dimen ti WHERE cource.class_id=leave.class_id and cource.class_id=ti.class_id and cource.content is NOT NULL and cource.content != '开班典礼' --作息时间匹配 AND ti.use_begin_date <= cource.class_date AND ti.use_end_date >= cource.class_date -- 请假开始时间 <= 上课开始时间 <= 请假结束时间 AND leave.begin_time <= concat(cource.class_date, ' ', ti.evening_begin_time) AND concat(cource.class_date, ' ', ti.evening_begin_time) <= leave.end_time --请假是否有效 AND leave.audit_state=1 AND leave.cancel_state=0 AND leave.valid_state=1 GROUP BY cource.class_id, cource.class_date ) evening on morning.dateinfo=evening.dateinfo AND morning.class_id=evening.class_id;
-
-
-
小结
- 实现DWM层请假状态表的构建
知识点12：数仓实现：DWM层旷课
-
目标：实现DWM层旷课状态表的构建
-
实施
-
分析
-
DWM
-
班级出勤状态表
时间 班级id 上午出勤人数 上午迟到人数 下午出勤人数 下午迟到人数 晚上出勤人数 晚上迟到人数 -
班级请假人数表
时间 班级id 上午请假人数 下午请假人数 晚上请假人数 -
DIM：班级总人数表
班级id 总人数
-
-
DWM：班级旷课信息表
时间 班级 上午旷课人数 下午旷课人数 晚上旷课人数
-
-
实现
-
建表
drop table itcast_dwm.class_truant_dwm; CREATE TABLE IF NOT EXISTS itcast_dwm.class_truant_dwm ( dateinfo String comment '日期', class_id int comment '班级id', morning_truant_count String comment '上午旷课人数', afternoon_truant_count String comment '下午旷课人数', evening_truant_count String comment '晚自习旷课人数') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_dwm.db/class_truant_dwm' TBLPROPERTIES ('orc.compress'='SNAPPY'); -
实现
INSERT INTO itcast_dwm.class_truant_dwm PARTITION (yearinfo, monthinfo, dayinfo) SELECT course.class_date dateinfo, course.class_id, nvl(ct.studying_student_count, 0) - nvl(att.morning_att_count, 0) - nvl(leave.morning_leave_count, 0) morning_truant_count, nvl(ct.studying_student_count, 0) - nvl(att.afternoon_att_count, 0) - nvl(leave.afternoon_leave_count, 0) afternoon_truant_count, nvl(ct.studying_student_count, 0) - nvl(att.evening_att_count, 0) - nvl(leave.evening_leave_count, 0) evening_truant_count, substr(course.class_date, 1,4) yearinfo, substr(course.class_date, 6,2) monthinfo, substr(course.class_date, 9,2) dayinfo from itcast_dimen.course_table_upload_detail_dimen course LEFT JOIN itcast_dwm.class_attendance_dwm att on course.class_id = att.class_id and att.dateinfo = course.class_date LEFT JOIN itcast_dwm.class_leave_dwm leave on course.class_id = leave.class_id AND course.class_date = leave.dateinfo LEFT JOIN itcast_dimen.class_studying_student_count_dimen ct on ct.class_id = course.class_id and course.class_date=ct.studying_date WHERE ct.studying_student_count IS NOT NULL AND course.content IS NOT NULL AND course.content != '开班典礼';
-
-
-
小结
- 实现DWM层旷课状态表的构建
知识点13：数仓实现：DWS层
-
目标：实现DWS每日班级出勤情况表的构建
-
实施
-
分析
-
DWM层
-
班级出勤信息表
时间 班级id 上午出勤 上午迟到 下午出勤 下午迟到 晚上出勤 晚上迟到 -
班级请假信息表
时间 班级id 上午请假人数 下午请假人数 晚上请假人数 -
班级旷课信息表
时间 班级id 上午旷课人数 下午旷课人数 晚上旷课人数 -
班级总人数
班级id 班级总人数
-
-
DWS：基于天维度得到每天三个时间段每个时间段的8个指标
时间 班级 上午出勤人数 上午出勤率 下午出勤人数 下午出勤率 晚上出勤人数 晚上出勤率
-
-
实现
-
建表
drop table itcast_dws.class_attendance_dws; CREATE TABLE IF NOT EXISTS itcast_dws.class_attendance_dws ( dateinfo String comment '日期', class_id int comment '班级id', studying_student_count int comment '在读班级人数', morning_att_count String comment '上午出勤人数', morning_att_ratio String comment '上午出勤率', afternoon_att_count String comment '下午出勤人数', afternoon_att_ratio String comment '下午出勤率', evening_att_count String comment '晚自习出勤人数', evening_att_ratio String comment '晚自习出勤率', morning_late_count String comment '上午迟到人数', morning_late_ratio String comment '上午迟到率', afternoon_late_count String comment '下午迟到人数', afternoon_late_ratio String comment '下午迟到率', evening_late_count String comment '晚自习迟到人数', evening_late_ratio String comment '晚自习迟到率', morning_leave_count String comment '上午请假人数', morning_leave_ratio String comment '上午请假率', afternoon_leave_count String comment '下午请假人数', afternoon_leave_ratio String comment '下午请假率', evening_leave_count String comment '晚自习请假人数', evening_leave_ratio String comment '晚自习请假率', morning_truant_count String comment '上午旷课人数', morning_truant_ratio String comment '上午旷课率', afternoon_truant_count String comment '下午旷课人数', afternoon_truant_ratio String comment '下午旷课率', evening_truant_count String comment '晚自习旷课人数', evening_truant_ratio String comment '晚自习旷课率') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_dws.db/class_attendance_dws' TBLPROPERTIES ('orc.compress'='SNAPPY'); --内存不足 drop table itcast_dws.class_attendance_dws; CREATE TABLE IF NOT EXISTS itcast_dws.class_attendance_dws ( dateinfo String comment '日期', class_id int comment '班级id', studying_student_count int comment '在读班级人数', morning_att_count String comment '上午出勤人数', morning_att_ratio String comment '上午出勤率', afternoon_att_count String comment '下午出勤人数', afternoon_att_ratio String comment '下午出勤率', evening_att_count String comment '晚自习出勤人数', evening_att_ratio String comment '晚自习出勤率', morning_late_count String comment '上午迟到人数', morning_late_ratio String comment '上午迟到率', afternoon_late_count String comment '下午迟到人数', afternoon_late_ratio String comment '下午迟到率', evening_late_count String comment '晚自习迟到人数', evening_late_ratio String comment '晚自习迟到率', morning_leave_count String comment '上午请假人数', morning_leave_ratio String comment '上午请假率', afternoon_leave_count String comment '下午请假人数', afternoon_leave_ratio String comment '下午请假率', evening_leave_count String comment '晚自习请假人数', evening_leave_ratio String comment '晚自习请假率', morning_truant_count String comment '上午旷课人数', morning_truant_ratio String comment '上午旷课率', afternoon_truant_count String comment '下午旷课人数', afternoon_truant_ratio String comment '下午旷课率', evening_truant_count String comment '晚自习旷课人数', evening_truant_ratio String comment '晚自习旷课率') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/user/hive/warehouse/itcast_dws.db/class_attendance_dws'; -
实现
INSERT INTO itcast_dws.class_attendance_dws PARTITION (yearinfo, monthinfo, dayinfo) SELECT course.class_date dateinfo, course.class_id, total.studying_student_count, --统计出勤情况 att.morning_att_count, cast((att.morning_att_count / total.studying_student_count) * 100 as DECIMAL(8,2)) morning_att_ratio, att.afternoon_att_count, cast((att.afternoon_att_count / total.studying_student_count) * 100 as DECIMAL(8,2)) afternoon_att_ratio, att.evening_att_count, cast((att.evening_att_count / total.studying_student_count) * 100 as DECIMAL(8,2)) evening_att_ratio, --统计迟到情况 att.morning_late_count, cast((att.morning_late_count / total.studying_student_count) * 100 as DECIMAL(8,2)) morning_late_ratio, att.afternoon_late_count, cast((att.afternoon_late_count / total.studying_student_count) * 100 as DECIMAL(8,2)) afternoon_late_ratio, att.evening_late_count, cast((att.evening_late_count / total.studying_student_count) * 100 as DECIMAL(8,2)) evening_late_ratio, --统计请假情况 lev.morning_leave_count, cast((lev.morning_leave_count / total.studying_student_count) * 100 as DECIMAL(8,2)) morning_leave_ratio, lev.afternoon_leave_count, cast((lev.afternoon_leave_count / total.studying_student_count) * 100 as DECIMAL(8,2)) afternoon_leave_ratio, lev.evening_leave_count, cast((lev.evening_leave_count / total.studying_student_count) * 100 as DECIMAL(8,2)) evening_leave_ratio, --统计旷课情况 tru.morning_truant_count, cast((tru.morning_truant_count / total.studying_student_count) * 100 as DECIMAL(8,2)) morning_truant_ratio, tru.afternoon_truant_count, cast((tru.afternoon_truant_count / total.studying_student_count) * 100 as DECIMAL(8,2)) afternoon_truant_ratio, tru.evening_truant_count, cast((tru.evening_truant_count / total.studying_student_count) * 100 as DECIMAL(8,2)) evening_truant_ratio, --时间维度 substr(course.class_date, 1,4) yearinfo, substr(course.class_date, 6,2) monthinfo, substr(course.class_date, 9,2) dayinfo from itcast_dimen.course_table_upload_detail_dimen course LEFT JOIN itcast_dimen.class_studying_student_count_dimen total on course.class_date=total.studying_date AND course.class_id = total.class_id LEFT JOIN itcast_dwm.class_attendance_dwm att on course.class_date=att.dateinfo AND course.class_id = att.class_id LEFT JOIN itcast_dwm.class_leave_dwm lev on course.class_id=lev.class_id AND course.class_date=lev.dateinfo LEFT JOIN itcast_dwm.class_truant_dwm tru on course.class_id=tru.class_id AND course.class_date=tru.dateinfo WHERE course.content IS NOT NULL AND course.content != '开班典礼' AND total.studying_student_count IS NOT NULL;
-
-
-
小结
- 实现DWS层的构建
知识点14：数仓实现：APP层
-
目标：实现APP层的构建
-
实施
-
分析
- 基于天维度下每个班级的指标，得到月维度以及年维度下的指标
-
实现
-
建表
drop table itcast_app.class_attendance_app; CREATE TABLE IF NOT EXISTS itcast_app.class_attendance_app ( dateinfo String comment '日期', class_id int comment '班级id', studying_student_count int comment '在读班级人数', morning_att_count String comment '上午出勤人数', morning_att_ratio String comment '上午出勤率', afternoon_att_count String comment '下午出勤人数', afternoon_att_ratio String comment '下午出勤率', evening_att_count String comment '晚自习出勤人数', evening_att_ratio String comment '晚自习出勤率', morning_late_count String comment '上午迟到人数', morning_late_ratio String comment '上午迟到率', afternoon_late_count String comment '下午迟到人数', afternoon_late_ratio String comment '下午迟到率', evening_late_count String comment '晚自习迟到人数', evening_late_ratio String comment '晚自习迟到率', morning_leave_count String comment '上午请假人数', morning_leave_ratio String comment '上午请假率', afternoon_leave_count String comment '下午请假人数', afternoon_leave_ratio String comment '下午请假率', evening_leave_count String comment '晚自习请假人数', evening_leave_ratio String comment '晚自习请假率', morning_truant_count String comment '上午旷课人数', morning_truant_ratio String comment '上午旷课率', afternoon_truant_count String comment '下午旷课人数', afternoon_truant_ratio String comment '下午旷课率', evening_truant_count String comment '晚自习旷课人数', evening_truant_ratio String comment '晚自习旷课率', time_type STRING COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合。') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as orc location '/user/hive/warehouse/itcast_app.db/class_attendance_app' TBLPROPERTIES ('orc.compress'='SNAPPY'); --内存不足 drop table itcast_app.class_attendance_app; CREATE TABLE IF NOT EXISTS itcast_app.class_attendance_app ( dateinfo String comment '日期', class_id int comment '班级id', studying_student_count int comment '在读班级人数', morning_att_count String comment '上午出勤人数', morning_att_ratio String comment '上午出勤率', afternoon_att_count String comment '下午出勤人数', afternoon_att_ratio String comment '下午出勤率', evening_att_count String comment '晚自习出勤人数', evening_att_ratio String comment '晚自习出勤率', morning_late_count String comment '上午迟到人数', morning_late_ratio String comment '上午迟到率', afternoon_late_count String comment '下午迟到人数', afternoon_late_ratio String comment '下午迟到率', evening_late_count String comment '晚自习迟到人数', evening_late_ratio String comment '晚自习迟到率', morning_leave_count String comment '上午请假人数', morning_leave_ratio String comment '上午请假率', afternoon_leave_count String comment '下午请假人数', afternoon_leave_ratio String comment '下午请假率', evening_leave_count String comment '晚自习请假人数', evening_leave_ratio String comment '晚自习请假率', morning_truant_count String comment '上午旷课人数', morning_truant_ratio String comment '上午旷课率', afternoon_truant_count String comment '下午旷课人数', afternoon_truant_ratio String comment '下午旷课率', evening_truant_count String comment '晚自习旷课人数', evening_truant_ratio String comment '晚自习旷课率', time_type STRING COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合。') comment '班级请假数据统计' PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/user/hive/warehouse/itcast_app.db/class_attendance_app'; -
实现
-
天
INSERT OVERWRITE TABLE itcast_app.class_attendance_app partition (yearinfo, monthinfo, dayinfo) select dateinfo, class_id, studying_student_count, morning_att_count, morning_att_ratio, afternoon_att_count, afternoon_att_ratio, evening_att_count, evening_att_ratio, morning_late_count, morning_late_ratio, afternoon_late_count, afternoon_late_ratio, evening_late_count, evening_late_ratio, morning_leave_count, morning_leave_ratio, afternoon_leave_count, afternoon_leave_ratio, evening_leave_count, evening_leave_ratio, morning_truant_count, morning_truant_ratio, afternoon_truant_count, afternoon_truant_ratio, evening_truant_count, evening_truant_ratio, '2', yearinfo, monthinfo, dayinfo from itcast_dws.class_attendance_dws; -
月
INSERT INTO itcast_app.class_attendance_app PARTITION(yearinfo, monthinfo, dayinfo) SELECT concat_ws('-', yearinfo, monthinfo), class_id, sum(studying_student_count), sum(morning_att_count), cast((sum(morning_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_att_ratio, sum(afternoon_att_count), cast((sum(afternoon_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_att_ratio, sum(evening_att_count), cast((sum(evening_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_att_ratio, sum(morning_late_count), cast((sum(morning_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_late_ratio, sum(afternoon_late_count), cast((sum(afternoon_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_late_ratio, sum(evening_late_count), cast((sum(evening_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_late_ratio, sum(morning_leave_count), cast((sum(morning_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_leave_ratio, sum(afternoon_leave_count), cast((sum(afternoon_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_leave_ratio, sum(evening_leave_count), cast((sum(evening_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_leave_ratio, sum(morning_truant_count), cast((sum(morning_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_truant_ratio, sum(afternoon_truant_count), cast((sum(afternoon_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_truant_ratio, sum(evening_truant_count), cast((sum(evening_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_truant_ratio, '4', yearinfo, monthinfo, '-1' as dayinfo from itcast_dws.class_attendance_dws --每个月每个班级 GROUP BY yearinfo, monthinfo, class_id; -
年
INSERT INTO itcast_app.class_attendance_app PARTITION(yearinfo, monthinfo, dayinfo) SELECT yearinfo, class_id, sum(studying_student_count), sum(morning_att_count), cast((sum(morning_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_att_ratio, sum(afternoon_att_count), cast((sum(afternoon_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_att_ratio, sum(evening_att_count), cast((sum(evening_att_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_att_ratio, sum(morning_late_count), cast((sum(morning_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_late_ratio, sum(afternoon_late_count), cast((sum(afternoon_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_late_ratio, sum(evening_late_count), cast((sum(evening_late_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_late_ratio, sum(morning_leave_count), cast((sum(morning_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_leave_ratio, sum(afternoon_leave_count), cast((sum(afternoon_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_leave_ratio, sum(evening_leave_count), cast((sum(evening_leave_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_leave_ratio, sum(morning_truant_count), cast((sum(morning_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) morning_truant_ratio, sum(afternoon_truant_count), cast((sum(afternoon_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) afternoon_truant_ratio, sum(evening_truant_count), cast((sum(evening_truant_count) / sum(studying_student_count)) * 100 as decimal(8, 2)) evening_truant_ratio, '5', yearinfo, '-1' as monthinfo, '-1' as dayinfo from itcast_dws.class_attendance_dws GROUP BY yearinfo,class_id;
-
-
-
-
小结
- 实现APP层的构建
知识点15：数仓实现：导出结果
-
目标：实现结果的导出
-
实施
-
建表
use scrm_bi; drop table class_attendance_app; CREATE TABLE IF NOT EXISTS class_attendance_app ( class_id int comment '班级id', studying_student_count float comment '在读班级人数', morning_att_count float comment '上午出勤人数', morning_att_ratio float comment '上午出勤率', afternoon_att_count float comment '下午出勤人数', afternoon_att_ratio float comment '下午出勤率', evening_att_count float comment '晚自习出勤人数', evening_att_ratio float comment '晚自习出勤率', morning_late_count float comment '上午迟到人数', morning_late_ratio float comment '上午迟到率', afternoon_late_count float comment '下午迟到人数', afternoon_late_ratio float comment '下午迟到率', evening_late_count float comment '晚自习迟到人数', evening_late_ratio float comment '晚自习迟到率', morning_leave_count float comment '上午请假人数', morning_leave_ratio float comment '上午请假率', afternoon_leave_count float comment '下午请假人数', afternoon_leave_ratio float comment '下午请假率', evening_leave_count float comment '晚自习请假人数', evening_leave_ratio float comment '晚自习请假率', morning_truant_count float comment '上午旷课人数', morning_truant_ratio float comment '上午旷课率', afternoon_truant_count float comment '下午旷课人数', afternoon_truant_ratio float comment '下午旷课率', evening_truant_count float comment '晚自习旷课人数', evening_truant_ratio float comment '晚自习旷课率', time_type varchar(8) COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合。', yearinfo varchar(8) COMMENT '年', monthinfo varchar(8) COMMENT '月', dayinfo varchar(8) COMMENT '日' ) comment '班级请假数据统计'; -
实现
sqoop export \ --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --table class_attendance_app \ --hcatalog-database itcast_app \ --hcatalog-table class_attendance_app \ -m 1
-
-
小结
- 实现结果的导出
知识点16：练习：有效线索主题
-
目标：自己动手实现有效线索主题的分析开发
-
实施
-
step1：了解线索主题的业务需求
- 需求：统计不同维度下的有效线索的个数
- 指标：统计有效线索的个数
- 怎么界定这个线索是一个有效线索
- 维度：时间、新老学员、线上线下
-
step2：了解线索主题的数据来源
-
customer_clue：线索信息表，记录了每一条线索的信息
- 用于对线索的id进行统计，统计有效线索个数
- 时间
- 新老学员
-
customer_relationship：用于提供线上线下维度
- orgin_type：线上线下
-
customer_appeal：线索申诉表
`id` int COMMENT 'customer_appeal_id', `customer_relationship_first_id` int COMMENT '第一条客户关系id', `employee_id` int COMMENT '申诉人', `employee_name` STRING COMMENT '申诉人姓名', `employee_department_id` int COMMENT '申诉人部门', `employee_tdepart_id` int COMMENT '申诉人所属部门', `appeal_status` int COMMENT '申诉状态，0:待稽核 1:无效 2：有效', `audit_id` int COMMENT '稽核人id', `audit_name` STRING COMMENT '稽核人姓名', `audit_department_id` int COMMENT '稽核人所在部门', `audit_department_name` STRING COMMENT '稽核人部门名称', `audit_date_time` STRING COMMENT '稽核时间', `create_date_time` STRING COMMENT '创建时间（申诉时间）', `update_date_time` STRING COMMENT '更新时间', `deleted` STRING COMMENT '删除标志位', `tenant` int COMMENT '租户id')-
用于提供判断是否是一个有效线索
where appeal_status != 1
-
-
-
step3：分析线索主题的数仓设计
-
step4：实现线索主题的数仓构建
-
comment '下午旷课率',
evening_truant_count float comment '晚自习旷课人数',
evening_truant_ratio float comment '晚自习旷课率',
time_type varchar(8) COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合。',
yearinfo varchar(8) COMMENT '年',
monthinfo varchar(8) COMMENT '月',
dayinfo varchar(8) COMMENT '日'
)
comment '班级请假数据统计';
-
实现
sqoop export \ --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \ --username root \ --password 123456 \ --driver com.mysql.jdbc.Driver \ --table class_attendance_app \ --hcatalog-database itcast_app \ --hcatalog-table class_attendance_app \ -m 1 -
小结
- 实现结果的导出
知识点16：练习：有效线索主题
-
目标：自己动手实现有效线索主题的分析开发
-
实施
-
step1：了解线索主题的业务需求
- 需求：统计不同维度下的有效线索的个数
- 指标：统计有效线索的个数
- 怎么界定这个线索是一个有效线索
- 维度：时间、新老学员、线上线下
-
step2：了解线索主题的数据来源
-
customer_clue：线索信息表，记录了每一条线索的信息
- 用于对线索的id进行统计，统计有效线索个数
- 时间
- 新老学员
-
customer_relationship：用于提供线上线下维度
- orgin_type：线上线下
-
customer_appeal：线索申诉表
`id` int COMMENT 'customer_appeal_id', `customer_relationship_first_id` int COMMENT '第一条客户关系id', `employee_id` int COMMENT '申诉人', `employee_name` STRING COMMENT '申诉人姓名', `employee_department_id` int COMMENT '申诉人部门', `employee_tdepart_id` int COMMENT '申诉人所属部门', `appeal_status` int COMMENT '申诉状态，0:待稽核 1:无效 2：有效', `audit_id` int COMMENT '稽核人id', `audit_name` STRING COMMENT '稽核人姓名', `audit_department_id` int COMMENT '稽核人所在部门', `audit_department_name` STRING COMMENT '稽核人部门名称', `audit_date_time` STRING COMMENT '稽核时间', `create_date_time` STRING COMMENT '创建时间（申诉时间）', `update_date_time` STRING COMMENT '更新时间', `deleted` STRING COMMENT '删除标志位', `tenant` int COMMENT '租户id')-
用于提供判断是否是一个有效线索
where appeal_status != 1
-
-
-
step3：分析线索主题的数仓设计
-
step4：实现线索主题的数仓构建
-