將混亂轉化為利潤：了解 ETL 流程

阻止公司向業務用戶提供重要決策信息的最嚴重錯誤之一是缺乏來自一個或多個數據源的可靠數據，這些數據收集在一個位置，有組織並準備好使用。

想像一下這種情況——您是一家手機殼公司的 IT 經理，該公司的零售店遍布美國。 您的董事會正在努力根據銷售情況做出決策，因為信息可用，但位置和格式不同。 一位董事要求您生成一個儀表板，其中包含來自所有銷售點的銷售信息，以便在下次董事會會議上展示。

您已經知道，由於格式和結構不同，整合信息幾乎是不可能的。 一些零售亭仍在使用 Oracle 數據庫中的專有系統。 更大的商店正在使用新的 Salesforce 系統。 在系統過渡期間開始運營的最新信息亭已共享用於計算銷售額的電子表格。

您將如何將來自不同位置、格式和結構的所有數據整理到一個獨特的數據庫中，以便為儀表板生成做好訪問準備？

ETL 基礎知識

ETL 代表提取、轉換和加載。 ETL 是一組流程，旨在將這種複雜的數據存儲轉變為有組織、可靠且可複制的流程，以幫助您的公司利用已有的數據產生更多的銷售額。

在我們的案例中，我們將從 Oracle 數據庫（大多數信息亭）、Salesforce（商店）和電子表格（較新的信息亭）接收數據，提取數據，在必要時進行轉換，然後加載到單個數據倉庫數據庫中通過報告工具訪問並用於生成儀表板和記分卡。

讓我們深入了解 ETL 的三個步驟來詳細說明該過程。

萃取

提取是從單個或多個來源接收數據的過程。 源可以具有不同的格式和結構，例如文檔、電子表格、CSV 文件、平面文件、關係數據庫（如 Oracle、MySQL、SQL Server）、非關係數據庫等。

有兩種主要的提取類型：完全提取和部分提取。

• 完全提取用於初始提取或當數據量和提取時間可接受時。
• 當不需要再次提取所有數據，或者數據量大到無法完全提取時，建議進行部分提取。 在部分提取中，只會提取更新的或新的數據。

除了這些方面，在選擇完全或部分提取時還需要考慮其他一些因素，我想描述其中之一：數據可用性和完整性。

這意味著只考慮提取已完成的事務，從而消除可能導致完整性錯誤的數據。 例如，通過 10 個問題識別工程師知識的在線測試。 如果工程師正在測試中並且回答了幾個問題但尚未完成，則提取過程無法從未完成的測試中讀取問題。 它可能會導致完整性錯誤。

轉型

提取數據後，我們可以開始轉換過程：根據業務規則和技術標準清理、操作和轉換數據，以保持可接受的數據質量水平。

根據許多因素，可能需要使用暫存區。 暫存區是一個中間存儲空間，用於臨時存儲從要轉換的數據源中提取的數據。

在一些項目中，通常是那些數據量很少的項目，沒有必要使用暫存區，但大多數項目都使用它。

在轉換階段執行了一系列任務：

• 選擇：選擇提取數據的標準。 可以在提取階段、轉化階段或兩個階段進行選擇。
• 集成：從提取階段到暫存區的數據組合。 這種組合意味著基於提取的數據在暫存區域中添加、刪除和更新數據。
• Joins：用於連接提取的數據，類似於SQL join（內連接、左連接、右連接、全連接等）
• 清理或擦洗：刪除不一致或無效的數據，或有錯誤的數據，以提高數據質量。 使用多個數據源會增加出現需要清理的數​​據問題的機會，例如：
  • 參照完整性（不存在類別的客戶）
  • 缺失值（沒有 ID 的客戶）
  • 唯一性（多人擁有相同的 SSN）
  • 拼寫錯誤（Sun Diego, Cannada, L.Angeles）
  • 矛盾的價值觀（Alex dob 04.27.1974，Alex dob 04.14.2000）
  • 和許多其他人
• 總結：總結數據集以供後用
• 匯總：分組匯總和匯總的數據
• 合併：來自多個來源或結構的數據合併到一組數據中

以下是一些常見的轉換類型：

• 刪除重複數據
• 拆分和合併
• 轉換（日期、時間、數字掩碼、測量值）
• 編碼（男轉男）
• 計算（item_value = unit_Price * 數量）
• 密鑰生成

正在加載

最後但同樣重要的是，ETL 中的最後一個過程是將數據加載到目的地。 加載是將轉換後的數據（無論是否來自暫存區域）插入存儲庫（通常是數據倉庫數據庫）的行為。

加載數據主要分為三種類型：完整或初始、增量和刷新。

• 完整或初始意味著提取和轉換數據的完整加載。 暫存區中的所有數據都將加載到最終目的地，為業務用戶做好準備。
• 增量加載是將轉換後的數據與最終目標中的數據進行比較並僅加載新數據的過程。 增量負載可以與刷新負載結合使用，如下所述。
• 刷新加載是更新最終目標中的數據以反映原始源中所做更改的過程。 刷新可以是完整的或增量的。

總之，每家公司，無論其規模大小，都可以使用 ETL 流程來整合已經存在的信息，並為決策生成更豐富的信息，將以前無法使用的數據轉化為新的收入來源。

測試

