etl什么意思

* *一、etl定义* *

etl是对业务系统的数据进行提取、清理和转换，然后加载到数据仓库中的过程。目的是整合企业中零散、杂乱、不统一的数据，为企业决策提供分析依据。

二、etl算法导图

三、算法应用场景

这八种etl算法主要分为四类。增量累加和拉链算法更符合数据仓库的历史数据跟踪。然而，在现实中，由于业务和性能的考虑，经常会有完全删除、完全插入和增量累加算法的数据表应用。

四、算法详解

1、全删全插算法

dml语句中delete/insert实现的逻辑主要应用于维度表、参数表、主文件表的加载，即适合源表为全数据表，该数据表的业务逻辑目前只需要保存最新的全数据，不需要跟踪过去的历史信息。

sql代码模型：

步骤1。清空目标表

截断表目标表名；

步骤2。完全插入

插入目标表名(字段1，* * *)

选择字段* * *

来源表

***join关联数据

where * * *

2、增量累全算法

使用upsert实现逻辑主要用于加载参数表和主文件表，即源表可以是增量表，也可以是全数据表，目标表始终有最新最完整的记录。

sql代码模型：

步骤1。创建处理源表的临时表* * *；

插入临时表(字段* * *)

选择字段* * *

来源表

***join关联数据

where * * *

—步骤2。merge into可以用来实现集成。目前还可以使用分步删除/插入或更新/插入操作

作为t1合并到目标表中(字段* * *)

使用临时表作为s1

on(pk)

当匹配时

更新集colx=s1。colx ***

当不匹配时

insert (field ***)值(field * * *)；

3、增量累加

用append实现逻辑，主要用于加载流表，即在目标表中加入日流量和事件数据，保留整个历史数据。流程表、快照表、统计分析表都是通过这个逻辑实现的。

sql代码模型：

步骤1。插入目标表

插入目标表(字段1***)

选择字段* * *

来源表

***join关联数据

where * * *

4、全历史拉链算法

拉链表定义：它是一个数据仓库etl数据表，至少由pk字段、轨迹变更字段、开链日期和闭链日期组成。

拉链表的优点：可以根据开链日期和闭链日期快速提取对应日期的有效数据。对于跟踪源系统的非事件流表数据，拉链算法起着更重要的作用，源业务系统的日变化数据通常是有限的。拉链处理可以在不丢失数据变化历史的情况下，大大减少每日快照带来的空间开销。

全历史拉链表算法的实现逻辑：提取当前有效记录-提取当前源系统的最新数据-根据pk字段将当前有效记录与最新源表进行比较，更新目标表的当前有效记录，进行闭链操作-根据所有字段将最新源表与当前有效记录进行比较，插入目标表。

sql代码模型：

步骤1。提取当前有效记录

插入临时表-开链-预(不包括开链字段* * *)

选择不包括开/闭链字段* * *

从目标表

其中结束日期=日期“最大日期”；

步骤2。提取日源系统的最新数据

源表的临时表

—步骤3今天的所有开放链数据，即今天包含新插入和数据更改的记录。

插入临时表-增量插件

选择不包括开/闭链字段* * *

临时表来自源表-当前

其中(不包括开闭链字段***)不在

(选择不包括开闭链字段* * *

从临时表-开链-预)；

—4今天，我们需要闭环数据，即今天的变更记录

插入临时表-增量-更新

选择不包括开闭链字段* * *，开始时间

临时表从-开链-预

其中(不包括开闭链字段***)不在

(选择不包括开闭链字段* * *

临时表从开链到关

);

步骤5更新闭链数据，即历史闭链(删除-插入替换更新)

从目标表中删除

在哪里(主键***)

(从临时表中选择主键***增量更新)

和结束日期=日期“最大日期”；

插入目标表

(不包括开放/关闭链字段* * *、开始时间和结束日期)

选择不包括开闭链字段* * *，开始时间，日期"数据日期"

临时表从-递增-递增；

—6插入开链数据，即当天添加记录

插入目标表

(不包括开放/关闭链字段* * *、开始时间和结束日期)

选择不包括开/关链字段***，日期"数据日期"，日期"最大日期"

临时表从增量导入；

5、增量拉链算法

算法实现的逻辑是提取前一天开链数据-pk相同的变更记录，关闭旧的记录链，打开新的记录链-pk不同，源表存在，增加新的开链记录。增量拉链的目的是跟踪数据的增量变化历史，根据pk对比拉一个新的开链数据。

sql代码模型：

—步骤1。提取当前有效记录

插入临时表-开链-预(不包括开链字段* * *)

选择不包括开/闭链字段* * *

从目标表

其中结束日期=日期“最大日期”；

步骤2。提取当天源系统的增量记录

源表的临时表

—步骤3。选择日源系统的新记录

插入临时表-增量插件

选择不包括开/闭链字段* * *

临时表从开链到关

哪里(pk)不在

(从临时表-开链-预中选择pk)；

步骤4。提取日源系统的历史变更记录

插入临时表-增量-更新

选择不包括开/闭链字段* * *

临时表从开链到关

内部连接临时表-开链-预

on(pk 等值)

哪里(变化字段非等值)；

步骤5。更新历史变更记录，关闭旧的历史链，打开新的历史链

将目标表更新为t1

设置变化字段 s1赋值,结束日期=日期"数据日期"

临时表从-增量-更新为s1

哪里(pk 等值)

和t1。结束日期=日期"最大日期"

插入目标表

(不包括开放/关闭链字段* * *、开始时间和结束日期)

选择不包括开/关链字段***，日期"数据日期"，日期"最大日期"

临时表from-increment-upd；

第6步。插入所有新链接数据

插入目标表

(不包括开放/关闭链字段* * *、开始时间和结束日期)

选择不包括开/关链字段***，日期"数据日期"，日期"最大日期"

临时表from-increment-ins；

6、增删拉链算法

算法的逻辑是：提取前一天的开链数据-提取源表的未删除记录-pk同变记录，关闭旧记录链，打开新记录链-pk对比，源表存在，添加开链记录-提取源表的删除记录-pk对比，旧开链记录存在，关闭旧记录链。

sql代码模型：

—步骤1。清理目标表

截断表目标表；

-第二步。完全插入

插入目标表(字段* * *)

选择字段* * *

来源表

***join关联数据

where * * *

7、全量增删拉链算法

算法实现逻辑是提取最后一天的开链数据-提取源表pk中未删除的记录相同的变更记录，关闭旧记录链，打开新记录链-pk比较，源表存在，添加开链记录-提取源表中删除的记录-pk比较，旧开链记录存在，关闭旧记录链-pk比较，提取旧开链中存在但不在源表中的记录，关闭旧记录链。主要使用业务字段来跟踪包含在总数据中的删除的变更历史。

sql代码模型：

—步骤1。清理目标表

截断表目标表；

-第二步。完全插入

插入目标表(字段* * *)

选择字段* * *

来源表

***join关联数据

where * * *

8、自拉链算法

根据源表的业务日期字段，与目标表的起止日期进行对比，首尾相连，拉出全历史拉链，主要是将流水表数据转换成拉链表数据。

sql代码模型：