面向城市基礎(chǔ)設(shè)施智慧管養(yǎng)的大數(shù)據(jù)智能融合方法

劉佳俊,喻 鋼,胡 珉

(1.上海大學(xué)悉尼工商學(xué)院,上海 201800; 2.上海大學(xué)—上海城建建筑產(chǎn)業(yè)化研究中心,上海 200072) (*通信作者電子郵箱yugang509@163.com)

面向城市基礎(chǔ)設(shè)施智慧管養(yǎng)的大數(shù)據(jù)智能融合方法

劉佳俊1,2,喻 鋼1,2*,胡 珉1,2

(1.上海大學(xué)悉尼工商學(xué)院,上海 201800; 2.上海大學(xué)—上海城建建筑產(chǎn)業(yè)化研究中心,上海 200072) (*通信作者電子郵箱yugang509@163.com)

針對運維大數(shù)據(jù)維度高、形式多樣化和變化迅速等特性,為提高數(shù)據(jù)融合效率以及平臺的數(shù)據(jù)統(tǒng)計和決策分析性能,降低抽取-轉(zhuǎn)換-加載(ETL)執(zhí)行時間開銷和數(shù)據(jù)中心負(fù)擔(dān),面向智慧管養(yǎng)需求提出一種多層次任務(wù)調(diào)度(MTS)ETL框架(MTS-ETL)。首先,將數(shù)據(jù)倉庫分為數(shù)據(jù)臨時區(qū)、數(shù)據(jù)倉儲區(qū)、數(shù)據(jù)分類區(qū)和數(shù)據(jù)分析區(qū),并根據(jù)所分區(qū)域?qū)⑼暾腅TL過程劃分為4個層次的ETL任務(wù)調(diào)度環(huán)節(jié),同時設(shè)計了多頻率ETL運行調(diào)度以及順序和非順序兩種ETL工作模式;接著,基于MTS-ETL框架的非順序工作模式進行數(shù)據(jù)融合的概念建模、邏輯建模和物理建模;最后,利用Pentaho Data Integration設(shè)計ETL轉(zhuǎn)換模塊和工作模塊以實現(xiàn)數(shù)據(jù)融合方法。在交通流量數(shù)據(jù)融合實驗中,該方法融合136 754條數(shù)據(jù)的時間僅為28.4 s;在千量級的數(shù)據(jù)融合實驗中比傳統(tǒng)ETL方法的總平均執(zhí)行時間降低了6.51%;報表分析結(jié)果表明其在融合400萬條數(shù)據(jù)時依然能保證ETL過程的可靠性。所提方法能夠有效融合運維大數(shù)據(jù),提高平臺統(tǒng)計分析性能,并維持ETL執(zhí)行時間開銷在較低水平。

大數(shù)據(jù);抽取-轉(zhuǎn)換-加載;數(shù)據(jù)融合;數(shù)據(jù)倉庫;城市基礎(chǔ)設(shè)施管養(yǎng)

0 引言

城市基礎(chǔ)設(shè)施管養(yǎng)是指充分利用信息化和大數(shù)據(jù)技術(shù)對城市道路、橋梁以及隧道等各類市政設(shè)施進行智慧化管理和養(yǎng)護,它涵蓋了交通建設(shè)運營、設(shè)施設(shè)備管理、項目資產(chǎn)巡檢以及養(yǎng)護合同收益等多項業(yè)務(wù)。隨著智慧城市建設(shè)的推進,城市基礎(chǔ)設(shè)施管養(yǎng)精細(xì)化的需求日漸突出,海量歷史和實時數(shù)據(jù)需要有效的大數(shù)據(jù)技術(shù)進行融合,才能為決策分析提供有力的支撐。運維大數(shù)據(jù)涵蓋了視頻、文本、流數(shù)據(jù)、建筑信息模型(Building Information Model, BIM)和地理信息系統(tǒng)(Geographic Information System, GIS)等多種數(shù)據(jù)組織形態(tài),具有數(shù)據(jù)維度高、形式多樣化以及價值密度低的特征,數(shù)據(jù)變化迅速、時空性和沖突性都比較強。既有的平臺雖然在一定程度上實現(xiàn)了狀態(tài)監(jiān)控和故障記錄等功能,但由于缺乏有效的大數(shù)據(jù)融合技術(shù),數(shù)據(jù)融合效率低下,同時導(dǎo)致數(shù)據(jù)統(tǒng)計和決策分析性能較差[1]。如何通過大數(shù)據(jù)融合方法把數(shù)據(jù)轉(zhuǎn)換為信息和知識成為實現(xiàn)智慧管養(yǎng)的主要瓶頸。

國內(nèi)外針對大數(shù)據(jù)融合方法的研究主要聚焦于抽取-轉(zhuǎn)換-加載(Extract-Transform-Load, ETL)建模與ETL架構(gòu)設(shè)計。文獻[2]提出可編程和可擴展的ETL框架以支持ETL轉(zhuǎn)換重用,但在缺乏GUI的建模環(huán)境下以編程方式自定義ETL流程的開發(fā)過程效率較低。文獻[3]提出基于模板的ETL開發(fā)方法,它允許導(dǎo)入來自源或目的端存儲的元數(shù)據(jù),添加映射或緩慢變更維度定義,并生成可執(zhí)行的ETL包,有助于減少數(shù)據(jù)倉庫各階段的開發(fā)和維護工作。文獻[4]提出基于ETL元數(shù)據(jù)模型批量更新數(shù)據(jù)倉庫表的方法,通過避免數(shù)據(jù)源未發(fā)生改變時產(chǎn)生不必要的負(fù)載,減少數(shù)據(jù)倉庫系統(tǒng)資源的消耗,但其缺少對增量加載過程的考慮。文獻[5]提出基于腳本技術(shù)的自動化ETL流程以減少ETL手動運行任務(wù)。文獻[6]提出基于模型驅(qū)動架構(gòu)的方法,開發(fā)了基于多代理模式的ETL過程以整合外部數(shù)據(jù)到數(shù)據(jù)倉庫,并且自動地產(chǎn)生代碼,它們的局限性在于靈活性和可重用性較低。文獻[7]以用戶需求為中心引入端到端的ETL過程設(shè)計方法,通過使用目標(biāo)建模技術(shù)以提高ETL概念建模的抽象水平,能較好地解決概念模型質(zhì)量問題。文獻[8]利用工具MaSSEETL設(shè)計了企業(yè)數(shù)據(jù)ETL和數(shù)據(jù)質(zhì)量解決方案。文獻[9]則設(shè)計了基于Web的ETL原型工具為用戶提供完整的ETL流程指導(dǎo),但它們在處理大規(guī)模數(shù)據(jù)時會產(chǎn)生性能瓶頸問題。文獻[10]通過使用Geokettle設(shè)計ETL情景并進行ETL建模,將數(shù)據(jù)源中的屬性與數(shù)據(jù)倉庫表的屬性進行映射,有助于自動執(zhí)行數(shù)據(jù)預(yù)處理,并在插入和更新數(shù)據(jù)時不產(chǎn)生大量的查詢。

