一、ETL定义

1．目标

数据集成和交互操作解决方案的实施目标是

• 及时以数据消费者（人和系统）所需的格式提供数据。
• 将数据物理地或虚拟地合并到数据中心。
• 通过开发共享模型和接口来降低管理解决方案的成本和复杂度。
• 识别威胁，自动触发警报并采取相应行动。
• 支持商务智能、数据分析、主数据管理以及运营效率的提升。

 

2．原则

在实施数据集成和互操作时，组织应遵循以下原则：

• 采用企业视角确保未来的可扩展性设计，通过迭代和增量交付实现。
• 平衡本地数据需求与企业数据需求，包括支撑与维护。
• 确保数据集成和互操作设计和活动的可靠性。业务专家应参与数据转换规则的设计和修改，包括持久性和虚拟性。

 

3．规范制定目的

为保证XX公司数仓ETL的正确设计、实施和维护所定义的一些规则和方法，具体包括ETL设计规范、开发规范以及维护规范。

 

4．应用阶段

完整的ETL应用过程包含三个阶段：

• 设计阶段：分析源和目标数据集的数据结构，定义合理的数据转换逻辑。
• 实施阶段：按照设计阶段制定的逻辑规则进行编码，实现数据的ETL过程。
• 维护阶段：对于非一次性数据整合项目，ETL过程需要重复执行，同时也需要不间断的维护和完善。

 

5．应用场景

比较常见的场景有：汇总业务交易数据、将应用程序数据从旧系统迁移到新系统、整合近期公司收购或合并的数据、整合来自外部供应商和合作伙伴的数据等。

 

 

 

二、ETL基本概念

ETL是数据抽取（Extract）、转换（Transform）、装载（Loading）的缩写。

 

• 数据抽取：从数据源获取所需数据的过程。数据抽取过程会过滤掉目标数据集中不需要的源数据字段或数据记录。
• 数据转换：按照目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。数据转换主要包括：格式转换、字段合并与拆分、数据翻译、数据匹配、数据聚合其他复杂计算。
• 数据装载：将数据加载到目标数据库中。

 

无论是在物理状态下或虚拟状态下，批量的或实时的执行ETL都是在应用程序和组织之间数据流动的必要步骤。根据数据集成的需求，ETL可以作为定期调度事件执行（批处理），也可以在有新数据或数据更新后执行（实时或事件驱动）。操作型数据处理往往是实时或准实时的，而分析或报表所需的数据通常在批量作业中。 数据集成的需求还可以决定抽取和转换的数据是否存储在物理状态下的分段结构中。物理分段允许追踪数据的审计痕迹，并且可以从中间点重新启动潜在进程。然而，分段结构不仅占用磁盘空间，而且读写耗时。对于需要超低延迟的数据集成需求来说，它通常不会包括数据集成中间结果的物理分段。

 

1．抽取

抽取过程包括选择所需的数据并从其源数据中提取。然后，被抽取的数据会在磁盘或内存中的物理数据存储库中进行储存。如果在磁盘上进行物理缓存，则缓存数据库可以和源数据库或目标数据库合并，或者与两者都合并。在理想情况下，如果这个过程在一个操作型系统上执行时，为了避免对操作流程产生负面影响，那么设计时应考虑尽可能少地使用资源。在非高峰时间进行批处理是抽取的一个选项，其中包括执行选择或识别待抽取更改数据的那些复杂处理。

 

2．转换

转换过程是让选定的数据与目标数据库的结构相兼容。转换包括多种情况。例如，当数据向目标移动时将它从源数据中移除，或是数据复制到多个目标中，或是数据用于触发事件但不会持久化。

转换的例子包括：

