數(shù)控機(jī)床傳感器數(shù)據(jù)分析中ETL系統(tǒng)改進(jìn)①

劉 峰, 蔡明高,2, 于 波, 于碧輝

1(中國(guó)科學(xué)院 沈陽(yáng)計(jì)算技術(shù)研究所,沈陽(yáng) 110168)2(中國(guó)科學(xué)院大學(xué),北京 100049)

數(shù)控機(jī)床傳感器數(shù)據(jù)分析中ETL系統(tǒng)改進(jìn)①

劉 峰1, 蔡明高1,2, 于 波1, 于碧輝1

1(中國(guó)科學(xué)院 沈陽(yáng)計(jì)算技術(shù)研究所,沈陽(yáng) 110168)2(中國(guó)科學(xué)院大學(xué),北京 100049)

ETL(Extract-Transform-Load)系統(tǒng)是數(shù)控機(jī)床傳感器數(shù)據(jù)分析中不可或缺的一個(gè)重要組成部分.針對(duì)近年來(lái)數(shù)據(jù)量越來(lái)越大和實(shí)時(shí)性要求越來(lái)越高的問(wèn)題,本文對(duì)ETL系統(tǒng)中傳統(tǒng)的變更數(shù)據(jù)捕獲方案做了改進(jìn),設(shè)計(jì)了適用于該數(shù)據(jù)的實(shí)時(shí)數(shù)據(jù)抽取方案,并詳述了整個(gè)分布式架構(gòu)的設(shè)計(jì).實(shí)驗(yàn)表明,該ETL系統(tǒng)在數(shù)控機(jī)床傳感器數(shù)據(jù)的實(shí)時(shí)處理上,具有較高的效率.

數(shù)控機(jī)床;傳感器;ETL;實(shí)時(shí)

隨著傳感器技術(shù)的發(fā)展,可以越來(lái)越準(zhǔn)確的對(duì)工業(yè)器件的工作數(shù)據(jù)進(jìn)行實(shí)時(shí)采集.這些數(shù)據(jù)中蘊(yùn)含著巨大的價(jià)值,通過(guò)挖掘分析可以更好的改進(jìn)生產(chǎn)流程、提高生產(chǎn)效率、減少產(chǎn)品缺陷、提高產(chǎn)品質(zhì)量、節(jié)約資源成本.如何通過(guò)各種技術(shù)手段,把數(shù)據(jù)轉(zhuǎn)化為信息、知識(shí)是數(shù)據(jù)分析中的首要一步.

數(shù)據(jù)倉(cāng)庫(kù)就是為了解決上述問(wèn)題應(yīng)運(yùn)而生的產(chǎn)物.數(shù)據(jù)倉(cāng)庫(kù)是一個(gè)面向主題的(Subject Oriented)、集成的(Integrate)、相對(duì)穩(wěn)定的(Non-Volatile)、反映歷史變化(Time Variant)的數(shù)據(jù)集合,用于支持管理決策,其目標(biāo)是將分散異構(gòu)的大量數(shù)據(jù)信息從多種數(shù)據(jù)源中分離,轉(zhuǎn)換為統(tǒng)一集成的信息并進(jìn)行存儲(chǔ)和分析.ETL(Extract-Transform-Load)是抽取、轉(zhuǎn)換、加載的英文縮寫,是業(yè)務(wù)集成過(guò)程與數(shù)據(jù)倉(cāng)庫(kù)的核心.根據(jù)國(guó)內(nèi)外眾多實(shí)踐得到的共識(shí),ETL的工作量約占整個(gè)數(shù)據(jù)倉(cāng)庫(kù)工作量的60%～80%,因此是數(shù)據(jù)倉(cāng)庫(kù)中非常重要的且不可或缺的一環(huán).

近年來(lái),數(shù)控機(jī)床生產(chǎn)過(guò)程中傳感器數(shù)據(jù)量越來(lái)越大以及對(duì)實(shí)時(shí)性要求越來(lái)越高,傳統(tǒng)ETL對(duì)數(shù)據(jù)的處理已經(jīng)力不從心.基于此,本文提出了一種改進(jìn)的ETL系統(tǒng).其中主要結(jié)合數(shù)控機(jī)床傳感器數(shù)據(jù)的特點(diǎn)設(shè)計(jì)了基于觸發(fā)器的分表結(jié)構(gòu)的變更數(shù)據(jù)捕獲的解決方案,并結(jié)合該方案提出了一種高效的實(shí)時(shí)抽取方案.

