前言
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、相關定義和關係
| 表型別 | 定義 |
|---|---|
| 增量表 | 資料每次落地此表是其所對應業務的新增或更新的記錄，如新增的訂單資訊和狀態變更的訂單，對應更新策略為全量更新 |
| 全量表 | 資料每次落地此表是其所對應業務的全部體量記錄，如全部會員使用者資訊 |
| 更新策略 | 定義 |
|---|---|
| 增量更新 | 當目標表的型別為增量表，對應更新策略為增量更新 |
| 全量更新 | 當目標表的型別為全量表，對應更新策略為全量更新； 特殊：增量表在初始化資料或者relaod情況下也是全量更新 |
2、明確區分欄位
明確源表和目標表中分割槽欄位、哪些欄位是為了更新策略設計的。
3、明確詳細更新策略
結合業務分析確定更新策略，如所選的排序欄位是否符合業務需求。
- 源表型別是全量表還是增量表；
- 每次更新取源資料的範圍；
- 目標表型別是全量表還是增量表；
- 如何落地儲存，是覆蓋、追加還是更新；
| 更新策略 | 源表分類 | 源資料範圍（一組資料來源） | 目標表分類 | 如何落地 | 備註 |
|---|---|---|---|---|---|
| 增量更新 | 增量表 | 源表新增記錄 | 增量表 | 全部新分割槽儲存 | 多用在數倉第一層，即無腦儲存 |
| 增量更新 | 增量表 | 源表新增和變化的記錄 | 增量表 | 新增主鍵記錄新分割槽儲存+舊分割槽更新 | 多用在數倉第二層及往後 |
| 增量更新 | 增量表 | 源表新增和變化的記錄 | 增量表 | 全部新分割槽儲存+舊分割槽刪除 | |
| 全量更新 | 增量表 | 源表全量記錄，即該業務歷史全量記錄 | 全量表 | 歷史記錄全量清空，再落地 | |
| 全量更新 | 增量表 | 源表全量記錄，即該業務歷史全量記錄 | 全量表 | 新分割槽落地，舊分割槽保持不變 | |
| 全量更新 | 全量表 | 源表全量記錄 | 全量表 | 歷史記錄全量清空，再無分割槽落地 | 用在不關注歷史變化的表中 |
| 全量更新 | 全量表 | 源表全量記錄 | 全量表 | 新分割槽落地，舊分割槽保持不變 | 多用在數倉第一層，即保留了歷史變化情況 |
| 全量更新 | 全量表 | 源表最新分割槽記錄，即為該業務全量記錄 | 全量表 | 歷史記錄全量清空，再無分割槽落地 | |
| 全量更新 | 全量表 | 源表最新分割槽記錄，即為該業務全量記錄 | 全量表 | 新分割槽落地，舊分割槽保持不變 | 這種情況說明上下兩層都將全量歷史記錄儲存，比較浪費資源，所以不太會出現這樣的場景 |
| 增量更新 | 全量表 | 源表最新分割槽記錄對比次新分割槽記錄，即該業務全量記錄對比上次資料落地的全量記錄，取其中變化的記錄 | 增量表 | 新分割槽落地，舊分割槽保持不變 | 這種情況不太會用到 |
| 全量更新-表初始化/Reload | 增量表 | 源表全量記錄 | 增量表 | 歷史記錄全量清空，再按分割槽落地 | |
| 全量更新-表初始化/Reload | 增量表 | 源表全量記錄 | 全量表 | 歷史記錄全量清空，再按分割槽落地 | |
| 全量更新-表初始化/Reload | 增量表 | 源表全量記錄 | 全量表 | 歷史記錄全量清空，再按分割槽落地 | |
| 全量更新-表初始化/Reload | 全量表 | 源表全量記錄 | 全量表 | 歷史記錄全量清空，再按分割槽落地 | |
| 全量更新-表初始化/Reload | 全量表 | 源表全量記錄 | 全量表 | 歷史記錄全量清空，再按分割槽落地 |
注：
對於增量更新策略，需要注意評估實際資料的取數邏輯是否有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、是否有監控系統。