六、数据仓库详细介绍（ETL）方法篇

0x00 前言

上文我们把数据仓库类比我们人类自身，数据仓库“吃”进去的是原材料（原始数据），经过 ETL 集成进入数据仓库，然后从 ODS 开始逐层流转最终供给到数据应用，整个数据流动过程中，在一些关键节点数据会被存储存储下来落入数仓模型。在数仓这个自运转的大生态系统中 ETL 扮演了原材料加工转化和能量传输两个重要角色，有了 ETL 的加持这个生态系统才开始活起来了。

在数据仓库领域，ETL 的重要性有时候甚至比数仓模型还高，ETL 水平的高低通常能决定数仓的下限（决定上限的主要是数据应用和源端数据质量）。对于数据仓库从业者或者数据开发，其实大多数情况下做的都是 ETL 的事情。很多数仓建设根本不需要特别高深的建模知识，这时候数仓建模都是 ETL 工程师搞定的：谁需要谁设计、谁用谁知道，不好用的时候自己就会去改进优化。

不过对于更规范的公司会有专门的模型设计师，但 ETL 工程师也一定要好好把关，因为不好用的模型（对需求的支撑、对计算资源的消耗、必要字段的缺失、ETL 使用字段比如摄入时间的缺失、幂等性、多天并行执行的可能性等等）会带来很多后续的严重问题（系统健壮性问题、性能问题、数据质量问题等等），而这些问题只要出现，通常都会归咎于数据开发，而非数仓建模师。

0x01 什么是 ETL

概念和理解

ETL（Extract-Transform-Load），用来描述将数据从来源端经过抽取、转换、加载至目标端的过程。ETL 一词较常用在数据仓库，但其对象并不限于数据仓库。

ETL 是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据， ETL 是BI（商业智能）项目重要的一个环节。

ETL 所描述的过程，一般常见的做法包含 ETL 或是 ELT（Extract-Load-Transform），并且混合使用。通常越大量的数据、复杂的转换逻辑、目标端为较强运算能力的数据库，越偏向使用 ELT，以便运用目标端数据库的平行处理能力。

ETL（or ELT）的流程可以用任何的编程语言去开发完成，由于 ETL 是极为复杂的过程，而手写程序不易管理，有越来越多的企业采用工具协助 ETL 的开发，并运用其内置的 metadata 功能来存储来源与目的的对应（mapping）以及转换规则。

工具可以提供较强大的连接功能（connectivity）来连接来源端及目标端，开发人员不用去熟悉各种相异的平台及数据的结构，亦能进行开发。

当然，为了这些好处，付出的代价便是金钱。

好吧，以上百度百科的内容把我想说的基本都说完了，并且没有一句多余的话，相比网上其它作者写的也要全面的多。

这里我再做些总结：

• ETL 概念常见于数据仓库，也是 BI（商业智能）项目重要的一个环节，实际上干的是数据集成、数据加工的事情。

• ETL 事实上已经被抽象成一种方法论，我们把它看作一个 IPO（In-Process-Out）过程可能会更好理解些：数据抽取过来后，经过加工处理然后输出。这个 IPO 过程被称为流水线，众多的流水线以一种合理的方式连接起来（串行/并行）就是一个完整的 ETL 过程。因此在数仓之外 ETL 依然有着广泛的适用场景。虽然看似大数据使得 ETL 工程师这个岗位消亡了，但是所有的数据开发岗是否还是做的 ETL 的事情呢？

• ETL 的三个操作在顺序上不做强制，可以根据实际情况去调整。如果源端计算能力强大我们可以使用 TEL，如果目标端计算能力强大我们就使用 ELT，如果我们的 ETL 工具很强那就可以采用 ETL。生产实践我更倾向于使用 ELT：源端通常是业务系统或者外部系统我们不太可能使用 TEL，使用 ETL 就需要深入研究 ETL 工具以便能最大化的在保证稳定性的前提下提高效率这会增加额外的学习成本，同时我们还需要把数据从数仓抽取到 ETL 服务器处理完还要再加载会数仓这一入一出带来的网络消耗也得考虑进去。

• ETL 工具使得 ETL 开发变的简单高效、流程依赖得到了更好的控制、有些 ETL 工具记录下来的转换规则运行日志等可以直接做为 ETL 元数据这让 ETL 运行监控变的更加容易。因此，虽然工具的引入带来了一定的费用和学习成本但我们通常认为这些都是值得的。

