大数据项目之离线数仓（二）

从 0-1 搭建项目，你需要做什么？

1）需要问项目经理的问题

（1）数据量（历史数据、增量、全量）：100g

（2）预算：50 万

（3）数据存储多久：1 年

（4）云主机、物理机：云主机

（5）日活：100 万

（6）数据源：接口、用户行为数据(文件)、业务数据（MySQL）

（7）项目周期：1 个月-3 个月

（8）团队多少人：3-5 个

（9）首批指标：1-10 个

（10）未来的规划：离线和实时 是否都要做

2）项目周期（2 个月）

（1）数据调研（2 周） + 集群搭建

（2）明确数据域（2 天）

（3）构建业务矩阵（3 天）

（4）建模 至下而上 （2 周）

①ODS 层 ②DWD 层 ③DIM 层

（5）指标体系建设 至上而下 （2 周）

（6）处理 bug 1 周

数仓建模准备

1）数据仓库建模的意义

如果把数据看作图书馆里的书，我们希望看到它们在书架上分门别类地放置；

• 减少重复计算。
• 快速查询所需要的数据。

2）ER 模型

如果对方问三范式问题。初步判断对方是一个 java 程序员，就不要和他深入聊，mysql高级、redis、多线程、JVM、SSM 等框架了。

应该把话题转移到大数据技术。Spark、flink、海量数据如何处理、维度建模。

3）维度建模

星型模型：事实表周围一级维度  减少 join => 大数据场景不适合频繁的 join

雪花模型：事实表周期多级维度

星座：多个事实表

4）事实表

（1）如何判断一张表是事实表？

具有度量值的  可以累加的  个数、件数、金额、次数

（2）同步策略

数据量大  =》 通常增量  特殊的，加购 （周期快照事实表）

（3）分类

①事务型事实表

找原子操作。 例如：下单 加购 支付

①选择业务过程

②声明粒度

③确定维度

④确定事实

不足：

连续性指标，不好找原子操作。  例如，库存（周期快照事实表）

多事实表关联。 例如，统计加购到支付的平均使用时长 （累积型快照事实表）

②周期快照事实表

③累积型快照事实表

5）维度表

（1）如何判断一张表是维度表？

没有度量值，都是描述信息。 身高 体重、年龄、性别

（2）同步策略

数据量小 =》 通常 全量 特殊的 用户表

（3）维度整合 减少 Join 操作

①商品表、商品品类表、SPU、商品一级分类、二级分类、三级分类=》商品维度表

②省份表、地区表 =》 地区维度表

③活动信息表、活动规则表 =》 活动维度表

（4）拉链表

对用户表做了拉链。

缓慢变化维 场景

6）建模工具是什么？

PowerDesigner、EZDML

数仓建模

1）数据调研

（1）先和 Java 人员要表，表中最好有字段的描述或者有表和字段的说明文档。（项目经理帮助协调） =》 快速熟悉表中业务。梳理清楚业务线，找到事实表和维度表。

（2）和业务人员聊 =》 验证你猜测的是否正确

（3）和产品经理聊

需求：派生指标、衍生指标

派生指标 = 原子指标（业务过程 + 度量值 + 聚合逻辑） + 统计周期 + 统计粒度 + 业务限定

需求中的业务过程必须和实际的后台业务能对应上。

2）明确数据域

（1）用户域：登录、注册

（2）流量域：启动、页面、动作、故障、曝光

（3）交易域：加购、下单、支付、物流、取消下单、取消支付

（4）工具域：领取优惠卷、使用优惠卷下单、使用优惠卷支付

（5）互动域：点赞、评论、收藏

3）构建业务矩阵

用户、商品、活动、时间、地区、优惠卷

（1）用户域：

登录、注册

（2）流量域：√

启动、页面、动作、故障、曝光

（3）交易域：

加购、下单、支付、物流、取消下单、取消支付

（4）工具域：

领取优惠卷、使用优惠卷下单、使用优惠卷支付

（5）互动域：

点赞、评论、收藏

4）建模 至下而上

（1）ODS 层

①保持数据原貌不做任何修改   起到备份作用

②采用压缩   减少磁盘空间，采用 Gzip 压缩

