ETL和ELT
ETL是Extract、Transfrom、Load即抽取、轉換、加載三個英文單詞首字母的集合：
E：抽取，從源系統(Souce)擷取資料；
T：轉換，将源系統擷取的資料進行處理加工，比如資料格式轉化、資料精度轉換、資料清洗、缺失資料補齊、異常資料排除等。
L：加載，将資料加載到目标資料庫(Target)。
ELT也是同樣三個單詞的首字母組合，隻是把T、L颠倒了下順序。ETL強調的是先進性資料轉換，然後再加載到目标。這個轉換過程可以在原系統進行，也可以在中間環境進行進行。而ELT是把資料加載到資料倉庫後再進行轉化。ETL優勢是充分利用各關聯系統的性能，提高效率，但程式部署分散，運維成本較高。ELT是充分發揮資料倉庫平台資料加工的高性能，并且可以儲存原始資料友善後續複用。
随着資料倉庫平台的性能越來越高，容量成本越來越低，目前更多的是采用ELT方式，充分利用資料倉庫的高性能，提高加工效率。但在資料加載前也需要進行資料編碼轉化、異常資料等影響加載的處理，確定資料正确加載到資料倉庫平台，但不做資料邏輯加工。
由于ETL出現較早，通常使用ETL來代表資料抽取加載和轉換的統稱。
ETL架構設計
資料ETL需要有ETL伺服器叢集執行資料ETL作業來進行資料抽取、轉換和加載，所有ETL作業的腳本部署多台ETL伺服器上，ETL作業可以根據伺服器資源由排程工具配置設定到任意一台ETL伺服器執行，常見架構如下圖：
ETL架構不僅僅是作為資料倉庫的架構，但也是全行批量資料交換的統一架構和标準，雖然資料倉庫是其中最大的一個資料加載的目标系統和資料源系統，但從架構規劃角度來看，需要從全行、全集團的角度來設計批量資料交換，考慮多機構間互動場景，減少不必要的轉換，提高效率和穩定性。
ETL伺服器叢集需要做到高可用，對于不能正常服務或負載過高的伺服器，排程平台不會将作業配置設定到該伺服器，所有的ETL作業腳本需要在每台伺服器上部署，不能隻部署一份代碼到共享存儲中。
在硬體資源上，伺服器的IO和記憶體需要配置較高，同是由于批量資料容量較大，網絡帶寬需要千兆以上，同時需要考慮在傳輸高峰不能影響交易系統的網絡通訊。
（1）檔案方式和端到端方式
資料抽取和加載從是否經過中間落地成檔案來區分主要有檔案落地方式和端到端不落地（記憶體）的兩種方式。檔案方式指ETL伺服器的抽取資料作業從源系統擷取轉煥為檔案放到檔案共享存儲中，再由加載作業到目标系統中。端到端方式是ETL伺服器從源系統擷取資料後在記憶體中直接加載到目标系統。
從步驟中可以看出端到端方式在記憶體中直接加載，從單個作業速度對比來看速度應該更快，開發更簡單，但端到端方式對記憶體資源要求較高，并行作業的最大值一般較檔案低，同時檔案具有以下好處：
- 各資料庫對檔案導入和導出支援較好，一般都會提供專門的工具和高性能接口（如oracle sqlload導入檔案和spool導出檔案的性能較高）。是以大批量的資料抽取和加載作業的效率從整體看檔案方式不一定比端到端的方式慢。
- 檔案方式耦合性比端到端低，如果發現資料加載出現問題，可以不用重新抽取資料，減少抽數對源系統的性能影響。
- 檔案通用性較好，如果涉及多網絡或多機構之間的資料交換，A子公司的ETL伺服器無法連接配接到B子公司的資料庫。另外對于非結構化資料來源廣泛，導出檔案比較通用。
具體采用檔案方式，端到端方式還是兩者都采用的方式，各公司需要考慮伺服器資源、現有工具及資料庫驅動性能、成本、資料交換場景等多種情況來确定。
（2）檔案方式方案需要考慮的要點
檔案方式比較通用，多機構之間較多采用檔案方式進行批量資料互動，但采用檔案方式進行架構設計和開發時需要關注以下幾點：
- 統一的檔案交換規範和檔案傳輸平台
檔案存儲規範制定檔案目錄、檔案命名、使用者權限、檔案校驗、檔案清理等規範，如果涉及到跨機構批量檔案傳輸，還需要統一有檔案傳輸平台，提供統一的檔案高效傳輸、加密、校驗、限流、檔案夾同步等功能。國内銀行使用較多的檔案傳輸平台有東方通、神州數位等公司産品。
檔案目錄規範中需要區分資料産生系統、資料使用系統、資料日期等，檔案名中需要說明産生系統、檔案内容描述、增量全量标志、資料日期等，規則舉例如下：
資料源系統/資料日期/目标系統/源系統_檔案内容描述_資料日期_增全量标志_頻率标志.txt
舉例：CBS/20190620/EDW/CBS_DEPOSIT-ACCOUT_20190620_ALL_D.txt
說明：【CBS、EDW為系統名】【ALL為全量标志】【D為每日】
- 檔案格式：定長or 變長（分隔符）
定長：檔案大，I/O資源消耗大，但能消除回車符、分隔符以及亂字元問題。
變長（分隔符）：檔案小，處理性能高，但需處理異常情況較多：
<1>分隔符：資料中存在分隔符，導緻加載報錯，可選用兩個連續的不可見字元作為分隔符，基本可以解決該問題；
<2>換行符：導出檔案時一般以換行符作為一行資料的結束，如果導出工具支援可以改成不可見字元作為換行符，不支援的話導出時對資料中的換行符進行替換；
<3>異常字元：如截取導緻的半個UTF-8字元的編碼或者HEX00等字元，一些資料庫不支援會報錯，一般這些字元發生在以前的主機上，異常情況下出現沒處理，可以提前在源系統進行資料清洗或者導出時進行替換清洗。
是以一般在這些問題都有較好解決方法不影響抽取加載作業效率的情況下，都會采用變長（分隔符）的方式。
- 檔案編碼
檔案導出需要統一編碼，一般采用UAT-8編碼，适應多國字元，但如果隻有國内應用，也可以考慮GB18030或GBK編碼，因為這兩種編碼中文字元比UTF-8編碼節省1/3多的存儲空間。性能較好。
（3）端到端方式需要考慮的要點
- 工具選擇
目前市場上商用的ETL工具如DATASTAGE、INFORMATICA，開源的TASKCTL都支援端到端的處理，商用工具還提供中間的圖形化的資料轉換編碼功能，但商用軟體一般成本較高，對于一些資料庫的高性能驅動還需要收費，開源工具功能較通用，但性能需要優化，同時需要有一定的技術能力來定制功能和軟體更新。
- 驅動選擇
選擇資料庫提供的高性能原生（native）驅動，不要使用ODBC驅動，原生的驅動性能數倍于ODBC等通用驅動，采集資料較多時能很大提高效率。
- 字元編碼
需要将資料從源系統導出時轉換為目标資料庫的編碼格式，在全公司的資料庫編碼和資料倉庫内的字元編碼需要進行統一規範，既可以減少轉換成本，也可以減少生僻字、無法轉換等異常情況。
抽取和加載開發設計
（1）開發需求分析
由于源系統和目标系統資料庫不同，資料品質不高，需要注意之間不同資料庫之間的字段類型、長度、精度的轉換，為後續資料加工做好清洗：
- 源系統字段沒有明确精度和長度時，如Oracle中字段類型為number,沒有定義精度，使用DATASTAGE時，當大于15位的number型數字接近最大值時會自動進位，是以在目标表設計字段精度時需要考慮這種異常情況。
- 字元字段的全角和半角是否都統一為半角；
- 字元字段左右空格是否都統一去掉；
在開發抽取加載作業時，需要配置以下主要資訊，這些資訊需要在資料調研和需求分析時提前确定：
（2）全表字段自動加載
一般開發時會采用固定字段抽取加載的方式，但由于源系統的表結構會經常變化，比如增加字段，字段長度變長，如果每次變化都要随之修改，許多時間會耗費在這些小修小改中，是以在進行抽取和加載時，需要根據源系統表結構自動生成對應的抽取腳本、目标表結構、加載腳本，自動适應源系統的表結構變化。
（3）源系統資料表變化通知和監控
雖然抽取和加載作業可以适應源系統表結構變化，但字段長度、精度變化、字段删除、代碼值變化和字段含義變化會對後續資料加工作業帶來影響。是以源系統需要将這些變更提前告知資料倉庫或目标系統，否則就會産生生産問題，但源系統開發同僚往往會産生遺漏，是以在公司資料治理制度中明确開發分工、資料問題責任界定。如在每次版本需求分析時需要考慮資料變化對資料倉庫及其它系統的影響，并在測試階段提前進行影響測試。在上線前也需要檢查下系統表結構變化的DDL檔案，分析影響并通知影響系統。
由于源系統字段的變化會影響到後續的資料流向的所有系統，是以在資料倉庫的模型設計時需要提前設計備援，減少字段長度、精度變化的影響，比如源系統字段長度是128，在資料倉庫主資料模型中可以設計為500。減少對後續資料使用系統的影響。具體影響分析工具會在後續的“中繼資料管理”中詳細說明。
由于隻靠源系統的通知并不完全可靠，還需要做好源資料表結構變化和代碼值變化的監控，每天對抽取的表結構和上一日進行比對，代碼值與代碼值映射表中比對，對發現未告知的情況進行郵件告警，并評估影響、及時處理，以免問題積累，需耗費大量精力修複。
（4）自動化腳本生成及執行
對于抽取加載作業需要做成标準化程式，即一個程式處理所有的抽取加載作業，根據不同的配置資訊來完成所有作業，在排程工具中的所有抽取加載作業指向的是同一個程式，由這個程式根據傳入的作業名和日期自動化生成腳本并執行。這樣對于開發隻是進行配置資訊的确認和導入即可，不需要涉及代碼開發。
許多ETL工具需要開發腳本再執行，特别一些商用的軟體如DATASTAGE還提供了可視化的開發界面，但這樣開發也比較耗時，對于使用的ETL工具如DATASTAGE、SQOOP也支援程式設計和腳本調用作業，是以可以用統一的程式來調用ETL工具進行抽取加載資料。提高開發效率，以下是供參考的流程。
（5）監控及異常處理
資料抽取和加載作業是資料倉庫每天第一批作業，如果發生問題往往對整個批處理時效産生較大影響，甚至影響監管報送時效。是以需要對作業進行監控，及時預警。
是以在開發抽取和加載作業時，需要注意：
- 統一傳回碼并提供錯誤資訊；
- 抽取和加載作業必須支援重跑，也就是在作業任何階段發生異常時可直接重做，需要設計時考慮異常中斷下，如何恢複初始資料；
- 排程平台需要根據抽取加載作業傳回碼判斷作業是否成功，是否可以繼續，對于異常情況需要及時與行内監控預警系統對接，按預警級别發送作業錯誤告警資訊；
- 排程平台需要擷取到作業的日志，對于一些ETL工具，這部分需要進行內建，以便減少背景日志檢視的工作量，直接在排程平台進行問題定位。
（6）開發分工
ETL作為全行或全公司的批量資料互動基礎架構，需要在全行或全公司進行規範和開發流程教育訓練。ETL伺服器及工具、抽取加載的标準程式由統一團隊來維護，需要進行權限配置設定并提供教育訓練及技術支援。那對于抽取加載作業具體由源系統還是目标系統來開發不同的公司有不同的做法，
- 由源系統開發，如果源系統是将資料加工結果給到目标系統，由于比較熟悉資料，一般由源系統加工完後直接開發抽取加載作業将資料提供給目标系統；
- 由目标系統開發，目标系統比較熟悉資料用途及優先級，如果全表抽取的話資料加工主要在目标系統，由目标系統來開發抽取加載作業，源系統隻需要做好資料權限配置設定即可。
- 由資料倉庫團隊統一開發，一般公司較小時可以由統一團隊來開發，但随着開發項目增多，會出現瓶頸，影響效率，需要由各資料使用方來開發抽取加載作業。
ETL工具
這裡我為大家推薦的是排程工具是： taskctl 6.0
TASKCTL作為靈活批量排程的開拓者，産品設計從一開始就專門為整潔的體驗而設計，并提供豐富、直覺的使用者界面，以簡化常見的作業排程執行編排流程。
TASKCTL産品功能架構圖
強大的核心自動化排程能力
TASKCTL提供了強大的核心自動化排程能力。
● 作業關系排程控制：對作業（流）實作作業（流）的依賴關系排程、作業并行排程、作業間互斥排程。
● 容錯政策控制：對作業（流）實作錯誤自動重做、錯誤自動忽略等控制
● 計劃排程：支援自然月曆排程、邏輯月曆排程等設定。
● 流程啟動觸發：提供事件觸發、檔案觸發、定時頻度觸發、自定義時間觸發、自定義條件觸發等控制。
● 其他：支援傳回值控制、跳轉控制、條件分支、參數控制、變量自動設定、作業輸入輸出傳遞、時間視窗控制、并發度控制、實體資源閥值控制、邏輯資源閥值控制、優先級控制、循環控制、斷點續做、負載均衡排程。
豐富的應用及使用者管理
TASKCTL提供了Windows圖形用戶端（C/S）、類Unix或Linux系統的字元用戶端等多種應用管道界面，可滿足不同程度的作業批量排程開發人員、運維人員及監控管理人員的使用，并提供了豐富的應用管理功能。
● 人工幹預功能（手工保障）：支援人工執行任意作業、人工執行指定分支、作業重做、正執行作業人工中斷、斷點設定、設定作業無效、強制通過、執行條件人工忽略、并行度實時人工調整、資源閥值人工調整、優先級實時人工調整、控制變量人工修改、追數排程等人工幹預功能。
● 統計分析：提供排程分布圖、系統運作時間視窗分布圖、作業運作時序圖、作業耗時排序圖、作業運作關聯分析、作業出錯率統計分析圖等展示。
● 系統平台管理：提供節點管理、使用者及權限管理、遷移部署、資訊備份、平台日志管理、版本管理等平台管理功能。
● 作業(流)定義設計功能：提供作業流程嵌套設計、資訊變量化設計、設計資訊自動檢測、單作業對話框方式定義、流程圖設計能力、單作業圖形拖拽設計、多作業批量化平面檔案方式設計、批量設計線上化、批量設計與圖形化設計實時互動等作業（流）設計功能。
● 全方位監控管理：提供流程圖實時動态監控、多元度的統計清單監控、短信實時監控、郵件方式非實時通知、啟動檔案到達監控、排程體系拓撲圖監控、資源監控等功能。
● 資訊查詢：提供曆史排程資訊查詢、作業日志查詢、作業資訊查詢、變量資訊查詢、人工操作記錄查詢功能。
靈活性設計
TASKCTL作為靈活排程的開拓者，從産品設計之初就立足于簡潔使用，靈活排程。
● 架構的靈活性：提供排程服務+執行代理部署結構、分布獨立排程、統一運維監控應用架構。
● 界面友好性：提供分鐘級的軟體安裝和部署，流程圖無交叉的友好展示、流程拖拽設計、秒級快速全局搜尋和定位、清爽簡潔操作界面。
● 産品開放性：提供跨平台、跨系統、廣泛的作業類型支援，提供作業類型統一擴充功能，并提供排程核心各種應用控制接口和核心各種資料的開放性。
● 産品高性能：超低的排程服務節點資源消耗和排程代理節點資源消耗大幅節省了主控端和代理的資源運作。高效的排程效率（吞吐量）40個/秒 ，可大幅提升整體排程品質，并可輕松支撐10萬+級作業排程應用。
● 低依賴：不依賴任何第三方中間件技術，全記憶體資料通路大大降低了軟硬體的依賴性。
● 高可靠：支援排程服務節點、排程執行代理節點的高可靠。
● 資訊安全：提供加密的網絡通訊安全、強密碼控制政策、和人工操作記錄審計功能。
taskctl 6.0 授權方式
關注公衆号 "taskctl" 關鍵字回複 "領取" 便可得到軟體永久授權方式