六、数据仓库详细介绍（ETL）经验篇-Toy模板网

这篇具有很好参考价值的文章主要介绍了六、数据仓库详细介绍（ETL）经验篇。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

0x00 前言

日常工作中大多数时候都是在做数据开发，ETL 无处不在。虽然最近两年主要做的大数据开发，但感觉日常干的这些还是 ETL 那点事儿，区别只是技术组件全换了、数据量大了很多。

前几年数仓势微，是因为传统的那些工具数据库等无法解决数据量进一步膨胀带来的计算问题，大数据火爆也是因为当时大数据开发门槛很高。可是最近两年随着大数据技术的成熟开发门槛越来越低了，数据仓库反而重新火起来了。

ETL 的事情就跟 SQL 一样入门很简单，但真要熟练运用也没那么容易，因为这两类技能仅靠理论学习很难掌握，必须不断的实践堆积才行。

为什么有的人开发的程序动不动就延迟、动不动就报错？
为什么有的人开发的程序源端数据稍微来点脏数据就各种数据质量问题？
为什么有的人开发的程序需求稍微一变就各种支持不了？随之而来的各种踢皮球。
为什么有的代码开发者自己都看不懂了？
为什么有的项目各种返工、各种推倒重来？

看完上边罗列的日常开发中常见的现象后，大家还会觉得 ETL 开发简单吗？

最后我们不妨总结下，高水平的数据开发者应该具备什么样的特质？

努力用最简洁的方式实现复杂的需求。复杂的代码理解和维护都很困难，且容易出现性能问题。
考虑功能实现的同时还要考虑性能和稳定性。
花更多的精力在源端数据探查，能够提前考虑导数据源端的各种问题从而开发出相对健壮的 ETL 代码。
充分了解业务，对需求有前瞻性预判，甚至能够辅助需求方做出改进，使得需求更加合理同时 ETL 开发更简单。极端情况下要有 A/B 预案（比如之前就有项目方提出了极不合理的需求又不听劝告，我们只能按需求方需求实现的同时，准备了更好的方案，以便需求方将来更改需求时能够从容应对）。
努力提高代码的可读性。
一般不会出现重大数据质量问题，并且极少的出现 Bug。
程序开发的同时，也能兼顾考虑后续的流程和数据质量监控问题。

0x01 ETL架构与规范

ETL 架构

etl服务器,数据仓库,数据仓库,etl,数据库

ETL 逻辑架构图

etl服务器,数据仓库,数据仓库,etl,数据库

数据架构图

ETL 逻辑架构需要跟数据架构放在一起看：

抽取层：ETL 需要先将源端的数据抽取到数据仓库 ODS 层保持源结构。
集成转换层：基于 ODS 层做集成转换写入数仓明细层或维度层。集成主要是值编码映射、消除冗余、不一致和错误。转换主要是将集成后的数据做进一步处理，以便将数据存储到统一设计的数仓明细层。
特殊业务处理：数据明细层主要为了统一规范化的存储全域数据，但为了更好的支撑下游的各种数据应用，还需要对数据做进一步的加工汇总转换。

抽取加载策略

etl服务器,数据仓库,数据仓库,etl,数据库

抽取加载策略

抽取和加载策略这是数仓面试中经常问的问题（当然有些面试官也会将其统称为 ETL 策略），为了能够更好的结构化表达，我们可以这样描述：

ETL 策略可以分为两类，抽取策略和加载策略。抽取策略我们需要考虑对源端系统的影响以及抽取行为的开销。加载策略我们更多的是考虑对目标端已有数据的影响、数据完整性、重复执行也要保证幂等性。

抽取策略又分为三类：

抽取周期：按小时、按天、按周、按月。抽取周期的选择，主要取决于业务对实时性的要求。周期越短实时性越高但成本也会越高。对于部分实时性要求高的需求每30分钟抽取到 ODS 层然后直接从 ODS 层支撑业务也是一个选择。
抽取时机：这个需要根据抽取周期来定，如果按日抽取的话，我们通常选择在凌晨业务不繁忙的时候进行，同时不建议刚过0点就开始，避免部分数据延迟到达。
抽取方式：增量、全量、新增。全量方式简单粗暴但耗费资源适用于少量数据、需要识别删除数据或者时点值（快照）数据抽取。增量是指新增和变化，我们需要识别数据插入和更新的时间这对源端数据有一定要求，适用于维度数据或者需要记录状态变化的事实数据例如订单。新增主要用于业务流水数据的抽取场景，这些数据在源端只会插入不会更新就算有删除我们也不关心。对于删除数据的识别最好的办法就是要求源端逻辑删除，当然现在我们还可以通过解析数据库日志的方法来处理。