目前,大數(shù)據(jù)融合技術(shù)研究與應(yīng)用領(lǐng)域主要為電信和郵政行業(yè)。文獻[11] 針對Teradata數(shù)據(jù)倉庫設(shè)計ETL模型,重點闡述了ETL實施流程中的ETL Automation無故障處理機制和異常處理機制,但缺少基于ETL模型的數(shù)據(jù)融合的具體實現(xiàn)方法。文獻[12]通過歸納自動化流程對ETL各類作業(yè)進行設(shè)計和命名規(guī)范,具體實現(xiàn)了ETL功能,但該方法無法適用于城市基礎(chǔ)設(shè)施管養(yǎng)領(lǐng)域中具有多源、異構(gòu)、時變和高維特征的數(shù)據(jù)融合。而城市基礎(chǔ)設(shè)施管養(yǎng)領(lǐng)域內(nèi)對于大數(shù)據(jù)融合技術(shù)的研究比較欠缺。許多既有的管養(yǎng)平臺沒有真正地將其各業(yè)務(wù)模塊與大數(shù)據(jù)融合技術(shù)進行整合,以實現(xiàn)管養(yǎng)智慧化。

因此,本文對面向城市基礎(chǔ)設(shè)施管養(yǎng)的大數(shù)據(jù)融合方法進行了探索,并針對性地設(shè)計了數(shù)據(jù)倉庫系統(tǒng)架構(gòu)和數(shù)據(jù)融合ETL框架,提出一種基于多層次任務(wù)ETL(Multilevel Task Scheduling ETL, MTS-ETL)框架的大數(shù)據(jù)融合方法。該方法將完整的ETL過程劃分為ETLⅠ、ETLⅡ、ETLⅢ和ETLⅣ這四個層次,并根據(jù)數(shù)據(jù)源不同的標(biāo)準(zhǔn)化程度分別設(shè)計了順序工作模式和非順序工作模式兩種ETL工作模式。通過對ETL執(zhí)行過程的概念建模、邏輯建模和物理建模,實現(xiàn)數(shù)據(jù)源屬性與數(shù)據(jù)倉庫表屬性之間的語義映射和ETL業(yè)務(wù)情景的定義;最后利用Pentaho Data Integration 實現(xiàn)了基于MTS-ETL框架的大數(shù)據(jù)融合方法。該方法的新穎之處在于,根據(jù)數(shù)據(jù)倉庫系統(tǒng)所劃分的4個存儲區(qū)劃分ETL過程拆為4個層次的任務(wù)調(diào)度,以提高ETL過程的容錯性;將異構(gòu)數(shù)據(jù)首先放入數(shù)據(jù)臨時區(qū)中,再進行數(shù)據(jù)標(biāo)準(zhǔn)化操作,以提高ETL抽取階段的數(shù)據(jù)加載效率;設(shè)計基于不同頻率運行ETL任務(wù),允許用戶根據(jù)業(yè)務(wù)訪問需求確定ETL運行頻率,以提高ETL調(diào)度的靈活性;將ETL面向的對象進行擴展,代替?zhèn)鹘y(tǒng)的存儲過程開發(fā),以提高ETL框架的適用性。

1 數(shù)據(jù)倉庫系統(tǒng)架構(gòu)

為實現(xiàn)諸如設(shè)備統(tǒng)計分析和設(shè)施管養(yǎng)輔助決策這類商業(yè)智能(Business Intelligence, BI)分析業(yè)務(wù)功能,必須通過數(shù)據(jù)倉庫構(gòu)建管養(yǎng)大數(shù)據(jù)的統(tǒng)一視圖以支撐綜合性的數(shù)據(jù)融合技術(shù)。數(shù)據(jù)倉庫的特點在于高度集成與管理城市基礎(chǔ)設(shè)施管養(yǎng)過程中產(chǎn)生的多源結(jié)構(gòu)化或非結(jié)構(gòu)化的靜態(tài)數(shù)據(jù)、動態(tài)數(shù)據(jù)和實時數(shù)據(jù)。本文面向城市基礎(chǔ)設(shè)施智慧管養(yǎng)需求,設(shè)計了如圖1所示的由數(shù)據(jù)源、數(shù)據(jù)集成、數(shù)據(jù)倉庫存儲、元數(shù)據(jù)管控、用戶訪問框架、技術(shù)架構(gòu)與環(huán)境以及基礎(chǔ)設(shè)施平臺所構(gòu)成的數(shù)據(jù)倉庫系統(tǒng)。

圖1 數(shù)據(jù)倉庫系統(tǒng)架構(gòu)