核心构成

数据仓库有两部分职责：集中存储企业全量的历史数据、对外提供统一一致的数据服务。

完整的 ETL 主要包含三部分内容：集成、计算、流程控制。

这里的集成，就是把源端多个异构数据源的数据集成到数据仓库中，主要包含三部分内容：消除冗余、纠正错误、打破孤岛。

这里的异构指的是数据来源的多样化：可以是数据库、也可以是文件、也可以是手工录入。当然数据库也可以是异构的，比如部分来自 Oracle 部分来自 MySQL。

这里的计算，主要特指数据被集成进数据仓库后在数仓内部的流转过程。

集成和计算又都可以拆分为许多个原子 ETL 过程，我们称之为节点。我们需要根据数据流向上的前后依赖关系，以一种合理的分类方式组装串联起来，而这个过程就叫做流程控制，也叫装配方案。

从整体 ETL 流程上看，装配方案可能会有多个而且是分段、分层的。

• 比如数据集成通常会按数据源设计独立的装配方案分开调度，数据集成完成后才会执行后续的计算。
• ODS 往后的计算流程，会根据节点间的依赖关系等拆分成多个装配方案，一级装配方案下可能还会接二级装配方案，这样所有的节点依赖都会被装配方案统一管理起来确保数据能够按正确的顺序往下流动，同时方便流程监控、错误重跑。

节点间的依赖主要通过装配方案来控制，装配方案间的依赖控制主要是以下三种:

1. 上游成功后调用下游。
2. 预估上游执行所需时长，定时启动下游任务。
3. 下游定时启动后先判断上游的多个依赖是否全都跑位如果没有就循环等待。

0x02 生产案例展示

以下是我前些年做过的一个 2B 项目上的案例，我摘抄了一部分纯技术的分享给大家。

设计方法千千万万，具体到别的其他项目我们还是要从实际出发重新设计的。

上边是最外层的 ETL 调度。总共分为 5 个总体流程。

• 第一个是主流程，数据从源端流入 ODS 再到 DW 再到 DM，最后边的 DWDM2 是我们的一些分析挖掘探索，为了不影响正常业务所有放到了最后边单独执行。
• 第二个流程是我们的埋点日志，我们每日调度读取前一天的日志文件然后逐层往后计算，支撑网站页面布局、操作流程等优化场景。
• 第三个流程是实时库存计算，每三个小时全量同步一次，计算完后通过数据服务开放给业务系统页面查询。
• 第四个流程之所以存在，跟当时的源端业务操作有关，因为源端在每日十点会有个开闭店动作这会引起部分关键数据的变化，所有会在十点做针对性的处理。
• 第五个流程类似于支撑性质的流程任务。除了 ETL 日志备份，我们其实还可以做些别的事情，比如存储空间检测、数据质量校验等等。

上边这个是我们 ETL 主流程的第一层装配方案。每一阶段的前后都会记录执行日志，这些日志元数据是后续 ETL 流程监控和数据质量的核心依据。

上边是我们 ETL 主流程的第二层装配方案，隶属于第一层装配方案的第二阶段 DW 层数据处理。

上边是我们 ETL 主流程的第三层层装配方案，隶属于第二层装配方案 DW 阶段里的第二阶段事实表处理。由于涉及的节点太多，一屏无法完全展示这里仅仅截取了一部分。

我们当时的设计事实上类似于 ELT，其算力主要依靠数据库，ETL 工具只是起到了流程控制的作用。由于 ETL 调度通常是半夜启动数据库相对比较空闲，我们才敢于同时并行很多个节点任务。如果数据库资源消耗过重我们就还得考虑减少并行节点个数，这样 ETL 设计的复杂度肯定还会更高。

0x03 传统数仓 ETL 实现方式

传统数仓的特点：

• 数据源都是结构化数据，通常是存在数据库里，有时候也会是文件。

• 数据仓库通常采用关系型数据库。

• 实时性要求不高，基本上都是 T+1，偶尔会是 15 分钟。实时需求通常会直接到源端系统查询。

ETL 实现方式通常分为三类：

