商業(yè)銀行數(shù)據(jù)倉庫系統(tǒng)中ETL的設(shè)計與實現(xiàn)

周飛 劉夢娜 張暉 李志剛 曹帥

摘 要：本文基于國內(nèi)某商業(yè)銀行數(shù)據(jù)中心的新核心系統(tǒng)項目，首先介紹近年來數(shù)據(jù)倉庫的國內(nèi)外發(fā)展狀況，其次根據(jù)商業(yè)銀行各業(yè)務(wù)系統(tǒng)的特點，分析出建設(shè)商業(yè)銀行數(shù)據(jù)倉庫的重大意義和存儲規(guī)劃。最后詳細介紹了數(shù)據(jù)調(diào)度ETL系統(tǒng)的開發(fā)與設(shè)計，從各個源系統(tǒng)中抽取數(shù)據(jù)到數(shù)據(jù)倉庫的實現(xiàn)方法，對銀行數(shù)據(jù)倉庫的設(shè)計與實現(xiàn)有一定的參考應(yīng)用價值。

關(guān)鍵詞：數(shù)據(jù)倉庫；ETL；商業(yè)銀行；商業(yè)智能

中圖分類號：TP311 文獻標識碼：A

文章編號：2096-1472（2018）-11-42-04

1 引言（Introduction）

數(shù)據(jù)倉庫是應(yīng)用于銀行業(yè)的重要技術(shù)。近年來，隨著商業(yè)銀行各業(yè)務(wù)系統(tǒng)的不斷發(fā)展，產(chǎn)生了日益龐大的歷史數(shù)據(jù)，這些數(shù)據(jù)是銀行十分重要的戰(zhàn)略資源，將這些重要的業(yè)務(wù)數(shù)據(jù)存儲于數(shù)據(jù)倉庫中統(tǒng)一管理，進行數(shù)據(jù)分析，挖掘出其中潛在的重要的商業(yè)信息，可以很好的輔助銀行領(lǐng)導(dǎo)層管理決策，解決銀行最緊迫的問題，使銀行的經(jīng)濟效益最大化，從而使銀行更好更快的發(fā)展。在大數(shù)據(jù)時代下，基于數(shù)據(jù)倉庫的數(shù)據(jù)挖掘是銀行業(yè)之間競爭的重要工具[1]。

