大数据ETL数据提取转换和加载处理

什么是 ETL？

提取转换加载（英语：Extract, transform, load，简称ETL），用来描述将资料从来源端经过抽取、转置、加载至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。

ETL（提取、转换、加载）指数据驱动型组织从多个来源收集数据，然后将数据集中起来以满足数据发现、报告、分析和决策需求的过程。

在 ETL 流程中，各种数据源的类型、格式、规模和可靠性可能大不相同，因此数据要经过处理才能供组织和用户使用。同时，面对不同的目标和技术实施条件，组织可能使用数据库、数据仓库或数据湖来存储目标数据。

ETL 的三个步骤

提取
在提取阶段，ETL 将识别数据并从数据源复制数据，以便将数据传输到目标数据存储。其中，数据源包括结构化数据源和非结构化数据源，例如文档、电子邮件、业务应用、数据库、设备、传感器、第三方等等。

转换
ETL 对上一步骤提取的原始格式的原始数据进行映射和转换，为最终数据存储做好准备。在转换过程中，ETL 将按照适当方式校验和验证数据，进行数据去重和/或聚合，确保数据可靠、可查询。

加载
ETL 将转换后的数据移动到目标数据存储。加载操作可分为两种，一种是初始加载所有源数据，另一种是加载源数据的增量变更。另外，您既可以实时加载数据，也可以按计划分批加载。

ELT 和 ETL 的区别

转换是 ETL 流程中最复杂的步骤。因此，ETL 和 ELT 的区别主要体现在两点上：

转换操作时间
转换操作位置

对于传统的数据仓库，企业首先从“源系统”（ERP 系统、CRM 系统等等）提取数据。然后，由于需要对数据集维度进行标准化，获取聚合数据，才能使用 OLAP 工具和运行 SQL 查询，企业必须对数据进行一系列转换操作。

过去，数据转换是在将数据加载到目标系统（通常为关系数据仓库）之前完成的。

如今，数据仓库的底层数据存储和处理技术的发展，让企业得以在目标系统中进行转换操作。ETL 和 ELT 流程都包含数据中转区域。在 ETL 中，中转区域位于专有或定制工具中，位于源系统（例如 CRM 系统）和目标系统（例如数据仓库）之间。

而在 ELT 中，中转区域位于数据仓库中，由 DBMS 的数据库引擎执行转换操作。因此，ELT 消除了 ETL 旨在辅助数据转换的数据准备和清洗功能。

ETL 和企业数据仓库

过去，ETL 工具主要用于向企业数据仓库交付数据，满足商务智能 (BI) 应用需求。数据仓库用于提供一个关于企业内所有活动的可靠信息源，其中的数据是基于严格的模式、元数据和验证规则构建的。

面向企业数据仓库的 ETL 工具必须满足数据集成需求，例如高容量、高性能批次加载；事件驱动、滴流馈给的集成流程；可编程转换；以及编排。只有这样，它们才能满足最严苛的转换任务和工作流要求，为多样化的数据源提供连接器。

在加载数据后，企业可使用多种策略确保数据源与目标数据存储同步，例如定期重新加载完整数据集，定期更新最新数据，或在数据源与目标数据仓库之间执行全量同步。这种实时集成又被称为变更数据捕获 (CDC)。对于这一高级流程，ETL 工具必须能够洞悉源数据库的事务语义，并正确地将事务传输到目标数据仓库。

ETL 和数据集市

与企业数据仓库相比，数据集市规模更小，更多是用作目标数据存储。它们通常专注于某一个部门或某一个产品线的信息。因此，面向数据集市的 ETL 工具的用户主要是业务线 (LOB) 专家、数据分析师和/或数据科学家。

面向数据集市的 ETL 工具必须适合业务人员和数据经理使用，而不是编程人员和 IT 人员。因此，它们应搭载可视化工作流，以此简化 ETL 管道设置。

ETL、ELT 和数据湖

数据湖与数据仓库、数据集市不同，它一般通过对象存储或 Hadoop 分布式文件系统 (HDFS) 存储数据，因此可以存储无模式的、结构化程度较低的数据；支持使用多种工具来查询非结构化数据。