基于Hadoop分布式集群的數(shù)據(jù)倉庫作為整個系統(tǒng)的物理實現(xiàn)部分,分為數(shù)據(jù)臨時區(qū)、數(shù)據(jù)倉儲區(qū)、數(shù)據(jù)分類區(qū)以及數(shù)據(jù)分析區(qū)。其中:臨時區(qū)存放各業(yè)務(wù)系統(tǒng)的源數(shù)據(jù);數(shù)據(jù)倉儲區(qū)則針對數(shù)據(jù)整合和數(shù)據(jù)歷史存儲需求組織集中化和一體化的數(shù)據(jù)存儲區(qū)域,并覆蓋多個數(shù)據(jù)主題域;粒度最細(xì)的實時數(shù)據(jù)及時性要求最高,因而數(shù)據(jù)分類區(qū)面向操作型分析,存儲粒度更為詳細(xì)的實時業(yè)務(wù)系統(tǒng)多變數(shù)據(jù);而數(shù)據(jù)分析區(qū)則采取星型模型結(jié)構(gòu)存儲匯總數(shù)據(jù)。Hadoop所提供的Hadoop分布式文件系統(tǒng)(Hadoop Distributed File System, HDFS)可以為ETL提供技術(shù)支持,而數(shù)據(jù)倉庫管理技術(shù)Hive則可以對HDFS上的文件進行轉(zhuǎn)換處理操作。由圖1可以看出,ETL作為數(shù)據(jù)源和數(shù)據(jù)倉庫之間的橋梁,確保數(shù)據(jù)能夠進入數(shù)據(jù)倉庫。

2 ETL框架設(shè)計

2.1 MTS-ETL框架

ETL將日常業(yè)務(wù)操作的數(shù)據(jù)轉(zhuǎn)化為數(shù)據(jù)倉庫存儲的決策支持型數(shù)據(jù),在邏輯上分為數(shù)據(jù)的抽取、清洗、轉(zhuǎn)換以及加載四個過程,其框架設(shè)計的好壞最終決定了數(shù)據(jù)倉庫系統(tǒng)性能的高低。傳統(tǒng)的ETL架構(gòu)存在以下局限性:1)將ETL包含在一個完整的過程中執(zhí)行,沒有對ETL流程進行更加細(xì)粒度的劃分；2)缺少數(shù)據(jù)臨時存儲區(qū)域以存儲來自異構(gòu)數(shù)據(jù)源的數(shù)據(jù),當(dāng)全量抽取或增量抽取的數(shù)據(jù)量很大時,容易造成多源并發(fā)抽取的性能瓶頸,加重數(shù)據(jù)倉庫存儲區(qū)的負(fù)擔(dān)；3) 沒有考慮數(shù)據(jù)源的數(shù)據(jù)頻度、量級以及對業(yè)務(wù)訪問的需求來確定數(shù)據(jù)抽取頻率。這種傳統(tǒng)的ETL架構(gòu)不能很好地適應(yīng)于基于管養(yǎng)業(yè)務(wù)需求所劃分出的數(shù)據(jù)倉庫四個數(shù)據(jù)存儲區(qū)域。所以,本文在對傳統(tǒng)ETL框架進行改進的基礎(chǔ)上,結(jié)合城市基礎(chǔ)設(shè)施智慧管養(yǎng)需求和數(shù)據(jù)倉庫系統(tǒng)架構(gòu),設(shè)計了如圖2所示的MTS-ETL框架,其中圓角矩形表示ETL各階段的執(zhí)行任務(wù),矩形表示各階段所存儲的數(shù)據(jù)。

圖2 MTS-ETL框架

MTS-ETL框架設(shè)計的核心思想在于把整個ETL過程分為不同層次ETL任務(wù)調(diào)度和不同頻率的ETL運行調(diào)度。如此改進后的MTS-ETL架構(gòu)優(yōu)點在于:1) 根據(jù)數(shù)據(jù)倉庫系統(tǒng)所劃分的數(shù)據(jù)臨時區(qū)、數(shù)據(jù)倉儲區(qū)、數(shù)據(jù)分類區(qū)以及數(shù)據(jù)分析區(qū),將完整ETL過程拆分為4個層次的任務(wù)調(diào)度,每個存儲區(qū)域都能夠通過執(zhí)行ETL任務(wù)對數(shù)據(jù)進行處理以組織所需的數(shù)據(jù)形態(tài)。而且由于數(shù)據(jù)源并不是一次性直接加載到數(shù)據(jù)倉庫,進行元數(shù)據(jù)管控、數(shù)據(jù)質(zhì)量審計以及錯誤數(shù)據(jù)的定位與排查也相對容易。2) 將ETL的數(shù)據(jù)抽取、轉(zhuǎn)換和加載分割開來,將抽取到的大批量異構(gòu)數(shù)據(jù)首先放入數(shù)據(jù)臨時區(qū)中, 再進行數(shù)據(jù)標(biāo)準(zhǔn)化操作,然后加載至數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū),提高了數(shù)據(jù)加載效率。3) 由于運維大數(shù)據(jù)的數(shù)據(jù)頻度和不同數(shù)據(jù)類型的量級差異性均較大,基于不同頻率運行ETL任務(wù)相對于傳統(tǒng)的ETL架構(gòu)而言,更加適應(yīng)基于不同頻率的數(shù)據(jù)分類和數(shù)據(jù)分析需求,MTS-ETL允許用戶根據(jù)業(yè)務(wù)訪問需求確定ETL運行頻率,因而MTS-ETL是面向用戶需求的,具有更大的靈活性。4) ETL對象并不限于數(shù)據(jù)倉庫,而是將ETL適用的范圍擴展為由數(shù)據(jù)源到數(shù)據(jù)倉庫目的端再到展示端數(shù)據(jù)庫的過程,具有更大的適用性。下面闡述MTS-ETL的四個任務(wù)調(diào)度環(huán)節(jié):

①ETLⅠ負(fù)責(zé)異構(gòu)數(shù)據(jù)源抽取并存放在數(shù)據(jù)臨時區(qū)。數(shù)據(jù)源獲取優(yōu)先選擇數(shù)據(jù)庫直連方式,即通過開放數(shù)據(jù)庫互連(Open Database Connectivity, ODBC)或數(shù)據(jù)庫Native連接方式,直接連接到源數(shù)據(jù)庫;其次選擇文件傳輸方式,按約定的接口文件格式導(dǎo)出數(shù)據(jù),以文件方式批量傳輸數(shù)據(jù);對少量且實時性要求較高的數(shù)據(jù)采用企業(yè)應(yīng)用集成(Enterprise Application Integration,EAI)方式,通過EAI平臺定義的接口服務(wù)進行傳輸;而對于無源系統(tǒng)支撐的數(shù)據(jù)源采取手工錄入方式。該階段臨時區(qū)數(shù)據(jù)與數(shù)據(jù)源基本保持一致,且臨時區(qū)存儲的數(shù)據(jù)被處理后不會被保留,其功能在于縮短多數(shù)據(jù)源融合時間,減輕數(shù)據(jù)源和數(shù)據(jù)存儲中心的負(fù)擔(dān)。

