黑马Hive+Spark离线数仓工业项目--数仓事实层DWB层构建（3）

野性酷女 2024-03-30 13:05 68阅读 0赞

## 网点物料事实指标分析及实现 ##

**目标**：实现DWB层网点物料事实指标表的构建

**路径**

\- step1：目标需求  
\- step2：数据来源  
\- step3：目标实现

**实施**

\- \*\*目标需求\*\*：基于物料申请单的信息统计物料申请数量、物料申请金额等指标

![e1b08d4c81144816bcb759940387aec4.png][]

**数据来源**

![807fec353cec404da38db3d2101b2dea.png][]

**目标实现**

**建表**

![87e2434d575f42e6bb0fca0094bb34bc.png][]

**抽取**

![75d3654a97c54a3b8f74db565e205648.png][]

![3bea6f42fa4940cb998c6f0f615fcc96.png][]

**在线教育项目回顾**

## **在线教育项目需求** ##

**目标**：掌握在线教育项目需求

**实施**

常规的需求：通过对数据进行数据分析处理，得到一些指标，来反映一些事实，支撑运营决策  
行业：在线教育行业  
产品：课程  
需求：提高学员报名的转换率，实现可持续化的运营发展  
\- 需求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：数仓设计 ##

目标：掌握业务分析主题中每个主题数仓的实现流程

实施

![6788d47fa449441fab4a469195eaecc4.png][]

**访问分析主题**  
\- 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累加重新计算月、年的出勤指标

## 05：技术架构 ##

目标：掌握整个项目的技术架构

实施

![5554d172c2f8483882cbca4f31b59d62.png][]

\- 数据源：MySQL数据库  
\- 数据采集：Sqoop  
\- 数据存储：Hive：离线数据仓库  
\- 数据处理：HiveSQL【MapReduce】 =》 以后简历中要改为SparkSQL等工具来实现  
\- 数据结果：MySQL  
\- 数据报表：FineBI  
\- 协调服务：Zookeeper  
\- 可视化交互：Hue  
\- 任务流调度：Oozie  
\- 集群管理监控：Cloudera Manager  
\- 项目版本管理：Git

## 06：项目优化 ##

**目标**：掌握Hive的常见优化

**实施**

属性优化

\- 本地模式

