ETL基础知识有哪些？3分钟让你轻松搞定

ETL发展的历史背景

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

在此情况下，为了实现企业全局数据的系统化运作管理（信息孤岛、数据统计、数据分析、数据挖掘） ，为DSS（决策支持系统）、BI（商务智能）、经营分析系统等深度开发应用奠定基础，挖掘数据价值 ，企业会开始着手建立数据仓库，数据中台。将相互分离的业务系统的数据源整合在一起，建立一个统一的数据采集、处理、存储、分发、共享中心，从而使公司的成员能够从不同业务部门查看综合数据，而这个过程中使用的数据处理方法之一就是ETL。

ETL是数据中心建设、BI分析项目中不可或缺的环节。各个业务系统中分布的、异构的数据源，经过ETL过程的数据抽取、转换，最终存储到目标数据库或者数据仓库，为上层BI数据分析，或其他业务功能做数据支撑。

什么是ETL

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

举个例子，某电商公司分析人员根据订单数据进行用户特征分析。这时需要基于订单数据，计算一些相应的分析指标，如每个用户的消费频次，销售额最大的单品，用户复购时间间隔等，这些指标都要通过计算转换得到。

ETL的流程

ETL如同它代表的三个英文单词，涉及三个独立的过程：抽取、转换和加载。工作流程往往作为一个正在进行的过程来实现，各模块可灵活进行组合，形成ETL处理流程。

1.数据抽取

数据抽取指的是从不同的网络、不同的操作平台、不同的数据库和数据格式、不同的应用中抽取数据的过程。目标源可能包括ERP、CRM和其他企业系统，以及来自第三方源的数据。

不同的系统倾向于使用不同的数据格式，在这个过程中，首先需要结合业务需求确定抽取的字段，形成一张公共需求表头，并且数据库字段也应与这些需求字段形成一一映射关系。这样通过数据抽取所得到的数据都具有统一、规整的字段内容，为后续的数据转换和加载提供基础，具体步骤如下：

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

2.数据转换

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

数据转换就是处理抽取上来的数据中存在的不一致的过程。数据转换一般包括两类：第一类：数据名称及格式的统一，即数据粒度转换、商务规则计算以及统一的命名、数据格式、计量单位等；第二类：数据仓库中存在源数据库中可能不存在的数据，因此需要进行字段的组合、分割或计算。主要涉及以下几个方面：

①空值处理：可捕获字段空值，进行加载或替换为其他含义数据，或数据分流问题库

②数据标准：统一元数据、统一标准字段、统一字段类型定义

③数据拆分：依据业务需求做数据拆分，如身份证号，拆分区划、出生日期、性别等

④数据验证：时间规则、业务规则、自定义规则

⑤数据替换：对于因业务因素，可实现无效数据、缺失数据的替换

⑥数据关联：关联其他数据或数学，保障数据完整性

3.数据加载

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

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

ETL与ELT有什么区别

ETL架构按其字面含义理解就是按照E-T-L这个顺序流程进行处理的架构：先抽取、然后转换、完成后加载到目标数据库中。

在ETL架构中，数据的流向是从源数据流到ETL工具，ETL工具是一个单独的数据处理引擎，一般会在单独的硬件服务器上，实现所有数据转化的工作，然后将数据加载到目标数据仓库中。如果要增加整个ETL过程的效率，则只能增强ETL工具服务器的配置，优化系统处理流程（一般可调的东西非常少）。

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

ELT架构的特殊优势：①ELT主要通过数据库引擎来实现系统的可扩展性；②ELT可以保持所有的数据始终在数据库当中，避免数据的加载和导出，从而保证效率，提高系统的可监控性；③ELT可以根据数据的分布情况进行并行处理优化，并可以利用数据库的固有功能优化磁盘I/O；④ELT的可扩展性取决于数据库引擎和其硬件服务器的可扩展性；⑤通过对相关数据库进行性能调优，ELT过程获得3到4倍的效率提升一般不是特别困难。

（1）当您想要执行复杂的计算时，ETL工具比数据仓库或数据池更有效

（2）如果要在加载到目标存储之前进行大量数据清理。ETL是一种更好的解决方案，因为您不会将不需要的数据移动到目标。

（3）当您仅使用结构化数据或传统结构化数据仓库时。ETL工具通常最有效地将结构化数据从一个环境移动到另一个环境。

（4）当你想要扩展补充数据时。如果要在将数据移动到目标存储时扩展补充数据，则需要使用ETL工具。例如，添加时间戳。

如何才能做好ETL

1.数据抽取设计

数据的抽取需要在调研阶段做大量工作，要搞清楚以下几个问题：数据是从几个业务系统中来？各个业务系统的数据库服务器运行什么DBMS？是否存在手工数据，手工数据量有多大？是否存在非结构化的数据？等等类似问题，当收集完这些信息之后进行数据抽取的设计。常见的数据抽取设计方式有四种：

（1）与存放DW的数据库系统相同的数据源处理方法

这一类数源在设计比较容易，一般情况下，DBMS(包括SQLServer，Oracle)都会提供数据库链接功能，在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select 语句直接访问。

（2）与DW数据库系统不同的数据源的处理方法

这一类数据源一般情况下也可以通过ODBC的方式建立数据库链接，如SQL Server和Oracle之间。如果不能建立数据库链接，可以有两种方式完成，一种是通过工具将源数据导出成.txt或者是.xls文件，然后再将这些源系统文件导入到ODS中。另外一种方法通过程序接口来完成。