③创建分区表  防止后续全表扫描

（2）DWD 层 事实表

①事务型事实表

找原子操作

a）选择业务过程

选择感兴趣的业务过程。 产品经理提出的指标中需要的。

b）声明粒度

粒度：一行信息代表什么含义。可以是一次下单、一周下单、一个月下单。

如果是一个月的下单，就没有办法统计一次下单情况。保持最小粒度。

只要你自己不做聚合操作就可以。

c）确定维度

确定感兴趣的维度。产品经理提出的指标中需要的。

例如：用户、商品、活动、时间、地区、优惠卷

d）确定事实

确定事实表的度量值。 可以累加的值，例如，个数、件数、次数、金额。

事务型事实表的不足：

• 连续性指标，不好找原子操作。例如，库存（周期快照事实表）
• 多事实表关联。例如，统计加购到支付的平均使用时长（累积型快照事实表）

（2）周期快照事实表

①选择业务过程

②声明粒度  =》 1 天

③确定维度

④确定事实

（3）累积型快照事实表

①选择业务过程

②声明粒度

③确定维度

④确定事实  确定多个事实表度量值

（3）DIM 层 维度表

①维度整合 减少 join

a）商品表、商品品类表、spu、商品一级分类、二级分类、三级分类=》商品维度表

b）省份表、地区表 =》 地区维度表

c）活动信息表、活动规则表 =》 活动维度表

②拉链表

对用户表做了拉链。

缓慢变化维 场景。

5）指标体系建设 至上而下

（1）ADS 层

需求、日活、新增、留存、转化率、GMV

（2）DWS 层 聚合层

需求：派生指标、衍生指标

派生指标 = 原子指标（业务过程 + 度量值 + 聚合逻辑） + 统计周期 + 统计粒度 + 业务限定

例如，统计，每天各个省份手机品牌交易总额

交易总额 （下单 + 金额 + sum ） + 每天 + 省份 + 手机品牌

找公共的：业务过程 + 统计周期 + 统计粒度   建宽表

数仓每层做了哪些事

1）ODS 层做了哪些事？

（1）保持数据原貌，不做任何修改

（2）压缩采用 gzip，压缩比是 100g 数据压缩完 10g 左右。

（3）创建分区表

2）DIM/DWD 层做了哪些事？

建模里面的操作，正常写。

（1）数据清洗的手段

HQL、MR、SparkSQL、Kettle、Python（项目中采用 SQL 进行清除）

（2）清洗规则  

金额必须都是数字，[0-9]、手机号、身份证、匹配网址 URL

解析数据、核心字段不能为空、过期数据删除、重复数据过滤

json => 很多字段 =》 一个一个判断 =》 取数，根据规则匹配

（3）清洗掉多少数据算合理

参考，1 万条数据清洗掉 1 条。

（4）脱敏

对手机号、身份证号等敏感数据脱敏。

①加*

135****0013 互联网公司经常采用

②加密算法 md5 需要用数据统计分析，还想保证安全

美团 滴滴 md5（12334354809）=》唯一值

③加权限 需要正常使用   军工、银行、政府

（5）压缩 snappy

（6）orc 列式存储

3）DWS 层做了哪些事？

指标体系建设里面的内容再来一遍。

4）ADS 层做了哪些事？

一分钟至少说出 30 个指标。

日活、月活、周活、留存、留存率、新增（日、周、年）、转化率、流失、回流、七天内连续 3 天登录（点赞、收藏、评价、购买、加购、下单、活动）、连续 3 周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠卷人数、使用优惠卷人数、沉默、值不值得买、退款人数、退款率 topn 热门商品

产品经理最关心的：留转 G 复活

数据量

数据量的描述都是压缩前的数据量。

1）ODS 层：

（1）用户行为数据（100g => 1 亿条；1g => 100 万条）

曝光（60g or 600 万条）、页面（20g）、动作（10g）、故障 + 启动（10g）

（2）业务数据（1-2g => 100 万-200 万条）

登录（20 万）、注册（100-1000）；

加购（每天增量 20 万、全量 100 万）、下单（10 万）、支付（9 万）、物流（9 万）、取消下单（500）、退款（500）；

