數據抽取及交換工具的設計與實現

王德勝++李弘++田佳燁

摘要：本文研究了ETL相關技術，并針對出版行業的現狀和應用需求，提出了面向出版行業的數據抽取及交換工具設計方案和實現方法，通過.NET開發工具和C#開發語言開發了一套可靈活定義的工具，在多家出版單位進行了應用，取得了良好的效果。

關鍵詞：數據抽取；數據交換；數字出版；商業智能

中圖分類號：TP311.13

文獻標識碼：A

DOI： 10.3969/j.issn.1003-6970.2015.08.008

0 引言

出版商業智能系統是面向出版單位的綜合數據分析系統，該系統基于出版企業各業務系統的數據進行綜合分析，另一方面，出版企業各業務系統之間也需要進行數據交換。由于出版企業采用不同公司提供的業務系統，各業務系統采用不同的數據庫，應用較廣泛的有SQL Server、Oracle，少量有采用MySQL等其它類型數據庫，另外還有許多的外部數據以Excel、TXT、XML等文件方式存儲，本文針對出版行業的現狀和應用需求，研究了通用的商業智能系統ETL工具的數據抽取、轉換和加載相關技術，提出了面向出版行業的數據抽取及交換工具設計方案和實現方法，通過.NET開發工具和C#開發語言開發了一套工具。

1 ETL技術綜述

ETL即數據抽取、轉換清洗、加載的過程，能夠按照統一的規則集成并提高數據的價值，是數據倉庫獲取高質量數據的關鍵環節，是從數據源獲取數據并對數據進行清洗轉換最終加載到數據倉庫的過程，是數據由數據源系統向目標數據源加載的主要方法。ETL工具從本質上而言是一種數據轉換工具，一般ETL工具還會設計任務管理和調度引擎。

1.1 數據抽取

數據抽取是按業務需求從源數據從數據源系統抽取數據倉庫系統所需的數據[錯誤！未定義書簽。]，ETL處理的數據源除了關系數據庫外，還可能是文檔，例如TXT、Excel、XML等文件數據，這要求抽取工具采用統一的數據接口，既滿足從數據庫抽取數據的需求，也可以滿足從文文本件抽取的需求。對于不同數據源、不同數據量的源數據以及不同性能要求的業務系統，采用的實現方式不同。一般的抽取方式包括全量抽取、增量抽取、全表比對、日志比較等，為保證抽取效率，減少對生產運營的影響，可針對不同的情況選用不同的抽取方式。

1.2 數據清洗轉換

從數據源中抽取的數據不一定完全滿足目的庫的要求，例如數據格式的不一致、數據輸入錯誤、數據不完整等等，數據轉換就是完成對抽取的源數據根據數據倉庫系統或目標數據庫的要求，進行數據的轉換、清洗、拆分、匯總等，保證來自不同系統、不同格式的數據和信息具有一致性和完整性，并按要求裝入目標數據庫。一般數據轉換可通過源數據與目標數據的映射關系來完成，其轉換規則可定義在映射表中，轉換工具根據定義的規則進行轉換。轉換工具可直接采用SQL語句的方式和轉換組件的方式進行轉換。比較而言，直接在SQL語句中進行轉換和加工更加簡單清晰，性能更高，但一般要求操作的數據是關系型數據庫。對于SQL語句無法處理的可以采用組件的方式處理。

1.3 數據加載

將清洗轉換后的數據加載到目的庫中通常是ETL過程的最后步驟，當目的庫是關系數據庫時，直接通過SQL語句進行insert、update、delete操作。目的數據源是文件格式如XML、Excel、TXT等文件時，可通過轉換工具根據映射表的設置轉換成要求的文件。

2 系統設計與實現

2.1 設計思想

ETL設計與開發，需深入到業務系統內部去獲取所需數據，需定義源數據、目標數據，數據抽取、轉換和裝載策略等。參考ETL設計開發的基本要求，本文對系統進行了整體設計，下文介紹主要設計思想。

數據交換的過程如圖1所示，系統設計了三種數據交換方式：

為提高數據交換的性能，對于源數據和目標數據都是關系型數據庫的情況，可以直接運用SQL語句的方式進行數據交換。

一般數據可通過系統的轉換工具進行數據轉換后直接寫入目標數據源。

復雜轉換需要經過中間數據表暫存數據，通過對中間表的清洗轉換處理后再加載到目標數據源。

