Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

SQL 与 ETL | ETL 程序模式

··818 字
SQL实践 有悟方法

上篇《SQL 与 ETL | 数据类平台架构》文中,探讨了数据类平台的架构,以及架构方案落地过程的路径、资源等,使得我们有了初步认识。SQL 在 ETL 过程、BI数据可视化领域,有举足轻重的地位,它是这些阶段中的基础能力与重要工具,了解数据类平台的架构以及过程,有助于我们找准位置。本文将续上篇文章的内容,谈谈 ETL 程序模式,从中,你将感受到SQL在ETL程序中所发挥的主要功能。

ETL程序在数据流中发挥的作用 #

ETL pipeline 示例
ETL pipeline 示例

在谈 ETL 程序模式之前,先梳理ETL程序从源到数据消费的整个 pipeline 过程,ETL程序的主要作用有:

  1. 从数据源载入数据,或数据源主动卸载数据
  2. 数据转换(并不明显,它体现在程序的业务逻辑实现中)
  3. 数据模型的加载、数据填充
  4. 数据集成区的数据卸载
  5. 实时数据引擎的流式计算

除 实时数据引擎的流式计算外,其它均为传统集中式数据仓库的经典戏码。

ETL 虽有 extract、translate、load 三个动作,有时却不明显。在总结程序模式时,有悟隐藏 translate 转换逻辑细节,并将 ETL 过程分解成一个个单独的 源->目标 单元程序。数据开发、或者早期称呼为 ETL 工程师的大部分日常,由开发这类单元程序工作所组成。本文所要谈的程序模式,指单元程序的模式。单元程序也叫作业。

etl 程序模式化的实际意义 #

我们搞 ETL 程序模式化,不是为像 java 的 GOF 显摆技术。实际上,熟悉数据开发的人员均有类似的感受,数据开发工作需要编写的代码量极少,从业时间愈长,编程能力退化愈发严重,与应用系统开发的技术人员无法相比。

程序模式化,有助于我们认清 ETL程序的结构,减少人为错误,可以把更多的精力、注意力放在构建完整的数据管道上面。另外,构建 ETL 数据管道时,通常大量的时间梳理源、目标的映射关系,这些关系可以概括为 一对一多对一,其中简单的关系映射占比超过一半以上。

通常我们使用电子表格作为载体来记录这些映射关系。这些映射关系的形式,是有规律的。这种规律正好可以用来建立对应的 ETL 程序模式。自然而然,可以往代码生成方向上走。关于代码生成如何实现,有悟介绍了一种常用方式,请看:

在动手编写 ETL 单元程序之前,先从整体上分析梳理每个目标表的映射源,根据数据源全增量方式、表结构种类来判断表的加载策略,为每一种策略定制一个程序模板,利用表、字段、映射关系信息,就可以使用程序生成程序、脚本生成程序的代码生成来帮助完成大部分代码编写工作,也叫代码自动化。

若仔细分析,大概会发现这种现象,在数据架构中,越靠近数据源的,它的映射关系越简单,一对一完整的数据复制非常常见。而越往上层应用靠拢,关系越复杂,甚至会感到使用电子表格来表示映射关系非常吃力。

映射关系表达难易与代码生成
映射关系表达难易与代码生成

代码生成,只是为了提高整体的开发效率、减少人为错误,并不是用来替代开发人员。只要完成大部分有规律的程序生成足已。

etl 程序模式 #

一般性原则: 因代码组织与管理的关系,一个 ETL 单元程序或者作业,通常对应一个加载目标。但若多个目标表加载的取源逻辑相同,这时会合并为一个作业(如果工具支持的话),类似于 oracle 的 insert all,insert into multiple tables ,向多个表分条件插入。

todo,插图

etl 程序分为增量加载、全量加载,这样描述并不严谨。程序模式受 extract(准备加载的数据源)与 load(目标表)的结构、数据保存设计的影响。

