技術(shù)報告商業(yè)銀行數(shù)據(jù)倉庫系統(tǒng)中ETL

1、計劃類別 項目編號 項目技術(shù)報告課題名稱 項目主持人 承擔單位 題目：商業(yè)銀行數(shù)據(jù)倉庫系統(tǒng)中ETL的設計與實現(xiàn)本文基于國內(nèi)某商業(yè)銀行數(shù)據(jù)中心的新核心系統(tǒng)項目，首先介紹近年來數(shù)據(jù)倉庫的國內(nèi)外發(fā)展狀況，其次根據(jù)商業(yè)銀行各業(yè)務系統(tǒng)的特點，分析出建設商業(yè)銀行數(shù)據(jù)倉庫的重大意義和存儲規(guī)劃。最后詳細介紹了數(shù)據(jù)調(diào)度ETL系統(tǒng)的開發(fā)與設計，從各個源系統(tǒng)中抽取數(shù)據(jù)到數(shù)據(jù)倉庫的實現(xiàn)方法，對銀行數(shù)據(jù)倉庫的設計與實現(xiàn)有一定的參考應用價值。關(guān)鍵詞：數(shù)據(jù)倉庫；ETL；商業(yè)銀行；商業(yè)智能文章編號：2096-1472（2018）-11-42-041 引言（Introduction）數(shù)據(jù)倉庫是應用于銀行業(yè)的重要技術(shù)。近年來，隨

2、著商業(yè)銀行各業(yè)務系統(tǒng)的不斷發(fā)展，產(chǎn)生了日益龐大的歷史數(shù)據(jù)，這些數(shù)據(jù)是銀行十分重要的戰(zhàn)略資源，將這些重要的業(yè)務數(shù)據(jù)存儲于數(shù)據(jù)倉庫中統(tǒng)一管理，進行數(shù)據(jù)分析，挖掘出其中潛在的重要的商業(yè)信息，可以很好的輔助銀行領(lǐng)導層管理決策，解決銀行最緊迫的問題，使銀行的經(jīng)濟效益最大化，從而使銀行更好更快的發(fā)展。在大數(shù)據(jù)時代下，基于數(shù)據(jù)倉庫的數(shù)據(jù)挖掘是銀行業(yè)之間競爭的重要工具1。在1990年Bill Inmon提出了一種新的數(shù)據(jù)庫技術(shù)，即數(shù)據(jù)倉庫，目的是用來存儲和組織各業(yè)務系統(tǒng)的海量數(shù)據(jù)，并對數(shù)據(jù)進行分析。ETL（Extract，Transform，Load）系統(tǒng)工具專用于業(yè)務數(shù)據(jù)到數(shù)據(jù)倉庫中存儲的處理過程，在數(shù)據(jù)源

3、和數(shù)據(jù)倉庫之間起到了橋梁和紐帶的作用，直接影響數(shù)據(jù)倉庫的建設和運行。對于ETL系統(tǒng)的研究和開發(fā)是建設數(shù)據(jù)倉庫必須考慮的重要問題，通過改進ETL技術(shù)提高數(shù)據(jù)的傳輸和存儲效率，是建設商業(yè)銀行數(shù)據(jù)倉庫永恒的目標，能更好的促進銀行業(yè)的發(fā)展。2 ETL技術(shù)簡介（Introduction of ETL technology）完整的數(shù)據(jù)倉庫系統(tǒng)如圖1所示，數(shù)據(jù)的ETL是建立數(shù)據(jù)倉庫系統(tǒng)的第一步2，3，是應用于數(shù)據(jù)倉庫項目的重要步驟，為后續(xù)的數(shù)據(jù)挖掘和決策支持做好準備。數(shù)據(jù)倉庫中數(shù)據(jù)的調(diào)度工作是通過ETL工具來實現(xiàn)的。ETL是數(shù)據(jù)調(diào)度系統(tǒng)三個階段抽?。‥xtract）、轉(zhuǎn)換（Transform）、裝載（Loa

