数据的搬运工——ETL

作者 | 李谦恒

数据工程师。逻辑重于代码，高效胜过勤奋。崇尚life work balance。

ETL 开发是数据工程师必备的技能之一，在数据仓库、BI 等场景中起到重要的作用。但很多从业者连 ETL 对应的英文是什么都不了解，更不要谈对 ETL 的深入解析，这无疑是非常不称职的。

本文将对 ETL 进行简单介绍。如有疑问欢迎共同讨论。

ETL 简介

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、**加载（load）**至目的端的过程。

ETL 一词较常用在数据仓库，但其对象并不限于数据仓库。

因为 ETL 更多的是一个抽象的概念，因此你可以用任何的编程语言来完成开发——无论是 python、java 甚至数据库的存储过程，只要它最终是让数据完成抽取——转化——加载的效果即可。但由于ETL是极为复杂的过程，而手写程序不易管理，有愈来愈多的企业采用工具或抽象成类来实现开发、管理。

一个好的 ETL 设计，应当具备以下特点

• 高效运行：整个 ETL 的运行时间不应过长，如无特殊场景，1小时就应该运行结束。建议使用增量更新的方式来提升效率。

• 易于维护：ETL 需要长期的稳定运维，但 ETL 工程师却并非那么稳定，因此设计时必须考虑到未来的维护。

  可以将 ETL 的每个步骤进行拆分——每个步骤都是一个原子业务操作。这样也许会降低部分效率，但会大幅度提升其他人接手的运维方便。

  如下图：任何人都能很清楚的看懂整个 ETL 的处理链路。后续的维护修改十分方便。

  

• 运行监控：针对 ETL 程序的运行应该有相关的管理和监控工具。一方面用于 ETL 的设置和调整，另一方面也是方便在 ETL 处理出现异常时能够及时通过人工的方式进行干预。保证ETL的正常运行。

• 异常处理：设计时需要考虑各种异常的情况；例如网络断了、数据库宕机、调度失常等等。同时，ETL 设计要考虑数据的自修复能力：不管 ETL 程序发生任何异常，都能够回到抽取前的状态，而不需要人工干预，更不能影响到已抽取的数据。

ETL 与任务调度系统

ETL 和任务调度系统往往同时出现，但他们并不是等价的；ETL 更强调的是数据的读取、转换、加载。而什么时候运行 ETL、多久运行一次、它的上下游依赖等信息，大部分时间由任务调度系统来控制。

当然，大部分 ETL 也都拥有最基础的调度功能；大部分任务调度系统也可以实现最基础的 ETL 。（例如 airflow 可以通过调用 python 来实现 ETL）

ETL 与数据仓库

数据仓库是由不同的分层（ODS、DW 等）组成，不同的分层间数据完全隔离。数据在不同分层中的流转，就是通过 ETL 来实现。

由于数据仓库非常依赖 ETL，因此大多数时候提到 数据仓库 就会很自然的说到 ETL。

ETL 一词较常用在数据仓库，但其对象并不限于数据仓库。

ETL 与 BI

良好的 BI 设计，对应的数据源一定是精心设计的中间表。而要从复杂繁琐的底层表里获得精心设计的中间表，ETL 是不可缺少的环节。

Extract——数据抽取

万事开头难。Extract 是 ETL 的第一步，设计的是否严谨直接影响到整个 ETL 的实用性，必须予以最高重视。

源数据

源头数据的格式，直接影响到我们抽取的方式。一般源数据格式如下：

1. RDBMS（关系型号数据库）：例如 mysql，这种数据我们可以直接通过 jdbc + SQL 获取到数据，最为省事。

2. NOSQL（非关系性数据库）：例如 mongodb，获取的话需要一定方法。

3. 文本文件（txt,csv,xlsx）：有时候数据也会以文本文件的形式存储。

4. 日志文件（.log）：日志文件也是重要的数据源