1 基于Hadoop的分布式架構(gòu)的設(shè)計(jì)

對(duì)數(shù)控機(jī)床數(shù)據(jù)進(jìn)行數(shù)據(jù)挖掘、分析,首先要做的就是對(duì)大量的歷史數(shù)據(jù)(冷數(shù)據(jù))進(jìn)行數(shù)據(jù)建模,然后將傳感器實(shí)時(shí)傳遞的數(shù)據(jù)(熱數(shù)據(jù))導(dǎo)入到建好的模型中進(jìn)行數(shù)據(jù)分析.這兩步對(duì)整個(gè)系統(tǒng)的實(shí)時(shí)性要求不同:用于建模的歷史數(shù)據(jù)對(duì)實(shí)時(shí)性要求較低,注重的是結(jié)果;而傳感器傳遞的數(shù)據(jù)對(duì)實(shí)時(shí)性要求很高,可能需要根據(jù)器件的實(shí)時(shí)數(shù)據(jù)來(lái)對(duì)其磨損程度以及壽命進(jìn)行一個(gè)實(shí)時(shí)的預(yù)測(cè).基于以上特點(diǎn),本文設(shè)計(jì)了如圖1所示的一種架構(gòu)方案.

圖1 系統(tǒng)總體框架圖

該架構(gòu)主要分為三層(批處理層、加速層、服務(wù)層)和一個(gè)實(shí)時(shí)消息系統(tǒng).

批處理層.該層主要用來(lái)批量處理歷史數(shù)據(jù),以便后續(xù)對(duì)歷史數(shù)據(jù)進(jìn)行建模.由于數(shù)據(jù)量巨大,且對(duì)實(shí)時(shí)性要求不高.本層采用基于HDFS的分布式存儲(chǔ)方式,基于MapReduce大數(shù)據(jù)計(jì)算模型對(duì)抽取的數(shù)據(jù)進(jìn)行批處理,最后加載到業(yè)務(wù)分析系統(tǒng)中.

該層首先將所有數(shù)據(jù)全部加載到HDFS文件系統(tǒng)中,在Hadoop之上搭建一層Hive數(shù)據(jù)倉(cāng)庫(kù),通過(guò)Hive的HQL語(yǔ)句來(lái)調(diào)用MapReduce,對(duì)數(shù)據(jù)進(jìn)行分布式計(jì)算處理.

加速層.該層是后續(xù)業(yè)務(wù)層模型已經(jīng)建立,主要是對(duì)一些實(shí)時(shí)的數(shù)據(jù)進(jìn)行處理,該層對(duì)數(shù)據(jù)的實(shí)時(shí)性要求比較高.本層采用Spark Stream數(shù)據(jù)流的方式對(duì)數(shù)據(jù)進(jìn)行抽取、轉(zhuǎn)換、加載.

服務(wù)層.該層主要是后續(xù)的業(yè)務(wù)層,ETL將處理過(guò)的數(shù)據(jù)加載到該業(yè)務(wù)層,進(jìn)行后續(xù)的數(shù)據(jù)分析、數(shù)據(jù)挖掘.

實(shí)時(shí)消息系統(tǒng).該系統(tǒng)主要用于數(shù)據(jù)源和批處理層、加速層之間進(jìn)行消息傳遞,會(huì)在后面詳細(xì)介紹.

圖2 批處理層

2 變更數(shù)據(jù)捕獲的解決方案

要加速數(shù)據(jù)抽取處理效率,首先要解決的就是變更數(shù)據(jù)的捕獲,不僅要求能夠準(zhǔn)確快速的獲得變化的數(shù)據(jù),而且不能對(duì)原有的系統(tǒng)造成太大的壓力,影響原有系統(tǒng).下面首先介紹幾種傳統(tǒng)的變更數(shù)據(jù)捕獲的方法:

2.1 基于時(shí)間戳

時(shí)間戳方式是指ETL系統(tǒng)在抽取數(shù)據(jù)時(shí),通過(guò)比較數(shù)據(jù)源表中的時(shí)間戳字段,來(lái)決定捕獲哪些數(shù)據(jù).這種方式需要在數(shù)據(jù)源表中添加一個(gè)時(shí)間戳字段,數(shù)據(jù)發(fā)生改變時(shí),相應(yīng)的時(shí)間戳字段發(fā)生改變.后前有的數(shù)據(jù)庫(kù)支持時(shí)間戳字段的自動(dòng)更新,但是有的數(shù)據(jù)庫(kù)就不支持.對(duì)于不支持的情況就需要在數(shù)據(jù)發(fā)生改變時(shí)進(jìn)行手動(dòng)添加,這就需要更改原始數(shù)據(jù)的采集程序,對(duì)原系統(tǒng)影響較大.

2.2 基于日志分析

絕大部分的關(guān)系型數(shù)據(jù)系統(tǒng)都有一個(gè)記錄數(shù)據(jù)操作的日志文件,ETL系統(tǒng)通過(guò)分析該日志文件,獲取插入、刪除、更新的數(shù)據(jù),從而達(dá)到變更數(shù)據(jù)捕獲的后的.但是這種方法對(duì)于不存在這種日志記錄的數(shù)據(jù)庫(kù)是不可用的.

2.3 基于全表對(duì)比

這種方式是在數(shù)據(jù)抽取過(guò)程中,將原數(shù)據(jù)與目標(biāo)數(shù)據(jù)庫(kù)中的數(shù)據(jù)逐條對(duì)比,將新增、更新、刪除的記錄寫入目標(biāo)數(shù)據(jù)庫(kù)中.這種方法適用于數(shù)據(jù)量小的情況下,數(shù)據(jù)量大的時(shí)候,這對(duì)系統(tǒng)的壓力過(guò)大,并且達(dá)不到實(shí)時(shí)性的要求.

2.4 基于全表刪除插入

顧名思義,這種方法就是在每次ETL數(shù)據(jù)抽取的時(shí)候,都先將目標(biāo)數(shù)據(jù)表清空,然后將源數(shù)據(jù)表內(nèi)所有數(shù)據(jù)加載到ETL系統(tǒng)中.對(duì)于數(shù)據(jù)量不是很大,執(zhí)行代價(jià)小于增量抽取的情況下,我們可以采用這種方式.

2.5 基于觸發(fā)器方式

2.5.1 傳統(tǒng)觸發(fā)器方式

傳統(tǒng)的觸發(fā)器方式是在數(shù)據(jù)源表上建立插入、修改、刪除3個(gè)觸發(fā)器,當(dāng)數(shù)據(jù)源表內(nèi)的數(shù)據(jù)發(fā)生變化時(shí),觸發(fā)器將變化的數(shù)據(jù)寫入一個(gè)臨時(shí)的變化表Temp表中,ETL從該變化表中抽取相應(yīng)的數(shù)據(jù),并從變化表中刪除抽取過(guò)的數(shù)據(jù).

通過(guò)測(cè)試,這種方式對(duì)于數(shù)據(jù)量不大的情況下效率會(huì)比前面幾種方式高.但是,數(shù)據(jù)量一旦過(guò)大,Temp表的壓力會(huì)非常大,而且會(huì)出現(xiàn)數(shù)據(jù)庫(kù)崩潰的現(xiàn)象.

2.5.2 改進(jìn)的觸發(fā)器方式

由于傳統(tǒng)觸發(fā)器方式會(huì)存在Temp表的壓力過(guò)大的情況,因此,我們對(duì)Temp表進(jìn)行了改進(jìn),做了分表處理.根據(jù)數(shù)據(jù)量的大小建立N張Temp分表:Temp1、Temp2、……TempN.為了保證負(fù)載均衡,我們這里沒(méi)有根據(jù)插入、修改、刪除等操作信息來(lái)進(jìn)行分表,而是入一個(gè)隨機(jī)數(shù),通過(guò)Hash得到分表的分表號(hào),進(jìn)而得到需要寫入的Temp表.

