**一、ETL定義 **

ETL是将業務系統的數據經過抽取、清洗轉換之後加載到數據倉庫的過程，目的是将企業中的分散、零亂、标準不統一的數據整合到一起，為企業的決策提供分析依據。

二、ETL算法導圖

三、算法應用場景

這8種ETL算法，其中主要分成4大類，增量累加、拉鍊算法是更符合數據倉庫曆史數據追蹤的算法，但現實中基于業務及性能考慮，往往存在全删全插、增量累全算法的數據表應用。

四、算法詳解

1、全删全插算法

用DML語句中的Delete/Insert實現邏輯，主要應用在維表、參數表、主檔表加載上，即适合源表是全量數據表，該數據表業務邏輯隻需保存當前最新全量數據，不需跟蹤過往曆史信息。

SQL代碼模型：

--步驟1. 清空目标表

TRUNCATE TABLE <目标表名>;

--步驟2. 全量插入

INSERT INTO <目标表名> (字段1，***)

SELECT 字段***

FROM <源表>

***JOIN <關聯數據>

WHERE ***;

2、增量累全算法

用Upsert實現邏輯，主要應用在參數表、主檔表加載上，即源表可以是增量或全量數據表，目标表始終最新最全記錄。

SQL代碼模型：

--步驟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實現邏輯，主要應用在流水表加載上，即每日産生的流水、事件數據，追加到目标表中保留全曆史數據。流水表、快照表、統計分析表等均是通過該邏輯實現。

SQL代碼模型：

--步驟1.插入目标表

INSERT INTO <目标表> (字段1***)

SELECT 字段***

FROM <源表>

***JOIN <關聯數據>

WHERE ***;

4、全曆史拉鍊算法

拉鍊表定義：是一張至少存在PK字段、跟蹤變化的字段、開鍊日期、閉鍊日期組成的數據倉庫ETL數據表。

拉鍊表優勢：根據開鍊、閉鍊日期可以快速提取對應日期有效數據，對于跟蹤源系統非事件流水類表數據，拉鍊算法發揮越大作用，源業務系統通常每日變化數據有限，通過拉鍊加工可以大大降低每日打快照帶來的空間開銷，且不損失數據變化曆史。

全曆史拉鍊表算法實現邏輯：提取當前有效記錄-提取當日源系統最新數據-根據PK字段比對當前有效記錄與最新源表，更新目标表當前有效記錄，進行閉鍊操作-根據全字段比對最新源表與當前有效記錄，插入目标表。

SQL代碼模型：

--步驟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相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK不同，源表存在，新增開鍊記錄。增量拉鍊，目的是追蹤數據增量變化曆史，根據PK比對新拉一條開鍊數據。

SQL代碼模型：

-- 步驟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、增删拉鍊算法

算法實現邏輯是，提取上日開鍊數據-提取源表非删除記錄-PK相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK比對，源表存在，新增開鍊記錄-提取源表删除記錄-PK比對，舊開鍊記錄存在，關閉舊記錄鍊。

SQL代碼模型：

-- 步驟1. 清理目标表

TRUNCATE TABLE <目标表>;

-- 步驟2. 全量插入

INSERT INTO <目标表> (字段***)

SELECT 字段***

FROM <源表>

***JOIN <關聯數據>

WHERE ***;

7、全量增删拉鍊算法

算法實現邏輯是提取上日開鍊數據-提取源表非删除記錄_PK相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK比對，源表存在，新增開鍊記錄-提取源表删除記錄-PK比對，舊開鍊記錄存在，關閉舊記錄鍊-PK比對，提取舊開鍊存在但源表不存在記錄，關閉舊記錄鍊；主要是利用業務字段跟蹤全量數據中包含删除的變化曆史。

SQL代碼模型：

-- 步驟1. 清理目标表

TRUNCATE TABLE <目标表>;

-- 步驟2. 全量插入

INSERT INTO <目标表> (字段***)

SELECT 字段***

FROM <源表>

***JOIN <關聯數據>

WHERE ***;

8、自拉鍊算法

根據源表業務日期字段，和目标表開鍊、閉鍊日期比對，首尾相接，拉出全曆史拉鍊，主要将流水表數據轉化成拉鍊表數據。

SQL代碼模型：

--步驟1. 清理目标表

TRUNCATE TABLE <目标表>;

--步驟2. 全量插入

INSERT INTO <目标表> (字段***)

SELECT 字段***

FROM <源表>

***JOIN <關聯數據>

WHERE ***;

在實際工作中所有數據表通常還會包含一些控制字段，即插入日期、更新日期、更新源頭字段，這樣對于數據變化敏感的數據倉庫，可以進一步追蹤數據變化曆史。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!