领取优惠卷（5 万）、使用优惠卷下单（4 万）、使用优惠卷支付（3 万）；

点赞（1000）、评论（1000）、收藏（1000）；

用户（活跃用户 100 万、新增 1000、总用户 1 千万）、商品 SPU（1-2 万）、商品 SKU（10-20 万）、活动（1000）、时间（忽略）、地区（忽略）

2）DWD 层 + DIM 层：

和 ODS 层几乎一致；

3）DWS 层

轻度聚合后，20g-50g。

4）ADS 层

10-50m 之间，可以忽略不计。

项目中遇到哪些问题？（*****）

1）Flume 零点漂移

2）Flume 挂掉及优化

3）Datax 空值、调优

4）HDFS 小文件处理

5）Kafka 挂掉

6）Kafka 丢失

7）Kafka 数据重复

8）Kafka 消息数据积压

9）Kafk 乱序

10）Kafka 顺序

11）Kafka 优化（提高吞吐量）

12）Kafka 底层怎么保证高效读写

13）Kafka 单条日志传输大小

14）Hive 优化（Hive on Spark）

15）Hive 解决数据倾斜方法

19）疑难指标编写（7 天内连续 3 次活跃、1 7 30 指标、路径分析、用户留存率、最近 7/30日各品牌复购率、最近 30 天发布的优惠券的补贴率、 同时在线人数）

20）DS 任务挂了怎么办？

21）DS 故障报警

离线---业务

SKU 和 SPU

SKU：一台银色、128G 内存的、支持联通网络的 iPhoneX。

SPU：iPhoneX。

Tm_id：品牌 Id 苹果，包括 IPHONE，耳机，MAC 等。

订单表跟订单详情表区别？

订单表的订单状态会变化，订单详情表不会，因为没有订单状态。

订单表记录 user_id，订单 id 订单编号，订单的总金额 order_status，支付方式，订单状态等。

订单详情表记录 user_id，商品 sku_id，具体的商品信息（商品名称 sku_name，价格order_price，数量 sku_num）

上卷和下钻

上卷：上卷是沿着维度的层次向上聚集汇总数据。

下探（钻）：下探是上卷的逆操作，它是沿着维度的层次向下，查看更详细的数据。比如这个经典的数据立方体模型：

维度有产品、年度、地区等，统计销售额。实际上，维度还可以更细粒度，如时间维可由年、季、月、日构成，地区也可以由国家、省份、市、区县构成等。

下钻可以理解为由粗粒度到细粒度来观察数据，比如对产品销售情况分析时，可以沿着时间维从年到月到日更细粒度的观察数据

增加维度粒度“月”。

上卷和下钻是相逆的操作，所以上卷可以理解为删掉维的某些粒度，由细粒度到粗粒度观察数据，向上聚合汇总数据。

TOB 和 TOC 解释

TOB（toBusiness）：表示面向的用户是企业。

TOC（toConsumer）：表示面向的用户是个人。

流转 G 复活指标

1）活跃

日活：100 万 ；月活：是日活的 2-3 倍 300 万

总注册的用户多少？1000 万-3000 万之间。

渠道来源：app  公众号  抖音  百度  36 氪  头条   地推

2）GMV

GMV：每天 10 万订单 （50 – 100 元） 500 万-1000 万

10%-20% 100 万-200 万（人员：程序员、人事、行政、财务、房租、收电费）

3）复购率

某日常商品复购；（手纸、面膜、牙膏）10%-20%

电脑、显示器、手表 1%

4）转化率

商品详情 =》 加购物车 =》下单 =》 支付

1%-5%  50-60%  80%-95%

5）留存率

1/2/3-60 日、周留存、月留存

搞活动： 10-20%

活动的话，数据量会增加多少？怎么解决？

日活增加 50%，GMV 增加多少 20%。（留转 G 复活）情人节，促销手纸。

集群资源都留有预量。11.11，6.18，数据量过大，提前动态增加服务器。

加多少机器：3-4 台

哪个商品卖的好？

面膜、手纸，每天销售 5000 个。

数据仓库每天跑多少张表，大概什么时候运行，运行多久？