在1990年Bill Inmon提出了一種新的數(shù)據(jù)庫技術(shù)，即數(shù)據(jù)倉庫，目的是用來存儲和組織各業(yè)務(wù)系統(tǒng)的海量數(shù)據(jù)，并對數(shù)據(jù)進行分析。ETL（Extract，Transform，Load）系統(tǒng)工具專用于業(yè)務(wù)數(shù)據(jù)到數(shù)據(jù)倉庫中存儲的處理過程，在數(shù)據(jù)源和數(shù)據(jù)倉庫之間起到了橋梁和紐帶的作用，直接影響數(shù)據(jù)倉庫的建設(shè)和運行。對于ETL系統(tǒng)的研究和開發(fā)是建設(shè)數(shù)據(jù)倉庫必須考慮的重要問題，通過改進ETL技術(shù)提高數(shù)據(jù)的傳輸和存儲效率，是建設(shè)商業(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]，是應(yīng)用于數(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）、裝載（Load）的英文首字母縮寫[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：復(fù)制來源數(shù)據(jù)。

（2）Capture：抽選所需資料。

（3）Detect：數(shù)據(jù)變更獲取。

2.2 數(shù)據(jù)轉(zhuǎn)換

將業(yè)務(wù)數(shù)據(jù)以Push/Pull方式從源系統(tǒng)環(huán)境傳送到數(shù)據(jù)倉庫環(huán)境中。數(shù)據(jù)轉(zhuǎn)換加工，完成的是從緩沖區(qū)到基礎(chǔ)區(qū)的加工，以及基礎(chǔ)區(qū)到匯總區(qū)的轉(zhuǎn)換任務(wù)。在本項目中這類作業(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)設(shè)計（Framework design of the ETL system）

我們分析了國內(nèi)A商業(yè)銀行各業(yè)務(wù)系統(tǒng)的數(shù)據(jù)源，對數(shù)據(jù)倉庫存儲的數(shù)據(jù)做一個總體的預(yù)估，如表1所示。根據(jù)數(shù)據(jù)的存儲情況，設(shè)計相應(yīng)的ETL系統(tǒng)架構(gòu)，把數(shù)據(jù)從數(shù)據(jù)源系統(tǒng)中抽取出數(shù)據(jù)，加載至數(shù)據(jù)倉庫中[6]。由于在源系統(tǒng)開發(fā)中有著非常多的挑戰(zhàn)，導(dǎo)致ETL系統(tǒng)開發(fā)存在著極高的難度。任何意外情況的發(fā)生都會導(dǎo)致ETL系統(tǒng)的開發(fā)延期，耽誤項目整體進度，增加項目成本。因此ETL調(diào)度系統(tǒng)的架構(gòu)設(shè)計一定要非常仔細謹慎。

3.1 ETL系統(tǒng)的功能與特點

根據(jù)商業(yè)銀行的業(yè)務(wù)特點和客戶對產(chǎn)品的需求，此項目設(shè)計的ETL系統(tǒng)將實現(xiàn)以下功能：

（1）ETL支持數(shù)據(jù)源多，異構(gòu)平臺的數(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)設(shè)計

結(jié)合國內(nèi)某商業(yè)銀行的業(yè)務(wù)特點，我們規(guī)劃出ETL系統(tǒng)架構(gòu)圖如圖2所示。

對于詳細的ETL系統(tǒng)架構(gòu)，我們參照下面四個方面來設(shè)計[7-9]。

（1）設(shè)計全局規(guī)劃

規(guī)劃源數(shù)據(jù)，各系統(tǒng)業(yè)務(wù)邏輯，目標數(shù)據(jù)倉庫三級設(shè)計架構(gòu)。根據(jù)各個系統(tǒng)業(yè)務(wù)邏輯的不同，開發(fā)相應(yīng)的ETL系統(tǒng)，解決當前數(shù)據(jù)調(diào)度存在的和還未解決的問題。

（2）選擇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è)計數(shù)據(jù)審計子系統(tǒng)，組織ETL過渡區(qū)。

（4）按照目標表獲取數(shù)據(jù)

開發(fā)完所有的ETL調(diào)度任務(wù)后，還需要深入研究詳細的轉(zhuǎn)換工作。確定數(shù)據(jù)倉庫中數(shù)據(jù)來源的目標表，完成源數(shù)據(jù)到目標表的映射后，繼續(xù)完成數(shù)據(jù)概要描述工作，全部理解每個表所需要的數(shù)據(jù)轉(zhuǎn)換。

4 ETL在BI項目中的應(yīng)用（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è)務(wù)表設(shè)計一個調(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)境，包括：導(dǎo)入初始化數(shù)據(jù)、設(shè)計調(diào)度程序、設(shè)計監(jiān)控程序。其中調(diào)度程序的設(shè)計分為ETL任務(wù)設(shè)計和ETL調(diào)度設(shè)計，此外還要開發(fā)一個數(shù)據(jù)檢測程序SendMessage作為調(diào)度程序的輔助工具。監(jiān)控程序的設(shè)計就是ETL監(jiān)控設(shè)計。

4.1 ETL任務(wù)設(shè)計

首先要分析整理出調(diào)度作業(yè)的任務(wù)信息，包括數(shù)據(jù)源系統(tǒng)、各個系統(tǒng)中所有調(diào)度作業(yè)的編號、調(diào)度作業(yè)的階段信息、調(diào)度作業(yè)之間的前后驅(qū)關(guān)系等。具體設(shè)計步驟如下：

（1）按照數(shù)據(jù)源系統(tǒng)和作業(yè)處理階段收集任務(wù)信息，包括任務(wù)的執(zhí)行周期、任務(wù)類型、任務(wù)渠道、任務(wù)階段等。把收集到的所有數(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)編號為40000，后續(xù)出現(xiàn)的數(shù)據(jù)源系統(tǒng)以10000為單位編排下去。將作業(yè)每個調(diào)度階段進行編號，如數(shù)據(jù)檢測階段編號為1000，數(shù)據(jù)導(dǎo)出階段編號為2000，數(shù)據(jù)傳輸階段編號為3000，數(shù)據(jù)裝載階段編號為4000，數(shù)據(jù)備份階段編號為5000，其他階段編號為9000等。

