ETL 資料擷取、轉換、載入系列（二）：資料轉換七步驟
前一篇文章我們針對 ETL（Extract-Transform-Load，提取轉換載入）的資料移轉程序，提出了幾個需要注意的規劃原則與應用方式。這次我們將再細部探討轉換的注意事項，最後導向資料轉換計畫。
在 ETL 執行程序中，可分為三大部分：
- 資料擷取（Extract）：資料萃取的程序，包含由資料源取得資料，例如編碼轉換、資料檢核，以及退回不合規格的資料。
- 資料轉換（Transform）：資料轉換的程序主要是內容轉換，包括代碼的轉換，甚至是一筆變多筆或多筆變一筆的情況。
- 資料載入（Load）：資料載入目標系統或產生目標系統所需的格式，此步驟需包含產出目標系統前保留一份與目標系統相同內容的資料。
ETL 作業細部說明
1. 資料擷取（Extract）
大部分資料移轉作業，特別是由專屬系統移轉至開放系統平台，通常必須由各個不同的資料源系統取得所需之數據資料。來源系統可能採用不同的架構、格式、方法儲存數據資料。不論是自行撰寫程式，或使用既有工具來執行資料擷取的工作，都必須注意到程式或工具對於擷取介面的可擴充性及多樣性，以便處理不同的資料源。
一般常見的資料來源為「文字檔案格式」和「關聯式資料庫系統」。若有非通用的資料格式或資料庫，通常建議由來源端產生一個通用格式的介面檔，尤其是對於一個資料有多個資料源時，此點特別重要。
特別是由大型主機的專屬系統移轉至開放系統平台， 因為系統可能已經運作數十年，除了專屬系統的資料格式較為特殊外，其中資料經過經年累月的累積，可能也有一些資料不完整的情形，有必要由來源端事先整理。
在資料由資料源萃取時，原則上盡量保留原始資料內容，但若有編碼不同，則需先轉換編碼，以便於處理資料。若資料源是由文字檔取得，則建議利用一個原始資料的欄位，保留完整的原始資料。
另外需進行初步資料核驗。針對擷取出來的資料，比對其格式和結構是否符合所需，例如：是固定欄位長度內容的資料、還是用分隔符號定義的資料？以何者為分隔符號？如果不符合，則可依事先定義對應的處理原則決定該採取何種反應，如：停止整個 ETL 流程，或將有問題的部份資料紀錄起來並發出警告，但整個 ETL 流程仍持續進行。
2. 資料轉換（Transform）
經過資料萃取的步驟後，資料編碼已經統一並存在於相同的儲存媒體（通常是資料庫）。此步驟主要依據目的端所需的資料格式或內容，將來源端的資料轉換成目的端所需資料，由於資料可能散佈於不同資料源，各自有不同的格式與定義，基於新系統的商業邏輯需求，因此需要重新彙整或分割。
資料轉換通常包含以下的作業：
- 多筆相關聯的資料依優先順序規則合併成一筆或分割為多筆。例如：客戶資料可能來自於多個資料源，當有相同欄位時，要以哪個資料源為主。
- 依據篩選規則，過濾出符合條件的資料、排除不需要的資料，並計算輸入、輸出、排出的資料筆數。
- 轉換或設定部份欄位的資料值，或經由各式各樣的文字或數值運算產出新欄位的資料值，例如：由民國年轉換為西元年，或由數值格式轉換為文字格式。
- 針對資料的某些欄位作排序、分組、計算產出新欄位的資料值。
- 依照部份欄位的資料值進行分割，或多欄位合併產生新資料。
- 參照其他資料表，帶出相關欄位值。
- 依照資料欄位值分割成不同處理流程。
- 產生資料的唯一流水號或帶出預設值。
基於各種不同的轉換與合併需求，ETL 工具中關於資料轉換的元件通常是最多元、最豐富的。所以在決定 ETL 產品時，系統需求與目的也是重要考量之一。
3. 資料載入（Load）
因為系統轉換需求所做的資料移轉，通常在資料擷取（Extract）與資料轉換（Transform）時，已完成大部分工作。此步驟是依照目的端所需的格式，將資料轉換至新的目的格式，必要時也要將資料傳輸至指定的目的地。
ETL 資料移轉七步驟
資料移轉規劃與一般專案開發前期規劃的步驟大致相同，差別在於焦點較集中，作業上較為單純，但仍需考慮細節。為確保資料移轉的一致性與完整性，需要一個良好的資料移轉執行程序，包含以下七個步驟：
1. 制定移轉計劃
此步驟通常是製作系統移轉計畫時列入考量，主要評估資料移轉所需的時程計畫，確保後續所需的資源與人力，例如：IT 與 User 單位是否可以投入，以確保順利移轉。
計畫內容包括：系統作業週期、前置作業所需的轉換時間、資料凍結時點及所有需轉換的表格清單、預估時程、權責人員…等，以掌控轉換進度狀況。
2. 計畫啟動
開始依計畫進行，此階段主要的工作是取得資料移轉相關人員的共識，確保所有成員有共同的目標。
3. 範圍分析
此步驟主要工作是對於需要移轉的資料，進行轉換範圍的分析及盤點有哪些資料需要移轉。
- 依據規劃之目的端系統資料與來源端資料進行盤點，定義系統架構與表格關聯、資料欄位對應關係與相異關係。
- 目的端與來源端之資料欄位或屬性，如移轉資料缺乏該類資訊，需提出如何補充或調整。
- 系統資料結構部分，需明確規範資料移轉之對應方式。於資料移轉前，需妥善規劃新舊系統資料結構間之對應關係（一對一、一對多或多對一），並據此進行資料移轉。
- 依據欄位資料盤點結果進行簡報說明，並說明資料移轉可能發生的問題，由相關人員討論後確認移轉作業。
4. 解決方案設計
在設計資料轉換的方法時，要一併考量轉換前與轉換後的資料比對方法。
5. 構建和測試
對資料移轉邏輯進行程式設計，或利用 ETL 轉換工具設計轉換規則，並進行測試。資料轉換過程中，應建立移轉 Log 紀錄，以便檢視資料錯誤原因，需包含移轉表格欄位名稱、第 n 筆及產生錯誤發生原因、移轉後資料筆數比對等資訊。
- 建立測試環境，模擬資料移轉程式之執行。
- 移轉程式需包括 Log 之產生，以便檢視錯誤資料，進而調整移轉程式。
- 依前置作業分析結果，如有需要撰寫資料補充或結構轉換的 SQL（Structured Query Language，結構化查詢語言）指令，並驗證轉換規則之完整性，以及對無法轉換資料的處理原則是否適用。
資料轉移所需的時間與資料轉移的前置作業有很密切的關連，前置作業執行的越詳盡，越能於資料轉移前發現問題。事先排除問題或訂定處理原則，能減少資料轉移時的滯延時間。
6. 執行並驗證
實際進行轉換並規劃驗證方式，以確認資料轉換正確無誤。按轉換計畫進行轉置到暫存資料庫，轉置完成後需做表格相關性檢核，並列印處理相關性異常紀錄。
- 針對資料移轉 Log 進行錯誤修正並移轉，逐一檢核各表格的對應筆數。確認無誤後，再抽檢其內容是否有異常之處。
- 就移轉建置後的資料提出驗證規劃，包括資料筆數、每人時數總和比對、正確性及完整性驗證等，並提交驗證結果，以確認資料移轉之正確性。
- 依據測試結果交付測試記錄。
7. 退役與監控
資料轉換並確認無誤後，若屬於應用系統移轉，則應關閉舊系統，以避免舊系統程式存取到舊的資料。若屬於資料倉儲建置或系統整合，則應持續監控，確保新產生的資料已經完整移轉。
結語
以上分享如何製作 ETL 資料轉換計畫，下期我們將解說 ETL 系列（三）轉檔步驟及中繼資料表的規劃，敬請期待！
資通電腦具備 40 多年系統整合開發經驗，提供各項應用程式開發、整合維護、API 介接、資料庫移轉、檔案交換… 等服務，協助政府機關與企業因應業務拓展、作業流程改造、資料管理等問題，為企業資訊化提供最有效率的系統服務。
參考資料：Data Migration Project Checklist: A Template for Effective data migration planning
延伸閱讀：【案例分享】系統整合如何簡化作業流程？