下面是相關(guān)的操作代碼:

(1)創(chuàng)建臨時(shí)變化表TempN

3 實(shí)時(shí)抽取的解決方案

變更數(shù)據(jù)捕獲完成后,下一步要做的就是將變更數(shù)據(jù)實(shí)時(shí)的抽取到我們的ETL系統(tǒng)中去.

常用的實(shí)時(shí)抽取方案是對(duì)ETL系統(tǒng)設(shè)定一定的輪詢時(shí)間,每隔特定的時(shí)間就對(duì)源數(shù)據(jù)庫(kù)進(jìn)行一次抽取.這樣做法的好處是系統(tǒng)設(shè)計(jì)簡(jiǎn)單,但是也存在很明顯的缺點(diǎn).對(duì)于數(shù)據(jù)生成速度不定的情況下:如果數(shù)據(jù)生成速度很快,那么本次抽取、處理的壓力就會(huì)很大,很有可能下次輪詢時(shí)本次處理還沒(méi)有結(jié)束;如果數(shù)據(jù)生成速度很慢,那么兩次抽取之間的空窗期就會(huì)很大,造成不必要的性能浪費(fèi).

另外數(shù)控機(jī)床傳感器數(shù)據(jù)分析中對(duì)數(shù)據(jù)的真實(shí)性要求很高,所以我們一般抽取來(lái)自傳感器的真實(shí)數(shù)據(jù),因此原數(shù)據(jù)庫(kù)中很少存在update、delete等操作,而大量存在的是insert.

基于以上兩點(diǎn)分析考慮,本論文基于Redis的Pub/Sub(消息發(fā)布/訂閱)模式,提出了一種直觀且高效的實(shí)時(shí)抽取解決方案.

如圖3所示,首先,我們開(kāi)啟數(shù)據(jù)庫(kù)獨(dú)立的表空間,然后,在消息系統(tǒng)中發(fā)布一個(gè)話題“有新數(shù)據(jù)到達(dá)”,分布式ETL系統(tǒng)去訂閱這個(gè)話題.在業(yè)務(wù)系統(tǒng)中實(shí)時(shí)監(jiān)控增量日志表的大小,一但其大小改變達(dá)到設(shè)定的閾值,則向話題中發(fā)布通知,通過(guò)消息推送,分布式ETL系統(tǒng)接收到該消息,故根據(jù)增量日志表中的記錄在業(yè)務(wù)數(shù)據(jù)庫(kù)中開(kāi)始數(shù)據(jù)抽取.

圖3 實(shí)時(shí)抽取解決方案

4 實(shí)驗(yàn)分析與總結(jié)

4.1 測(cè)試方案

基于以上的設(shè)計(jì)思想,我們對(duì)該ETL做了實(shí)例驗(yàn)證.

數(shù)控機(jī)床某部件軸承傳感器實(shí)時(shí)將軸承數(shù)據(jù)寫入業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫(kù),監(jiān)控組件對(duì)Temp表進(jìn)行實(shí)時(shí)監(jiān)控,并通過(guò)消息系統(tǒng)實(shí)時(shí)通知ETL,ETL實(shí)時(shí)抽取數(shù)據(jù)進(jìn)行清洗、轉(zhuǎn)換等操作,最后將處理完成的數(shù)據(jù)寫入服務(wù)層數(shù)據(jù)庫(kù)中.

4.2 測(cè)試環(huán)境

源數(shù)據(jù)庫(kù):SQL Server2008;目標(biāo)數(shù)據(jù)庫(kù):Hbase1.2.1;

測(cè)試數(shù)據(jù):數(shù)控機(jī)床軸承數(shù)據(jù);網(wǎng)絡(luò):100 Mbps局域網(wǎng);

ETL系統(tǒng)集群:Ubuntu 14.04.

4.3 測(cè)試數(shù)據(jù)結(jié)果