（2）對收集到的所有作業(yè)進行任務(wù)編碼，例如核心系統(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è)依次按順序生成相應(yīng)的檢測，裝載，備份作業(yè)編號。統(tǒng)計所有的作業(yè)編號，階段信息和渠道信息，生成作業(yè)信息表。

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

（4）將前面整理好的作業(yè)任務(wù)轉(zhuǎn)化為SQL語句程序，在ETL工作站上部署該程序，系統(tǒng)啟動后，調(diào)度生成程序根據(jù)作業(yè)信息表和作業(yè)流程表生成作業(yè)調(diào)度表。

4.2 ETL調(diào)度設(shè)計

由于數(shù)據(jù)源系統(tǒng)非常多，所需加載的數(shù)據(jù)也非常多，導(dǎo)致ETL調(diào)度程序的設(shè)計非常復(fù)雜，因此設(shè)計一個完善且易于維護的ETL調(diào)度程序，對數(shù)據(jù)倉庫的正常工作運行是非常重要[12]。ETL調(diào)度流程如圖3所示。結(jié)合業(yè)務(wù)系統(tǒng)的特點，設(shè)計出高效的ETL調(diào)度程序，具體設(shè)計步驟如下：

（1）作業(yè)調(diào)度表生成完畢，開始執(zhí)行調(diào)度任務(wù)程序，根據(jù)作業(yè)調(diào)度表中作業(yè)信息，作業(yè)調(diào)度執(zhí)行的情況，生成調(diào)度執(zhí)行情況表。

（2）根據(jù)作業(yè)優(yōu)先級關(guān)系向各個ETL工作站分配相應(yīng)數(shù)量的作業(yè)任務(wù)。如果某ETL工作站執(zhí)行作業(yè)已經(jīng)完成，則將新的作業(yè)分配給該ETL工作站，并更新調(diào)度執(zhí)行情況表。

（3）假如某個調(diào)度任務(wù)執(zhí)行出錯，則根據(jù)作業(yè)錯誤處理級別對作業(yè)進行相應(yīng)處理，如等待人工處理、自動跳過錯誤任務(wù)、重復(fù)執(zhí)行等。如果需要人工處理調(diào)度任務(wù)，則人工對調(diào)度任務(wù)進行相應(yīng)處理，并記錄日志。

（4）當作業(yè)調(diào)度表中任務(wù)全部執(zhí)行完成后，暫停調(diào)度程序，等待下一批次調(diào)度任務(wù)，ETL工作站也處于等待狀態(tài)。

4.3 ETL監(jiān)控設(shè)計

ETL作業(yè)任務(wù)運行過程中，需要實時監(jiān)控所有的作業(yè)執(zhí)行情況，其中包括ETL單個作業(yè)的狀態(tài)監(jiān)控等信息，ETL監(jiān)控的主要作用是監(jiān)控任務(wù)的執(zhí)行情況及支持對錯誤任務(wù)的手工處理。

接收調(diào)度執(zhí)行終端的反饋結(jié)果并進行相應(yīng)的處理，反饋結(jié)果有任務(wù)成功，失敗狀態(tài)。接收數(shù)據(jù)檢測消息發(fā)送器的消息并做相應(yīng)的處理，如接收到核心系統(tǒng)渠道2018-03-01的數(shù)據(jù)準備好，調(diào)度則開始跑批對應(yīng)的任務(wù)。ETL監(jiān)控相關(guān)流程如圖4所示。

4.4 ETL調(diào)度系統(tǒng)的實現(xiàn)

在上述設(shè)計的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)建一個臨時作業(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è)務(wù)數(shù)據(jù)存儲備份至數(shù)據(jù)倉庫中。當需要重新調(diào)度新的作業(yè)時，初始化INIT_JOB表為空，插入新增作業(yè)信息，根據(jù)新作業(yè)的業(yè)務(wù)特點，修改SQL腳本邏輯，實現(xiàn)新增作業(yè)的調(diào)度功能。