②ETLⅡ是MTS-ETL框架的關(guān)鍵環(huán)節(jié),首先實現(xiàn)數(shù)據(jù)類型的標(biāo)準(zhǔn)化,即盡量在源系統(tǒng)側(cè)提升數(shù)據(jù)質(zhì)量,再做清洗和轉(zhuǎn)換操作,以方便后續(xù)數(shù)據(jù)校驗。第二層次的核心工作在于對來自臨時區(qū)的數(shù)據(jù)執(zhí)行過濾、解析、修正、去重、分類、聚合、排序以及匹配等清洗和轉(zhuǎn)換操作后裝載入數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū)。其中,數(shù)據(jù)倉儲區(qū)采用3NF存儲結(jié)構(gòu)形成統(tǒng)一的數(shù)據(jù)模型,而數(shù)據(jù)分類區(qū)則按不同粒度對數(shù)據(jù)倉儲區(qū)的數(shù)據(jù)進行分類存儲。

③ETLⅢ 負(fù)責(zé)將來自數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū)的數(shù)據(jù)進行匯總,目的在于按照不同BI技術(shù)手段的功能定位進一步組織數(shù)據(jù)實體,比如利用關(guān)鍵績效指標(biāo)(Key Performance Indicator, KPI)和固定報表滿足決策層和管理層需求;而實時查詢、動態(tài)報表、聯(lián)機分析處理(Online Analytical Processing, OLAP)分析用于實現(xiàn)數(shù)據(jù)的深層次多維分析。

④ETLⅣ 邏輯上不屬于基本的ETL過程,而是結(jié)合了管養(yǎng)平臺自身特點和現(xiàn)實需求,專門為各目標(biāo)系統(tǒng)數(shù)據(jù)庫而增設(shè)的處理環(huán)節(jié)。基于不同的粒度,將數(shù)據(jù)倉庫累積的數(shù)據(jù)增量裝載至數(shù)據(jù)庫,以便于系統(tǒng)更加高效地調(diào)用融合后的數(shù)據(jù),從而支持多種數(shù)據(jù)可視化方案以及系統(tǒng)分析結(jié)果的展示與呈現(xiàn)。

MTS-ETL框架中不僅包含四個層次的ETL任務(wù)調(diào)度,還包括多個頻率的運行調(diào)度。ETL定時任務(wù)作為多頻率運行調(diào)度中必不可少的環(huán)節(jié),取決于抽取各業(yè)務(wù)系統(tǒng)數(shù)據(jù)的頻率程度(年、月、日、小時)。在默認(rèn)情況下,MTS-ETL通過指定日期參數(shù)抽取數(shù)據(jù)源。

2.2 MTS-ETL工作機制

MTS-ETL框架采用如圖3所示的分級與依賴機制,分為以下三個步驟:

1)ETL參數(shù)初始化工作。主要配置資源庫和運行目錄等ETL參數(shù)文件,其中ETL控制表記錄了每個ETL工作流的運行狀態(tài)和運行批次日期。如果ETL控制表有未執(zhí)行完的流程,則根據(jù)ETL控制表與配置參數(shù)生成運行所需的參數(shù)文件；否則還需要生成運行數(shù)據(jù)的批次日期。

2)ETL執(zhí)行工作。從ETLⅠ到ETLⅣ依次執(zhí)行各ETL目錄內(nèi)的工作流。

3)ETL收尾工作。等待工作流成功結(jié)束后更新ETL控制表狀態(tài),并獲取資源庫和生成運行日志。

圖3 MTS-ETL工作機制

圖4 順序工作模式

圖5 非順序工作模式

由于數(shù)據(jù)源的標(biāo)準(zhǔn)化程度不同,MTS-ETL框架設(shè)計了兩種工作模式:順序工作模式和非順序工作模式。順序模式如圖4所示。圖4中，Step 4的臨時區(qū)目標(biāo)表保存了與源系統(tǒng)一致的業(yè)務(wù)數(shù)據(jù),為數(shù)據(jù)加載進入數(shù)據(jù)倉儲區(qū)作為臨時存儲數(shù)據(jù),臨時區(qū)數(shù)據(jù)存儲與源系統(tǒng)表名稱保持一致,增加源系統(tǒng)名稱作前綴形式命名,數(shù)據(jù)表采用增加LOAD_DATE標(biāo)識方式區(qū)分不同時段的加載數(shù)據(jù)。

但是對于穩(wěn)定的標(biāo)準(zhǔn)維度定義或者個別源表,包括主要用于實時查詢的字典表,都可以通過逗號分隔值(Comma-Separated Value, CSV)文件格式數(shù)據(jù)直接裝載入數(shù)據(jù)分類區(qū),該過程為標(biāo)準(zhǔn)的增量裝載過程,這種非順序工作模式如圖5所示。圖5中，Step 3通過Mapping Group實現(xiàn)對于外鍵的代理鍵的匹配過程,Step 5根據(jù)數(shù)據(jù)源的字段比如XX_CODE等聯(lián)合主鍵查詢目標(biāo)表,Step 6 至 Step 8描述了標(biāo)準(zhǔn)的增量裝載過程,對于變化的數(shù)據(jù)采取更新操作,未發(fā)生歷史變化的數(shù)據(jù)可以更新時間戳,而新數(shù)據(jù)則在生成序列后插入目標(biāo)表。

3 數(shù)據(jù)倉庫和ETL建模

3.1 數(shù)據(jù)倉庫模型設(shè)計

本節(jié)以通行流量主題為例來闡述數(shù)據(jù)倉庫模型的設(shè)計。通過組建多維數(shù)據(jù)模型,從各個角度分析數(shù)據(jù),得到通行流量的環(huán)比和同比、車型分類流量以及通行流量預(yù)測等信息。