• 格式变化。技术上的格式转换，如从EBCDIC到ASCII的格式转换。
• 结构变化。数据结构的变化，如从非规范化到规范化的记录。
• 语义转换。数据值转换时保持语义的一致化表达，如源性别代码可以包括0、1、2和3，而目标性别代码可以表示为UNKNOWN、FEMALE、MALE或NOT PROVIDED。
• 消除重复。如规则需要唯一的键值或记录，以确保包括扫描目标、检测和删除重复行的方法。
• 重新排序。改变数据元素或记录的顺序以适应已定义的模式。转换可以批量执行，也可以实时执行，或者是将转换结果存储在物理状态下的缓存区域，或者是将转换后的数据存储在虚拟状态下的内存中，直至移动到加载步骤为止。转换阶段所产生的数据应准备好与目标结构中的数据进行集成。

 

3．加载

加载过程是在目标系统中物理存储或呈现转换结果。根据所执行的转换、目标系统的目的和其预期用途，数据可能需要被进一步的处理以便与其他数据集成，或者可能以一种最终形式呈现给消费者。

 

4．流程

如果目标系统比源系统或中间应用系统具有更强的转换能力，那么数据处理的顺序可以切换为ELT——抽取、加载、转换。ELT允许在数据加载到目标系统后再进行转换。ELT允许源数据以原始数据的形式在目标系统上实例化，这对其他进程是有用的。用ELT的方式加载至数据湖，这在大数据环境中是很常见的。

 

5．映射

映射（Mapping）是转换的同义词，它既是从源结构到目标结构建立查找矩阵的过程，也是该过程的结果。映射定义了要抽取的源数据与抽取数据的识别规则、要加载的目标与要更新的目标行的识别规则（如果有的话）以及要应用的任何转换或计算规则。许多数据集成工具提供了映射的可视化界面，因此开发人员可以使用图形界面创建转换代码。

 

 

 

三、ETL架构原则

1．数仓分层原则

根据XX公司的数据现状，可以采用主流的数据仓库四层架构

• 数据贴源层：ODS（Operational Data Store）：存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理
• 数据明细层：DWD（Data Warehouse Detail）：对ODS层数据进行清洗，并进行轻度汇总，以某一个维度为线索，组成跨主题的宽表
• 数据服务层：DWS（Data WareHouse Servce）：以DWD层为基础，按主题进行重度汇总，提取指标
• 数据应用层：ADS（ApplicationData Store）：为统计报表提供数据，形成最终的应用、运营数据
• 数据维度层：DIM（Dimension）：贯穿数仓各层，存储维度、字典、主数据等描述型数据

 

2．主题域划分原则

按照XX公司的业务域或业务过程划分

按照XX公司的数据域划分

 

3．数据模型设计原则

高内聚、低耦合

核心模型和跨站模型要分离

公共处理逻辑下沉及单一

成本与性能平衡

数据可回滚

 

 

 

四、ETL开发规范

1．层次调用规范

ODS -> DWD -> DWS -> ADS

 

2．数仓冗余规范

• 冗余字段要使用高频，下游3个或以上使用。
• 冗余字段引入不应造成本身数据产寿过多的延后。
• 冗余字段和已有字段的重复率不应过大，原则上不超过60%，如需join或源表拓展

 

3．NULL字段处理规范

• 对于维度字段，设置为-1
• 对于指标字段，设置为0

 

4．指标口径规范

• 指标数理：根据业务分析框架OSM模型确立数据指标；统一指标和维度管理，指标命名、计算口径、统计来源；维度定义规范、维度值一致。
• 指标分类：
  • 原子指标：基于某一业务事件行为下的度量，是业务定义中不可再拆分的指标，具有明确业务含义的名称，如订单量、交易金额。
  • 派生指标：是1个原子指标+多个修饰词（可选）+时间周期，是原子指标业务统计范围的圈定。派生指标又分以下二种类型：
  • 事务型指标：是指对业务过程进行衡量的指标。例如，订单量、订单支付金额，这类指标需要维护原子指标以及修饰词，在此基础上创建派生指标。
  • 存量型指标：是指对实体对象（如司机、乘客）某些状态的统计，例如注册司机总数、注册乘客总数，这类指标需要维护原子指标以及修饰词，在此基础上创建派生指标，对应的时间周期一般为“历史截止当前某个时间”。
  • 衍生指标：是在事务性指标和存量型指标的基础上复合成的。主要有比率型、比例型、统计型均值。

 