當作業(yè)調(diào)度開發(fā)結(jié)束后，通過查詢調(diào)度作業(yè)日志，查看數(shù)據(jù)調(diào)度是否成功，調(diào)度成功則數(shù)據(jù)成功存儲至數(shù)據(jù)倉庫中，如圖6所示。若調(diào)度失敗，則查看日志，找到調(diào)度失敗原因并修改，重新執(zhí)行調(diào)度任務(wù)，直至調(diào)度作業(yè)執(zhí)行成功。

5 結(jié)論（Conclusion）

本論文以國內(nèi)某商業(yè)銀行的BI項目為背景，設(shè)計開發(fā)了商業(yè)銀行數(shù)據(jù)倉庫的ETL調(diào)度系統(tǒng)，實現(xiàn)了銀行各業(yè)務(wù)數(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)導(dǎo)層管理決策，解決銀行最緊迫的問題，使銀行的經(jīng)濟效益最大化，從而使銀行更好更快的發(fā)展。

參考文獻（References）

[1] Ren S，Sun Q，Shi Y.Customer Segmentation of Bank Based on Data Warehouse and Data Mining[C].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 Non-Owned Data Sources[J].Data & Knowledge Engineering，2017.

[3] V Theodorou，A Abelló，M Thiele，et al.Frequent Patterns in ETL Workflows： An Empirical Approach[J].Data & Knowledge Engineering，2017：112.

[4] Dupor S，Jovanovic V.An approach to conceptual modelling of ETL processes[C].International Convention on Information and Communication Technology，Electronics and Microelectronics.IEEE，2014：1485-1490.

[5] 徐俊剛，裴瑩.數(shù)據(jù)ETL研究綜述[J].計算機科學(xué)，2011，38（4）：15-20.

[6] 常艷莉，李一凡，趙懷慈.基于銀行數(shù)據(jù)倉庫的ETL系統(tǒng)的設(shè)計與實現(xiàn)[J].微計算機信息，2006， 22（24）：21-23.

[7] 沈琦，陳博.基于大數(shù)據(jù)處理的ETL框架的研究與設(shè)計[J].電子設(shè)計工程，2016，24（2）：25-27.

[8] 張同楊.一種ETL數(shù)據(jù)整合架構(gòu)設(shè)計與實現(xiàn)[J].價值工程，2016，35（31）：88-91.

[9] 張靖，雷航，唐雪飛，等.ETL應(yīng)用優(yōu)化設(shè)計與實現(xiàn)研究[J].微電子學(xué)與計算機，2012，29（4）：134-137.

[10] 林尤惠，項穎，張鳳祺.ETL在銀行數(shù)據(jù)中心系統(tǒng)中的應(yīng)用[J].自動化與信息工程，2009，30（2）：17-20.

[11] Waas F，Wrembel R，F(xiàn)reudenreich T，et al.On-Demand ELT Architecture for Right-Time BI：Extending the Vision[J].International Journal of Data Warehousing & Mining，2013，9（2）：21-38.

[12] 宋旭東，劉曉冰.數(shù)據(jù)倉庫ETL任務(wù)調(diào)度模型研究[J].控制與決策，2011，26（2）：271-275.

作者簡介：

周 飛（1989-），男，碩士生.研究領(lǐng)域：機器學(xué)習(xí)，數(shù)據(jù)挖掘.

劉夢娜（1992-），女，碩士生.研究領(lǐng)域：機器學(xué)習(xí)，數(shù)據(jù)挖掘.

張 暉（1994-），男，碩士生.研究領(lǐng)域：機器學(xué)習(xí)，數(shù)據(jù)挖掘.

李志剛（1994-），男，碩士生.研究領(lǐng)域：機器學(xué)習(xí)，數(shù)據(jù)挖掘.

曹 帥（1993-），男，碩士生.研究領(lǐng)域：機器學(xué)習(xí)，數(shù)據(jù)挖掘.