90%的 ETL 都只涉及到结构化数据——使用 SQL 都可以直接抽取。针对 非结构化的数据 可以先使用 python 或其他工具将其转化成结构化数据，然后再进行抽取。

部分 ETL 工具也可以直接获取文本、Mongdb 等非结构数据。

抽取频次

大多数时候，我们都建议使用增量抽取的方法。

增量抽取，指在进行插入、更新操作时，只更新需要改变的地方，不需要更新或者已经更新过的地方则不会重复更新。

• 如果没有特殊要求可以一天一次，但是需要避开拉去日志的高峰期

• 对于有实时性要求的日志，可以一小时一次，或者直接使用kafka等相关工具收集，需要考虑到系统能否承受

Transform——数据转换

严格的说，ETL 的 T 步骤应分为两部分：数据清洗（Cleaning）和数据转换（Transform）。

Cleaning

顾名思义，就是把不需要的和不符合规范的数据进行处理。数据清洗最好不要放在抽取的环节进行，考虑到有时可能会查原始数据。一般各公司都会有自己的规范，以下列出几点仅供参考

Cleaning 的任务就是将不符合要求的脏数据清洗为我们需要的干净数据；脏数据主要分为缺失数据、错误的数据和重复的数据三大类。

• 缺失数据

  此处主要指本不该缺失但却出现缺失的数据，对于 ETL 来说保证数据的准确性是第一位，我们需要做的就是将缺失的数据反馈给业务方和后端的研发，在他们修复缺失数据后重新提取。

  在数据分析、数据挖掘等其他场景中，缺失值的处理方式可能与 ETL 并不相同。这是由出发点不同而决定的。

• 错误数据

  错误的数据比较笼统，比如手机号字段里出现了人名，亦或是日期的字段存的是一个 int。这种错误的发生大多是因为业务系统产生的 bug，笔者依旧建议将该类数据反馈给研发，解决后再提取。

  但如何找出错误的数据也是一个问题，这里需要一些基础的规则来帮助判断。

• 重复数据

  大多数情况下不会出现重复数据的，提取的时候加一下去重规则即可

数据清洗深挖了也是极度复杂的，直接涉及到 数据质量体系 和 数据治理 这两大块，但是在普通的 ETL 中，我们不需要过高的要求，简单处理即可。

Transform

Transform 的任务主要是进行不一致的数据转换。

• 验证数据正确性；主要是把不符合业务含义的数据做一处理，比如，把一个表示数量的字段中的字符串替换为 0，把一个日期字段的非日期字符串过滤掉等等；

• 规范数据格式；比如，把所有的日期都格式化成yyyy-MM-dd HH:mm:ss的格式等；

• 数据转码；把一个源数据中用编码表示的字段，通过关联编码表，转换成代表其真实意义的值等等；

• 数据标准，统一；比如在源数据中表示男女的方式有很多种，在抽取的时候，直接根据模型中定义的值做转化，统一表示男女；

• 行列转化等等

Trasform 的具体应用场景自然不止上文提及的这几种，往深了说也是十分复杂的。具体应用方法还得视具体需求而定。

Load——数据加载

Load 即为将数据输出至指定的库表中，相对来说比较简单，但是要注意对数据的结果进行审核——确定输出后的数据量级、质量是正确无误的。

ETL的细节

在上面只是对ETL三者的概念进行了简单的介绍，实际在开发中还有很多细节要注意。

ELT与ETL

根据转换转换发生的顺序和位置，数据集成可以分为 ETL 和 ELT 两种模式。ETL 在数据源抽取后首先进行转换，然后将转换的结果写入目的地。ELT 则是在抽取后将结果先写入目的地，然后由下游应用利用数据库的聚合分析能力或者外部计算框架，例如 Spark 来完成转换的步骤。

严格意义上来说，数据仓库其实是 ELT架构而非 ETL 架构——数据不做任何修改的load进ODS层，然后再进行各种转化处理。

如果是采用 ETL 的架构的话，那T的步骤就只能依赖于 ETL 工具，而且数据大概率在内存中——可能会 OOM。

