ETL还是ELT？这个工具玩转数据清洗实现高效率

除了大数据我们听到得多，还有就是ETL，ETL，ETL，那么，什么是ETL到底是什么呢？

什么是ETL，以及ETL背景

随着企业的发展，各业务线、产品线、部门都会承建各种信息化系统方便开展自己的业务。随着信息化建设的不断深入，由于业务系统之间各自为政、相互独立造成的数据孤岛”现象尤为普遍，业务不集成、流程不互通、数据不共享。这给企业进行数据的分析利用、报表开发、分析挖掘等带来了巨大困难。

 

在此情况下，为了实现企业全局数据的系统化运作管理（信息孤岛、数据统计、数据分析、数据挖掘） ，为DSS（决策支持系统）、BI（商务智能）、经营分析系统等深度开发应用奠定基础，挖掘数据价值 ，企业会开始着手建立数据仓库，数据中台。将相互分离的业务系统的数据源整合在一起，建立一个统一的数据采集、处理、存储、分发、共享中心。

 

在BI项目中ETL会花掉整个项目至少1/3的时间，ETL设计的好坏直接关接到BI项目的成败。

ETL基础概念-定义

ETL是将业务系统的数据经过抽取（Extract）、清洗转换（Transform）之后加载（Load）到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

 

ETL基础概念-过程

ETL处理分为五大模块，分别是：数据抽取、数据清洗、库内转换、规则检查、数据加载。各模块可灵活进行组合，形成ETL处理流程。简单介绍一下各个模块之间的主要功能。

数据抽取

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

数据清洗与转换

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

数据加载

将数据缓冲区的数据直接加载到数据库对应表中，如果是全量方式则采用LOAD方式，如果是增量则根据业务规则MERGE进数据库

ETL VS ELT

ETL架构按其字面含义理解就是按照E-T-L这个顺序流程进行处理的架构：先抽取、然后转换、完成后加载到目标数据库中。在ETL架构中，数据的流向是从源数据流到ETL工具，ETL工具是一个单独的数据处理引擎，一般会在单独的硬件服务器上，实现所有数据转化的工作，然后将数据加载到目标数据仓库中。如果要增加整个ETL过程的效率，则只能增强ETL工具服务器的配置，优化系统处理流程（一般可调的东西非常少）。

 

ELT架构则把“L”这一步工作提前到“T”之前来完成：先抽取、然后加载到目标数据库中、在目标数据库中完成转换操作。在ELT架构中，ELT只负责提供图形化的界面来设计业务规则，数据的整个加工过程都在目标和源的数据库之间流动，ELT协调相关的数据库系统来执行相关的应用，数据加工过程既可以在源数据库端执行，也可以在目标数据仓库端执行（主要取决于系统的架构设计和数据属性）。当ETL过程需要提高效率，则可以通过对相关数据库进行调优，或者改变执行加工的服务器就可以达到。

 

大家可以仔细看看上面两种架构图，体会一下他们之间的区别，下面来分析一下他们各自的优点：

ETL架构的优势

• 可以分担数据库系统的负载（采用单独的硬件服务器）
• 相对于ELT架构可以实现更为复杂的数据转换逻辑
• 采用单独的硬件服务器
• 与底层的数据库存储无关

ELT架构的优势

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

ETL 工具

为什么要使用ETL工具

• 当数据来自不同的物理主机，这时候如果使用SQL语句去处理的话就显得比较吃力且开销也更大
• 数据来源可以是各种不同的数据库或者文件，这时候需要先把他们整理成统一的格式后才可以进行数据的处理，这一过程用代码实现显然有些麻烦
• 在数据库中我们当然可以使用存储过程去处理数据，但是处理海量数据的时候存储过程显然比较吃力，而且会占用较多数据库的资源，这可能会导致数据库资源不足，进而影响数据库的性能。

ETL工具选择依据

• 对平台的支持程度
• 抽取和装载的性能是不是较高，且对业务系统的性能影响大不大，侵入性高不高
• 对数据源的支持程度
• 是否具有良好的集成性和开放性
• 数据转换和加工的功能强不强
• 是否具有管理和调度的功能

主流ETL工具推荐

亿信ABI

1 通过数据清洗加工获取目标数据

场景说明：需要根据已知的基础工资和奖金数据，导出实际工资数据。

步骤一：抽取

拖拽表输入组件到设计面板，从SCOTT数据库抽取EMP表.并可以预览数据

 

步骤二：转换

拖拽清洗组件，并把奖金字段的NULL值替换成0，具体操作如图。

 

增加表达式组件，增加工资字段=基础工资+奖金.定义字段为浮点型,字段长度7,精度2。

 

步骤三：加载

拖拽表输出组件，加载到LE_TE数据库,新建EMP表.还可以预览最终数据。

 

 

以上通过亿信ABI,完成了简单的拖拽式ETL过程。

2 串联ETL过程

场景说明：抽取到最新的学生信息、成绩信息、课程信息，计算学生总成绩并实现每日数据抽取。

步骤一：制作ETL过程流,拉取学生信息,考试成绩信息,课程信息。

 

步骤二：通过贴源抽取到最新的学生信息,成绩信息,课程信息.计算学生总成绩。

 

步骤三：设置调度任务,定时执行ETL过程流。这样，一个串行的ETL过程就完成了。

 

将ETL工具和BI完美融合的数据分析工具，数亿信ABI最厉害！完美融合，省时省力,提高工作效率！