基本一个项目建一个库，表格个数为初始的原始数据表格加上统计结果表格的总数。（一般 70-100 张表格）。

用户行为 5 张；业务数据 33 张表 =》ods34 =》dwd=>32 张=》dws 22 张宽表=>ads=》15 张 =》103 张。

Datax：00:10 => 10-20 分钟左右 第一次全量。

用户行为数据，每天 0：30 开始运行。=》ds =》 5-6 个小时运行完指标。

所有离线数据报表控制在 8 小时之内。

大数据实时处理部分控制在 5 分钟之内。（分钟级别、秒级别）

如果是实时推荐系统，需要秒级响应。

哪张表数据量最大

1）用户行为数据

曝光（60g or 6000 万条）、页面（20g）

2）业务数据（1-2g => 100 万-200 万条）

登录（20 万）、注册（100-1000）；

加购（20 万）、下单（10 万）

用户（活跃用户 100 万、新增 1000、总用户 1 千万）

商品 SKU（10 万-20 万）

2.15.10 哪张表最费时间，有没有优化

最费时间，一般是发生数据倾斜时，会比较费时间。

1）Group By

（1）统计各个省份对应的交易额

第一个统计完的指标和最后一个统计完是时间相差 20 倍

我们从 Yarn 上看到的

一共执行了多长时间 4-5 小时

你想：发生了数据倾斜 任务停止掉

（2）解决办法：

①开启 map-side 预聚合

②skewindata

解决后的效果怎么样 ？

30-50 分钟内执行完了

2）Join

统计 事实表 和维度表 join => mapjoin

（1）小表  大表 join mapjoin

解决办法： mapjoin

（2）大表 =》 大表 join

项目中什么出现 统计 加购到支付的平均使用时长

执行时间 4-5 小时

yarn

①：skewjoin

②：smbjoin 分桶有序 join

使用的前提 （分桶且有序）

③：左表随机 右表扩容

④：通过建模 规避 大表 join 大表

累积型快照事实表

并发峰值多少？大概哪个时间点？

高峰期晚上 7-12 点。Kafka 里面 20m/s

2 万/s 并发峰值在 1-2 万人

分析过最难的指标

• 路径分析
• 用户留存率
• 最近 7/30 日各品牌复购率
• 7 天内连续 3 天登录
• 每分钟同时在线人数

数仓中使用的哪种文件存储格式

常用的包括：textFile，ORC，Parquet，一般企业里使用 ORC 或者 Parquet，因为是列式存储，且压缩比非常高，所以相比于 textFile，查询速度快，占用硬盘空间少。

数仓当中数据多久删除一次

（1）部分公司永久不删

（2）有一年、两年“删除”一次的，这里面说的删除是，先将超时数据压缩下载到单独安装的磁盘上。然后删除集群上数据。 很少有公司不备份数据，直接删除的。

Mysql 业务库中某张表发生变化，数仓中表需要做什么改变

修改表结构，将新增字段放置最后！

拉链表的退链如何实现

拉链表用于记录维度表中的历史变化。在拉链表中，当某个维度属性发生变化时，会插入一条新的记录，同时将原记录的有效期设置为截至。退链是指将一个已经生效的变更恢复到上一个状态。实现思路如下：

（1）定位要退链的记录：例如找到用户最近一次信息更新

（2）查询上一条记录：查询这条记录之前的一条记录（主键相同，不同版本记录，而不是单指上一条）

（3）更新有效期：将当前记录的生效时间或者有效开始时间更新为无效，将上条记录截至日期改为最大值。

离线数仓如何补数

补数：指重新处理一段历史时间范围内的数据，以修复数据问题。

利用调度框架，海豚调度器的补数功能进行补数。

产品经理给新指标，该如何开发

（1）确定需求：与产品经理和业务部门沟通

（2）数据源分析：分析现有数据源确定是否可以满足新指标计算需求，如果不能支撑需要引入新的数据源或者扩展现有数据源

（3）数据模型设计：根据新指标，设计模型

（4）数据处理流程设计：利用 sql 进行数据的提取、清洗、转化和加载，实现指标

新出指标，原有建模无法实现，如何操作