加载策略分为四类：

直接追加：对于抽取方式为新增的情况，我们直接追加即可。
直接覆盖：对于抽取方式为全量抽取，又不需要保留历史变化情况，我们可以采取直接覆盖的方式。但 ODS 层不能使用这种加载策略需要每天新建一个分区，且该任务不可重复执行（在流程控制和运维时候需要特别注意）。
更新追加：对于不需要考虑历史变化，只需要记录最新状态的数据我们采用更新追加的方式。维度表不太重要的属性也可以采用该方式。
历史表加载：对于需要考虑历史变化的数据，我们不能更新追加，必须采用拉链表方式存储。维度表或者事实表的状态变更适合采用此种方式。

两种重要的设计模板

、

etl服务器,数据仓库,数据仓库,etl,数据库

设计模板-数据抽取加载策略

抽取加载策略设计文档，最常用的地方是 ETL 抽取层。

方便数仓开发与源端系统沟通，做为源端系统改造的参考依据、用于评估对源端系统的影响。
做为 ETL 元数据，为数仓开发者提供参考。
是数据同步工作的指导性文档。

etl服务器,数据仓库,数据仓库,etl,数据库

设计模板-ETL数据映射

规范的 ETL 映射设计文档，包含数据流的在不同层次/阶段的映射(Mapping)文档。描述了 ETL 表/字段数据血缘、源表到目标表的映射逻辑，是 ETL 开发的重要指导性文档，同时也是 ETL 元数据的重要组成部分。

我们可以通过系统工具设计，也可以直接使用 Excel 。

0x02 ETL 规范

ETL 规范是为保证 ETL 系统能够正确、稳定、高效运行，保证多人开发过程中的风格一致，而在 ETL 设计、实施和维护环节定义的一系列规则和方法，具体包括 ETL 设计规范、开发规范以及维护规范。

etl服务器,数据仓库,数据仓库,etl,数据库

设计规范

抽取加载策略设计文档

节点名称，与目标表一致
负责人
源表、目标表
抽取方式、加载策略
增量新增数据的判断条件
是否支持重复执行

ETL 映射设计文档

节点名称，与目标表一致
所在层级、所属 Job
Job 中的位置、上游节点名称
负责人
参数列表
源表、目标表
字段映射转换关系
是否支持重复执行

调度设计文档

顶层调度有几个？
顶层 Job 调度时机
顶层调度参数列表
每个 Job 内的任务调度顺序编排
调度是否支持重复执行
调度是否支持并行

开发规范

命名规范

Job 命名规范：J_层次名_内容描述。
节点命名规范：跟目标表一致。
参数命名规范：统一命名，禁止私自创造。

代码编写原则

代码清晰、整齐，具有一定的可观赏性。
代码编写要充分考虑执行速度最优原则。
代码行整体层次分明、结构化强。
代码中应有必要的注释以增强代码的可读性。
规范要求非强制性地约束代码开发人员的代码编写行为。在实际应用中，只要不违反常规要求，允许存在可理解的偏差。

代码开发规范