\`\`\`properties  
hive.exec.mode.local.auto=true;  
\`\`\`

\- JVM重用

\`\`\`properties  
mapreduce.job.jvm.numtasks=10  
\`\`\`

\- 推测执行

\`\`\`properties  
mapreduce.map.speculative=true  
mapreduce.reduce.speculative=true  
hive.mapred.reduce.tasks.speculative.execution=true  
\`\`\`

\- Fetch抓取

\`\`\`properties  
hive.fetch.task.conversion=more  
\`\`\`

\- 并行执行

\`\`\`properties  
hive.exec.parallel=true  
hive.exec.parallel.thread.number=16  
\`\`\`

\- 压缩

\`\`\`properties  
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  
\`\`\`

\- 矢量化查询

\`\`\`properties  
hive.vectorized.execution.enabled = true;  
hive.vectorized.execution.reduce.enabled = true;  
\`\`\`

\- 零拷贝

\`\`\`properties  
hive.exec.orc.zerocopy=true;  
\`\`\`

\- 关联优化

\`\`\`properties  
hive.optimize.correlation=true;  
\`\`\`

\- CBO优化器

\`\`\`properties  
hive.cbo.enable=true;  
hive.compute.query.using.stats=true;  
hive.stats.fetch.column.stats=true;  
hive.stats.fetch.partition.stats=true;  
\`\`\`

\- 小文件处理

\`\`\`properties  
\#设置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;  
\`\`\`

\- 索引优化

\`\`\`properties  
hive.optimize.index.filter=true  
\`\`\`

\- 谓词下推PPD

\`\`\`properties  
hive.optimize.ppd=true;  
\`\`\`

![d5a9b7dead0a46338f1dccdc40ce9468.png][]

\- Inner Join和Full outer Join，条件写在on后面，还是where后面，性能上面没有区别  
\- Left outer Join时 ，右侧的表写在on后面，左侧的表写在where后面，性能上有提高  
\- Right outer Join时，左侧的表写在on后面、右侧的表写在where后面，性能上有提高  
\- 如果SQL语句中出现不确定结果的函数，也无法实现下推

![7b7d03fac46b4145a8ba7821b6b29d73.png][]

**SQL优化**

\- 核心思想：先过滤后处理

\- where和having使用  
\- join中on和where使用  
\- 将大表过滤成为小表再join

**设计优化**

\- 分区表：减少了MapReduce输入，避免不需要的过滤

\- 分桶表：减少了比较次数，实现数据分类，大数据拆分，构建Map Join

\- 文件存储：优先选用列式存储：parquet、orc

## 07：项目问题 ##

目标：掌握Hive的常见优化

实施

内存问题：现象程序运行失败

\- OOM：out of memory

![bfc52274f4374f519e161f71f25d47ee.png][]

堆内存不足：给Task进程分配更多的内存

![a493af9e64bd4ac18b3edc09bae33ad6.png][]

\- 物理内存不足

\- 允许NodeManager使用更多的内存  
\- 硬件资源可以扩充：扩充物理内存  
\- 调整代码：基于分区处理、避免Map Join

\- 虚拟内存不足：调整虚拟内存的比例，默认为2.1

\- \*\*数据倾斜问题\*\*：程序运行时间长，一直卡在99%或者100%

![26c40ee901dc40fdb4d6e2fc6e1498de.png][]

![1e4b6f9531794e02aee74e71cfdb96d8.png][]

**现象**

\- 运行一个程序，这个程序的某一个Task一直在运行，其他的Task都运行结束了，进度卡在99%或者100%

基本原因

\- 基本原因：这个ReduceTask的负载要比其他Task的负载要高

\- ReduceTask的数据分配不均衡

![f2c77157003b4edf999a1f9193bc17b8.png][]

**根本原因：分区的规则**

\- 默认分区：根据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过程

![ea01f581bd9b44409eee66efc85bdafd.png][]

![10dd7f18117e48f0abf0ccbec993a181.png][]

\- 调大分区个数：重分区  
\- Join时候， 可以将小的数据实现广播  
\- 自定义分区规则:RDD五大特性：对于二元组类型的RDD，可以指定分区器  
\- reduceByKey(partitionClass = HashPartition)

\- 技术面试：理论为主

\- Hadoop：HDFS读写原理，YARN中程序运行流程、端口号、哪些进程、MapReduce运行过程

\- Hive：SQL语句，函数应用

\- 字符串函数、日期函数、判断函数、窗口函数

[e1b08d4c81144816bcb759940387aec4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/56a53fa6f18e4e03ab954f21410a8edc.png
[807fec353cec404da38db3d2101b2dea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9c028bdf37f0420191586a3d4134968f.png
[87e2434d575f42e6bb0fca0094bb34bc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/13a4fd50cd9c498bb2ed4693dc7eeab7.png
[75d3654a97c54a3b8f74db565e205648.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7496ac0885844a628bbca25d237825f6.png
[3bea6f42fa4940cb998c6f0f615fcc96.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/dd140259b5cb4950a1f1d6955ef14bd6.png
[6788d47fa449441fab4a469195eaecc4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/55f4b497a38441ddb8563b14a9fea8da.png
[5554d172c2f8483882cbca4f31b59d62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ce8c6475932947339a3fafee10eedc9a.png
[d5a9b7dead0a46338f1dccdc40ce9468.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/0835729ec74a4faeaa0e64ccb7d566b3.png
[7b7d03fac46b4145a8ba7821b6b29d73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5b08adecf85c4ec5ba31e085c2827341.png
[bfc52274f4374f519e161f71f25d47ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/008490e4868c474f8b9f78f51d5f9ca4.png
[a493af9e64bd4ac18b3edc09bae33ad6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9a05ea6f20f446598a3e7893fa79a24c.png
[26c40ee901dc40fdb4d6e2fc6e1498de.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/daa66b3c5a954c21a6fe8c607638587c.png
[1e4b6f9531794e02aee74e71cfdb96d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b7e519c97eb4428fba433d7d2cc97a43.png
[f2c77157003b4edf999a1f9193bc17b8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/08c29153aab449038f3b13938e636242.png
[ea01f581bd9b44409eee66efc85bdafd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/937ce8e706ad4ecc88ab4a9687780226.png
[10dd7f18117e48f0abf0ccbec993a181.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bd85693ff57445edacba10f0fa258999.png