5．数据表处理规范

• 增量表：新增数据，增量数据是上次导出之后的新数据
  • 记录每次增加的量，而不是总量
  • 增量表，只报变化量，无变化不报
  • 每天一个分区
• 全量表：每天的所有的最新状态的数据
  • 全量表，有无变化，都要报
  • 每次上报的数据都是所有的数据（变化的 + 没有变化的）
  • 只有一个分区
• 快照表：按日分区，记录截止数据日期的全量数据
  • 快照表，有无变化，都要报
  • 每次上报的数据都是所有的数据（变化的 + 没有变化的）
  • 一天一个分区
• 拉链表：记录截止数据日期的全量数据
  • 记录一个事物从开始，一直到当前状态的所有变化的信息
  • 拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总量
  • 当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）
  • 只有一个分区

 

6．表的生命周期管理

P0：非常重要的主题域数据和非常重要的应用数据，具有不可恢复性。

P1：重要的业务数据和重要的应用数据，具有不可恢复性。

P2：重要的业务数据和重要的应用数据，具有可恢复性。

P3：不重要的业务数据和不重要的应用数据，具有可恢复性。

 

 

 

五、ETL开发原则

1．编码原则

• 代码行清晰、整齐，具有一定的可观赏性。
• 代码编写要充分考虑执行速度最优、资源效率最高原则。
• 代码行整体层次分明、结构化强。
• 代码中应有必要的注释以增强代码的可读性。
• 使用XX公司数据中台功能组件对编码进行脚本格式化。

 

2．基本要求

• 代码中应用到的所有SQL关键字、保留字都需使用全大写、小写；不能使用大小写混合的方式，例如Select或seLECT等方式。
• 代码中应用到的除关键字、保留字之外的代码，都要求使用小写。
• 对数据库和数仓的表格进行SQL操作时，表名前需写明库名或命名空间。
• 禁止使用SELECT *操作，所有操作必须明确指定列名。
• 使用XX公司数据中台进行代码格式化操作。

 

3．数据类型

• 数据中台建模表字段类型应尽量与业务系统一致。
• 在对精度要求极其严格的场景下请谨慎使用DECIMAL类型。
• 关于货币类型，除非模型有特殊说明，否则中间层金额相关的数据不执行任何四舍五入操作，以避免后续的汇总计算中出现不同口径的汇总结果不一致的情况。

 

4．SQL注释

• 每条SQL语句的注释单独成行并置于语句前面。
• 字段如需要注释，则要紧跟在字段后面。
• 应为不易理解的分支条件表达式添加注释。
• 应说明重要计算的功能。
• 常量及变量注释时，必须注释被保存值的含义，按需注释合法的取值范围。

 

 

 

六、ETL流程规范

1．抽取作业

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

• 确定范围

将从源数据库（X5系统）获得数据的过程。在做这一步的之前，往往要预先确定需要什么数据，划分好范围。

• 收集数据源

拿到数据范围后，需要知道数据分别位于哪些数据源，是否和数据仓库同源，针对不同的数据源数据选择不同的抽取方式。

• 了解生命周期

了解各个领域数据的生命周期、更新周期，能够梳理出数据的抽取频率，即所谓的调度周期。调度周期过长会造成数据时效性和价值的下降，而一味追求调度频率，则会对集群造成较大的资源压力。

 

2．转换作业

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

• 数据清洗

任务是过滤不符合条件或者错误的数据。这一步常常出现在刚刚开始建立数据仓库或者源业务系统仍未成熟的时候，此时发现错误数据需要联系源业务系统进行更正，部分可预期的空值或者测试用数据可以过滤掉。

• 数据转换