數(shù)據(jù)屬性選擇包括:從項目基礎(chǔ)數(shù)據(jù)中選擇項目編號(PROJECTID)、公司編號(CORPORATIONID)和項目名稱(PROJECTNAME)3個屬性;從區(qū)段數(shù)據(jù)中選擇區(qū)段編號(SECTIONID)、區(qū)段名稱(SECTIONNAME)、行車方向編號(DIRECTIONPKID)3個屬性;從通行流量數(shù)據(jù)中選擇記錄編號(RECORDID)、流量編號(IDX)、日流量(FLOWOFDAY)、區(qū)段流量(SECTIONFLOWS)、總流量(TOTALFLOW)、發(fā)生日期(OCCURDATE)6個屬性。在此步驟,通過流量事實表(FACT_DAYFLOW)、項目維度表(TB_PROJECT)、區(qū)段維度表(TB_SECTION)和日期維度表(TB_DATE)組成的星型模型來構(gòu)建數(shù)據(jù)倉庫多維方案,其星型模式結(jié)果如圖6所示。

圖6 星型模型

3.2 ETL模型設(shè)計

3.2.1 概念建模

ETL概念建模旨在為ETL過程創(chuàng)建一個概念模型,以描述數(shù)據(jù)源中的字段與數(shù)據(jù)倉庫表中的字段之間的映射關(guān)系。流量數(shù)據(jù)是存儲于數(shù)據(jù)庫的完全結(jié)構(gòu)化數(shù)據(jù),標(biāo)準(zhǔn)化程度和實時程度較高,它不需要在數(shù)據(jù)倉庫的數(shù)據(jù)臨時區(qū)進行緩存。基于這種高度標(biāo)準(zhǔn)化的數(shù)據(jù)特征,MTS-ETL框架允許采取非順序工作模式,選擇性地跳過ETLⅠ將異構(gòu)數(shù)據(jù)源抽取并存放在數(shù)據(jù)臨時區(qū)的環(huán)節(jié),而是從源數(shù)據(jù)庫抽取數(shù)據(jù)并增量裝載入數(shù)據(jù)倉庫的數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū)。

非順序工作模式的優(yōu)點不僅在于降低了ETL抽取工作的開發(fā)難度,同時提高了部分標(biāo)準(zhǔn)數(shù)據(jù)源的集成效率,特別是MTS-ETL展示層對實時數(shù)據(jù)訪問要求較高的情況下,ETL工作效率直接影響數(shù)據(jù)可視化和動態(tài)報表服務(wù)的性能。它與順序工作模式的主要差別在于,首先需要通過ETLⅠ將數(shù)據(jù)加載進入數(shù)據(jù)倉儲區(qū)作為臨時存儲數(shù)據(jù),之后再從臨時區(qū)加載所需數(shù)據(jù)到數(shù)據(jù)倉庫表。

圖7 FACT_DAYFLOW的概念模型

圖8 FACT_DAYFLOW的邏輯模型

流量事實表(FACT_DAYFLOW)的概念模型如圖7所示。圖7展示了從數(shù)據(jù)庫映射數(shù)據(jù)源的屬性到數(shù)據(jù)倉庫表。其中,數(shù)據(jù)源為日流量表(T_DAYFLOW),數(shù)據(jù)倉庫表為流量事實表(FACT_DAYFLOW)。基于非順序模式,ETLⅡ直接對來自源數(shù)據(jù)庫的數(shù)據(jù)執(zhí)行清洗和轉(zhuǎn)換操作,然后裝載入數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū)。

日流量表和流量事實表之間的映射通過以下幾個轉(zhuǎn)換步驟實現(xiàn),具體包括數(shù)據(jù)字段選擇、數(shù)據(jù)類型轉(zhuǎn)換、數(shù)據(jù)的屬性值映射與過濾、數(shù)據(jù)計算與排序、數(shù)據(jù)分組與聚合,根據(jù)數(shù)據(jù)源編號等字段查詢目標(biāo)表以獲取維度表的外鍵、映射字段以匹配流量事實表與數(shù)據(jù)源的字段,以及將數(shù)據(jù)增量裝載入數(shù)據(jù)倉儲區(qū)的流量事實表中以形成統(tǒng)一的運營主題數(shù)據(jù)模型。數(shù)據(jù)分類區(qū)則會根據(jù)運營、管養(yǎng)、資產(chǎn)以及收益等分類將數(shù)據(jù)倉儲區(qū)的流量數(shù)據(jù)存儲到運營主題域中,并且數(shù)據(jù)分類區(qū)的流量數(shù)據(jù)將會基于不同頻率通過ETLⅢ進行匯總存儲,充分體現(xiàn)了MTS-ETL的多層次數(shù)據(jù)處理特點。概念建模是數(shù)據(jù)映射以及以后的ETL和前端的開發(fā)工作的基礎(chǔ),下面將基于概念模型對ETL邏輯建模進行流程分解描述。

3.2.2 邏輯建模

邏輯建模關(guān)注的是從數(shù)據(jù)抽取開始直到數(shù)據(jù)存儲結(jié)束這一過程中從數(shù)據(jù)源到數(shù)據(jù)倉庫的數(shù)據(jù)流,它是概念建模的延伸。它將概念建模上的文字描述轉(zhuǎn)換為邏輯建模符號,并按照所抽取的數(shù)據(jù)和數(shù)據(jù)流邏輯組織轉(zhuǎn)換流程,以達(dá)到清洗數(shù)據(jù)的目的。流量事實表(FACT_DAYFLOW)的邏輯建模結(jié)果如圖8所示。

3.2.3 物理建模

物理建模是根據(jù)邏輯模型對應(yīng)到具體數(shù)據(jù)模型的機器實現(xiàn),以對真實的數(shù)據(jù)庫和數(shù)據(jù)倉庫進行描述,所以需要為數(shù)據(jù)倉庫表的每個屬性明確物理模型的數(shù)據(jù)類型,才能將轉(zhuǎn)換的結(jié)果映射到數(shù)據(jù)倉庫中的已存在的表中,流量事實表(FACT_DAYFLOW)的物理建模結(jié)果如圖9所示。

