ETL數倉測試

葛上亭長發表於2021-08-03

前言

datalake架構

離線資料 ODS -> DW -> DM

實時資料

名詞解釋

名詞 描述
源表 資料清洗之前的原始資料表
目標表 資料清洗之後的表
單源 一個渠道的資料落到一個表裡面
多源 多個渠道的資料落到一個表裡面
增量更新 每次只對需要更新的時間範圍進行更新
全量更新 每次更新時都需要更新整個資料集
關聯邏輯 一組關聯邏輯裡含3部分：關聯關係、過濾條件、聚合取行
寬表 寬表從字面意義上講就是欄位比較多的資料庫表。通常是指業務主題相關的指標、維度、屬性關聯在一起的一張資料庫表。
指標型別 1、基礎指標：指表達業務實體原子量化屬性的且不可再分的概念集合，如交易筆數、交易金額、交易使用者數等。
2、複合指標：指建立在基礎指標之上，通過一定運算規則形成的計算指標集合，如平均使用者交易額、資產負債率等。
3、派生指標：指基礎指標或複合指標與維度成員、統計屬性、管理屬性等相結合產生的指標，如交易金額的完成值、計劃值，累計值、同比、環比、佔比等。
4、標準資訊：經過標準化處理的實體非量化屬性資訊，或分段統計資訊，省份（北京、廣州等）、繳費方式（繳費、充值）渠道（網廳、直充、專區）、消費金額（30元、50元、100元等）。

ETL測試

1、關聯邏輯測試

1.1、內容

由資料來源一個表或多個表關聯並增加一些過濾條件，如果需求中要求根據業務主鍵分組取其中一條，那麼過濾條件中需要有分組取TOP1的邏輯，如DL層根據order_no分組，biz_date倒序>fetch_time倒序>file_line_no倒序的排序規則取top 1記錄，能夠儘量保證所取的結果比較穩定。

關聯的過程如果非常複雜，可能有多個子關聯的步驟，本質上一個子關聯就是上面的情況。

普通關聯一般有以下3部分組成：

  • 表之間的join關係；
  • 過濾條件，如果業務隔離、無效資料隔離、重複資料取一條；
  • 基於前面兩步後的取record的邏輯，業務主鍵相同的資料分組取TOP1。

源主表和目標表關聯欄位的對應關係有幾類：

源主表關聯欄位 目標表關聯欄位 關係 備註
不為主鍵 為主鍵 多對1 分組取一條或者分組合並
為主鍵 為主鍵 1對1 總量保持不變
為主鍵 不為主鍵 1對多 如表拆分，ETL中暫未遇到這些情況
不為主鍵 不為主鍵 多對多 如平移的情況

1.2、方法

驗證目標表的資料量，實現ETL的關聯邏輯將源表關聯起來，得到關聯之後的資料量，跟實際ETL之後的資料量對比，預期這兩個資料量是一致的。

其中對於複雜關聯策略，將源表關聯起來的時候可以拆成多個步驟來實現，每個步驟的測試過程需要一箇中間表，這個中間表的形式可以是新建的一個普通表，或者一個臨時表，或者一個檢視。

測試join後分組取排序取Top1的情況是否穩定可參考大資料原則中第七個原則的case4。（以後有時間了會再寫一篇文章講講大資料原則）

關注因關聯關係而丟掉的資料量，如果資料量過大，需要相關DA關注並進一步分析是否存在潛在資料問題。

關注因過濾條件而篩掉的資料量，如果資料量過大，需要相關DA關注並進一步分析是否存在潛在資料問題。

1.3、資料質量分析

在需求分析後，對內容和測試方法有了方向後，需要對源資料進行表畫像和關聯表畫像的分析， 評估待測資料是否有質量問題，如果有再跟ETL設計溝通是上游資料問題還是設計問題。

1.4、案例

