ETL基础概念及要求详解

概念

ETL即Extract（抽取）Transform（转换）Load（装载）的过程，如下图，同时也包括数据清洗的部分，是数据从源端到目标端的一个数据处理的过程，是构建数据仓库的重要一环，但不局限于构建数据仓库其他BI（商业智能）的设计和开发数据分析平台也经常用到ETL。

在进行ETL作业时按作业进行的加工顺序又分为ETL和ELT。

ETL与ELT

ETL可以通过采用单独的硬件服务器分担数据库系统的负载，相对于ELT架构可以实现更为复杂的数据转化逻辑，同时与底层的数据库数据存储无关。

ELT先加载后装换，如下图所示，其充分利用数据库引擎来实现系统的可扩展性（当数据加工过程在晚上时，可以充分利用数据库引擎的资源)，也可以保持所有的数据始终在数据库当中，避免数据的加载和导出，从而保证效率，提高系统的可监控性。同时可以根据数据的分布情况进行并行处理优化，并可以利用数据库的固有功能优化磁盘I/O。通过对相关数据库进行性能调优，ELT过程获得3到4倍的效率提升比较容易。

数据湖与数据仓库

简单来说，数据湖是所有未经处理的源数据集合，可以是非结构化的，数据仓库是经过结构化、过滤等处理后的数据集合。
数据湖：Data Lake，可简写为DL，是一个数据存储库。现在企业的数据仓库都会通过分层的方式将数据存储在文件夹、文件中，而数据湖使用的是平面架构来存储数据。我们需要做的只是给每个数据元素分配一个唯一的标识符，并通过元数据标签来进行标注。当企业中出现业务问题时，可以从数据湖中查询数据，然后分析业务对应的那一小部分数据集来解决业务问题。

数据仓库：Data Warehouse，可简写为DW或DWH。数据仓库是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。主要包括以下三类：
（1）企业数据仓库 (EDW) ：充当帮助企业内决策支持服务的主数据库。EDW 提供对跨组织信息的访问，一种数据表示的集成方法，并且可以运行复杂的查询。
（2）操作数据存储 (ODS)：ODS 实时刷新并用于运行例行任务，包括存储员工记录。存储在这里的数据可以被清理，冗余检查和解决。它还可以用于整合来自不同来源的对比数据，以便业务运营、分析和报告能够顺利运行。
（3）数据集市：数据集市是数据仓库的子集，因为它存储特定部门、地区或业务单位的数据。数据集市有助于增加用户响应并减少分析数据量。此处的数据存储在 ODS 中，然后ODS将其发送到 EDW，并在其中存储和使用。

ETL应用场景

数据的生命周期如下，ETL技术贯穿其整个生命周期，而无论是数据是平台（数据仓库平台、数据交换平台、大数据平台、历史数据平台）还是数据分析过程（数据挖掘、模拟实验室、传统BI、推荐引擎）都逃离不了数据的生命周期六过程，因此都需要应用ETL技术。

ETL具体流程及操作要求

抽取

在数据抽取中需要考虑以下问题：
1.数据是从几个业务系统中来？
2.是否存在手工数据？
3.各业务系统数据库DBMS(数据库管理系统)是什么?(考虑同构、异构)
4.是否存在非结构化数据？
5.能否支持增量抽取？
6.是否支持工具抽取？

清洗

数据清洗主要清洗三类不符合要求的数据：
1.不完整的数据（字段缺失）
2.错误的数据（乱码、字段定义与内容不符）
3.重复的数据

要求：反复清洗、发现问题、解决问题（是否过滤或修正一般要求与客户确认）

转换

数据转换主要包含三类：
1.代码标准化

2.数据粒度转化
数据粒度及数据的细化程度，例如省县乡三级分划，只到省级的数据相比于到乡级的数据细化程度更低，数据粒度大。

ETL过程中依据需求可能需要将小粒度的业务数据聚合为大粒度的数据。

3.根据业务规则计算
根据业务规则（映射关系、公式等）将业务系统中数据转化为符合需求的数据。

加载

加载，即将源系统的数据加载到目标数据库对应表中，并根据不同的数据库平台采用不同的加载工具，同时根据不同的业务需求、接口形式设计不同的加载算法。

ETL设计模式

SQL+脚本语言

应用SQL语言实现业务逻辑的表达并应用脚本语言（shell、perl、python等）将SQL语言封装为可执行脚本。

优势：开发灵活、能够跨平台使用、成本低
不足：开发人员要求高、不易维护、规范性差。

ETL工具设计

应用ETL工具的组件进行相关设计，常用ETL工具如：Kettle、Powercenter、Datastage、Talend等。

优势：可视化、高效、易维护
不足：成本高、灵活性差、复杂场景不易实现

ETL工具+SQL

结合使用ETL工具与SQL，根据工具特性和业务逻辑的复杂度平衡设计，在异构平台使用ETL工具快速抽取各业务系统数据，复杂的业务转换阶段采用SQL解决，使ETL整个处理过程达到最优。

优势：开发灵活、开发效率高、易维护、可视化、规范、标准
不足：成本较高

ETL接口设计要求

接口即ETL的最终产出结果，可以以表、文件、信息等方式存在。

明确接口属性

1.事件类表：用于记录交易等动作的发生。在源系统中会新增、大部分不会修改和删除，少量表存在删除情况。如定期存款登记簿。
2.状态类表：用于记录数据信息的状态。在源系统中会新增、修改，也存在删除的情况。如客户信息表。
3.码表及参数表：用于记录源系统中使用到的数据代码和参数。

约定接口形式

接口表现形式：表/文件/消息（流）

确定接口抽取方法

抽取方法：工具抽取/脚本抽取
抽取机制：增量/全量
抽取频度：日/周/月/年
抽取约束：并发限制/抽取时机/数据库确认

规范接口格式

1.文件字符集编码:—个业务系统的接口编码只有一种编码风格:UTF8或 ASCII
2.分隔符:字段与字段间的分隔符建议最好采用字段内容不包含的分隔符
3.文件扩展名:文件的后缀通常应考虑是否压缩或非压缩情况
4.文件传输方式:上下游间数据传递方式确定好︰如FTP/消息队列
5.接口通知机制规范：标志文件/消息通知