系統采用靈活定義的方式解決各種情況下的數據交換需求，包括各種類型數據庫連接的定義、源數據與目標數據的映射關系定義、數據清洗轉換規則定義，系統提供可視操作界面并將定義的結果保存到數據庫表中，整個程序將依據各種定義進行分別處理，完成數據抽取、轉換及加載的各項功能。

為了更方便用戶操作各項數據交換任務，系統提供了作業調度管理的功能，系統按照調度計劃、作業組、作業的層次關系進行管理，以作業為最小的操作單元，作業組可包含作業和其他作業組，一項調度計劃可包含多項作業組和作業，對作業、作業組和調度計劃系統提供界面由用戶根據實際應用需要定義。系統將針對調度計劃采用隊列掃描的方式執行每一項作業。

為適應不同類型的數據庫，系統按照分層設計的理念對數據庫的訪問處理封裝為一層，在程序處理時將根據不同的數據庫做出不同的處理。根據出版行業實際情況目前系統提供了對SQL Server、My SQL、Oracle的支持，支持Excel、TXT和XML文件。該設計提供了良好的可擴展性，對該層代碼進行擴充即可適應更多類型的數據庫。

系統選用.NET集成開發工具，C#開發語言進行開發實現。系統采用三層架構，包括UI層、業務邏輯層和數據層，此種設計保證系統具備良好的可擴展性。

2.2 數據庫連接

用戶對數據庫連接屬性定義，可定義各種數據源的連接屬性，支持SQL Server、My SQL、Oracle等關系型數據庫的連接，定義的項目包括標識、服務器地址、數據庫名稱、用戶名、密碼等。系統將根據用戶的定義信息動態創建數據源連接對象，該連接對象既可以作為源數據庫也可以作為目標數據庫。

2.3 數據映射關系

數據映射關系定義了抽取和轉換的規則，即數據源表中的數據字段與目標庫表中的對應關系定義，映射關系采用主子表結構進行定義，主表中需要從已定義的數據庫連接中選擇目標表數據源連接和源數據表連接、是否根據源數據表結構創建目標表，子表定義相關表字段的對應關系及轉換規則。

系統提供可視化界面方便用戶建立源數據到目標數據的抽取映射關系。為方便用戶選擇，可直接通過源數據中提取源數據表和目標數據中提取對應庫表中的表及字段供用戶選擇，用戶直接選擇對應關系，定義轉換規則，也可以直接輸入表中的字段名建立對應關系，定義完成后的映射關系和轉換規則保存到映射關系到表中。系統將根據定義的映射關系完成抽取、轉換和加載。

2.4 復雜的清洗轉換處理

對于復雜的數據轉換，需要由源數據抽取到中間庫表，此時可將中間表看作目標數據源，經過專門的清洗轉換處理，形成與目標數據庫一致的數據，再定義一個由中間庫表到目標數據的映射關系，即將中間數據庫看作源數據，將規范的數據由中間表加載到目標數據的表中。

為完成專門的清洗轉換處理，用戶可通過系統提供的可視化工具進行清洗轉換定義，支持SQL語句更新的方式進行數據清洗和轉換，用戶可定義更新的表、更新的字段、更新的條件，提供了對統計、計算、類型轉換、字符串拆分處理等基本處理功能，針對出版行業的需求提供了標準書號與簡書號轉換等特色的轉換功能，系統將根據用戶定義的清洗轉換規則進行轉換處理。系統同時支持用戶直接寫SQL語句進行清洗轉換，更復雜的處理通過二次開發的方式解決。

2.5作業管理

作業管理是整個數據抽取與交換軟件的核心，作業是指一個完整的操作過程，作業管理包括作業定義和執行，系統可分三類作業，對于一項作業，首選作業類型，系統將根據不同的作業類型進行分別處理。

直接執行SQL語句。

直接執行SQL語，只能針對于關系型數據庫，作業定義時，首先選擇一個數據源，定義SQL語句，用戶可以直接輸入SQL語句，也可以通過系統提供的SQL編輯工具進行添加，一個任務可以添加多個SQL語句。

通過映射關系定義由一個數據庫抽取數據到另外一個數據庫的庫表中。

任務定義時，首先需要選擇已定義好的映射關系表，同時需定義目標數據獲取時所使用的條件，設置對比標識列（一般為表的唯一標識列，可以是復合字段），同時可設置操作是完全添加、增量更新、是否反向刪除等抽取方式。如果輸出為文件類型，需定義輸出位置，文件命名規則，EXCEL、XML樣式等信息。

