ETL需求要求

• 2021 年 8 月 19 日
• 筆記
• 測試開發

前言

ETL落地dw層，dw層各表一般是由多個表關聯取數得到的大寬表，在ETL需求中的dw設計應該考慮以下內容，目的是確保需求更清晰，開發和測試才能更高效的進行。

業務需求為基礎

基於業務需求做足夠多的業務分析，來自客戶的整體業務需求、來自標籤、人群、dashboard等具體的業務需求。

關聯邏輯設計

關聯設計，即表之間的join關係、join之後的過濾條件和取record的邏輯。

1、明確主表、依賴表；

2、明確表之間的join關係，即inner join、left join、right join，以及關聯條件；

• 需求中不應該出現模糊的join關係，必須說明是left、right or inner join。

3、主表和依賴表也可以是多個表join得到的；

4、明確join之後的過濾條件，如業務隔離、無效數據隔離、重複數據取一條；

3、明確所有關聯關係中的取record的邏輯，即關聯後全取or分組排序取top 1；

• 兩表關聯時容易產生一個業務主鍵對應多條記錄的問題，確認是否關聯後全取；
• 否則關聯後分組排序取top1，確認分組和排序的條件；
• 分析分組排序後取TOP1是否有不穩定的情況，如果有要麼增加排序條件，要麼接受不穩定的情況。

4、明確和統一常見的處理邏輯：

• 字元串欄位值對比時，明確是否區分大小寫，如默認不區分大小寫，特殊註明才區分；
• 兩欄位值相等判斷時，明確是否將NULL和空字元串做相等處理，具體有以下3點：
  • where條件里是否要將null轉換成”執行；
  • join條件里是否要將null轉換成”執行；
  • 這個邏輯保持跟其它應用一致；
  • 空字元串落地後是否轉成null；
• AB倆數值類型計算(如加減乘除等)、字元串類型關聯時A或B為null會有如下結果，明確是否將null設置默認值後處理；
  • null = null，為null
  • 2 > null，為null
  • 2 + null，為null
  • ‘abc’ = null，為null

轉換邏輯設計

轉換設計，即每個欄位取值邏輯設計。

1、如果有一些通用的取值邏輯，可以定義一些通用的UDF（User Defined Function）；

• 臟數據處理；
• 業務隔離；
• 欄位類型轉換和處理，遵循兩個原則，即含臟數據處理邏輯、只能大轉小，具體例子如
  • string類型轉date和timestamp類型，if （yyyy-MM-dd HH:mm:ss字元串）{轉date類型或timestamp類型} else {null}
  • String類型轉int類型，if （數值字元串&在int類型的取值範圍）{轉int類型} else {null}，注INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)；
  • int類型轉bigint類型；
  • varchar類型轉char類型；
  • 空字元串落地轉null。（評估會有什麼影響？）

2、不管是通用的還是非通用的取值邏輯，最優以偽程式碼的形式說明，如下：

• if (){} else if (){} else {}
• isnull(A,B,C)
• modify columnName date

3、明確和統一常見的處理邏輯：

同關聯邏輯設計。

4、其它

• 明確關鍵欄位的業務說明，如哪個欄位是訂單生成日期，哪個欄位是訂單變更日期；
• 欄位類型轉換是否合適，如date(yyyy-MM-dd) 、timestamp(yyyy-MM-dd HH:mm:ss)，盡量不轉String類型；
• 分析和評估各種數值類型的欄位在業務上是否可能超過其範圍，如INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)。

更新策略設計

更新策略設計，即ETL動作將新生成的數據或者變化的任何數據更新到數據倉庫的過程的設計。

1、相關定義和關係

2、明確區分欄位

明確源表和目標表中分區欄位、哪些欄位是為了更新策略設計的。

3、明確詳細更新策略

結合業務分析確定更新策略，如所選的排序欄位是否符合業務需求。

• 源表類型是全量表還是增量表；
• 每次更新取源數據的範圍；
• 目標表類型是全量表還是增量表；
• 如何落地存儲，是覆蓋、追加還是更新；

註：

對於增量更新策略，需要注意評估實際數據的取數邏輯是否有TOP1不穩定的情況；