4、d）的英文首字母縮寫4，5。下面我們來簡要的介紹ETL的三個階段。2.1 數(shù)據(jù)抽取首先分析從每個數(shù)據(jù)源獲取數(shù)據(jù)的策略，分析從數(shù)據(jù)源系統(tǒng)中收集到的原始數(shù)據(jù)，存入ETL環(huán)境的物理存儲磁盤上。每次執(zhí)行完一次ETL調(diào)度后，源系統(tǒng)會產(chǎn)生新的數(shù)據(jù)，稱這些新的數(shù)據(jù)為變化的數(shù)據(jù)，用變化的原因標記有變化的數(shù)據(jù)，抽取其中所有變化的數(shù)據(jù)。簡要的抽取步驟為：（1）Dump：復制來源數(shù)據(jù)。（2）Capture：抽選所需資料。（3）Detect：數(shù)據(jù)變更獲取。2.2 數(shù)據(jù)轉(zhuǎn)換將業(yè)務數(shù)據(jù)以Push/Pull方式從源系統(tǒng)環(huán)境傳送到數(shù)據(jù)倉庫環(huán)境中。數(shù)據(jù)轉(zhuǎn)換加工，完成的是從緩沖區(qū)到基礎(chǔ)區(qū)的加工，以及基礎(chǔ)區(qū)到匯總區(qū)的轉(zhuǎn)換任務。在本

5、項目中這類作業(yè)通過JAVA程序調(diào)用實現(xiàn)，JAVA程序調(diào)用SQL腳本，執(zhí)行具有特定轉(zhuǎn)化邏輯的SQL語句。簡要的轉(zhuǎn)換步驟為：（1）Staging Load：將數(shù)據(jù)加載暫存區(qū)。（2）Validation：檢查數(shù)據(jù)并指定索引鍵。（3）Transformation：將來源文件的結(jié)構(gòu)轉(zhuǎn)換成目標倉儲表格結(jié)構(gòu)。（4）Merge：將多個數(shù)據(jù)源的數(shù)據(jù)融合。2.3 數(shù)據(jù)裝載將源系統(tǒng)抽取轉(zhuǎn)換的數(shù)據(jù)最終裝載到數(shù)據(jù)倉庫中，簡要的裝載步驟為：（1）Apply：將變更記錄更新到數(shù)據(jù)倉庫區(qū)。（2）Summarize and Mart：建立數(shù)據(jù)匯總和數(shù)據(jù)集市。（3）Cube：建立數(shù)據(jù)集市的多維分析。3 ETL系統(tǒng)的架構(gòu)設計（Fr

6、amework design of the ETL system）我們分析了國內(nèi)A商業(yè)銀行各業(yè)務系統(tǒng)的數(shù)據(jù)源，對數(shù)據(jù)倉庫存儲的數(shù)據(jù)做一個總體的預估，如表1所示。根據(jù)數(shù)據(jù)的存儲情況，設計相應的ETL系統(tǒng)架構(gòu)，把數(shù)據(jù)從數(shù)據(jù)源系統(tǒng)中抽取出數(shù)據(jù)，加載至數(shù)據(jù)倉庫中6。由于在源系統(tǒng)開發(fā)中有著非常多的挑戰(zhàn)，導致ETL系統(tǒng)開發(fā)存在著極高的難度。任何意外情況的發(fā)生都會導致ETL系統(tǒng)的開發(fā)延期，耽誤項目整體進度，增加項目成本。因此ETL調(diào)度系統(tǒng)的架構(gòu)設計一定要非常仔細謹慎。3.1 ETL系統(tǒng)的功能與特點根據(jù)商業(yè)銀行的業(yè)務特點和客戶對產(chǎn)品的需求，此項目設計的ETL系統(tǒng)將實現(xiàn)以下功能：（1）ETL支持數(shù)據(jù)源多，異構(gòu)平

7、臺的數(shù)據(jù)關(guān)聯(lián)。支持系統(tǒng)的可視化、參數(shù)化。支持集群、負載均衡。（2）ETL支持事件觸發(fā)、消息觸發(fā)等多種觸發(fā)機制。（3）ETL支持多并發(fā)處理方式，可以根據(jù)系統(tǒng)處理資源的情況調(diào)整并發(fā)程度，處理效率高。（4）ETL支持多節(jié)點處理，可以根據(jù)系統(tǒng)性能的要求，增加硬件功能來提高系統(tǒng)的生成效率。3.2 ETL系統(tǒng)架構(gòu)設計結(jié)合國內(nèi)某商業(yè)銀行的業(yè)務特點，我們規(guī)劃出ETL系統(tǒng)架構(gòu)圖如圖2所示。對于詳細的ETL系統(tǒng)架構(gòu)，我們參照下面四個方面來設計7-9。（1）設計全局規(guī)劃規(guī)劃源數(shù)據(jù)，各系統(tǒng)業(yè)務邏輯，目標數(shù)據(jù)倉庫三級設計架構(gòu)。根據(jù)各個系統(tǒng)業(yè)務邏輯的不同，開發(fā)相應的ETL系統(tǒng)，解決當前數(shù)據(jù)調(diào)度存在的和還未解決的問題。（2