引入新的数据源或者扩展现有数据源

和哪些部门沟通，以及沟通什么内容

产品经理：需求、统一业务口径

后端开发：数据源以及存储，数据格式，业务逻辑

前端开发：数据可视化要求，用户体验

测试人员：测试用例  bug

你们的需求和指标都是谁给的

产品经理

业务场景：时间跨度比较大，数据模型的数据怎么更新的，

例如：借款，使用一年，再还款，这个数据时间跨度大，在处理的时

候怎么处理

累积型快照事实表。

map 任务从 0%到 1%很慢，但是从 1%到 100%很快，期间出了什么问题？

1）申请资源

2）加载数据

3）如果存在于外部系统交互，获取连接慢

数据倾斜场景除了 group by 和 join 外，还有哪些场景

（1）使用 Snappy 压缩，原始文件大小不等，Map 阶段数据倾斜。

（2）over 开窗，partition by 导致数据倾斜。

没有意义的用户是怎么判断的

在离线数据仓库中，没有意义的用户通常指那些对业务价值贡献极低或没有贡献的用户。这类用户可能是僵尸用户、垃圾用户或恶意用户。

（1）低活跃度用户：这类用户在平台上的活跃度极低，可能很长时间没有登录或者几乎没有操作。可以通过用户登录频率、活动次数等指标来定义低活跃度用户。

（2）低价值用户：这类用户对平台的业务价值贡献很低，例如在电商平台上从未进行过购买，或者在内容平台上几乎没有观看、点赞和评论等操作。可以通过消费金额、互动次数等指标来定义低价值用户。

（3）僵尸用户：这类用户可能是批量注册的账户或者长时间不活跃的用户。僵尸用户可能会导致平台数据泡沫，影响业务分析和决策。可以通过注册信息、活跃度和价值指标综合判断僵尸用户。

（4）恶意用户：这类用户可能会进行恶意操作，如发布垃圾信息、刷单、刷点击等。这些操作可能会对平台的数据准确性和用户体验产生负面影响。可以通过用户行为分析、异常检测等方法来识别恶意用户。

你的公司方向是电子商务，自营的还是提货平台？你们会有自己的商品吗？

我们的公司是自营的，有自己的商品，我们有自己经营的商品维度表，也有商品从采购到上架、发货、收货等一系列流程的业务数据和日志数据。

ods 事实表中订单状态会发生变化，你们是通过什么方式去监测数据变化的

订单状态的改变会引起状态码的变更，这对应了不同的业务过程，可以被提取为不同的事务事实表，无论退款发生在哪个结点，支付成功、发货、收货这些业务过程都可以被记录在对应的事务事实表中。

当天订单没有闭环结束的数据量？

在离线数仓中，当天订单没有闭环结束的数据量通常指以下几种数据：以当天 10 万订单为标准

（1）未支付订单：指当天生成的订单，但客户还未完成支付行为的数据。300-500

（2）待发货订单：指当天已经收到付款的订单，但还未处理发货行为的数据。6 万

（3）退货订单：指当天客户发起的退货请求，但商家还未处理完退货流程的数据。200-300

你们维度数据要做 ETL 吗？除了用户信息脱敏？没有做其他 ETL 吗

除了用户信息脱敏，我们还对用户埋点数据中的 IP、UA 等信息进行解析，同时我们对数据进行去重操作，还会对空值以及异常数据进行处理，对于部分维度数据我们将其合并。

怎么做加密，加密数据要用怎么办，我讲的 md5，他问我md5 怎么做恢复

对于 MD5 加密算法，由于它是一种不可逆的散列算法，无法恢复原始数据。

表生命周期管理怎么做的？

表的生命周期管理是数据仓库和数据库管理的一个重要方面，需要关注表的创建、使用、优化和删除等阶段。管理表的生命周期有如下几个阶段：

1）表设计

2）数据导入和更新

3）数据存储和备份

4）数据安全和权限管理

5）性能优化和监控

6）数据归档和删除

当 ADS 计算完，如何判断指标是正确的

（1）样本数据验证：从计算结果抽取部分样本数据，与业务部门实际数据对比。

（2）逻辑验证：检查指标的计算 sql 是否正确