測試是 ETL 最重要的階段之一，但也是最容易被忽視的階段之一。

轉換來自不同來源和結構的數據並將其加載到數據倉庫中非常複雜，並且會產生錯誤。 上面的轉換階段描述了最常見的錯誤。

數據準確性是成功的關鍵，而不准確性則是災難的根源。 因此，ETL 專業人員的使命是在整個過程中保證數據的完整性。 在每個階段之後，必須進行測試。 無論是從單一來源還是從多個來源提取數據，都必須檢查數據以確保沒有錯誤。

任何轉換後都必須這樣做。 例如，在轉換階段匯總數據時，必須檢查數據以保證沒有數據丟失並且總和是正確的。

將轉換後的數據加載到數據倉庫後，必須再次應用測試過程。 加載的數據需要與轉換後的數據進行比較，然後再與提取的數據進行比較。

在我們的手機案例公司示例中，我們使用三種不同的來源（專有的 Oracle 數據庫、Salesforce 和電子表格）和不同的格式。 測試階段可以使用來自原始來源的樣本數據，並將它們與暫存區域中的數據進行比較，以保證提取正確無誤。

樣本數據——在這種情況下可以是來自三個不同位置（商店、舊信息亭、新信息亭）的銷售信息——將與原始數據源進行比較。 必須分析差異（如果有）以查看它們是否可以接受或是否存在錯誤。

如果發現錯誤，則必須對其進行修復，如果需要修復錯誤，則需要做出一些決定：是否應該修改原始數據？ 有可能這樣做嗎？ 如果錯誤無法在原始來源中修復，是否可以通過一些轉換來修復？

在某些情況下，必須消除有錯誤的數據並觸發警報以通知負責人。

一些測試示例：

• 數據需要驗證
• 數據質量
• 表現
• 數據規則
• 數據建模

日誌記錄

記錄 ETL 過程是確保您擁有可維護且易於修復的系統的關鍵保證。

具有正確日誌記錄過程的 ETL 對於保持整個 ETL 操作處於持續改進狀態非常重要，有助於團隊管理數據源、數據格式、轉換、目標等方面的錯誤和問題。

強大的日誌流程可幫助團隊節省時間，使他們能夠更快、更輕鬆地識別問題，並且首席工程師需要更少的時間來直接查明問題。 有時，在提取大量數據的過程中會出現錯誤，如果沒有日誌，很難識別問題——有時幾乎是不可能的。 沒有日誌，整個過程必須重新運行。 使用日誌，團隊可以快速識別導致問題的文件和行，並僅修復該數據。

我能想像日誌不那麼重要的唯一情況是非常小的非自動化系統，其中流程手動運行並且可以手動監控少量數據。

日誌提高了自動化程度。 自動運行的具有大量數據的 ETL 流程需要日誌系統。 如果計劃和執行得當，構建日誌系統所付出的所有努力都將以更快的錯誤識別、更可靠的數據以及在日誌文件中發現的改進點的形式獲得回報。

創建日誌系統主要分為三個步驟：生成、歸檔和分析。

• 生成是記錄 ETL 管道執行過程中發生的事情的過程：該過程何時啟動、正在提取哪個文件或表、保存在暫存區域中的數據、錯誤消息等。 必須記錄所有可以幫助工程師的重要信息。 警告：注意不要產生太多的信息，只會消耗時間和空間，不會有幫助。
• 存檔日誌數據意味著保留過去執行的跟踪記錄以搜索過去的場景，以便識別錯誤或與當前場景進行比較以尋求改進。 檢查歷史中特定點的相關性以保存是很重要的——很久以前的數據，其中結構多次更改，不值得保留。
• 分析。 日誌分析至關重要。 存儲大量未經分析的數據是沒有意義的。 生成和存儲數據只需要時間和金錢。 日誌分析很重要，不僅可以幫助搜索錯誤，還可以識別改進點並提高整體數據質量。

表現

ETL 流程可以處理大量數據，並且可能會花費大量成本——無論是在設置它們所花費的時間方面，還是在處理數據所需的計算資源方面。 在規劃集成時，工程師必須牢記使用所有數據的必要性。 最好的辦法是使用最少的數據量來實現目標，而不是花費時間和金錢來遷移無用的數據。 此外，請記住，數據量往往會隨著時間的推移而增加，因此請嘗試考慮您未來的需求。

設計日誌系統需要付出多少努力？

它取決於許多不同的因素，例如要處理的數據的數量和頻率。 可以手動管理數據很少的小型系統，而無需投資高級日誌系統。

擁有大量數據、許多不同流程、不同數據源和格式以及復雜管道的公司必須投資創建日誌系統。 正如我們已經提到的，一個好的日誌系統可以節省大量的時間和金錢。

對於更明顯的方法，無論公司規模、數據量和集成頻率如何，ETL 流程都必須是有利可圖的。 對時間、金錢和技術資源的投資必須產生回報——正如經濟學家米爾頓弗里德曼所說：“沒有免費的午餐。”

總之，ETL 流程可以幫助公司利用他們已經擁有但沒有以正確方式使用的數據來增加利潤。 ETL 管道可以集成不同的系統、包含重要信息的電子表格以及分佈在不同部門和分支機構的其他數據部分，從而使組織能夠充分利用其數據。