这一步是整个ETL流程中最为占用时间和资源的一步。数据转换包含了简单的数据不一致转换，数据粒度转换和耗时的数据关联整合或拆分动作。这里可能存在各种各样千奇百怪的需求。对于核心数据仓库来说，里面往往是对数据进行按照主题划分合并的动作。同时，也会添加一些为了提升执行效率而进行反范式化添加的冗余字段。

使用SQL开发存储过程完成转换作业是常用的方法。它的优点是开发简单、能支持绝大部分转换场景；缺点在于占用资源多且受制于单一数据库性能，无法做到横向扩展。

因此，除了业务的理解能力外，对SQL海量数据处理的优化能力在此也非常重要。比如：利用数据库的分区性，选择良好的分区键、建表时合理选择主键和索引，关联时候必须使用主键或索引进行关联、关注数据库对SQL的流程优化逻辑，尽量选择拆分复杂SQL，引导数据库根据你选择流程进行数据处理、合理反范式化设计表，留出适当的冗余字段，减少关联动作。

具体的优化根据不同的数据库有着不同的处理方式，根据所选用的数据库不同而定。

 

3．加载作业

这部分的主要目标是把数据加载至目的地，比如数据仓库中。依据数据仓库的特性和抽取方式，把数据加载到特定的分区中。

• 全量加载

全表删除后再进行数据加载的方式。全量加载从技术角度上说，比增量加载要简单很多。一般只要在数据加载之前，清空目标表，再全量导入源表数据即可。但是由于数据量，系统资源和数据的实时性的要求，很多情况下我们都需要使用增量加载机制。

• 增量加载

目标表仅更新源表变化的数据。增量加载难度在于必须设计正确有效的方法从数据源中抽取变化的数据以及虽然没有变化，但受到变化数据影响的源数据，同时将这些变化的和未变化但受影响的数据在完成相应的逻辑转换后更新到数据仓库中。优秀的增量抽取机制不但要求 ETL 能够将业务系统中的变化数据按一定的频率准确地捕获到，同时不能对业务系统造成太大的压力，影响现有业务，而且要满足数据转换过程中的逻辑要求和加载后目标表的数据正确性。

 

4．流程控制

使用数据中台集中调度控制抽取加载和转换作业的运行，决定执行顺序，进行错误捕捉和处理。

• 系统的划分和前后流程的依赖

由于整个ETL系统里面可能跨越数十个业务系统，开发人员有数十拨人，必须支持按照业务系统进行划分管理。同时任务流之间建立起调度依赖，以组成自动化链路的构建。

• 日志和警告系统

每一步的流程都记录有日志，起始时间，完成时间，错误原因等，方便ETL流程开发人员检查错误。邮件功能能够对于发生错误的流程告警，及时通知错误人员进行错误检查和修复。

• 较高可靠性

在正式运行前，需要模拟相关场景进行功能、脚本及调度的测试，以保证生产数据的正常ETL。

 

 

 

七、ETL维护规范

1．运行监控

合理使用平台的管理和监控工具。一方面用于ETL的设置和调整，另一方面也是方便在ETL处理出现异常时能够及时进行反馈通知，通过人工的方式进行干预。保证ETL的正常运行。

 

2．异常处理

设计时需要考虑各种异常的情况；例如网络中断、数据库宕机、调度失常等等。同时，脚本和调度的设计要考虑数据的自修复能力：不管ETL程序发生任何异常，都能够回到抽取前的状态，而不需要人工干预，更不能影响到已抽取的数据。这样需要ETL设计上不仅满足最基本的链路，同样要考虑到备份及重跑机制。

 

3．链路解耦

ETL不是一蹴而就的，工作的开展需要ETL长期的稳定性。人员的流动、业务结构的调整和技术架构的变更都会对ETL整个链路造成影响。可以将ETL的每个步骤拆分为独立的原子业务操作，虽然在初始阶段会增加部分工作，但随着时间的推进会大幅增加运维的便利。任何人任何时间都能够了解ETL的流程，接手其中的工作。