数据湖也支持 ETL（提取、转换和加载）。它能够以 “as-is” 状态存储数据，支持在捕获数据后转换、分析和处理数据。这带来了以下优势：

全面记录所有数据，避免因聚合或过滤而丢失任何信号。
超快速摄取数据，尤其适用于物联网 (IoT) 流处理、日志分析、网站指标等场景。
在捕获数据时发现趋势。
支持部署新型人工智能 (AI) 技术，从而检测大规模的非结构化数据集中的模式。

面向数据湖的 ETL 工具应提供可视化数据集成工具，这有助于提高数据科学家和数据工程师效率。数据湖架构中的其他常见工具还包括：

Cloud Streaming 服务：将大规模的实时数据流摄取到数据湖，满足消息传递、应用日志、运营遥测、Web 点击流数据跟踪、事件处理以及安全分析服务需求。此外，与 Kafka 兼容可确保这些服务能够从近乎无限的数据源检索数据。
基于 Spark 的云技术服务：快速对大型数据集执行数据处理和转换。Spark 服务可从对象存储或 HDFS 加载数据集，然后跨可扩展计算实例集群，在内存中处理和转换数据，最后将数据写回数据湖或数据集市和/或数据仓库。

ETL 使用场景

ETL 流程能够极其快速、可靠地将数据摄取到数据湖，满足数据科学和分析需求，同时创建高质量的模式，对于众多行业都至关重要。同时，ETL 解决方案还可以规模化地加载和转换事务性数据，为大规模数据创建有序视图。这样，企业可以可视化地展现和预测行业趋势。在现实中，众多行业都采用 ETL 流程来捕获切实可行的洞察，实现快速决策并提高效率。

金融服务
金融服务机构通常要采集大量的结构化数据和非结构化数据，以此捕获客户行为洞察。基于这些洞察，金融服务机构可以更好地分析风险，优化银行金融服务和线上平台，甚至为 ATM 供应现金。

石油和天然气
石油和天然气行业一般使用 ETL 解决方案来生成特定地理区域油气资源的使用、存储和趋势预测。ETL 可充分收集一个提炼站点所有传感器的信息，然后处理信息，使信息易于读取。

汽车
ETL 解决方案可帮助汽车经销商和制造商洞悉销售模式，调整营销活动，补充库存以及跟进销售线索。

电信
面对当今前所未来的数据规模和数据类型，电信服务提供商采用 ETL 解决方案来更好地管理和洞悉数据。在处理和分析数据后，企业可基于数据洞察改善广告、社交媒体、SEO、客户满意度和盈利能力等等。

医疗保健
为降低成本和改善服务，医疗保健行业采用 ETL 解决方案管理患者记录、收集保险信息和满足不断变化的监管要求。

生命科学
临床实验室采用 ETL 解决方案和人工智能 (AI) 技术来处理研究机构生成的各种类型的数据。例如，在疫苗开发协作中，实验室需要收集、处理和分析大量数据。

公共部门
随着物联网 (IoT) 功能的迅猛发展，智慧城市使用 ETL 和人工智能技术来优化流量、监视水质、提高停车率等等。

ETL常用工具

OWB(Oracle Warehouse Builder)
ODI(Oracle Data Integrator)
Informatic PowerCenter
AICloudETL、DataStage
Repository Explorer
Beeload
Canal
DataSpider
DataX
Kettle
Flink
Spark Streaming
TASCTL
Confluent
Fivetran
FlyData
Matillion
SnapLogic
StreamSets
Striim
Alooma
Flume
Sqoop
Lambda Architecture

比较常用的是：Sqoop、Kettle、Canal、DataX、StreamSets。笔者用过Datax对各种数据源的支持较好。

ETL工作原理

ETL架构

ETL过程概览

提取、转换、加载（ETL）的工作是定期将数据从源系统移动到目标系统。ETL 过程分三个步骤：

从源数据库中提取相关数据
转换数据，使其更适合分析
将数据加载到目标数据库

数据集市数据采集示意图：

Extract提取

在数据提取过程中，原始数据将从源位置复制或导出到暂存区域。数据管理团队可以从各种结构化或非结构化数据源中提取数据。这些来源包括但不限于：