数据表结构的种类 #

数据表结构种类
数据表结构种类

在数据平台中,数据表结构种类有以下几类:

  1. 带业务主键(可以多个字段)信息表
  2. 带ID标识的维度表(码值或数据字典)
  3. 业务主键包含数据日期的数据表,用于保存历史
  4. 历史拉链表,属上面 类型“3” 的变种, 除可节省存储空间外,保存数据完整的生命周期

针对不同结构类型的表,要在加载完毕后,表示最新时点或者最完整的数据状态,要采用不同的加载步骤。


⚠️ 开卷:

  1. 保留数据的历史,可以使用哪种类型的表结构
  2. 什么样的数据需要保留历史?

数据源提供方式 #

单元程序所依赖的数据来源可以有多个,可能是文件,也可能以数据表的形式存在。将这些数据加载到目标表的过程中,可能直接将数据覆盖掉目标,也可能先与目标比较出有变化的数据,再采取一定的策略将目标中的数据替换掉。

因此前文所说的 “etl 程序分为增量加载、全量加载”,是不完全准确的说法,增量、全量仅指相对于目标表是否有无变化,并无指明数据源提供全量、增量。这个因素会影响单元程序的结构。

etl 程序模式 #

根据摄取到的全量、增量数据源,以及目标表结构种类,为达到表示最新数据状态的目的,可以总结出以下几种 ETL 单元程序模式。

etl 程序模式
etl 程序模式

如图所示,单元程序中使用到的源表,有可能直接来自数据源,一一对应,也有可能需要经过一定的预处理。在编制作业时,所引用的具体表或者文件,数据结构有可能与源存在差异。比如,缺少部分字段,或者源系统提供的数据文件过大、无法提供增量需要先预处理等等。预处理过程,也可以认为是“源->目标”的一种模式。不过,它并没有被包含到本文中的ETL程序模式。

加载策略名称 其它称呼 目标 加载 策略说明
truncate + insert 替换、replace 全量 信息表、维度表 全量 替换模式,清空目标表,再 insert 或者 load
delete + insert 追加、append 增量 带日期的数据表 增量 追加模式。先部分删除,再 insert 或者 load。如按日期删除目标表中数据,多用于重跑
update + insert upsert 增量、全量 信息表 增量 比较数据与目标表数据:
  1. 若in SOURCE and not in TARGET,则 INSERT ;
  2. 若source ≠ target ,则 UPDATE
update + insert + delete upsert 全量 信息表 增量 比较数据与目标表数据:
  1. 若in SOURCE and not in TARGET,则 INSERT ;
  2. 若source ≠ target ,则 UPDATE;
  3. 若不保留 not in SOURCE and in TARGET 的记录,则 DELETE
Close & Open 拉链加载 增量、全量 拉链表 增量 不带逻辑删除的历史拉链表加载,新增数据 insert,变更数据 update + insert
Close & Open(D) 拉链加载 全量 拉链表 增量 带逻辑删除的历史拉链表加载,新增数据 insert,变更数据 update + insert,删除数据用 update 闭链
cycle cleanup 循环清理、Rotate 以日期存储数据的表或分区表 定期清理数据,如临时区、缓冲区中的数据表,表中数据设定一定的保留周期,过期数据清理。

⚠️ 开卷:

  1. 尝试将数据源所提供数据进行预处理的程序模式化,补充到 ETL程序模式策略。
  2. 你能理解ETL策略中所包含的预处理以及它的作用吗?

模式 truncate + insert #

清空目标表后,再将数据全表插入,用于全表覆盖。比如将业务系统中最新的客户表数据复制到平台。

一般用于目标表为全量表、数据源表全量提供,并且数据量不大的情况。与实体信息表类似的,还有一些系统参数表,这类表的数据量非常小,直接使用全表覆盖加载即可。

使用 SQL 示意的伪代码:

begin
	-- step 1
	-- 清空目标表
	truncate table 目标表;

	-- step 2
	-- 插入新的数据
	insert into 目标表
    values (..., 数据日期)
	select 源表.*, 'yyyymmdd'
	from   源表;
	commit;
end;

在关系型数据库中,truncate 删除表中数据的速度最快。

模式 delete + insert #

truncate + insert 类似,在加载前用 delete 语句先删除目标表数据,后再将数据插入目标表中。多用于重跑机制。也叫数据追加。

使用 SQL 示意的伪代码:

begin
	-- step 1
	-- 删除指定日期的数据
	delete from 目标表 where 数据日期 = 'yyyymmdd';
	commit;

	-- step 2
	-- 插入新的数据
	insert into 目标表
    values (..., 数据日期)
	select 源.*, 'yyyymmdd'
	from   源
	where  数据日期 = 'yyyymmdd';
	commit;
end;

适用条件:

  1. 目标表有可标识字段,如日期
  2. 源含增量数据或本身为增量数据

对于行式数据库,在表的末尾追加插入的数据比逐行插入数据快的空隙要快。此时的追加与本小节中的“追加”属于两个不同的概念,请知晓。

模式 update + insert #

truncate+insert 演变而来。当数据源增量时,或全量数据数据较大时,这时需要优化最终加载到目标表的数据量。

当增量更新目标表数据时,需要识别出相对于目标表来说 个性的记录行 的记录行,即要对源的是数据行,做相对于目标表 有发生修改的新出现 的标识 。

update + insert模式
update + insert模式

使用 SQL 表示的伪代码:

begin
    -- step 0 
    -- 预处理数据
    -- 将源裁剪与目标表相同字段、相同主键的可比较数据

	-- step 1
	-- 待加载的源表与目标表对比
	create template table 临时表
	select 源.*, case when 目标.主键 is null then '新增' else '修改' end choose
	from   源
	left   join 目标
	on     源.主键 = 目标.主键
	where  源.其它字段 <> 目标.其它字段
	or     目标.主键 is null;

	-- step 2
	-- 加载变化的数据
	update 目标表
	set    字段列表 = 临时表.字段列表
	from   临时表
	where  目标表.主键 = 临时表.主键
	and    临时表.choose = '修改';

	-- step 3
	-- 加载新增的数据
	insert into 目标表
	values ()
	select 临时表.字段
	from   临时表
	where  临时表.choose = '新增';
	commit;

	-- 以上 step2 和 step3 可合并为 merge 语句
end;

适用于目标表结构种类为信息表、数据源数据量较大,并且需要有变化的(新增的、修改的)记录数不多的情况。


⚠️ 开卷:

  1. 当有变化的记录数较多的情况,本策略还可用吗?若不适用,模式要如何调整?
  2. 设计出 update + insert 模式的重跑清理逻辑

update + insert 的类ETL作业流程图
update + insert 的类ETL作业流程图

若你曾经或者正在使用 ETL 工具开发作业,那么大概的流程如上图。由于会对同一个目标进行 DML操作,若目标有表级锁,那么 update、insert 需要串联逐一执行,以避免死锁,故一个目标表的加载要由一组3个作业组成的作业组来完成。

模式 update + insert + delete #

比模式 update + insert 多了删除操作。即根据目标表的加载需要,加载前将目标表与源比对,源中不存在的记录认为已被删除,加载时需要对目标表相应的行做处理(标识删除或者物理删除)。

update + insert + delete 模式
update + insert + delete 模式

相应,sql 伪代码改为:

begin
    -- step 0 
    -- 预处理数据
    -- 将源裁剪与目标表相同字段、相同主键的可比较数据

	-- step 1
	-- 待加载的源表与目标表对比
	create template table 临时表
	select 源.*
	     , case 
	       when 目标.主键 is null then '新增' 
	       when 源.主键 is null then '删除'
	       else '修改' end choose
	from   源
	full   join 目标
	on     源.主键 = 目标.主键
	where  源.其它字段 <> 目标.其它字段
	or     目标.主键 is null
	or     源.主键 is null;

	-- step 2
	-- 加载变化的数据
	update 目标表
	set    字段列表 = 临时表.字段列表
	from   临时表
	where  目标表.主键 = 临时表.主键
	and    临时表.choose = '修改';

	-- step 3
	-- 加载新增的数据
	insert into 目标表
	values ()
	select 临时表.字段
	from   临时表
	where  临时表.choose = '新增';

	-- step 4
	-- 处理删除的记录
	delete
	from   目标表
	where  exists
	       ( select 1
	       	 from  临时表
	       	 where 临时表.主键 = 目标表.主键
	       	 and   临时表.choose = '删除');
	commit;

end;

使用类 ETL 图形化工具开发的作业流程图如下:

update + insert + delete 的类ETL作业流程图
update + insert + delete 的类ETL作业流程图


⚠️ 开卷:

  1. upsert + deleteupsert 可以共用代码吗?

模式 Close & Open #

专门针对结构为拉链的目标表而设计的模式。

拉链表加载效果示例
拉链表加载效果示例

目标表中分别有 PK1 和 PK2 的状态。当发现 PK1 出现新的状态后,修改目标表中的原 PK1 的结束日期为新状态的前一天,称为 闭链,然后将新的状态记录添加到目标表中,开始日期为新状态的产生日期,称为 开链。因 PK2 无新的状态,故不变。

Close & Open 模式
Close & Open 模式

伪代码为:

begin
    -- step 0 
    -- 预处理数据
    -- 将源裁剪与目标表相同字段、相同主键的可比较数据

	-- step 1
	-- 待加载的源表与目标表对比
	create template table 临时表
	select 源.*, case when 目标.主键 is null then '新增' else '修改' end choose
	from   源
	left   join 目标
	on     源.主键 = 目标.主键
	where  源.其它字段 <> 目标.其它字段
	or     目标.主键 is null;

	-- step 2
	-- 闭链
	update 目标表
	set    结束日期 = 临时表.数据日期 - 1 day
	from   临时表
	where  目标表.主键 = 临时表.主键
	and    临时表.choose = '修改';

	-- step 3
	-- 开链
	insert into 目标表
	values (...)
	select 主键, 数据日期, ...
	from   临时表;
	commit;

end;

实际上,close & open 策略与 upsert 非常接近,仅因目标表结构种类的区别,update 对应 闭链、 insert 对应 开链


⚠️ 开卷:

  1. 在伪代码的最开始处添加一段逻辑,使得历史拉链表的单元程序支持重跑。
  2. 试图画出 close & open 类图形化ETL作业流程图。

模式 Close & Open(D) #

模式 Close & Open(D) 之于 Close & Open,相当 update + insert + delete 之于 update + insert

源以全集形式出现,且需要将目标中不再出现在源中的元素进行标识,以此来表示数据生命周期结束状态。本模式比 Close & Open 多了一个针对消失了的数据的 闭链 步骤。


⚠️ 开卷:

  1. 写出 Close & Open(D) 的伪代码
  2. Close & Open(D)Close & Open 可以共用程序模板吗?
  3. 试图画出 close & open(D) 类图形化ETL作业流程图。

cycle cleanup #

对表进行周期性清理。这些表的结构上需要包含呈现周期性规律的存储结构。

周期性清理策略有两种:

  1. 直接删除超过一定周期的数据;
  2. 使用固定桶数的环形存储器,周期内一个存储时间单位占用一个桶。当所有桶都使用完,重新清理第一个桶并将数据存放其中。

⚠️ 开卷:

  1. 什么样的数据适合使用环形存储器?

etl 程序模式策略选择 #

etl 程序模式策略选择
etl 程序模式策略选择