（3）指标间关系验证：比较不同指标间关系，检查它们是否符合预期。例如：某个指标是另外一个指标的累计值，那么这两个指标一定存在关系

（4）历史数据对比：将计算结果和过去数据进行对比，观察指标的变化趋势

（5）异常值检测：检查计算结果中是否存在异常值

（6）跨数据部门对比：可以将计算结果与其它部门或团队的数据进行对比，进一步验证

ADS 层指标计算错误，如何解决

（1）确定错误范围：找出指标计算错误的时间范围，指标及相关维度，缩小排查范围

（2）检查数据处理逻辑：从 ods 层开始排查，找出可能导致计算错误的数据清洗，转换和聚合等步骤，确认处理逻辑不出错误

（3）审查数据质量：确保每层数据完整性，一致性，准确性和时效性

（4）重新计算指标：修复数据质量问题和处理逻辑后，重新计算

任务跑起来之后，整个集群资源占用比例

数仓脚本是串行执行的，ods=》dim=》dwd=》dws=》ads

所以占用集群资源比例，仅为当前执行层的脚本转化成底层 Sparkjob 需要的资源

CPU 与内存比 1:4

离线: 128M 数据 512M 内存 （1 个 CPU 4G 内存 1g 数据）

实时:并行度与 Kafka 分区一致，CPU 与 Slot 比 1:3

20M/s -> 3个分区 -> CPU与Slot比 1:3 -> 3个Slot -> Core数1个 -> CPU与内存比 1:4 -> TM 1 slot -> TM 4G 资源

JobManager 2G 内存 1CPU

平均 一个 Flink 作业 6G 内存，2Core

真实项目流程

1）数仓搭建完成之前：

（1）需求分析：与产品了解需求，明确数仓实现功能

（2）数据源分析：分析数据源是否充足，是否需要引进新数据源

（3）数据模型设计

（4）技术选型

（5）数据处理流程设计：Hivesql

2）数仓搭建完成之后：

（1）开发与维护

（2）数据监控与报警

（3）数据质量管理

（4）性能优化

（5）新需求开发

（6）报表与可视化

（7）文档编写与知识分享

埋点

1）埋点选择

免费的埋点：上课演示。前端程序员自己埋点。

收费的埋点：神策、百度统计、友盟统计。

2）埋点方式主要有两种：

（1）按照页面埋点，有几个页面就创建几个表。

（2）按照用户行为：页面数据、事件数据、曝光数据、启动数据和错误数据。 咱们项目中采用的这种方式。

3）埋点数据日志格式

为了减少网络上数据的传输，日志格式设计时都会有公共信息。

{

"common": { -- 公共信息

"ar": "230000", -- 地区编码

"ba": "iPhone", -- 手机品牌

"ch": "Appstore", -- 渠道

"md": "iPhone 8", -- 手机型号

"mid": "YXfhjAYH6As2z9Iq", -- 设备 id

"os": "iOS 13.2.9", -- 操作系统

"uid": "485", -- 会员 id

"vc": "v2.1.134" -- app 版本号

},

"actions": [ --动作(事件)

{

"action_id": "favor_add", --动作 id

"item": "3", --目标 id

"item_type": "sku_id", --目标类型

"ts": 1585744376605 --动作时间戳

}

]，

"displays": [

{

"displayType": "query", -- 曝光类型

"item": "3", -- 曝光对象 id

"item_type": "sku_id", -- 曝光对象类型

"order": 1 --出现顺序

},

{

"displayType": "promotion",

"item": "6",

"item_type": "sku_id",

"order": 2

},

{

"displayType": "promotion",

"item": "9",

"item_type": "sku_id",

"order": 3

}

],

"page": { --页面信息

"during_time": 7648, -- 持续时间毫秒

"item": "3", -- 目标 id

"item_type": "sku_id", -- 目标类型

"last_page_id": "login", -- 上页类型

"page_id": "good_detail", -- 页面 ID

"sourceType": "promotion" -- 来源类型

},

"err":{ --错误

"error_code": "1234", --错误码

"msg": "***********" --错误信息

},

"ts": 1585744374423 --跳入时间戳