數(shù)據(jù)延遲的影響因素主要有以下幾個(gè)方面:其一,對(duì)監(jiān)控?cái)?shù)據(jù)表大小設(shè)定的閾值,臨時(shí)表到達(dá)閾值之后觸發(fā)ETL系統(tǒng)進(jìn)行抽取,因而不同閾值會(huì)對(duì)數(shù)據(jù)的延遲產(chǎn)生影響.其二,是臨時(shí)表的個(gè)數(shù),不同個(gè)數(shù)的臨時(shí)表對(duì)源數(shù)據(jù)庫(kù)的壓力也不同,數(shù)據(jù)擁堵情況不同,進(jìn)而造成的延遲也不同.其三,ETL流程對(duì)數(shù)據(jù)的處理時(shí)間.

1)閾值

設(shè)定臨時(shí)表個(gè)數(shù)為1,控制新數(shù)據(jù)的插入速度為勻速,需要處理的數(shù)據(jù)條數(shù)為0條(排除干擾),記錄系統(tǒng)吞吐量隨新插入的數(shù)據(jù)條數(shù)的變化.

處理數(shù)據(jù)數(shù)量小于10000條時(shí),系統(tǒng)吞吐量逐漸增大;10000條左右時(shí)系統(tǒng)吞吐量達(dá)到最大;而數(shù)據(jù)量大于10000條之后,系統(tǒng)吞吐量逐漸降低.

數(shù)據(jù)量達(dá)到10000條之后,系統(tǒng)吞吐量下降,分析原因有如下幾個(gè)方面:其一,ETL組件對(duì)數(shù)據(jù)的處理速度有限,數(shù)據(jù)量增大之后,組件間交互的開(kāi)銷增大,導(dǎo)致數(shù)據(jù)延遲增大;其二,實(shí)驗(yàn)中只用到了一張Temp表,數(shù)據(jù)量增大之后,該表的壓力增大,數(shù)據(jù)庫(kù)性能下降,數(shù)據(jù)延遲增大.

2)臨時(shí)表個(gè)數(shù)

設(shè)定源數(shù)據(jù)庫(kù)新插入的數(shù)據(jù)為50000條(即閾值設(shè)定為50000),并且控制新數(shù)據(jù)的插入速度勻速,需要處理的數(shù)據(jù)條數(shù)為0(排除干擾),記錄系統(tǒng)吞吐量隨Temp表的個(gè)數(shù)的變化.

從圖5中可以看出,在Temp表個(gè)數(shù)為3時(shí)系統(tǒng)吞吐量達(dá)到最大,但是仍小于圖4中10000條時(shí)的吞吐量,這時(shí)由于Temp表數(shù)量增多,ETL系統(tǒng)時(shí)數(shù)據(jù)交互增多導(dǎo)致的;另外,Temp表個(gè)數(shù)大于3個(gè)時(shí),隨著表個(gè)數(shù)增多系統(tǒng)吞吐量逐漸減小,且有加速的趨勢(shì).

圖4 系統(tǒng)吞吐量與閾值關(guān)系圖

圖5 系統(tǒng)吞吐量與Temp表個(gè)數(shù)關(guān)系圖

3)待處理數(shù)據(jù)數(shù)量

設(shè)定臨時(shí)表個(gè)數(shù)為1,控制新數(shù)據(jù)的插入速度為勻速,源數(shù)據(jù)庫(kù)新插入的數(shù)據(jù)為10000條,記錄系統(tǒng)吞吐量和從第一條數(shù)據(jù)進(jìn)入到源數(shù)據(jù)庫(kù)到最后一條數(shù)據(jù)插入到目標(biāo)數(shù)據(jù)庫(kù)所用的時(shí)間.

由圖6可以看出,待處理數(shù)據(jù)條數(shù)對(duì)系統(tǒng)吞吐量的影響不大.且由圖7可以看出,當(dāng)閾值設(shè)為1000條,Temp表個(gè)數(shù)設(shè)為1時(shí),延遲時(shí)間不超過(guò)8 s.

圖6 系統(tǒng)吞吐量與待處理數(shù)據(jù)條數(shù)關(guān)系圖

圖7 系統(tǒng)處理時(shí)間與待處理數(shù)據(jù)條數(shù)關(guān)系圖

實(shí)驗(yàn)表明,該ETL系統(tǒng)在數(shù)控機(jī)床傳感器數(shù)據(jù)分析中有較高的效率,可以做到很好的實(shí)時(shí)性.