如果是采用 ELT 的架构，那么T也可以使用 SQL 语句来实现了。

全量更新与增量更新

全量更新和增量更新是数据同步的两种主要方法。

全量更新

顾名思义，全量更新就是周期性定时（一般是每天的夜里）全量把数据从一个地方拷贝到另外一个地方；这种全量同步的方式的优点在于数据的一致性较高。但随着数据量的逐步加大，全量更新所耗费的时间、资源都会逐步增大。最后甚至无法完成。

因此全量更新适用于数据量较小且不会轻易变动的库表

增量更新

我们一般所说的增量更新，指的是只插入（insert）不更新的情况。增量更新的优点在于需要处理的数据量更少，运行效率更高。但长久来看，增量的数据一致性相比全量较差。且重跑历史数据时可能会较为复杂。

但需要注意的是，大部分表并不只存在 insert 语句。可能还会存在 update，甚至物理删除的情况。同步时需小心

尽可能使用增量更新的方式来完成数据同步。

实时(流式)ETL

在建立数据仓库时，ETL 通常都采用批处理的方式，一般来说是每天的夜间进行跑批。

随着数据仓库技术的逐步成熟，企业对数据仓库的时间延迟有了更高的要求，也就出现了目前常说的实时 ETL（Real-Time ETL）。实时 ETL 是数据仓库领域里比较新的一部分内容。

实时 ETL 的主要难点在于数据的聚合和缓慢变化维的处理。需要考虑到与批处理结果的兼容。

ETL的元数据管理

元数据是 ETL 中非常重要的一部分。通常来说，我们可以把元数据分为三类，分别为业务元数据（Business Metadata），技术元数据（Technical Metadata）

• 业务元数据：本次ETL的业务目的？牵涉到的指标、维度含义。

• 技术元数据：比如目标表的表结构、本次 ETL 的加载时长、消耗内存等。

对于元数据管理不应该是规范，应该是硬性规定。

常用的etl工具

kettle

是一款国外开源的ETL工具，纯java编写，可以在Windows、Linux、Unix上运行，数据抽取高效稳定。

kettle同时支持windows、linux和macos等操作系统

kettle家族目前包括4个产品：Spoon、Pan、CHEF、Kitchen。

• Spoon：允许你通过图形界面来设计ETL转换过程（Transformation）。

• Pan：允许你批量运行由Spoon设计的ETL转换 (例如使用一个时间调度器)。Pan是一个后台执行的程序，没有图形界面。

• CHEF：允许你创建任务（Job）。任务通过允许每个转换，任务，脚本等等，更有利于自动化更新数据仓库的复杂工作。任务通过允许每个转换，任务，脚本等等。任务将会被检查，看看是否正确地运行了。

• Kitchen：允许你批量使用由Chef设计的任务 (例如使用一个时间调度器)。KITCHEN也是一个后台运行的程序。

但要注意：kettle的内存释放极差，一定要监测kettle的内存使用情况。

SSIS

SSIS是Microsoft SQL Server Integration Services的简称，是微软开发的一款生成高性能数据集成解决方案（包括数据仓库的提取、转换和加载 (ETL) 包）的平台。

SSIS的开发使用和kettle类似，但由于kettle是免费开源，且网络上的相关文档中，kettle远比SSIS丰富。因此大部分时间中，用户都会选择使用kettle来作用ETL工具。

全文完

以下文章您可能也会感兴趣：

• WePY 2.0 新特性

• SSL证书的自动化管理

• 了解一下第三方登录

• 分布式 ID 生成策略

• 单元测试的实践之路

• 可线性化检查：与 NP 完全问题做斗争
  

• Java 类型系统从入门到放弃
  

• Webpack 快速上手（下）
  

• Webpack 快速上手（中）
  

• Webpack 快速上手（上）

我们正在招聘 Java 工程师，欢迎有兴趣的同学投递简历到 rd-hr@xingren.com 。