（3）对于文件类型数据源（.txt，.xls）

可以培训业务人员利用数据库工具将这些数据导入到指定的数据库，然后从指定的数据库抽取。或者可以借助工具实现，如SQL SERVER 2005 的SSIS服务的平面数据源和平面目标等组件导入ODS中去。

（4）增量更新问题

对于数据量大的系统，必须考虑增量抽取。一般情况，业务系统会记录业务发生的时间，可以用作增量的标志，每次抽取之前首先判断ODS中记录最大的时间，然后根据这个时间去业务系统取大于这个时间的所有记录。利用业务系统的时间戳，一般情况下，业务系统没有或者部分有时间戳。

2.数据清洗处理规范

不符合要求的数据主要有不完成数据（缺失值）、错误数据（异常值）、重复数据、不同类型需归一化处理数据几类。

几类数据的处理方法如下：

缺失值：不完整的数据，其特征是是一些应该有的信息缺失，如供应商的名称，分公司的名称，客户的区域信息缺失、业务系统中主表与明细表不能匹配等。需要将这一类数据过滤出来，按缺失的内容分别采取定（范围）删（字段）补（数据）。

定范围：哪些字段缺失，缺失范围如何，缺失字段的重要性如何？删字段：删数据的判断，a\对业务清晰的判断，b\“有心杀贼，无力回天”缺失数据太多。这时候可以看看是否有其他数据可以弥补。补数据：就是补充缺失值。这里有三种补数据的方式：A、业务知识惊讶填充；B、使用均值、中位数、众数填充；C、使用其他渠道补充，如身份证前6位是地区=手机号归属地。补全后才写入数据仓库。

异常值：产生原因是业务系统不够健全，在接收输入后没有进行判断直接写入后台数据库造成的，比如数值数据输成全角数字字符、字符串数据后面有一个回车、日期格式不正确、日期越界等。这一类数据也要分类，对于类似于全角字符、数据前后有不面见字符的问题只能写SQL的方式找出来，然后要求客户在业务系统修正之后抽取；日期格式不正确的或者是日期越界的这一类错误会导致ETL运行失败，这一类错误需要去业务系统数据库用SQL的方式挑出来，交给业务主管部门要求限期修正，修正之后再抽取。

重复数据：特别是维表中比较常见，将重复的数据的记录所有字段导出来，让客户确认并整理。

数据归一化：归一化的问题，就是将绝对数变成相对数的问题。因为不同维度的绝对数是没有可比性的，这时候需要将绝对数转化成相对一个标准的相对数。那如何进行归一化处理呢？三种方式，A、最值归一化、均值方差归一化、非线性归一化。

数据清洗是一个反复的过程，不可能在几天内完成，只有不断的发现问题，解决问题。对于是否过滤、是否修正一般要求客户确认；对于过滤掉的数据，写入Excel文件或者将过滤数据写入数据表，在ETL开发的初期可以每天向业务单位发送过滤数据的邮件，促使他们尽快的修正错误，同时也可以作为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉了，对于每个过滤规则认真进行验证，并要用户确认才行。

3.数据转换处理规范

数据转换的任务主要是进行不一致的数据转换、数据粒度的转换和一些商务规则的计算。

（1）不一致数据转换，这个过程是一个整合的过程，将不同业务系统的相同类型的数据统一，比如同一个供应商在结算系统的编码是XX0001，而在CRM中编码是YY0001，这样在抽取过来之后统一转换成一个编码。

（2）数据粒度的转换，业务系统一般存储非常明细的数据，而数据仓库中的数据是用来分析的，不需要非常明细的数据，会将业务系统数据按照数据仓库粒度进行聚合。一般数据转换有离散化和属性构造两种方式。离散化主要分为简单离散、分桶离散、聚类离散、回归平滑四类，属性构造分为特征工程和随意构造后筛选。

（3）商务规则的计算，不同的企业有不同的业务规则，不同的数据指标，这些指标有的时候不是简单的加加减减就能完成，这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中，供分析使用。

4.ETL日志与警告发送

（1）ETL日志

记录日志的目的是随时可以知道ETL运行情况，如果出错了，出错在那里。

ETL日志分为三类。①执行过程日志，是在ETL执行过程中每执行一步的记录，记录每次运行每一步骤的起始时间，影响了多少行数据，流水账形式。②是错误日志，当某个模块出错的时候需要写错误日志，记录每次出错的时间，出错的模块以及出错的信息等。③日志是总体日志，只记录ETL开始时间，结束时间是否成功信息。

如果使用ETL工具，工具会自动产生一些日志，这一类日志也可以作为ETL日志的一部分。

（2）警告发送

ETL出错了，不仅要写ETL出错日志而且要向系统管理员发送警告，发送警告的方式有多种，常用的就是给系统管理员发送邮件，并附上错误信息，便于管理员排查。

小结

在这里涉及到ETL中，我们只要有一个清晰的认识，它不是想象中的简单一蹴而就，在实际的过程，你可以会遇到各种各样的问题，甚至是部门之间沟通的问题。出现以上问题时，可以和团队小伙伴或者业务侧一起制定解决方案，不断完善，只有这样才能保证我们的业务分析结果是准确的，才能指导公司做出正确的决策。