剑指数据仓库-项目篇(四) - DWS层&&ADS层指标计算

一、项目回顾

二、首先做的是DWS轻度汇总层

• 2.1、案例：抽取数据过程中遇到的问题
  • 2.1.1、DWS调度
• 2.2、如何在ADS层做指标计算
• 2.3、分组求TopN
• 2.4、Hive的行转列&&Dbeaver数据导出

三、部署Rundeck遇到的问题（Linux的OOM机制）

一、项目回顾

• https://blog.csdn.net/SparkOnYarn/article/details/105454067
• 业务字段上没有updatetime的话需要去补这个字段；物理delete和逻辑delete；数据建模说白了就是根据业务建表结构；生产上经常会把两张表合为一张表，虽然占据空间大，但是性能高；join和group by的性能肯定是低的；无论是变态版星型模型还是雪花模型就是多张主表和明细表join后得到的一张表；无论是对外、对内的指标；我们可以存储这个指标的最小粒度的数据；

指标分析都和时空有关，时空大数据，和地理、时间挂钩；这张标准的4层架构图要印在脑子里，第一次是初始全量表 --> DWD数据表；把DWD的数据和ODS的数据join和union如果之前存在标记为开始时间；事实表和维度表正常做join，DWS叫做轻度汇总表，也叫聚合事实表；

二、首先做的是DWS轻度汇总层

1、事先建立好ODS DWD层所有相关表
2、抽取数据从ODS --> DWD层

抽取数据需要部署Rundeck调度工具，或者Azkaban，不要使用airflow，虽然很炫，但是坑很多；

/opt/shell/offlineDW/ods_init.sh ruozedata_depot
/opt/shell/offlineDW/ods_init.sh ruozedata_depothead
/opt/shell/offlineDW/ods_init.sh ruozedata_depotitem
/opt/shell/offlineDW/ods_init.sh ruozedata_material
/opt/shell/offlineDW/ods_init.sh ruozedata_materialcategory
/opt/shell/offlineDW/ods_init.sh ruozedata_supplier

下一步，进行调度，此处进行的是全量初始化，今天是20号，包含19号的数据做全量；我们站到20号来执行的；把所有相关的表都抽取到ods层；

• 如下显示正在running
  
• 出现问题：检查rundeck下mysql的连接配置（数据库、用户名、密码是否都启动对了），检查hdfs是否启动，Linux上的mysql服务是否已经启动？

点击左侧的ACTIVITY直接查看job任务运行状态？
接下来就是做ods_update.sh脚本内容，千万不要傻乎乎的去做create，直接复制job，然后workflow中修改脚本名字。运行这个job就可以了。

2.1、案例：抽取数据过程中遇到的问题

sqopp脚本在抽取的时候可能没问题，但是数据可能有问题，MySQL和Hive做count数比较，一般出现换行情况的话会形成多行导致数据不一样。

MySQL数据库中ruozedata_materialcategory这个表的数据使用ods_init.sh抽取到数据有一行出现了问题；

碰到数据不对情况下如何操作，把这个表数据干掉，手动直接进行抽取；

生产重要性5颗星：hive的建表字段和MySQL是冲突的怎么办?

先把表中数据删除，再修改ruozedata_materialcategory表的DDL语句，把它的换行符换掉；

第一次尝试：

• 因为mysql的商品表中有一行中的内容是字段是以换行符区分的；那我们在Hive中把这个数据的换行符换成特殊的值，在Dbeaver中换行符仅支持\n；这种方法pass.

第二次尝试：

• 那我们如何使用SQL去replace呢？
  SQL改了之后，shell脚本也要同步进行更改。

进入到shell的脚本目录中去，专门的拷贝ods_init.sh的这个shell脚本，其实就是改那句select语句；
去到MySQL中的ruozedata_materialcategory这张表中，右键点击这张表，选择生成SQL --> 再选择select；

SELECT `Id`, `Name`, `CategoryLevel`, `ParentId`, sort, status, serial_no, replace(replace(remark,char(10),''),char(13),'') as remark, create_time, creator, update_time, updater, tenant_id, `UpdateTime`
FROM ruozedata_erp.ruozedata_materialcategory;

此时就不存在Hive表了，这种操作不用删除表，数据在运行脚本时会直接进行覆盖；在数据抽取过程中这是一个很正常的过程，千万不要认为shell脚本执行的命令都是okay，其实不然，你的数据是不okay的。

MySQL和Hive做count数比较，可以知道两者的数据是否相同。

SQOOP抽取的时候是okay的，不代表数据是okay的。

ods_init和ods_update的数据已经就是okay的了，此时去做dwd层的数据；

此时去做dwd层的数据：

还是一葫芦画瓢拷贝一个dwd层数据，全是改成dwd，这个数据肯定是串行的，那并行的如何去做呢，做两个shell脚本，并行着同一时间去启动，dwd启动的时候去做一个dwd层的update；