確定更新策略方案的前提下，明確目標表的數據源個數、落地順序（子調度策略），上表的前提是源數據是固定一組；

關於「全量更新-表初始化/Reload」：

• 數據倉庫初始化也是一種全量更新的策略，即將歷史全量數據遷移到數據倉庫，後續再切換為定期全量更新或增量更新；
• 表初始化更新策略不可能存在全量表到增量表的情況；
• Reload，即根據更新策略分析重跑當前層ETL的方法，以及後續各層的Reload方法、Recalculate方法，如後續ETL重跑、Dashboard重跑、標籤回算等；
• 本質上數倉表初始化是一種特殊的Reload。

4、更新策略開發實現

增量更新

• 捕捉變化的數據有如下幾種：

  • 採用快照方式，需要業務系統建立insert,update,delete觸發器；
  • 時間戳方式，在業務系統表建一個時間戳欄位，一旦數據發生變化，則修改此欄位；
  • 全表刪除插入方式，每次ETL操作先將目標表數據刪除，然後抽取；
  • hash比對，是全表比對的一個擴展，通過計算主要業務欄位的MD5校驗碼存入hash維表，通過與hash維表的比對進行抽取；
  • 日誌表方式，跟進業務系統的日誌表進行數據抽取；
  • 資料庫變化數據捕捉，通過分析資料庫自身日誌判斷變化的數據；

• 全表刪除插入方式，設計1，

  • Step 1: 源數據進行關聯、轉換後插入目標表;
  • Step 2: 新建目標表的臨時表;
  • Step 3: 在目標表上以KeyID欄位組合分組，按目標表中落地的時間欄位降序排，取TOP1落到臨時表（此步驟要根據具體的業務設計）;
  • Step 4: 刪除目標表；
  • Step 5: 將臨時表更改為正式目標表。

• 全表刪除插入方式，設計2，

  • Step 1: 多組源數據按順序進行關聯、轉換後插入目標表;
  • Step 2: 新建目標表的臨時表;
  • Step 3: 在目標表上以KeyID欄位組合分組，按目標表中落地的時間欄位降序排，取TOP1落到臨時表（此步驟要根據具體的業務設計）;
  • Step 4: 刪除目標表；
  • Step 5: 將臨時表更改為正式目標表。

• 設計1與設計2的區別是設計1中只有一個數據源，設計2中數據會跨源覆蓋。

5、明確更新周期

根據業務評估和定義更新周期

調度策略設計

調度策略設計，即對整個ETL過程中的各個子的處理過程根據依賴關係設計優先順序。

1、明確調度的周期；

2、明確每次調度處理的數據邊界，如今天ETL處理的是昨天的業務數據；

3、明確該次所要處理的數據是否穩定，如果不穩定是否有影響；

• 如源數據按訂單日期來切分處理的數據範圍，即每天零點啟動調度ETL處理訂單日期是前一天的業務數據，但是當天零點之前訂單可能延遲1個小時落到ETL的源數據層，即未取全所有的數據；

4、明確調度啟動時間；

5、明確調度的周期，一般同更新周期；

6、一般採取條件驅動的策略來進行Job的調度，Job一滿足驅動條件便開始運行，明確每個Job使用以下驅動條件中的哪一種：

• 前導Job驅動，ETL過程中各個操作需按一定次序進行，前導Job表示ETL過程中先要進行處理的Job；
• 下傳文件驅動，當下傳文件未下傳完畢時，下傳文件清洗不能進行，因此，下傳文件通常作為清洗文件的驅動條件，當系統檢測到下傳文件已下傳並正確後，便可進行相應的清洗；
• 時間驅動，當到達某個時點時，Job便開始運行；
• 上述三種條件綜合驅動，要上述三種情況至少兩種均滿足，Job才能運行。

7、明確調度的每個驅動條件是否符合業務需求；

8、明確是否有調度監控，如果有測試的時候可用於參考；

9、明確測試的是自動調度過程，而非僅僅手動按調度策略執行的過程；

10、明確調度失敗是否可能會導致臟數據、是否有重新調度等。

其他

1、明確是否有性能指標；

2、是否要考慮故障恢復的情況處理；

3、是否有監控系統。