8、）選擇ETL工具在數(shù)據(jù)倉庫市場上存在多種ETL工具，本文采用調(diào)度抽取工具Datastage主動到各個源系統(tǒng)中獲取數(shù)據(jù)。（3）開發(fā)默認策略對ETL工具的基本需求進行整體考慮，開發(fā)默認策略。這些包括：從每個源系統(tǒng)獲取數(shù)據(jù)，歸檔獲取的數(shù)據(jù)或分級的數(shù)據(jù)，監(jiān)管維度和特定事實的數(shù)據(jù)質(zhì)量，維度屬性變化的管理，確保數(shù)據(jù)倉庫和ETL系統(tǒng)滿足系統(tǒng)可用性需求，設計數(shù)據(jù)審計子系統(tǒng)，組織ETL過渡區(qū)。（4）按照目標表獲取數(shù)據(jù)開發(fā)完所有的ETL調(diào)度任務后，還需要深入研究詳細的轉(zhuǎn)換工作。確定數(shù)據(jù)倉庫中數(shù)據(jù)來源的目標表，完成源數(shù)據(jù)到目標表的映射后，繼續(xù)完成數(shù)據(jù)概要描述工作，全部理解每個表所需要的數(shù)據(jù)轉(zhuǎn)換。4 ETL在BI項目

9、中的應用（ETL application in the Business Intelligence project）下面在A商業(yè)銀行BI（Business Intelligence）項目，我們通過ETL調(diào)度系統(tǒng)來完成數(shù)據(jù)的轉(zhuǎn)換和存儲。在遵循ETL邏輯架構(gòu)的基礎(chǔ)上，為各個源數(shù)據(jù)系統(tǒng)中的每一張業(yè)務表設計一個調(diào)度作業(yè)，表中數(shù)據(jù)的開發(fā)就是對調(diào)度作業(yè)數(shù)據(jù)的ETL操作過程。完成調(diào)度作業(yè)中數(shù)據(jù)的ETL階段代碼編寫、ETL調(diào)度開發(fā)，實現(xiàn)整個ETL數(shù)據(jù)存儲到數(shù)據(jù)倉庫中10，11。進行ETL調(diào)度系統(tǒng)開發(fā)，首先要搭建開發(fā)環(huán)境，包括：導入初始化數(shù)據(jù)、設計調(diào)度程序、設計監(jiān)控程序。其中調(diào)度程序的設計分為ETL任務設計和E

10、TL調(diào)度設計，此外還要開發(fā)一個數(shù)據(jù)檢測程序SendMessage作為調(diào)度程序的輔助工具。監(jiān)控程序的設計就是ETL監(jiān)控設計。4.1 ETL任務設計首先要分析整理出調(diào)度作業(yè)的任務信息，包括數(shù)據(jù)源系統(tǒng)、各個系統(tǒng)中所有調(diào)度作業(yè)的編號、調(diào)度作業(yè)的階段信息、調(diào)度作業(yè)之間的前后驅(qū)關(guān)系等。具體設計步驟如下：（1）按照數(shù)據(jù)源系統(tǒng)和作業(yè)處理階段收集任務信息，包括任務的執(zhí)行周期、任務類型、任務渠道、任務階段等。把收集到的所有數(shù)據(jù)源系統(tǒng)依次編號，例如現(xiàn)在已經(jīng)收集到了核心系統(tǒng)、信貸系統(tǒng)、ECIF系統(tǒng)、財管系統(tǒng)，依次將核心系統(tǒng)編號為10000、信貸系統(tǒng)編號為20000、ECIF系統(tǒng)編號為30000，財管系統(tǒng)編號為4000

11、0，后續(xù)出現(xiàn)的數(shù)據(jù)源系統(tǒng)以10000為單位編排下去。將作業(yè)每個調(diào)度階段進行編號，如數(shù)據(jù)檢測階段編號為1000，數(shù)據(jù)導出階段編號為2000，數(shù)據(jù)傳輸階段編號為3000，數(shù)據(jù)裝載階段編號為4000，數(shù)據(jù)備份階段編號為5000，其他階段編號為9000等。（2）對收集到的所有作業(yè)進行任務編碼，例如核心系統(tǒng)共100個作業(yè)，每個作業(yè)都經(jīng)過檢測裝載備份三個階段，第一個作業(yè)檢測階段編號11001，其中萬位數(shù)的1代表核心系統(tǒng)10000，千位數(shù)的1代表檢測階段1000，后三位001代表第一個作業(yè)。第一個作業(yè)生成裝載階段的作業(yè)編號14001，備份階段的作業(yè)編號15001，其他作業(yè)依次按順序生成相應的檢測，裝載，備份