1 何剛.基于Hadoop平臺(tái)的分布式ETL研究與實(shí)現(xiàn)[碩士學(xué)位論文].上海:東華大學(xué),2014.

2 宋杰,郝文寧,陳剛,等.基于MapReduce的分布式ETL體系結(jié)構(gòu)研究.計(jì)算機(jī)科學(xué),2013,40(6):152–154.

3 張亮.分布式數(shù)據(jù)倉(cāng)庫(kù)中ETL技術(shù)的研究[碩士學(xué)位論文].沈陽(yáng):沈陽(yáng)航空工業(yè)學(xué)院,2009.

4 劉勝.基于增量ETL的分布式數(shù)據(jù)交換平臺(tái)的研究與實(shí)現(xiàn)[碩士學(xué)位論文].北京:國(guó)防科學(xué)技術(shù)大學(xué),2011.

5 林建昌.電力行業(yè)分布式ETL數(shù)據(jù)集成系統(tǒng)研究與實(shí)現(xiàn)[碩士學(xué)位論文].成都:電子科技大學(xué),2015.

6 馬瑞新,許力.基于SOA的實(shí)時(shí)ETL的研究與實(shí)現(xiàn).計(jì)算機(jī)工程與科學(xué),2007,29(8):115–117.

7 段成,王增平,吳克河.一種輕量級(jí)電網(wǎng)實(shí)時(shí)數(shù)據(jù)ETL系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn).電力系統(tǒng)保護(hù)與控制,2010,38(18):174–177,182.[doi:10.7667/j.issn.1674-3415.2010.18.034]

8 Kumar Hota CPP,Ramu Y,Subba Rao DBV.A relative study on traditional ETL and ETL with apache hadoop.NCRTIT2K16.Andhra Pradesh,India.2016.74–78.

9 Awad MMI,Abdullah MS.A framework for interoperable distributed ETL components based on SOA.Proc.of the 2nd International Conference on Software Technology and Engineering.San Juan,PR,USA.2010.V1-67–V1-70.

10 Kakish K,Kraft TA.ETL evolution for real-time data warehousing.Proc.of the Conference on Information Systems Applied Research.New Orleans Louisiana.2012.

11 Liu XF,Thomsen C,Pedersen TB.ETLMR:A highly scalable dimensional ETL framework based on mapReduce.Proc.2011 International Conference on Data Warehousing and Knowledge Discovery.Berlin Heidelberg.2011.196–3111.

12 章水鑫,徐宏炳,于立.增量式ETL工具的研究與實(shí)現(xiàn).現(xiàn)代計(jì)算機(jī),2005,(3):6–10.

13 廖颯.數(shù)據(jù)倉(cāng)庫(kù)增量ETL的設(shè)計(jì)及優(yōu)化.貴陽(yáng)學(xué)院學(xué)報(bào)(自然科學(xué)版),2008,3(3):5–9.

Improvement of ETL System in NC Machine Sensor Data Analysis

LIU Feng1,CAI Ming-Gao1,2,YU Bo1,YU Bi-Hui11(Shenyang Institute of Computer Technology,Chinese Academy of Sciences,Shenyang 110168,China)2(University of Chinese Academy of Sciences,Beijing 100049,China)

ETL(Extract-Transform-Load)is an indispensable component of the NC machine sensor data analysis.In view of the increasing amount of data in recent years,and the rising requirements for real-time data processing,this paper improves the traditional change data capture methods,and designs real-time data capture solutions based on the characteristics of those data.What’s more,it details the entire ETL distributed architecture design.Finally,experiments show that the ETL system on NC machine sensor data real-time processing has higher efficiency.

NC machine;sensor;ETL;real-time

劉峰,蔡明高,于波,于碧輝.數(shù)控機(jī)床傳感器數(shù)據(jù)分析中ETL系統(tǒng)改進(jìn).計(jì)算機(jī)系統(tǒng)應(yīng)用,2017,26(9):93–97.http://www.c-sa.org.cn/1003-3254/5968.html

2016-12-27;采用時(shí)間:2017-01-23