dwd是紧跟着ods的时间的，ods_update比如是晚上1点做，那dwd_update就是今天晚上2点做；从ods层汇总到dwd层。

dws_bill.sh生成的表不是普通表，每一天的数据都是基于来自dwd的事实表和维度表进行覆盖的，大数据不就是处理海量数据存储计算的

<h1 id="id_2.1.1>2.1.1、DWS调度

紧跟着的我们去做的是：DWS层的调度；cat dws_bill.sh的调度：
leftjoin出来的一张大宽表，dws层的结果每一次都是做的insert overwrite，dws表不是普通的表 --> 这张表的每一天的调度都是重新刷的，每一天都把计算的结果进行重新刷和覆盖；大数据本来就是进行海量数据结算和存储的。

我们同样的还是在rundeck中新建一个job，shell脚本取决于你的数据量，这个脚本就可以选择在凌晨3点执行；此时可以手动去做。95%的公司对于cdh也都是免费版本使用；DWS层轻度汇总层就已经完成了。

<h1 id="id_2.2>2.2、如何在ADS层做指标计算

最近30天的每天每小时的订单量（含退货）、退货订单量？

• 多维的最小粒度的数据，基于小时

Hive上的维表是怎么做的？
先把ADS的表创建好，

凭借：ERP --> ODS --> DWD层提交给J哥 去获取–>DWS --> ADS层的数据；
只要运营人员上班了，一般在上班前数据能够出来就行了；最终所有的结果还需要发一封邮件，所有job的完成度；查看哪些job挂了，重要的job还需要有电话进行提醒。

为什么我们是串行的，因为我们的业务逻辑是串行的；就相当于是公平调度器一样，订单总量和订单退货总量。

要去求订单总量，直接从bill这张表中去重数据：

为什么要distinct dws_ruozedata_bill.number？一个订单中有多条明细 --> 为了去重数据；类型模糊查询"退货"的，

两张表做一个内连接是没有问题的，为什么？
每张表的计算结果只有1行1列

A表数据：28，B表数据：4；–> A B两表做内连接的结果：28、4（1行2列）；

A表：28 29
B表：4 5
产生笛卡尔积，导致数据紊乱。

2.3、分组求TopN

select 
	firstcategory,materialname,
	round(sum(allprice,2)) as sell_sku_allprice
	from ruozedata_erp.dws_ruozedata_bill
	where concat_ws('-',`type`,subtype) in ('出库-销售'，'出库-其它','出库-零售')
	group by firstcategory,materialname;

那修改需求呢，一级类别的前三畅销商品?

2.4、Hive的行转列&&Dbeaver数据导出

需求：每年每个月的各个商品各个仓库的采购，销售的数量是多少？

• 首先计算采购，都是基于DWS的指标进行计算的，基本没和其它表进行join，顶多就是一个union，自己join自己；

行转列的意思是什么呢？

运营部门的数据导出只需要excel表的话，直接Dbeaver中进行导出，表头的字段需要自己进行修改。

步骤一：
选中所有数据，右键选中导出结果集：

步骤二：
导出选择为csv格式：

步骤三：

步骤四：选择输出目录：

三、运行Rundeck遇到的问题(Linux OOM)

阐述背景：一开始按照J哥的文档进行部署Rundeck，结果发现一直报一个jvm内存不够的错误，当时更换了rundeck的版本发现并没有什么问题，也没太在意，但是启动rundeck后，新建job任务后，会发现这个rundeck进成会莫名其妙的挂掉；因为我买的是2核4g的云主机，内存只有4G，猜测这个进程吃内存触发了Linux的OOM机制。

扩展：Linux的OOM机制（Out Of Memory Killer）简而言之，会在系统内存耗尽的情况下杀掉耗内存最多的进程，具体的日志记录是在/var/log/messages：

1、监听系统日志：
tail -F /var/log/messages
Apr 22 12:19:57 hadoop001 kernel: Out of memory: Kill process 22088 (java) score 201 or sacrifice child
Apr 22 12:19:57 hadoop001 kernel: Killed process 22088, UID 500, (java) total-vm:3656768kB, anon-rss:785776kB, file-rss:868kB

2、查看到22082这个进程Out of memory
[hadoop@hadoop001 ~]$ sudo netstat -nlp|grep 22088
tcp        0      0 ::ffff:172.17.0.5:4440      :::*                        LISTEN      22088/java          

3、此时使用jps查看就说明22082这个进程已经挂了：
[hadoop@hadoop001 ~]$ jps
9393 SecondaryNameNode
9073 NameNode
9554 ResourceManager
22421 Jps
9654 NodeManager
22088 -- process information unavailable
9210 DataNode
17706 RunJar