圖9顯示了流量事實表(FACT_DAYFLOW)的物理建模過程,該過程與概念和邏輯建模中的轉(zhuǎn)換過程一致。其中,流量事實表(FACT_DAYFLOW)包含PROJECTID,SECTIONID和DATEID屬性作為外鍵FK,這些屬性為字符串?dāng)?shù)據(jù)類型。其余的屬性還包含數(shù)值類型的總流量,以及字符串類型的流量信息編號、區(qū)段流量和流量發(fā)生日期。

圖9 FACT_DAYFLOW的物理模型

4 基于MTS-ETL的數(shù)據(jù)融合方法實現(xiàn)

本文使用開源的ETL工具Pentaho Data Integration來實現(xiàn)基于MTS-ETL框架的數(shù)據(jù)融合方法,核心包括:轉(zhuǎn)換模塊(Transformation Module)和工作模塊(Job Module)。Transformation Module完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換,Job Module則完成整個工作流的控制。

4.1 轉(zhuǎn)換模塊

Transformation Module是基于MTS-ETL框架的數(shù)據(jù)融合方法的基礎(chǔ),具體包含:引入數(shù)據(jù)源、引入目的數(shù)據(jù)源、開發(fā)中間轉(zhuǎn)換以及引入增量全局參數(shù)等步驟。以第3章對流量事實表的ETL建模結(jié)果為例,Transformation Module實現(xiàn)過程如圖10所示。

圖10 轉(zhuǎn)換模塊設(shè)計

從圖10來看,Step1是使用表輸入控件從數(shù)據(jù)庫源中抽取數(shù)據(jù),數(shù)據(jù)源即日流量表(T_DAYFLOW),這里采用增量抽取的方式,參照系統(tǒng)時間(SYSDATE)抽取上月20號到本月19號的流量數(shù)據(jù)并進行了排序。Step 2是通過字段選擇控件對基于使用星型模式創(chuàng)建的數(shù)據(jù)屬性名稱進行選擇和調(diào)整,該步驟可以將DATE屬性的數(shù)據(jù)類型轉(zhuǎn)換為日期類型,而不需要的屬性則可以被篩選掉。Step 3通過過濾記錄控件過濾掉FLOWOFDAY為NULL的記錄。Step 4的值映射控件用來計算字段前面步驟傳遞來的屬性,并映射出新的屬性。Step 5通過JavaScript控件進行屬性拼接。Step 6選擇需要進行分組統(tǒng)計的屬性名稱,執(zhí)行聚合操作的前提是必須對數(shù)據(jù)進行排序以生成有效的數(shù)據(jù)流。Step 7進行分組,同時將各區(qū)段方向的日流量值進行拼接。Step 8利用JavaScript控件首先將字符串類型的屬性轉(zhuǎn)換為整型,接著計算各區(qū)段方向的日流量總和,然后Step 9 再通過創(chuàng)建字典的方式將各區(qū)段方向?qū)傩灾岛蛯?yīng)的日流量值進行匹配,空屬性賦值NULL并拼接上逗號分隔符后得到新的字符串。經(jīng)過Step10的字段選擇控件基本上就得到了數(shù)據(jù)倉庫目標(biāo)表所需的主要屬性,接著在Step11中基于不同分組從1開始生成編號。由于事實表需要每個維度的主鍵ID以及度量值,Step12至Step14使用數(shù)據(jù)庫查詢控件,以獲取每個維度表的主鍵ID,維度表由項目信息維度表(TB_PROJECT)、區(qū)段信息維度表(TB_DISTRICT)和日期時間維度表(TB_DATE)組成。在獲取每個維度的ID之后,將屬性從數(shù)據(jù)源映射到FACT_DAYFLOW的屬性表還需要使用Step15的字段選擇控件,最后一步是使用插入或更新控件增量加載轉(zhuǎn)換結(jié)果到數(shù)據(jù)倉庫目標(biāo)表。

4.2 工作模塊

Job Module用于控制和調(diào)度各個轉(zhuǎn)換模塊之間的執(zhí)行順序,它還可以通過電子郵件發(fā)送通知和寫日志等,如圖11所示。Job Module以START控件作為初始化來啟動作業(yè),這里使用Pentaho Data Integration內(nèi)置的時間調(diào)度方式設(shè)置Job Module執(zhí)行定時任務(wù),作業(yè)啟動后依次加載Transformation Module、LOAD_TIME、LOAD_PROJECT、LOAD_SECTION、FACT_DAYFLOW。若Transformation Module均成功執(zhí)行,則成功控件將顯示執(zhí)行成功;否則將通過發(fā)送郵件控件發(fā)送通知,接著通過設(shè)置變量和寫日志控件將ETL運行記錄寫入資源庫日志,以便迅速查找錯誤信息和判別執(zhí)行效率較低的Transformation Module,從而進行定位優(yōu)化。

圖11 工作模塊設(shè)計

4.3 MTS-ETL測試

本節(jié)的測試主要是驗證MTS-ETL設(shè)計方案的執(zhí)行效率和處理性能。下面通過將源數(shù)據(jù)庫的2015年9月20號到10月19號的136 754條交通流量數(shù)據(jù)抽取、轉(zhuǎn)換并加載至數(shù)據(jù)倉庫目標(biāo)表,以證明MTS-ETL的可行性。MTS-ETL測試結(jié)果可以在如圖12所示的執(zhí)行結(jié)果面板的步驟度量標(biāo)簽中看到,FACT_DAYFLOW表生成3 776行數(shù)據(jù)并且狀態(tài)已完成,這意味著整個轉(zhuǎn)換成功執(zhí)行且無任何錯誤,且該方法融合136 754條數(shù)據(jù)的時間僅為28.4 s。