12、作業(yè)編號。統(tǒng)計所有的作業(yè)編號，階段信息和渠道信息，生成作業(yè)信息表。（3）根據(jù)作業(yè)信息表中的任務信息，分析其所屬的階段和數(shù)據(jù)源，確定各任務之間的前后驅(qū)關(guān)系。一個ETL調(diào)度系統(tǒng)一般都是由上萬個不同的任務共同協(xié)作完成，任務之間的關(guān)系決定整個數(shù)據(jù)調(diào)度過程是否能夠順利進行。ETL調(diào)度系統(tǒng)中處理作業(yè)是按順序來的。先執(zhí)行的作業(yè)被稱為前驅(qū)任務，用來決定其他作業(yè)是否執(zhí)行，后執(zhí)行的作業(yè)被稱為后驅(qū)任務。ETL任務中只有前一個作業(yè)處理執(zhí)行成功才會執(zhí)行后一個作業(yè)，所以作業(yè)之間的前后驅(qū)關(guān)系很重要。每個數(shù)據(jù)源系統(tǒng)設有一個首作業(yè)，首作業(yè)的后驅(qū)作業(yè)是所有的檢測作業(yè)，檢測作業(yè)的后驅(qū)作業(yè)是裝載作業(yè)，裝載作業(yè)的后驅(qū)作業(yè)是備份作業(yè)。梳

13、理完成所有作業(yè)的前后驅(qū)關(guān)系，整合到調(diào)度系統(tǒng)中，在作業(yè)信息表中添加階段開始和結(jié)束的標志性任務，生成作業(yè)流程表。（4）將前面整理好的作業(yè)任務轉(zhuǎn)化為SQL語句程序，在ETL工作站上部署該程序，系統(tǒng)啟動后，調(diào)度生成程序根據(jù)作業(yè)信息表和作業(yè)流程表生成作業(yè)調(diào)度表。4.2 ETL調(diào)度設計由于數(shù)據(jù)源系統(tǒng)非常多，所需加載的數(shù)據(jù)也非常多，導致ETL調(diào)度程序的設計非常復雜，因此設計一個完善且易于維護的ETL調(diào)度程序，對數(shù)據(jù)倉庫的正常工作運行是非常重要12。ETL調(diào)度流程如圖3所示。結(jié)合業(yè)務系統(tǒng)的特點，設計出高效的ETL調(diào)度程序，具體設計步驟如下：（1）作業(yè)調(diào)度表生成完畢，開始執(zhí)行調(diào)度任務程序，根據(jù)作業(yè)調(diào)度表中作業(yè)信

14、息，作業(yè)調(diào)度執(zhí)行的情況，生成調(diào)度執(zhí)行情況表。（2）根據(jù)作業(yè)優(yōu)先級關(guān)系向各個ETL工作站分配相應數(shù)量的作業(yè)任務。如果某ETL工作站執(zhí)行作業(yè)已經(jīng)完成，則將新的作業(yè)分配給該ETL工作站，并更新調(diào)度執(zhí)行情況表。（3）假如某個調(diào)度任務執(zhí)行出錯，則根據(jù)作業(yè)錯誤處理級別對作業(yè)進行相應處理，如等待人工處理、自動跳過錯誤任務、重復執(zhí)行等。如果需要人工處理調(diào)度任務，則人工對調(diào)度任務進行相應處理，并記錄日志。（4）當作業(yè)調(diào)度表中任務全部執(zhí)行完成后，暫停調(diào)度程序，等待下一批次調(diào)度任務，ETL工作站也處于等待狀態(tài)。4.3 ETL監(jiān)控設計ETL作業(yè)任務運行過程中，需要實時監(jiān)控所有的作業(yè)執(zhí)行情況，其中包括ETL單個作業(yè)的狀