型別 邏輯 細節 資料質量分析 備註
關聯關係 A left join B on A.a = B.b A表的關聯欄位組合起來是否是主鍵，如果不是主鍵則B表有資料關聯不上發生丟資料
過濾條件 order_no is not null
聚合取行 group by order_no order by xdate desc，ydate.asc 分組排序取Top1，按order_no分組，根據xdate降序排、ydate升序排，然後取第一條記錄
多層關聯邏輯 通過多組關聯邏輯得到一個或多箇中間結果表；再基於中間表等通過一組關聯邏輯得到最終ETL的中間表 關聯邏輯的解釋可參看名詞解釋

2、轉換測試

2.1、內容

欄位取值邏輯

  • 通用取值邏輯，通常可以定義一些通用的UDF來處理：

    ​ 資料格式的合法性，如對於資料來源中時間、數值、字元等資料的處理，是否符合資料倉儲規則，是否進行統一的轉換；

    ​ 空值的處理，如是否捕獲欄位空值，或者需要對空值進行替換為其他含義值的處理。

  • 業務取值邏輯

    ​ 值域的有效性，如是否有超出維表或者業務值域的範圍；

    ​ 主鍵的有效性，如主鍵是否唯一；

    ​ 髒資料的處理，如比如不符合業務邏輯的資料。

  • 其它

    ​ 源表資料型別轉換成期望的目標表資料型別；

    ​ 驗證源表編碼轉換成期望的目標表編碼；

    ​ 亂碼的檢查，如特殊符號或者亂碼符號的護理規則；

    ​ 目標表增加欄位的內容和邏輯，如增加了一些ETL時間的欄位等；

    ​ 抽樣測試，根據具體情況確定樣本量。

2.2、方法

驗證目標表資料內容的正確性。

基於1.2中的由ETL的關聯關係得到的中間表，抽樣或者全量資料基於中間表逐一將單個欄位進行邏輯處理，得到期望結果，驗證實際結果是否匹配期望結果。

2.3、資料質量分析

在需求分析後，對2.1內容和2.2測試方法有了方向後，需要對源表中資料進行欄位畫像的分析，評估待測資料是否有質量問題，如果有再跟ETL設計溝通是上游資料問題還是設計問題。

2.4、案例

型別 邏輯 細節 資料質量分析 備註
通用 髒資料處理 在某一層（例如落到DW時）將髒資料置為空串，或null，且在系統中保持一致
業務隔離 將不符合規則的資料過濾（如電話號碼格式不正確） GRL: 在ods 加一個data_invalid_type 的分割槽，將合法和不合法資料儲存在不同的分割槽中
通用 欄位型別轉換 欄位型別轉換處理，遵循兩個原則，即含髒資料處理邏輯、只能大轉小 通用型別的可以定義為UDF：比如在 ETL過程中會對一些敏感資料做加密，可將加密邏輯做成 UDF 函式，可以供所有的表使用
string - > date if （yyyy-MM-dd HH:mm:ss字串）
{轉date型別}
else {null}		 E.g.: to_date('2019-04-19 10:29:50')
string - > timestamp if （yyyy-MM-dd HH:mm:ss字串）
{轉timestamp型別}
else {null}
timestamp - >date if （timestamp）
{轉date yyyy-MM-dd HH:mm:ss型別}
else {null}		 select from_unixtime(cast(timestamp時間戳/ 1000 as int), 'yyyy-MM-dd HH:mm:ss');
date(yyyy-MM-dd HH:mm:ss) - >date (yyyy-MM-dd) 時間欄位擷取到天 select substr(date,0,10)
date(yyyy-MMM-dd HH:mm:ss) - >date (yyyyMMMdd) 時間欄位格式轉換 from_unixtime(unix_timestamp(substr(date,0,10),'yyyy-MM-dd'),'yyyyMMdd')
string - > int if （數值字串&在int型別的取值範圍）
{轉int型別}
else {null}		 INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)；
Hive: Cast(欄位 as int)
int - > bigint
varchar - > char
空字元 - > null select count(1) from 表 where 欄位=''; 預期是0 減少資料大小，規範資料格式
通用 isnull(A,B,C)
視窗函式：
row_number() over(partition by XXX order by XXX)		 用於多條記錄選取其中一條的情況，如選取使用者最後一次購買的產品 row_number() over(
partition by userid
order by date desc )
時間相關 計算日期差：
datediff(date1,date2)		 用於計算兩個日期之間的間隔 select datediff(to_date('2020-12-03'),to_date('2020-12-01'));
--2
計算月份差：months_between() 用於計算兩個日期的月份間隔 一：select months_between('2020-10-10','2020-09-10');
--1
二： select months_between(to_date('2020-10-10'),to_date('2020-09-01'));
--1.29032258
取某個月份的同一天add_months() 如：取一年前的20號 select add_months('2020-12-21',-12);
--2019-12-21
計算多少天后：date_add() select date_add('2020-09-15',1);
--2020-09-16
條件函式 if(A,B,C) 如果A滿足，則返回B, 否則返回C select if(1>2,1,0);
--0
case when 條件1 then 結果1
when 條件2 then 結果2
...
end		 如果滿足條件1 則返回結果1
如果滿足條件2 則返回結果2
...
字串函式 substr(order_code,1,2) 字串擷取 select substr('abcde',0,2) ;
--ab
length() 返回字串的長度 select length('abcde');
--5
instr() 尋找字串的位置,沒有找到則返回0 select instr('abcde','b');
--2
split() 字串分割 select split('EI4331_1','_')//返回陣列型別
["EI4331","1"]
select split('EI4331_1','_')[0]
EI4331
regexp_replace() 字串替換函式 select regexp_replace('abcdef','c','g');
--abgdef