脚本是否有备注、复杂计算逻辑是否有注释。
任务是否支持多次重跑而输出不变，不能有 insert into 语句。
分区表是否使用分区键过滤并且有有效裁剪。
外连接的过逑条件是否使用正确，例如在左连接的 where 语句存在右表的过滤条件。
关联小表，是否使用/*+ map join * /。
不允许引用别的计算任务临时表。
原则上不允许存在一个任务更新多个目标表。
是否存在笛卡尔积。
禁止在代码里面使用 drop、create、rename 等 DDL 语句。
使用动态分区时，有没有检查分区键值为 NULL 的情况。
对于重要的任务 DQC 质量监控规则是否配置，严禁裸奔。
代码中有没有进行适当的规避数据倾斜语句。

日志输出规范

每个任务节点，都需要输出成功/失败标志，如果失败最好输出错误信息。
每个 Job 也要输出成功/失败标志，如果失败必须输出失败任务节点名称。

部署规范

需要定义清楚，每个工作流应该部署到哪台服务器的哪个目录下边。

需要定义清楚，ETL 服务器的目录结构。

以下是我们当时的 ETL 部署规范（工具是 Kettle，ETL服务器有两台，一台做数据抽取如 ODS 层另一台是剩余的数据处理）。

更多数仓规范，请参考文末的扩展阅读。

0x03 ETL 开发流程

etl服务器,数据仓库,数据仓库,etl,数据库

以上流程适用于数仓的 ETL 系统整体设计、细分模块设计，也适用于日常的数据开发。对于整体设计的数据探查针对的是所有数据源，细分模块设计和日常数据开发针对的是需要用到的表。日常的临时开发如果是一次性的则不需要过多考虑流程依赖和配置调度。

需求理解

我们需要结合对业务对项目的了解，去充分理解需求，明白需求方真正想要什么，并根据自己的专业知识做出有效评估，最好能拿着原型找需求方做个需求确认。我们只有比需求方理解的更透彻深远才更有可能做的更好，减少返工同时得到客户的认可。

数据探查

当我们充分理解需求后，就需要根据需求去寻找需要的数据了。如果是数仓整体设计我们需要对源端数据充分了解；如果是日常数据开发我们需要非常熟悉手边的数据或数据仓库中的表，实在不行可以找更熟悉的人去咨询。

当找到“需要的数据”后，在程序开发（或写SQL）前还需要深入的数据探查，去考察数据内容、数据质量以及数据存储结构。否则容易造成两类常见的后果：使用了错误的数据或者错误的使用了数据。

数据探查，需要我们摸清以下几方面内容：

简单看下表的元数据信息：重点关注命名、备注、字段类型、最后更新时间等。有时候也需要关注数据量级、数据增量等。
简单拿几条数据出来看看：我们需要用到的列是查看的重点，同时要确认真实数据跟元数据信息是否一致，不一致的要进一步求证（是自己理解错误？还是脏数据影响？还是元数据错了？）。
对关键字段需要查询下分布情况，如果发现跟常识或者跟自己对业务的理解存在偏差就需要进一步求证（看是数据问题？还是自己认知问题？还是单位或计算口径问题？）。空值率、异常数据等在这一步也能被发现。
着重看下数据的完整性：比如数据内容（或列）缺失、数据条数缺失等。
看看有没有其它数据质量问题：比如重复数据、脏数据、编码映射问题等。
需要使用多张表的时候，要确认好 join 字段，并确保两表之间是 1:m 的关系，m:n 的关系很少碰到并且多数情况下是自己用错了。
大致评估出实际的开发工时：需要参考的因素如下，需求的复杂度、数据源的理解探查难度、数据源的数据质量（是否需要特别的清洗、编码映射、缺失数据补全）、数据存储结构转换成最终需求的复杂度（有的需要分多步落中间表完成，有的源表数据直接就是最终需要的数值）、自身能力等等。

程序开发

需求理解透彻、数据探查这两步算是分析阶段了。经过前两步的充分分析后，具体的开发实现就是很简单的事情了。我们按照上文“代码开发规范”的要求，努力拿文章开头总结的“高水平的数据开发者应该具备的特质”去要求自己，努力将分析阶段想好的实现方案高质量、高效率落地即可。这同样也是个纯经验问题，一年两年三年多少都还是会有些差别的。

最后，我重新再列几点比较重要的：

充分实现需求还是第一位。
尽可能提高代码的可读性。
用最简单的方式实现。
开发健壮性的代码。特别是当使用别人写入的表的时候，为防止别人操作不规范，我们就要考虑是否需要去除空格、去重、空值的处理等等。
预估或测试性能问题。
结合加载策略，考虑重复执行的幂等性问题。
如果需要上调度，还要考虑传入参数的问题。

流程依赖

如果需要程序自动执行，就要考虑流程依赖了。根据节点间的依赖关系、每个节点的资源消耗和每个节点的执行耗时，去合理编排流程。有依赖的必须串行，无依赖可以并行用于降低总执行时长，当然我们也要明白任务并行会增加瞬时资源消耗。

此外我们还应该清楚一点：流程依赖里配置的执行先后顺序，后执行的不一定就真的依赖于先执行的任务节点。工作流的编排逻辑是上边提到的三方面因素的综合考量结果。

最后，除了工作流编排外，我们还需要考虑以下三点：

执行过程中的日志记录。
补数、重跑情况下的参数传递问题。内层工作流或底层节点绝对不能把参数（主要是日期）写死，我们需要在调度工作流的时候，工作流内的所有节点都能接受到来自外层传入的参数。
尽量的将可以重复跑的和不能重复跑的节点放到不同的工作流。
尽量的将可并行补多个日期数据的节点跟不能并行补数的节点放到不同的工作流。

配置调度

具体到调度层面，我们需要把每个工作流都打上如下标签（使用说明书）：