经典面试题：说说ETL的过程

为什么要讲ETL工程

ETL工程是数据分析处理中非常重要的一环，ETL工程的输入是各种数据源，输出是各种用于分析的表和数据文件，因此它直接决定了用来分析的数据是否易用、数据质量的好坏、数据是否完整、数据是否可信等关键性问题。可以说，ETL在数据分析工作中占据着非常重要的地位，所以在本文我们会带领大家了解下ETL的内部构造。

本文的目标

因为这是入门系列文章中的一篇，因此我们不会过于沉入技术细节，而是从一个全局的角度，用尽可能简洁的语言，带领大家了解下ETL的概念、作用、组成结构、数据处理方式等，旨在让大家对ETL有一个比较全面的了解，期望可以在面试或者工作中帮助到大家。

本文的讲解思路

第一部分，讲解ETL的概念和作用，让大家对ETL有个初步的了解。第二部分，讲解ETL针对不同的数据和情况是如何处理的，让大家了解它是如何运作的。第三部分，讲解ETL的运行过程，以及在编写ETL程序时，应该注意哪些问题，这部分主要是为了让大家对ETL有个稍微深入一点的了解，在面试和工作中都可以自如应对。

×××以下为正文

01ETL简介

ETL ，是三个英文单词Extract-Transform-Load的首字母缩写，用来描述将数据从来源端经过萃取（extract）、转化（transform）、加载（load）至目的端的过程。ETL通常用于数据仓库的构建过程，它的主要作用是将企业中分散、非完全结构化、标准不统一的各种数据，整合到一起，形成企业级统一数据仓库，为企业的分析决策提供有质量保证的数据源。

02ETL的一般过程

从ETL的字面来看，就知道它主要包含三大阶段，分别是数据抽取、数据转换、数据加载。接下来我们将依次对这三个阶段进行详细的介绍。

1.数据抽取 

这个阶段的主要目标是汇总多种数据源，为下一步的转换做准备。在动手做抽取之前，你需要充分了解你的各种数据源，理解并利用他们的特性，结合实际分析业务需求，选择合适的抽取方式。下面我们针对常见的几种数据源，谈下他们常用的抽取方式。

1.1 关系型数据库 

这种数据源的特性是数据高度结构化、数据量不算特别大、数据更新相对频繁、数据质量较高、数据获取比较方便。根据分析业务需求的不同，一般分为离线抽取和实时抽取。离线抽取，应用场景是对短时间内的更新不敏感，只关心一段时间后的最终状态，一般是固定周期定时抽取。抽取方法，可以采用快照的方式，对整个表做全表快照；或者是采用基于时间字段的增量抽取，但是需要对这个字段的更新场景非常了解。

实时抽取，通常的应用场景是对数据的最新状态非常敏感，当有新数据插入或者旧数据更新时，需要及时抽取。抽取方法有很多，其中常用的一种是基于消费数据库binlog的方式，如阿里开源的canal。

1.2 服务端程序日志文件

这种数据源的特性是各种日志数据混杂在一起（需要过滤出自己需要的）、数据格式比较多样（格式可能不统一）、数据量相对较大。对于这类数据，通常的方法就是过滤抽取。抽取方法，可以使用flume监控文件目录，或者使用FileBeat实时监控文件内容变化。

1.3 客户端用户行为数据

客户端用户操作日志的数据收集方案，其实也算是一种抽取方式。这种数据源的特点是数据量非常大、数据源非常分散、数据质量参差不齐、数据结构化程度高。针对这种数据，通常的抽取方法，就是专门设计一个数据收集系统来处理收集数据的问题。

1.4 其他

实际工作中，我们可能还会有其他数据源，如NoSQL数据库、人工整理的数据（例如Excel）、消息队列中的数据、系统运行数据、时序数据库等。这些都需要根据具体数据源的特性和具体分析业务的需要，来设计抽取方式，篇幅有限，这里就不一一举例了。

2.数据转换

这个阶段是ETL的核心环节，也是最复杂的环节。它的主要目标是将抽取到的各种数据，进行数据的清洗、格式的转换、缺失值填补、剔除重复等操作，最终得到一份格式统一、高度结构化、数据质量高、兼容性好的数据，为后续的分析决策提供可靠的数据支持。下面对其主要过程做下简单说明。

2.1 数据清洗

这个环节的主要作用是清洗掉脏数据，以免混入正常数据中，造成数据质量下降，引导有偏差的错误决策。通常的做法有，md5校验、关键字段检查、格式类型一致性检查、无效性检查等。

md5校验通常用于通过公网传输的数据，为防止数据被篡改，需要比对md5值来保证数据的安全性，没能通过md5检验比对的数据将被视为脏数据过滤掉。

关键字段检查，是指在一条数据记录中，非常关键的字段，如果其值不合法，或者类型不对，将被视为非法数据。比如，识别用户行为的字段，其值不在规定取值范围内时，将无法识别具体是做了什么操作，将被视为非法数据清洗掉。

格式类型一致性检查，就是检查一条数据记录的整体格式或者重要字段的类型是否符合规范，如果不符合，同样也是没办法处理，只能作为脏数据处理掉。例如，不合法的日期格式。

无效性检查，通常是比较严格的过滤方法，它主要目的是为了保证数据的高可靠性，通常是事先规定好数据的可信范围，不满足的将被视为不可信的无效数据。

2.2 格式转换

这个环节的主要作用是对数据格式做统一化规范化处理，方便后续的分析作业能够更好地使用数据。一般根据源数据格式和目标格式的不同，会有不同的实现。一般包含记录格式转换和字段格式转换。记录格式转换，就是最终将一条记录转换成什么格式，例如转成json格式或csv格式。字段格式转换是对字段值进行格式的统一化处理，如将长整型时间戳全部转化为人类可读的日期格式。

