浅谈ETL

前言

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的处理链路。后续的维护修改十分方便。

ETLdemo.png

• 运行监控：针对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 来完成转换的步骤。

img

严格意义上来说，数据仓库其实是 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上运行，数据抽取高效稳定。

img

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) 包）的平台。

img

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