3、更新策略測試

3.1、內容

資料遷移到數倉的過程，通常步驟如下：

​ Step 1：初始化數倉，即將歷史全量資料遷移到資料倉儲；

​ Step 2：定時啟動遷移動作。

定時啟動遷移動作將新生成的資料或者變化的任何資料更新到資料倉儲的過程，就需要考慮更新邏輯或者叫更新策略。

注意，每個目標表都有Step 1和Step 2所用到的兩種更新策略，如果開發實現是不同的，那麼要分開測試。

3.2、方法

驗證目標表資料量和資料內容的正確性。

3.2.1、單源增量更新

目標表僅單一源資料，如Tmall資料。

Pre-Condition：源表的關聯、轉換邏輯是正確的，這裡單獨測單源更新策略。

Step 1：構造源表測試資料；

Step 2：執行ETL，得到實際落地目標表的資料；

Step 3：構造源表中滿足更新策略的資料；

Step 4：執行ETL，驗證目標表中落地資料量和內容的正確性。

3.2.2、多源增量更新-全表刪除插入方式

目標表有多個源資料，如Tmall、JD、Offline資料。

Pre-Condition：源1、源2單獨的關聯、轉換邏輯是正確的，這裡單獨測不同源之間的更新策略。

Step 1：構造源1測試資料；

Step 2：執行ETL，得到實際落地目標表的資料；

Step 3：構造源2中滿足更新策略的資料；

Step 4：執行ETL，得到實際落地目標表的資料；

Step 5：根據關聯、轉換邏輯，設計sql得到源2單獨落地的目標資料；

Step 6：用Step 5中資料根據更新策略覆蓋Step 2中落地目標表的資料，得到Step 4期望的目標表資料；

Step 7：對比Step 4和Step 6，驗證實際落地資料量和內容是否正確。

3.2.3、增量更新-時間戳方式

測試結果是否遺漏資料，如果為時間戳方式，要尤其注意時間戳是否帶時分秒
增量規則是否正確，對於源表做好足夠的資料探查，明白源表中的資料的增量是怎麼回事，必要時需要討論，然後根據業務規則做增量規則方案。

3.2.4、全量更新-全量載入方式

同關聯和轉換部分的方法，所以本質上全量更新策略不需要單獨測試。