2.3 缺失值填补

这个环节是针对数据中缺失的空位进行填充，以保证数据类型的一致性或完整性。例如，对于一个数量类的整数型字段，当其值缺失时，可以考虑填充0。这里要注意的是缺失值填充要相对谨慎一些才行，因为这个过程相当于是在修改原始数据，一定要确保填充的值对后续的分析不会造成误导性的影响。

2.4 剔除重复数据 

这个环节的主要作用是防止数据重复上传，造成误判。例如，一个用户播放一个视频，产生一条播放数据，如果由于某种原因，数据重复上传了3次。在服务端看来这个用户就看了3次这个视频，可能就会误以为这个用户非常喜欢这个节目。因此，剔除重复数据，是非常有必要的，也是保证数据质量的前提。

2.5 增加必要信息 

这个环节通常的作用是对数据增加一些追踪信息或者是生成一些必要的标识字段。有些字段是和具体的业务强相关的，也有一些通用的字段。比如，源数据里只有长整型时间戳字段，可以增加可读性比较高的日期时间字段，还可以增加数据来源字段便于追踪数据血缘关系。

2.6 其他 

有些公司根据自己公司的具体业务，可能会增加数据抽样、数据聚合、指标合并等环节，这和公司的分析诉求是强相关的，这里就不展开讲了。

3.数据加载 

这部分的主要目标是把数据加载至目的地，比如数据仓库中。通常的做法是，将处理好的数据写成特定格式（如parquet、csv等）的文件，然后再把文件挂载到指定的表分区上。也有些表的数据量很小，不会采用分区表，而是直接生成最终的数据表。

ETL vs. ELT

可能有些同学听说过ELT的概念，这里我们也简单介绍下，并说下它和ETL的区别。ELT，从字面上看，就是把L（加载）放在了T（转换）前面，也就是说处理过程是萃取（extract）、加载（load）、转化（transform）。实际上，称为ELTL更合适，先把数据抽取出来，之后加载到目的地，然后再进行数据转化，最后再把转化后的结果加载到新的数据表中。

它和ETL的最大区别就是，处理上的顺序略有不同。ELT这样做的好处，是为了将数据先转移到另一个体系中，之后就可以利用新体系下的一些框架完成对数据的高效处理（数据转换）。

笔者公司就是采用的这种方式，我们会把收集到的用户数据，首先以原始文件的形式加载至数据仓库的原始层，之后再调用spark处理框架对数据做各种处理转换，最后再把转换后的结果加载到数据仓库的新的表中。在这个过程中，就是先把数据转移到分布式存储（HDFS）上，然后才能利用分布式的数据处理框架（spark）完成高效的数据转换操作。

03ETL过程中的4个基本过程

前面几个部分我们介绍了ETL的概念，以及它的各个环节。本小节我们来说下，ETL的实际运行过程。ETL不是一个一劳永逸的过程，因为数据会源源不断地来，因此ETL需要定时或实时地对新来的数据进行数据。这里我们就简单介绍下用户行为数据的离线ETL的大致运行过程。

第一步，需要客户端SDK和数据收集服务的配合，不断地抽取客户端上发生的用户行为，并以文件的形式写入磁盘。

第二步，会有一个定时调度程序，负责将收集到的大量行为日志文件，切割并上传至HDFS上，并在指定位置写入上传完成的标记文件。

第三步，另一个定时调度程序会启动一个数据处理作业，去读取上传完成标记，待上传完成后，读入原始数据，开始进行数据转换处理，并将处理好的结果写入HDFS特定的位置。

第四步，通过特定脚本或者SDK将第三步产生的文件加载到对应的数据的特定分区上，至此一次完整的过程就完成了。 有些同学可能会有疑问，为什么第二步和第三步不放在一个工作流中调度？这是因为第二步是发生在数据收集服务端集群上的，是一个多台并行的调度，而第三步是调度起一个spark处理程序，是提交一个作业到yarn集群上，两个过程是无法组成一个工作流的。

开发ETL工程的注意事项

ETL程序的开发是一个非常复杂的过程，笔者公司也是经过了很久的迭代才趋于稳定的。以下罗列其中一些注意事项，希望对相关开发同学有所帮助：

数据清洗一定要慎重。因为清洗操作稍有不慎，就可能把正常的数据给过滤掉了。因此，比较好的实践方式是，在处理过程中增加计数机制，即记录整个批次的原始数据量，以及每个特定逻辑清洗掉了多少数据，这样不仅能做到心中有数，同时也可以将这些计数结果监控起来，如果某个数值异常升高，可能是业务数据发生了变化或某个逻辑出了问题。

缺失值填充要结合具体业务。前面提到了关于缺失值的填充，这里强调一点是要结合具体业务和分析需求来对缺失值进行填充，如果无法明确该如何填充，其实存Null值比填充更好。因为错误的填充缺失值，可能会破坏原有的数据。

设计好数据组织规范。是否有一个比较好的数据组织规范，决定了数据加载环节实现的难易程度。建议大家要从后向前设计，先设计好存储规范，然后再进行前面的数据转换设计。

统一实时ETL和离线ETL。有些业务场景会出现一份数据既用于实时业务，也用于离线分析。那么，值得注意的是，最好离线和实时的ETL处理逻辑要能够统一，避免由此导致出现不一致的统计口径。

总结

作为一名入门级选手，只需要了解上文的这些内容就差不多了，剩下的就是在具体工作中，结合公司的具体业务，在实际操练过程中，加深对ETL的理解。希望本文能够对你有所帮助。