• 完全依赖数据库。集成使用 Gateway、GoldenGate 、数据库 load 命令等实现。计算使用 SQL 甚至存储过程实现。流程依赖使用 Shell、存储过程或者 Java 开发依赖配置规则。
• 自研 ETL。通常会开发一套 web 页面实现多种数据源连接、SQL 编辑、流程控制、数据上传下载、参数传递、错误告警、调度等功能。
• 第三方 ETL 工具。这类工具会提供多种不同类型的组件（transform 组件、load 组件、output 组件、流程控制组件等等）和一套图形化配置界面，我们可以在图形化界面里选择合适的组件完成集成和计算，然后通过对组件的合理排布实现流程控制。我们如果想借助数据库的算力就更多的使用 SQL 脚本组件和数据集成和流程控制的功能。如果我们也想使用其计算 Transform 功能就需要给 ETL 服务器分配足够的资源且对 ETL 工具有非常深的了解以便尽可能的提高 ETL 效率和稳定性。

生产实践，应该选择实现方式呢？我认为参考依据主要有以下六点：

• 流程依赖关系的复杂度。复杂度高的必须使用 ETL 工具。
• 根据实际情况去选择。如果是一次性的工作就没必要花太多心思去调研了，直接用最擅长的方式高效率高质量出活即可。比如之前一个系统割接项目我们就是使用几十个存储过程来实现的，而当时的老系统异构数据库数据同步使用的是 shell + 存储过程（估计是几个运维背景的人做的）。
• 团队成员对相关技术的熟练程度。记得之前公司有一个自研的 ETL+调度的系统用着也挺好的。
• 如果是项目制的话得看甲方是否有明确要求，比如之前做的项目甲方明确要求使用 ODI，据说是找高校的专家给出的方案，没办法我们只能现学现用。
• 看项目预算。有预算的话可以采购 ETL 工具，无预算的话可以使用 Kettle 或者依赖数据库。

0x04 大数据时代 ETL 实现方式

传统数仓到大数据仓库的发展，数仓理论没变，ETL 理论也还是之前的那些，但环境和技术却发生了很大的变化。

环境的变化：

• 线下到线上业务场景的转变。线下场景数据生成和使用通常会在特定时间下进行而且夜间通常是没人用的，而线上是随时随地。
• 2B 到 2C 客户群体的转变。用户规模、用户喜好的多样性、客户端类型的复杂性、源端数据质量等等都面临挑战。
• 部分场景对时效性要求更高了。估计是技术的快速进步，数据应用早已不仅仅限定在事前预测和事后分析了，部分场景还要求实时互动和监控，比如推荐、营销效果监测、告警、风控等等。
• 移动互联网的普及造成数据规模的突增。比如之前 Top1 级别的院线日订单量也就五六十万，现在互联网过千万过亿的比比皆是。而且数据内容也开始出现了半结构化数据，数据缺失的情况也很常见了。

技术的变化：

• 大数据技术的快速发展成熟。从最初的 Mapreduce 到 Hive 再到 Spark、Flink。数据库也从 Hbase、MongoDB 发展到了 Clickhouse、Doris。性能稳定性易用性等都有很大改善。
• 开源思想的深入人心。之前 ETL 工具、数据库等都是工具产品型公司独立研发出来的，而他们需要花费巨大的人力物力，通过很长时间的迭代优化。而现在开源思想深入人心，几乎所有互联网公司都开始拥抱开源甚至二次开发，手撕源码成为大部分技术极客的标配。

虽然，本质上没有超出之前的范畴（还是那三类：依靠数据库、自研、ETL 工具），但大数据时代的 ETL 实现方式特点已经非常明显了。

集成、计算和流程控制三部分各自都已经孵化出来了很好的组件。

• 数据集成同步工具：Flume、Sqoop、DataX、Canal、Waterdrop、Streamsets

• 数据计算组件：MapReduce、Tez、Spark、Flink，当然 Waterdrop 和 Streamsets 也能实现很多计算操作

• 流程控制组件：开源组件里的流程控制主要是在调度系统中实现的，比如 DolphinScheduler、airFlow、azkaban。当然对于简单的流程依赖我们也同样可以使用 Shell 或者程序实现。

• 数据库组件：Hive、Hbase、druid、Kylin、ClickHouse、Doris 等等。当然这些基本都是支持 SQL 的，这时候我们完全依赖数据库（数据库+程序/Shell/调度系统）也是不错的选择。