欢迎您,来到孕妇堂!

孕妇堂首页|手机版

当前位置:首页 > 生活知识 > 生活

etl什么意思

时间:2023-08-28 13:06:06 浏览:89

* *一、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代码模型:

步骤1。清理目标表

截断表目标表;

步骤2。完全插入

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

选择字段* * *

来源表

***join关联数据

where * * *

在实际工作中,所有数据表通常都包含一些控制字段,即插入日期、更新日期和更新源字段,以便对数据变化敏感的数据仓库可以进一步跟踪数据变化历史。

etl什么意思(etl算法详解)

相关文章

猜你喜欢

反馈