SQL 或 NoSQL 服务器
CRM 和 ERP 系统
平面文件
电子邮件
网页

什么是数据提取？

在数据提取中，提取、转换、加载（ETL）工具从多个来源提取或复制原始数据并将其存储在暂存区域中。暂存区（或登录区）是用于临时存储提取数据的中间存储区。数据暂存区通常是暂时的，这意味着它们的内容在数据提取完成后会被删除。但是，暂存区也可能会出于故障排除的目的而保留数据存档。

系统将数据从数据来源发送到目标数据存储的频率取决于底层变更数据捕获机制。数据提取通常通过以下三种方式之一进行。

更新通知

在更新通知中，源系统会在数据记录发生更改时通知您。然后，您可以针对该更改运行提取过程。大多数数据库和 Web 应用程序都提供更新机制来支持这种数据集成方法。

增量提取

某些数据来源无法提供更新通知，但可以识别和提取在给定时间段内经过修改的数据。在这种情况下，系统会定期检查更改，例如每周一次、每月一次或活动结束时。您只需要提取已更改的数据。

完全提取

某些系统无法识别数据更改或发出通知，因此重新加载所有数据是唯一的选择。这种提取方法要求您保留上次提取的副本以检查哪些记录是新的。由于此方法涉及大量数据传输，因此我们建议您仅将其用于小型表格。

Transform转换

在暂存区，会对原始数据进行数据处理。在这里，数据得到转换和整合，以用于其预期的分析用例。此阶段可能涉及以下任务：

过滤、清理、去重、验证和认证数据。
基于原始数据进行计算、转换或汇总。这可能包括更改行标题和列标题以保持一致性、转换货币或其他计量单位、编辑文本字符串等。
进行审计以确保数据质量和合规性。
删除、加密或保护受行业或政府监管机构管理的数据。
将数据格式化为表或联接表，以匹配目标数据仓库的模式。

什么是数据转换？

在数据转换中，提取、转换、加载（ETL）工具将转换和整合暂存区域中的原始数据，为目标数据仓库做好准备。数据转换阶段可能涉及以下类型的数据更改。

基本数据转换

基本转换通过消除错误、清空数据字段或简化数据来提高数据质量。这些转换的示例如下。

数据清理

数据清理可消除错误并将源数据映射到目标数据格式。例如，您可以将空数据字段映射到数字 0，将数据值“Parent”映射到“P”，或将“Child”映射到“C”。

数据去重复

数据清理中的去重复将识别并删除重复记录。

数据格式修订

格式修订会将字符集、测量单位和日期/时间值等数据转换为一致的格式。例如，一家食品公司可能有不同的配方数据库，其成分以千克和磅为单位。ETL 会将所有单位转换为磅。

高级数据转换

高级转换使用业务规则来优化数据以便于分析。这些转换的示例如下。

推导

推导将业务规则应用于您的数据，以根据现有值计算新值。例如，您可以通过减去费用或通过将每件商品的价格乘以订购的商品数量来计算购买的总成本来将收入转换为利润。

联结

在数据准备中，联结将链接来自不同数据来源的相同数据。例如，您可以通过将不同供应商的采购价值相加，并仅将最终总计存储在目标系统中来找出某件物品的总采购成本。

拆分

您可以在目标系统中将列或数据属性划分为多个列。例如，如果数据来源将客户名称保存为“Jane John Doe”，您可以将其拆分为名字、中间名和姓氏。

汇总

汇总通过将大量数据值减少到较小的数据集中来提高数据质量。例如，客户订单发票值可以有许多不同的小金额。您可以通过将给定时间段内的数据相加来汇总数据，以构建客户生命周期价值（CLV）指标。

加密

您可以通过在数据流到目标数据库之前添加加密来保护敏感数据，以遵守数据法律或数据隐私。

Load加载

在这最后一步中，将转换后的数据从暂存区域移至目标数据仓库。通常，这涉及到对所有数据进行初始加载，然后定期加载增量数据变化，偶尔需要进行完全刷新以擦除和替换仓库中的数据。对于大多数使用 ETL 的组织而言，该流程是一个自动化流程，且定义明确、可连续执行和批量驱动。通常，ETL 发生在非工作时间，此时源系统和数据仓库的流量最低。