為了對比不同數(shù)量級下傳統(tǒng)ETL與MTS-ETL過程的執(zhí)行效率,證明基于MTS-ETL框架的數(shù)據(jù)倉庫系統(tǒng)的高效性,本文對千量級小規(guī)模流量數(shù)據(jù)融合進行了測試,傳統(tǒng)ETL和MTS-ETL的任務(wù)執(zhí)行時間與測試數(shù)據(jù)量的關(guān)系如圖13(a)所示。當(dāng)數(shù)據(jù)量小于5 000條時,傳統(tǒng)ETL與MTS-ETL執(zhí)行時間開銷差別極小;但隨著數(shù)據(jù)量繼續(xù)增加,傳統(tǒng)ETL執(zhí)行時間隨著數(shù)據(jù)量的增加而明顯遞增,而MTS-ETL執(zhí)行時間依舊維持在較低水平的增長率,且其總平均執(zhí)行時間開銷比傳統(tǒng)ETL降低了6.51%。為進一步證明MTS-ETL對百萬量級的大規(guī)模數(shù)據(jù)依然具有穩(wěn)定的融合性能,本文對大規(guī)模流量數(shù)據(jù)融合進行測試發(fā)現(xiàn),400萬條交通流量數(shù)據(jù)融合僅需349.5 s,如圖13(b)所示的MTS-ETL任務(wù)執(zhí)行時間與測試數(shù)據(jù)量的關(guān)系。

圖12 MTS-ETL測試結(jié)果

圖13 數(shù)據(jù)融合執(zhí)行時間開銷測試結(jié)果

4.4 實例應(yīng)用

上海城市基礎(chǔ)設(shè)施管養(yǎng)平臺是集工程管理、資產(chǎn)管理、養(yǎng)護管理、收益管理和運營管理為一體,能夠為城市基礎(chǔ)設(shè)施運維提供綜合展示、管理分析、養(yǎng)護分析、運營分析以及報表分析等輔助決策信息的智慧管養(yǎng)平臺。以實現(xiàn)流量報表分析服務(wù)為例,通過基于MTS-ETL框架的大數(shù)據(jù)融合方法對上海嘉瀏高速公路2012年—2015年約400萬條的交通流量數(shù)據(jù)進行了融合與集成。

由于運維大數(shù)據(jù)的層次非常深且零碎,會給數(shù)據(jù)庫存儲過程和觸發(fā)器開發(fā)帶來相當(dāng)大的編寫難度,導(dǎo)致開發(fā)效率不高。MTS-ETL框架相對于傳統(tǒng)ETL架構(gòu)的優(yōu)勢在于:首先MTS-ETL是同時面向數(shù)據(jù)倉庫和數(shù)據(jù)庫的,它將ETL架構(gòu)從數(shù)據(jù)倉庫端延伸到了平臺展示層的數(shù)據(jù)庫端,通過ETL轉(zhuǎn)換和工作模塊取代大部分存儲過程開發(fā),解決了存儲過程對深層次數(shù)據(jù)統(tǒng)計和轉(zhuǎn)換能力較弱的問題。其次,MTS-ETL是面向管養(yǎng)數(shù)據(jù)分析的,傳統(tǒng)ETL架構(gòu)并沒有對數(shù)據(jù)倉庫系統(tǒng)目標(biāo)區(qū)域作詳細(xì)劃分,導(dǎo)致系統(tǒng)內(nèi)部數(shù)據(jù)存儲邏輯不夠清晰,而MTS-ETL根據(jù)數(shù)據(jù)倉庫系統(tǒng)不同存儲區(qū)域規(guī)劃了ETL調(diào)度任務(wù),特別是針對不同主題域分類存儲數(shù)據(jù),使系統(tǒng)具備更加快速響應(yīng)的數(shù)據(jù)分析能力,改善了ETL架構(gòu)數(shù)據(jù)處理的性能。最后,MTS-ETL是面向用戶需求的,針對運維大數(shù)據(jù)形式多樣化的特點開辟了數(shù)據(jù)臨時區(qū)和兩種工作模式;并且針對運維大數(shù)據(jù)多頻率的特點設(shè)計了四個頻度的ETL運行調(diào)度環(huán)節(jié),增強了傳統(tǒng)ETL架構(gòu)的實用性。

基于4.1和4.2節(jié)的Transformation Module和Job Module開發(fā),通過Pentaho Data Integration從源數(shù)據(jù)庫抽取數(shù)據(jù)并增量裝載入數(shù)據(jù)倉庫的數(shù)據(jù)倉儲區(qū)和數(shù)據(jù)分類區(qū)之后,接著在ETLⅢ進行數(shù)據(jù)匯總,最后在ETLⅣ將數(shù)據(jù)分析區(qū)的數(shù)據(jù)抽取至報表數(shù)據(jù)庫,使得報表分析數(shù)據(jù)可以動態(tài)實時同步而不再需要二次處理過程,如圖14所示的數(shù)據(jù)融合過程。

為了對城市基礎(chǔ)設(shè)施智慧管養(yǎng)平臺構(gòu)建開源的ETL方案,本文采用Pentaho Report Designer進行報表設(shè)計,原因不僅在于它具有易于訪問廣泛的數(shù)據(jù)源和易于發(fā)布報表到Web端,還在于它有易于導(dǎo)航的GUI和與插件,比如使用Ctools組件生成平臺報表所需要的圖形和儀表盤。通過對流量報表進行設(shè)計,再將報表部署到Pentaho BI Server上,最后集成到平臺報表分析模塊后,可以得到如圖15所示的上海嘉瀏高速公路2015年度車流量報表分析結(jié)果。

圖14 數(shù)據(jù)融合過程

圖15 上海嘉瀏高速公路2015年度車流量報表分析結(jié)果

5 結(jié)語

本文面向城市基礎(chǔ)設(shè)施智慧管養(yǎng)需求,研究了大數(shù)據(jù)環(huán)境下的智能融合方法,提出多層次任務(wù)調(diào)度ETL框架;基于MTS-ETL框架下ETL工作模式,詳細(xì)闡述了數(shù)據(jù)融合方法的概念建模、邏輯建模和物理建模過程;利用Pentaho Data Integration實現(xiàn)了數(shù)據(jù)融合方法并將其應(yīng)用于上海市城市基礎(chǔ)設(shè)施智慧管養(yǎng)平臺,為城市基礎(chǔ)設(shè)施養(yǎng)護輔助決策分析奠定了基礎(chǔ),為適應(yīng)快速發(fā)展的養(yǎng)護管理工作和促進養(yǎng)護信息共享作出了貢獻。未來工作重點在于,研究支持半自動化抽取建筑信息模型(BIM)數(shù)據(jù)的ETL過程:利用基于本體的語義網(wǎng)技術(shù)構(gòu)建BIM本體庫,通過識別與數(shù)據(jù)倉庫模式相關(guān)的部分?jǐn)?shù)據(jù)源模式,半自動化地定義屬性間的語義映射以支持抽取過程,從而可以快速插入和管理新數(shù)據(jù)源。

