ETL，Extract-Transform-Load的缩写，是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程。ETL是数据集成的第一步，也是构建数据仓库最重要的步骤，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

ETL涉及三个独立的过程：抽取、转换和加载。

ETL的工作流程

数据抽取

数据抽取指的是从不同的网络、不同的操作平台、不同的数据库和数据格式、不同的应用中抽取数据的过程。目标源可能包括ERP、CRM和其他企业系统，以及来自第三方源的数据。在这个过程中，首先需要结合业务需求确定抽取的字段，形成一张公共需求表头，并且数据库字段也应与这些需求字段形成一一映射关系。这样通过数据抽取所得到的数据都具有统一、规整的字段内容，为后续的数据转换和加载提供基础，具体步骤如下：

• 确定数据源: 需要确定从哪些源系统进行数据抽取
• 定义数据接口: 对每个源文件及系统的每个字段进行详细说明
• 确定数据抽取的方法: 是主动抽取还是由源系统推送？是增量抽取还是全量抽取？是按照每日抽取还是按照每月抽取？

数据转换

数据转换实际还包含数据清洗的工作，需要根据业务规则对异常数据进行清洗，主要将不完整数据、错误数据、重复数据进行处理，保证后续分析结果的准确性。

数据转换就是处理抽取上来的数据中存在的不一致的过程。数据转换一般包括两类：

• 第一类: 数据名称及格式的统一，即数据粒度转换、商务规则计算以及统一的命名、数据格式、计量单位等
• 第二类: 数据仓库中存在源数据库中可能不存在的数据，因此需要进行字段的组合、分割或计算。

主要涉及以下几个方面：

• 空值处理: 可捕获字段空值，进行加载或替换为其他含义数据，或数据分流问题库
• 数据标准: 统一元数据、统一标准字段、统一字段类型定义
• 数据拆分: 依据业务需求做数据拆分，如身份证号，拆分区划、出生日期、性别等
• 数据验证: 时间规则、业务规则、自定义规则
• 数据替换: 对于因业务因素，可实现无效数据、缺失数据的替换
• 数据关联: 关联其他数据或数学，保障数据完整性

数据加载

数据加载的主要任务是将经过清洗后的干净的数据集按照物理数据模型定义的表结构装入目标数据仓库的数据表中，如果是全量方式则采用LOAD方式，如果是增量则根据业务规则MERGE进数据库，并允许人工干预，以及提供强大的错误报告、系统日志、数据备份与恢复功能。整个操作过程往往要跨网络、跨操作平台。

在实际的工作中，数据加载需要结合使用的数据库系统（Oracle、Mysql、Spark、Impala等），确定最优的数据加载方案，节约CPU、硬盘IO和网络传输资源。

ETL 和 ELT 的区别

ETL（提取、转换、加载）和 ELT（提取、加载、转换）的核心区别在于数据转换发生的顺序和位置。ETL 在将数据写入目标系统（如数据仓库）之前进行转换；而 ELT 则是先将原始数据直接加载到目标系统，之后再利用系统的算力进行转换。

维度	ETL（提取、转换、加载）	ELT（提取、加载、转换）
转换位置	加载到目标库之前（通常在临时服务器）	加载到目标库之后（在数据库或数据湖内完成）
加载速度	较慢（因需等待所有数据转换完成）	极快（原始数据直接快速入库）
存储成本	较低（转换后才入库，只存需要的数据）	较高（保留了海量原始数据，可随时回溯）
系统依赖	依赖专用的 ETL 工具或服务器算力	严重依赖云数据仓库的计算性能（如 Snowflake, BigQuery）
灵活性	较低（业务逻辑变化时，重新处理成本高）	极高（支持随时调整下游分析模型，保留原始数据）

如何选择 ETL 和 ELT：

• 选择 ETL 的场景: 适合处理敏感数据（需在入库前脱敏），或使用本地传统关系型数据库（存储和算力受限），以及旧系统迁移的固定场景。
• 选择 ELT 的场景: 现代化大数据架构的首选。当企业使用云数据仓库、数据湖，且需要处理海量多结构数据、支持实时/近实时商业智能（BI）分析时，通常推荐采用 ELT。

做好 ETL 设计

数据抽取设计

数据的抽取需要在调研阶段做大量工作，要搞清楚以下几个问题：

1. 数据是从几个业务系统中来？
2. 各个业务系统的数据库服务器运行什么DBMS？
3. 是否存在手工数据，手工数据量有多大？
4. 是否存在非结构化的数据？

等等类似问题，当收集完这些信息之后进行数据抽取的设计。

常见的数据抽取设计方式有四种：

• 与存放DW (Data Warehouse) 的数据库系统相同的数据源处理方法: 这一类数源在设计比较容易，一般情况下，DBMS(包括SQLServer，Oracle)都会提供数据库链接功能，在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select 语句直接访问。
• 与DW数据库系统不同的数据源的处理方法: 这一类数据源一般情况下也可以通过ODBC的方式建立数据库链接，如SQL Server和Oracle之间。如果不能建立数据库链接，可以有两种方式完成，一种是通过工具将源数据导出成.txt或者是.xls文件，然后再将这些源系统文件导入到ODS中。另外一种方法通过程序接口来完成。
• 对于文件类型数据源 (.txt，.xls): 可以培训业务人员利用数据库工具将这些数据导入到指定的数据库，然后从指定的数据库抽取。或者可以借助工具实现，如SQL SERVER 2005 的SSIS服务的平面数据源和平面目标等组件导入ODS中去。
• 增量更新问题: 对于数据量大的系统，必须考虑增量抽取。一般情况，业务系统会记录业务发生的时间，可以用作增量的标志，每次抽取之前首先判断ODS中记录最大的时间，然后根据这个时间去业务系统取大于这个时间的所有记录。利用业务系统的时间戳，一般情况下，业务系统没有或者部分有时间戳。

数据清洗处理规范