3.2.5、測試執行的小TIPS

分來源的表，建議開發每準備啟動ETL Job處理下一個來源前備份一次資料；

構造資料時，準備全面的資料，避免來回上傳叢集重跑資料，佔資源，浪費時間。

3.2.6、增量/全量的適用場景
  1. 全量同步主要是對資料量少的表，比喻電商專案中的商品表、使用者表、商戶表、地區表、訂單狀態等實體和維度表，這類資料量少，可以使用全量同步。優點在於簡單快捷.
  2. 增量同步主要是針對資料量龐大的大的場景，比喻訂單表，每天產生的訂單表資料量龐大，如果使用全量同步，資料佔用磁碟的將會非常恐怖，所以一般會採用增量同步的方式。這種方式邏輯較複雜，速度較慢，且要求源表主鍵能夠匹配識別。

4、排程策略測試

4.1、內容

整個ETL處理的過程有多個資料層，假如將每一層的處理到每個目標表的過程定義一個的Job，那麼會有很多Job，同一層的Job可能會存在依賴關係，層與層之間肯定會存在依賴關係。因此，快速見效的ETL處理流程將是一個比較複雜的過程，為了對複雜的ETL處理過程更好的排程，更充分得利用系統資源，從而要考慮和設計最優的排程策略。

根據更新策略的更新週期可以確定排程策略的週期，預期是保持一致的。

驗證排程策略依賴關係是否合理，最終落地資料的完整性、正確性。

驗證job的上下游配置正確。

4.2、方法

測試方法同關聯、轉換、更新策略。

5、效能測試

5.1、內容

基於某環境引數和資料量，統計單個任務或任務樹的執行時間以及資源佔用情況，以確認改進的效能和可擴充套件性，可貫穿整個測試周期。

對於任務樹的效能，確保資料在規定和預計的時間內被載入到相應的位置。

效能指標，在指定資料量的情況下，一般從第一個job開始到最後一個job執行結束所花費的時間跨度越短、資源利用率越在70%左右越好。

5.2、方法

統計不同資料量下每個Job執行的時間；

統計不同資料量下全部Job執行的時間；

統計不同資料量下系統資源利用率。

6、測試用例

目前專案的測試用例一般覆蓋以下幾個部分:

  • 關聯；
  • 轉換；
  • 排程策略；
  • 更新策略。

具體的專案測試用例根據專案的測試範圍來決定測試用例覆蓋的內容。

關於轉換邏輯部分的編寫說明：
1、if else的情況：

  • 條件邏輯對比總數【測試用例裡體現一個，實際可抽樣兩個寫進測試用例】；
  • 每個條件邏輯抽樣對比所有欄位；

2、列舉型別：

  • distinct目標表欄位，驗證結果是否在列舉範圍內；
  • 列舉型別對比總數【測試用例裡體現一個，實際可抽樣兩個寫進測試用例】；
  • 部分列舉型別抽樣對比所有欄位；
  • 源資料不再此列舉範圍內的，驗證全部留空

3、isnull(A,B,C)

同如下邏輯:
if A is not null and A <> ''
取A;
else if B is not null and B <> ''
取B；
else if C is not null and C <> ''
取C；
else
取NULL

注意：資料庫欄位長度的空字串值全都重置為null
驗證方式：

確定表之間的關聯邏輯；
查詢A非null和‘’，驗證取A
查詢A是null且B非null，驗證取B；
查詢A是‘’且B非null，驗證取B；
查詢A是null且B是null且C非null，驗證取C；

7、可能遇到的挑戰

ETL設計複雜，設計人員沒有花足夠的精力去優化，導致開發和測試人員分析困難；

需要足夠的敏感性去分析ETL設計是否合理，如果關聯部分分組取TOP1是否穩定合理，轉換邏輯中的列舉狀態是否覆蓋源資料所有值；

ETL關聯、轉換、更新策略、排程策略的複雜性是非常巨大的。

相關文章