通過轉換清洗規則在中間庫進行數據轉換處理。

系統根據用戶定義的清洗規則，自動轉換為可執行的SQL語句執行該項作業。

系統支持作業組，可將相關或功能相近多個作業定義到一個集合中。可以選擇一批作業或作業組組成一個新的集合，其中每個子項可以有任意下屬子項，子項可以為作業也可以為作業組，但在作業組包含其他作業組時應慎重使用避免死循環。系統將保存作業組標識，作業組描述信息，關聯的作業或作業組及執行順序。

2.6 調度計劃

調度計劃是實現作業或作業組自動化運行的一種方式，設置什么時間點來處理什么信息的一種定義。一個調度計劃包括一組作業或作業組，系統將以調度計劃為單位進行調度管理。可設置一項調度計劃是否啟用，是自動執行還是手動執行，對于自動執行的調度計劃，提供可視化界面設置調度計劃的運行開始時間、運行周期，來實現作業或作業組自動化運行的定義，參見圖2。

如果配置為自動運行，系統在程序啟動時會自動加載設置為啟用的調度計劃，根據計劃運行頻率算出每個作業下次運行時間，自動啟動運行線程，系統將不斷掃描是否有需要自動運行的任務，如果運行時間相等則將調度編號推送到運行隊列表中，由另一線程掃描運行。系統根據調度計劃定義信息.調度中包含的作業組，作業定義信息，作業定義包含的抽取規則、映射關系等按照作業的順序自動運行，運行結果將寫入日志文件。程序邏輯如圖3所示：

程序啟動，主線程自動掃描調度定義，每間隔一段時間掃描當前時間等于下次運行時間的調度，將時間相等的調度放人到調度作業隊列中。

作業執行線程每間隔一秒掃描調度作業隊列，如果有執行的調度，將調度編號傳人到調度執行類中。

調度執行類加載調度信息，加載調度下屬所包含的作業或作業組，如果為作業組則循環加載作業組下屬子項，如是作業則執行該項作業。

作業執行：加載作業信息，判斷作業類型分別執行。

①如果執行SQL，先加載目標數據源連接，順次執行所包含的SQL腳本。同時記錄腳本執行狀態。

②通過映射關系定義執行數據交換，先加載作業所包含的源數據源定義連接，根據源數據定義及作業中加載獲取源數據，根據目標數據定義及作業中定義的目標數據條件加載目標數據，根據條件比對、更新、插入的列生成數據表。根據定義，如果目標庫為關系型數據對內存表執行添加、更新、或反向刪除操作，如果目標庫為文件類型，則據規則輸出TXT、EXCEL、XML。

③根據用戶定義的轉換清洗規則，自動轉換為SQL語句，在中間庫進行數據轉換處理。

記錄日志：將作業的運行時間，各個環節狀態記錄到日志表中，如果執行出錯，系統將錯誤記錄到日志中，同時事務回滾。

3 結束語

針對不同數據庫類型的數據交換的應用需求，本文設計的系統通過可視化界面為用戶提供靈活的定制功能，包括數據庫連接的定義、數據轉換所需要的源數據庫和目標數據庫的連接選取、源數據表列與目標數據表列的映射和轉換規則，對復雜的清洗轉換處理采用在中間庫表中由用戶進行自由定義規則的處理方法，通過靈活定義的方式保證系統具有較強的適應性。同時用戶可通過對調度計劃、作業組、作業靈活定義的方式實現各種需求的作業和調度管理功能。該系統可滿足各種不同的數據抽取、轉換、加載的應用需求。

系統在電子工業出版社、浙江出版聯合集團有限公司及其下屬出版社等單位進行了應用，主要用途包括在不同的業務系統間自動交換數據，與企業外部系統交換數據，作為出版商業智能軟件的數據抽取工具將各業務系統的數據集中到數據倉庫中。隨著數字出版業務的發展，海量的、多媒體化的數字內容加工、處理、轉換、存儲、分發、傳播和不間斷的網絡服務不斷增多，同時對全球化的、超大規模的用戶數據進行分析、挖掘和推薦等智能性處理的需求也伴隨著商業模式的創新而日益膨脹，本文設計的系統作為基礎的數據交換工具為這類應用提供支撐，并且在我們承擔的國家科技支撐計劃課題“動態數字出版技術應用與運營示范”（2012BAH88F04）也得到了良好應用。