江苏龙网

  1. 主页 > 生活百科 > >

8种ETL调度算法归纳总结，看完这些你就全明白了

ETL调度算法

摘要：ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程 ， 是构建数据仓库的重要一环 ， 用户从数据源抽取出所需的数据 ， 经过数据清洗 ， 最终按照预先定义好的数据仓库模型 ， 将数据加载到数据仓库中 。目的是将企业中的分散、零乱、标准不统一的数据整合到一起 ， 为企业的决策提供分析依据 。
 
ETL算法概览
8种ETL调度算法归纳总结，看完这些你就全明白了

文章插图
 
> 算法应用场景概览
8种ETL调度算法归纳总结，看完这些你就全明白了

文章插图
 
以上共计累积了8种ETL算法 ， 其中主要分成4大类 ， 增量类加、拉链算法是更符合数据仓库历史数据追踪的算法 ， 但现实中基于业务及性能考虑 ， 往往存在全删全插、增量类全算法的数据表应用 。
全删全插模型即Delete/Insert实现逻辑；
> 应用场景
主要应用在维表、参数表、主档表加载上 ， 即适合源表是全量数据表 ， 该数据表业务逻辑只需保存当前最新全量数据 ， 不需跟踪过往历史信息 。
> 算法实现逻辑
1.清空目标表；
2.源表全量插入；
> ETL代码原型.
--1. 清理目标表TRUNCATE TABLE <目标表>; --2. 全量插入INSERT INTO <目标表>(字段***)SELECT 字段***FROM <源表>***JOIN <关联数据>WHERE***;增量类全模型即Upsert实现逻辑；
> 应用场景
主要应用在参数表、主档表加载上 ， 即源表可以是增量或全量数据表 ， 目标表始终最新最全记录 。
> 算法实现逻辑
  1. 利用PK主键比对；
  2. 目标表和源表PK一致的变化记录 ， 更新目标表；
  3. 源表存在但目标表不存在 ， 直接插入；
> ETL代码原型
-- 1. 生成加工源表Create temp Table <临时表> ***;INSERT INTO <临时表> (字段***)SELECT 字段***FROM <源表>***JOIN <关联数据>WHERE ***; -- 2. 可利用Merge Into实现累全能力 ， 当前也可以采用分步Delete/Insert或Update/Insert操作Merge INTO <目标表> As T1 (字段***)Using <临时表> as S1on (***PK***)when Matched thenupdate set Colx = S1.Colx ***when Not Matched thenINSERT (字段***)values (字段*** );增量累加模型即Append实现逻辑；
> 应用场景
主要应用在流水表加载上 ， 即每日产生的流水、事件数据 ， 追加到目标表中保留全历史数据 。流水表、快照表、统计分析表等均是通过该逻辑实现 。
> 算法实现逻辑
1.源表直接插入目标表；
> ETL代码原型
--1.插入目标表INSERT INTO <目标表>(字段***)SELECT 字段***FROM <源表>***JOIN <关联数据>WHERE***;全立式拉链模型> 拉链表背景知识
? 概念
拉链表是一张至少存在PK字段、跟踪变化的字段、开链日期、闭链日期组成的数据仓库ETL数据表；
? 益处
根据开链、闭链日期可以快速提取对应日期有效数据；
对于跟踪源系统非事件流水类表数据 ， 拉链算法发挥越大作用 ， 源业务系统通常每日变化数据有限 ， 通过拉链加工可以大大降低每日打快照带来的空间开销 ， 且不损失数据变化历史；
? 示例 ， 提取指定日期有效数据
8种ETL调度算法归纳总结，看完这些你就全明白了

文章插图
 
提取2020年2月5日当日有效数据
Select *From <目标表>Where 开始日期<=date'2020-02-05'And结束日期 >date'2020-02-05';最终提取到数据：
8种ETL调度算法归纳总结，看完这些你就全明白了

文章插图
 
> 应用场景
全历史拉链 ， 跟踪源表全量变化历史 ， 若源表记录不存在 ， 则说明数据闭链；根据PK新拉一条有效记录 。
> 算法实现逻辑
1.提取当前有效记录；
2.提取当日源系统最新数据；
3.根据PK字段比对当前有效记录与最新源表 ， 更新目标表当前有效记录 ， 进行闭链操作；
4.根据全字段比对最新源表与当前有效记录 ， 插入目标表；
> ETL代码原型
——1. 提取当前有效记录Insert into <临时表-开链-pre> (不含开闭链字段***)Select 不含开闭链字段***From <目标表>Where 结束日期 =date'<最大日期>';;-- 2. 提取当日源系统最新数据<源表临时表-cur>-- 3 今天全部开链的数据 ， 即包含今天全新插入、数据发生变化的记录Insert Into <临时表-增量-ins>Select 不含开闭链字段***From <源表临时表-cur>where (不含开闭链字段***) not in(Select 不含开闭链字段*** From <临时表-开链-pre>);-- 4 今天需要闭链的数据 ， 即今天发生变化的记录Insert into <临时表-增量-upd>Select 不含开闭链字段***,开始时间From <临时表-开链-pre>where (不含开闭链字段***) not in(Select 不含开闭链字段*** From <临时表-开链-cur>);-- 5 更新闭链数据 ， 即历史记录闭链（删除-插入替代更新）DELETE FROM <目标表>WHERE (PK***) IN(Select PK*** From <临时表-增量-upd>)AND 结束日期=date'<最大日期>';INSERT INTO <目标表>(不含开闭链字段***,开始时间,结束日期)Select 不含开闭链字段***,开始时间,date'<数据日期>'From <临时表-增量-upd>;-- 6 插入开链数据 ， 即当日新增记录INSERT INTO <目标表> .(不含开闭链字段***,开始时间,结束日期)Select 不含开闭链字段***,date'<数据日期>',date'<最大日期>'From <临时表-增量-ins>;


推荐阅读


上一篇：车漆保养最好的办法有哪些？

下一篇：2021中秋节放假安排19号会上班吗