References)

[1] ZHAO J, DENG W. Fuzzy multiobjective decision support model for urban rail transit projects in China[J]. Transport, 2013, 28(3): 224-235.

[2] SILVA M S, TIMES V C, KWAKYE M. A framework for ETL systems development[J]. Journal of Information amp; Data Management, 2012, 3(3): 300-315.

[3] STUMPTNER R, FREUDENTHALER B, KRENN M. BIAccelerator — a template-based approach for rapid ETL development[C]// ISMIS 2012: Proceedings of the 20th International Symposium on Methodologies for Intelligent Systems. Berlin: Springer, 2012: 435-444.

[4] RAHMAN N, MARZ J, AKHTER S. An ETL metadata model for data warehousing[J]. Journal of Computing amp; Information Technology, 2012, 20(2).

[5] RADHAKRISHNA V, SRAVANKIRAN V, RAVIKIRAN K. Automating ETL process with scripting technology[C]// Proceedings of the 2012 Nirma University International Conference on Engineering. Piscataway, NJ: IEEE, 2013: 1-4.

[6] SADIQ A, FAZZIKI A E, SADGAL M. An Agent based ETL system: towards an automatic code generation[J]. World Applied Sciences Journal, 2014, 31(5): 979-987.

[7] THEODOROU V, ABELLA, THIELE M, et al. A framework for user-centered declarative ETL[C]// DOLAP 2014: Proceedings of the 17th International Workshop on Data Warehousing and OLAP. New York: ACM, 2014: 67-70.

[8] GILL R, SINGH J. An open source ETL tool — medium and small scale enterprise ETL (MaSSEETL) [J]. International Journal of Computer Applications, 2014, 108(4): 15-22.

[9] NOVAK M, RABUZIN K. Prototype of a Web ETL tool[J]. International Journal of Advanced Computer Science amp; Applications, 2014, 5(6): 97-103.

[10] ASTRIANI W, TRISMININGSIH R. Extraction, Transformation, and Loading (ETL) module for hotspot spatial data warehouse using Geokettle[J]. Procedia Environmental Sciences, 2016, 33: 626-634.

[11] 王可欣. ETL技術(shù)在電信數(shù)據(jù)運營中的應(yīng)用[J]. 電腦知識與技術(shù), 2016, 12(24): 256-257. (WANG K X. Application of ETL technology in telecommunication data operation [J]. Computer Knowledge and Technology, 2016, 12(24): 256-257.)

[12] 張建興. 中國郵政速遞數(shù)據(jù)倉庫系統(tǒng)ETL的設(shè)計與實現(xiàn)[D]. 北京: 北京交通大學(xué), 2014. (ZHANG J X. Design and implementation of ETL for China post data warehouse system [D]. Beijing: Beijing Jiaotong University, 2014.)

Intelligentintegrationapproachofbigdataforurbaninfrastructuremanagementandmaintenance

LIU Jiajun1,2, YU Gang1,2*, HU Min1,2

(1.SHU-UTSSILCBusinessSchool,Shanghai201800,China;2.ShanghaiUniversity-ShanghaiUrbanConstructionGroupResearchCenterofBuildingIndustrialization,Shanghai200072,China)

In order to improve the efficiency of data integration, enhance both statistical and decisional analysis performance of the platform and reduce Extract-Transform-Load (ETL) execution time and the burden of data center, according to the operation and maintenance big data with characteristics of high dimension, diversity and variability, a Multilevel Task Scheduling (MTS) ETL framework (MTS-ETL) was proposed for intelligent maintenance requirements. Firstly, the data warehouse was divided into a series of parts, including data temporary area, data storage area, data classification area and data analysis area. In the light of the sub-region, the integral ETL process was divided into four levels of ETL task scheduling. Moreover, the multi-frequency ETL operation scheduling and sequential and non-sequential ETL working modes were designed at the same time. Secondly, the conceptual modelling, logical modelling and physical modelling of data integration were implemented based on the non-sequential mode of MTS-ETL framework. Finally, the ETL transformation module and job module were designed by using Pentaho Data Integration to realize this data integration method. In the traffic flow data integration experiment, the method integrated 136 754 data for only 28.4 seconds, and reduced the total average execution time by 6.51% compared to the traditional ETL method in a thousand-scale data integration experiment. The reliability of ETL process was proved by the report analysis results of integrating 4 million data. The proposed method can effectively integrate the operation and maintenance of big data, improve the statistical analysis performance of platform and maintain ETL execution time at a low level.

big data; Extract-Transform-Load (ETL); data integration; data warehouse; urban infrastructure management and maintenance

2017- 04- 11;

2017- 06- 24。

上海市城鄉(xiāng)建設(shè)和交通委員會建管項目(2014-009-002);上海市科委重點項目(13511504803);上海市國資委重大科研項目(2014008)。

劉佳俊(1994—),男,湖北荊州人,碩士研究生,主要研究方向:數(shù)據(jù)倉庫、ETL建模; 喻鋼(1977—),男,江西南昌人,博士,講師,主要研究方向:地下工程信息化施工管理; 胡珉(1970—),女,上海人,副教授,博士,主要研究方向:建筑信息化。

時間 2017- 09- 25 10:58:07。 網(wǎng)絡(luò)出版地址 http://kns.cnki.net/kcms/detail/51.1307.TP.20170925.1058.002.html。

1001- 9081(2017)10- 2983- 08

10.11772/j.issn.1001- 9081.2017.10.2983

TP311.13

A

This work is partially supported by the Construction Project of Shanghai Urban-Rural Construction and Transportation Committee (2014-009-002); the Key Project of Shanghai Committee of Science and Technology (13511504803); the Major Project of Shanghai SASAC (2014008).

LIUJiajun, born in 1994, M. S. candidate. His research interests include data warehouse, ETL modelling.

YUGang, born in 1977, Ph. D., lecturer. His research interests include informationized construction and management for underground engineering.

HUMin, born in 1970, Ph. D., associate professor. Her research interests include building informationization.