ETL基础概念及要求详解

╰半夏微凉° 2024-03-27 15:42 71阅读 0赞

#### ETL基础概念及要求详解 ####

*  概念
 *   *  ETL与ELT
     *  数据湖与数据仓库
 *  ETL应用场景
 *  ETL具体流程及操作要求
 *   *  抽取
     *  清洗
     *  转换
     *  加载
 *  ETL设计模式
 *   *  SQL+脚本语言
     *  ETL工具设计
     *  ETL工具+SQL
 *  ETL接口设计要求
 *   *  明确接口属性
     *  约定接口形式
     *  确定接口抽取方法
     *  规范接口格式

## 概念 ##

ETL即Extract（抽取）Transform（转换）Load（装载）的过程，如下图，同时也包括数据清洗的部分，是数据从源端到目标端的一个数据处理的过程，是构建数据仓库的重要一环，但不局限于构建数据仓库其他BI（商业智能）的设计和开发数据分析平台也经常用到ETL。  
![在这里插入图片描述][3ef7857c5dd8470f95a10abd8c4ed271.png]  
在进行ETL作业时按作业进行的加工顺序又分为ETL和ELT。

### ETL与ELT ###

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

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

### 数据湖与数据仓库 ###

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

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

## ETL应用场景 ##

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

## ETL具体流程及操作要求 ##

### 抽取 ###

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

### 清洗 ###

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

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

### 转换 ###

数据转换主要包含三类：  
1.代码标准化  
![在这里插入图片描述][79f33e5cabb844a485174c6c2441d2bd.png]  
2.数据粒度转化  
数据粒度及数据的细化程度，例如省县乡三级分划，只到省级的数据相比于到乡级的数据细化程度更低，数据粒度大。

ETL过程中依据需求可能需要将小粒度的业务数据聚合为大粒度的数据。  
![在这里插入图片描述][0313a3f82ad5450e835236c36d195966.png]  
3.根据业务规则计算  
根据业务规则（映射关系、公式等）将业务系统中数据转化为符合需求的数据。

### 加载 ###

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

## ETL设计模式 ##

### SQL+脚本语言 ###

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

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

### ETL工具设计 ###

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

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

### ETL工具+SQL ###

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

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

![在这里插入图片描述][b91590e0b88a476bb8bddb7157c605d4.png]

## ETL接口设计要求 ##

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

### 明确接口属性 ###

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

### 约定接口形式 ###

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

### 确定接口抽取方法 ###

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

### 规范接口格式 ###

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

[3ef7857c5dd8470f95a10abd8c4ed271.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9a934a5c35054326b42ad405b94ff4eb.png
[443bebaa005c47a9ae937e0f4d78a0f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/28541e85ca054844b5386076111e89b2.png
[a9c67a02b7534e778ade8bcbb3fcf1bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ff097e5f929c4f5ab8de52d10189ed65.png
[79f33e5cabb844a485174c6c2441d2bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/630c5823ead24181a5c2c52197b11c04.png
[0313a3f82ad5450e835236c36d195966.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f361162a81d54bf499cc63e43626aba3.png
[b91590e0b88a476bb8bddb7157c605d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9f1fb2f0f8ff409f84361f99847ef9b4.png