15、態(tài)監(jiān)控等信息，ETL監(jiān)控的主要作用是監(jiān)控任務的執(zhí)行情況及支持對錯誤任務的手工處理。接收調(diào)度執(zhí)行終端的反饋結(jié)果并進行相應的處理，反饋結(jié)果有任務成功，失敗狀態(tài)。接收數(shù)據(jù)檢測消息發(fā)送器的消息并做相應的處理，如接收到核心系統(tǒng)渠道2018-03-01的數(shù)據(jù)準備好，調(diào)度則開始跑批對應的任務。ETL監(jiān)控相關(guān)流程如圖4所示。4.4 ETL調(diào)度系統(tǒng)的實現(xiàn)在上述設計的ETL調(diào)度系統(tǒng)中，所有的操作過程都是通過SQL程序語句來實現(xiàn)的。開發(fā)外部SQL腳本程序，調(diào)用該SQL程序來實現(xiàn)ETL系統(tǒng)的數(shù)據(jù)調(diào)度功能。SQL腳本邏輯可以根據(jù)不同的需求分別編寫，實現(xiàn)其功能，這樣做的好處是配置作業(yè)比較容易。根據(jù)調(diào)度作業(yè)信息表的結(jié)構(gòu)創(chuàng)建

16、一個臨時作業(yè)表INIT_JOB，INIT_JOB表起到一個調(diào)度作業(yè)中轉(zhuǎn)的作用。INIT_JOB表結(jié)構(gòu)如圖5所示。該臨時表可以記錄SQL腳本程序調(diào)用作業(yè)所需的大部分信息，例如：作業(yè)類型、作業(yè)名稱、所屬階段和渠道、調(diào)用腳本及參數(shù)等。這樣做的好處是可以方便的初始化作業(yè)信息。向INIT_JOB表中添加新增作業(yè)信息，通過INIT_JOB臨時表完成新增作業(yè)的信息存儲，調(diào)用SQL腳本完成作業(yè)的數(shù)據(jù)開發(fā)過程，最終將表中的業(yè)務數(shù)據(jù)存儲備份至數(shù)據(jù)倉庫中。當需要重新調(diào)度新的作業(yè)時，初始化INIT_JOB表為空，插入新增作業(yè)信息，根據(jù)新作業(yè)的業(yè)務特點，修改SQL腳本邏輯，實現(xiàn)新增作業(yè)的調(diào)度功能。當作業(yè)調(diào)度開發(fā)結(jié)束后，

17、通過查詢調(diào)度作業(yè)日志，查看數(shù)據(jù)調(diào)度是否成功，調(diào)度成功則數(shù)據(jù)成功存儲至數(shù)據(jù)倉庫中，如圖6所示。若調(diào)度失敗，則查看日志，找到調(diào)度失敗原因并修改，重新執(zhí)行調(diào)度任務，直至調(diào)度作業(yè)執(zhí)行成功。5 結(jié)論（Conclusion）本論文以國內(nèi)某商業(yè)銀行的BI項目為背景，設計開發(fā)了商業(yè)銀行數(shù)據(jù)倉庫的ETL調(diào)度系統(tǒng)，實現(xiàn)了銀行各業(yè)務數(shù)據(jù)到數(shù)據(jù)倉庫的集中存儲，實現(xiàn)全行數(shù)據(jù)的統(tǒng)一化、標準化、高質(zhì)量、高效率，極大的提升了商業(yè)銀行數(shù)據(jù)信息化水平。在大數(shù)據(jù)時代下，數(shù)據(jù)挖掘是銀行業(yè)之間競爭的重要工具，基于數(shù)據(jù)倉庫的數(shù)據(jù)分析，挖掘出其中潛在的重要的商業(yè)信息，可以很好的輔助銀行領(lǐng)導層管理決策，解決銀行最緊迫的問題，使銀行的經(jīng)濟效益

18、最大化，從而使銀行更好更快的發(fā)展。參考文獻（References）1 Ren S，Sun Q，Shi Y.Customer Segmentation of Bank Based on Data Warehouse and Data MiningC.The IEEE International Conference on Information Management and Engineering.IEEE，2010：349-353.2 L Baldacci，M Golfarelli，S Graziani，et al.QETL：An Approach to On-Demand ETL from

19、Non-Owned Data SourcesJ.Data & Knowledge Engineering，2017.3 V Theodorou，A Abell，M Thiele，et al.Frequent Patterns in ETL Workflows： An Empirical ApproachJ.Data & Knowledge Engineering，2017：112.4 Dupor S，Jovanovic V.An approach to conceptual modelling of ETL processesC.International Convention on Informatio