ETL算法

ETL算法

1.全删全插

应用场景

适合源表是全量数据表，该数据表业务逻辑只需保存当前最新全量数据，不需跟踪过往历史信息。主要应用在维表、参数表

算法实现逻辑

1.清空目标表；

2.源表全量插入；

ETL代码原型

--   1. 清理目标表
TRUNCATE TABLE <目标表>;
 
--   2. 全量插入
INSERT INTO <目标表>   (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE   ***;

---

2.增量累全模型

即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 S1
on (***PK***)
when Matched then
update set Colx = S1.Colx ***
when Not Matched then
INSERT (字段***)   values (字段*** )
;

---

3.增量累加模型

即Append实现逻辑；

应用场景

主要应用在流水表加载上，即每日产生的流水、事件数据，追加到目标表中保留全历史数据。流水表、快照表、统计分析表等均是通过该逻辑实现。

算法实现逻辑

1.源表直接插入目标表；

ETL代码原型

--   1.插入目标表
INSERT INTO <目标表>   (字段***)
SELECT 字段***
FROM <源表>
***JOIN <关联数据>
WHERE   ***;

---

4.全历史拉链模型

应用场景

全历史拉链，跟踪源表全量变化历史，若源表记录不存在，则说明数据闭链；根据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>;

---

5.增量拉链模型

应用场景

增量拉链，目的是追踪数据增量变化历史，根据PK比对新拉一条开链数据；

算法要求源表增量或者全量。

算法实现逻辑

1.提取上日开链数据；

2.PK相同变化记录，关闭旧记录链，开启新记录链；

3.PK不同，源表存在，新增开链记录

ETL代码原型

--   1. 提取当前有效记录
Insert into <临时表-开链-pre> (不含开闭链字段***)
Select 不含开闭链字段***
From <目标表>
Where 结束日期 =date'<最大日期>';

--   2. 提取当日源系统增量记录
<源表临时表-cur>

--   3. 提取当日源系统新增记录
Insert into <临时表-增量-ins>
Select 不含开闭链字段***
From <临时表-开链-cur>
where (***PK***) not in
  (select ***PK*** from <临时表-开链-pre>);
  
--   4. 提取当日源系统历史变化记录
Insert into <临时表-增量-upd>
Select 不含开闭链字段***
From <临时表-开链-cur>
inner join <临时表-开链-pre>
on (***PK 等值***)
where (***变化字段 非等值***);

--   5. 更新历史变化记录，关闭历史旧链，开启新链
update <目标表> AS T1
SET <***变化字段 S1赋值***>,结束日期 = date'<数据日期>'
FROM <临时表-增量-upd> AS S1
WHERE ( <***PK 等值***> )
AND   T1.结束日期 =date'<最大日期>'
;
INSERT INTO <目标表>
      (不含开闭链字段***,开始时间,结束日期)
SELECT 不含开闭链字段***,date'<数据日期>',date'<最大日期>'
FROM <临时表-增量-upd>;

--   6. 插入全新开链数据
INSERT INTO <目标表>
      (不含开闭链字段***,开始时间,结束日期)
SELECT 不含开闭链字段***,date'<数据日期>',date'<最大日期>'
FROM <临时表-增量-ins>;

---

6.增删拉链模型

应用场景

主要是利用业务字段跟踪增量数据中包含删除的变化历史。

算法实现逻辑

1.提取上日开链数据；

2.提取源表非删除记录；

3.PK相同变化记录，关闭旧记录链，开启新记录链；

4.PK比对，源表存在，新增开链记录；

5.提取源表删除记录；

6.PK比对，旧开链记录存在，关闭旧记录链；

ETL代码原型

---

7.全量增删拉链模型

应用场景

主要是利用业务字段跟踪全量数据中包含删除的变化历史。

算法实现逻辑

1.提取上日开链数据；

2.提取源表非删除记录；

3.PK相同变化记录，关闭旧记录链，开启新记录链；

4.PK比对，源表存在，新增开链记录；

5.提取源表删除记录；

6.PK比对，旧开链记录存在，关闭旧记录链；

7.PK比对，提取旧开链存在但源表不存在记录，关闭旧记录链；

ETL代码原型

应用场景

算法实现逻辑

ETL代码原型