ETL是英文Extract-Transform-Load 的縮寫(xiě)，用來(lái)描述將數(shù)據(jù)從來(lái)源端經(jīng)過(guò)抽取（extract）、轉(zhuǎn)換（transform）、加載（load）至目的端的過(guò)程。常見(jiàn)于數(shù)據(jù)倉(cāng)庫(kù)開(kāi)發(fā)中將數(shù)據(jù)由業(yè)務(wù)系統(tǒng)歸集到數(shù)據(jù)倉(cāng)庫(kù)（DW）或者數(shù)據(jù)集市的過(guò)程。在ETL三個(gè)部分中，花費(fèi)時(shí)間最長(zhǎng)的是“T”(Transform，清洗、轉(zhuǎn)換)的部分，一般情況下這部分工作量是整個(gè)ETL的2/3。

1抽取作業(yè)
將從源數(shù)據(jù)庫(kù)（通常為業(yè)務(wù)系統(tǒng)）獲得數(shù)據(jù)的過(guò)程。

在做這一步的之前，往往要預(yù)先分析自己需要什么數(shù)據(jù)，劃分好范圍，確認(rèn)具體的技術(shù)部門(mén)和業(yè)務(wù)部門(mén)。

1.1手工開(kāi)發(fā)抽取作業(yè)時(shí)候的常用方法：
1.1.1當(dāng)數(shù)據(jù)源和DW為同一類(lèi)數(shù)據(jù)庫(kù)時(shí)
一般情況下，DBMS(SQLServer、Oracle)都會(huì)提供數(shù)據(jù)庫(kù)鏈接功能，可以在數(shù)據(jù)源（業(yè)務(wù)系統(tǒng)）和DW內(nèi)建立數(shù)據(jù)庫(kù)鏈接（如DB2的聯(lián)邦數(shù)據(jù)庫(kù)NICKNAME），然后在DW內(nèi)直接SELECT訪(fǎng)問(wèn)。

優(yōu)點(diǎn)是實(shí)現(xiàn)使用簡(jiǎn)單，邏輯簡(jiǎn)單；缺點(diǎn)是容易被濫用對(duì)源數(shù)據(jù)庫(kù)造成較大的負(fù)載壓力。

1.1.2當(dāng)數(shù)據(jù)源和ODS為不同類(lèi)型數(shù)據(jù)庫(kù)時(shí)
o???將源數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)出為文本文件，利用FTP協(xié)議進(jìn)行傳輸導(dǎo)入ODS區(qū)域。

優(yōu)點(diǎn)是實(shí)現(xiàn)簡(jiǎn)單，對(duì)源系統(tǒng)壓力較小。缺點(diǎn)是傳輸步驟增加了，處理需要的時(shí)間增加。

o???將部分?jǐn)?shù)據(jù)庫(kù)間能通過(guò)ODBC建立源數(shù)據(jù)庫(kù)和目標(biāo)數(shù)據(jù)庫(kù)鏈接，此時(shí)也能直接使用SELECT獲取數(shù)據(jù)。

優(yōu)點(diǎn)是實(shí)現(xiàn)使用簡(jiǎn)單，邏輯簡(jiǎn)單；缺點(diǎn)是容易被濫用對(duì)源數(shù)據(jù)庫(kù)造成較大的負(fù)載壓力，且建立時(shí)較為復(fù)雜。

1.2更新數(shù)據(jù)的時(shí)間和數(shù)量的問(wèn)題
1.2.1實(shí)時(shí)抽取數(shù)據(jù)
這類(lèi)抽取方式在數(shù)據(jù)倉(cāng)庫(kù)中很少見(jiàn)到，因?yàn)橐话銇?lái)說(shuō)數(shù)據(jù)倉(cāng)庫(kù)對(duì)數(shù)據(jù)的實(shí)時(shí)性要求并不高。實(shí)時(shí)抽取常見(jiàn)于BI中的CRM系統(tǒng)，比如在實(shí)時(shí)營(yíng)銷(xiāo)中，客戶(hù)一旦進(jìn)行了某類(lèi)操作就實(shí)時(shí)觸發(fā)對(duì)應(yīng)的營(yíng)銷(xiāo)行為。

o???時(shí)間戳方式

要求源表中存在一個(gè)或多個(gè)字段(時(shí)間戳),其值隨著新紀(jì)錄的增加而不斷增加，執(zhí)行數(shù)據(jù)抽取時(shí)，程序定時(shí)循環(huán)檢查通過(guò)時(shí)間戳對(duì)數(shù)據(jù)進(jìn)行過(guò)濾，抽取結(jié)束后，程序記錄時(shí)間戳信息。

這種方式的優(yōu)點(diǎn)是對(duì)源系統(tǒng)的侵入較小，缺點(diǎn)是抽取程序需要不斷掃描源系統(tǒng)的表，對(duì)其 有一定壓力。

o???觸發(fā)器方式

要求用戶(hù)在源數(shù)據(jù)庫(kù)中有創(chuàng)建觸發(fā)器和臨時(shí)表的權(quán)限，觸發(fā)器捕獲新增的數(shù)據(jù)到臨時(shí)表中，執(zhí)行抽取時(shí)，程序自動(dòng)從臨時(shí)表中讀取數(shù)據(jù)。

這種方式的優(yōu)點(diǎn)是實(shí)時(shí)性極高，缺點(diǎn)是對(duì)源系統(tǒng)的侵入性較大，同時(shí)會(huì)對(duì)源數(shù)據(jù)庫(kù)造成很大的壓力（行級(jí)觸發(fā)器），很可能影響源系統(tǒng)的正常業(yè)務(wù)。

o???程序接口方式

改造源系統(tǒng)，在修改數(shù)據(jù)時(shí)通過(guò)程序接口同步發(fā)送數(shù)據(jù)至目標(biāo)庫(kù)，發(fā)送數(shù)據(jù)的動(dòng)作可以跟業(yè)務(wù)修改數(shù)據(jù)動(dòng)作脫耦，獨(dú)立發(fā)送。

這種方法的優(yōu)點(diǎn)是對(duì)源系統(tǒng)的造成壓力較小，實(shí)時(shí)性較強(qiáng)；缺點(diǎn)是需要對(duì)源系統(tǒng)的侵入性較強(qiáng)，需要源系統(tǒng)做較大的改造。

1.2.2批量抽取數(shù)據(jù)
為了保證數(shù)據(jù)抽取時(shí)數(shù)據(jù)的準(zhǔn)確性、完整性和唯一性，同時(shí)降低抽取作業(yè)對(duì)源數(shù)據(jù)庫(kù)造成的壓力，抽取作業(yè)的加載必須避開(kāi)源數(shù)據(jù)的生成時(shí)間。這種方法一般用于實(shí)時(shí)性要求不高的數(shù)據(jù)。比如T+1或者每月1日進(jìn)行抽取。

1.2.2.1常用實(shí)現(xiàn)
o???日志檢查

需要源數(shù)據(jù)庫(kù)生成數(shù)據(jù)完畢之后，在外部生成日志。抽取程序定時(shí)檢查源系統(tǒng)的執(zhí)行日志，發(fā)現(xiàn)完成標(biāo)志后發(fā)起抽取作業(yè)。

這種方式優(yōu)點(diǎn)是可靠性高，對(duì)源數(shù)據(jù)庫(kù)造成的壓力較小。缺點(diǎn)是需要源數(shù)據(jù)庫(kù)配合生成可供檢查的外部日志。

o???約定時(shí)間抽取

可以直接約定一個(gè)加載完畢同時(shí)對(duì)源數(shù)據(jù)庫(kù)壓力較小的時(shí)間（如每日凌晨2點(diǎn)），抽取程序建立定時(shí)任務(wù)，時(shí)間一到自動(dòng)發(fā)起抽取作業(yè)。

這種方式優(yōu)點(diǎn)是對(duì)源數(shù)據(jù)庫(kù)的侵入性和造成的壓力較小；缺點(diǎn)是可靠性不高，可能會(huì)發(fā)生數(shù)據(jù)未生成完畢也直接進(jìn)行抽取的情況。

1.2.2.2根據(jù)下載時(shí)候?qū)?shù)據(jù)的篩選方式可以分為
o???全量下載

用于：

·源數(shù)據(jù)量較小，如維表。

·數(shù)據(jù)變化較大，比如90%的數(shù)據(jù)都產(chǎn)生了變化的表。

·變化的數(shù)據(jù)不能預(yù)期，無(wú)法標(biāo)示，如賬戶(hù)表。

的時(shí)候。

優(yōu)點(diǎn)在于下載較為簡(jiǎn)單且能容納任何情況的數(shù)據(jù)變化；缺點(diǎn)是如果數(shù)據(jù)量較大，需要抽取相當(dāng)長(zhǎng)的時(shí)間，同時(shí)會(huì)占用大量的IO和網(wǎng)絡(luò)資源。

o???增量下載

常用于數(shù)據(jù)只增不減的表，如交易明細(xì)表等。

·時(shí)間戳

源系統(tǒng)在修改或添加數(shù)據(jù)時(shí)更新對(duì)應(yīng)的時(shí)間戳字段（如交易表的日期字段），抽取程序根據(jù)時(shí)間戳選擇需要更新的數(shù)據(jù)進(jìn)行抽取。

·觸發(fā)器方式

要求用戶(hù)在源數(shù)據(jù)庫(kù)中有創(chuàng)建觸發(fā)器和臨時(shí)表的權(quán)限，觸發(fā)器捕獲新增的數(shù)據(jù)到臨時(shí)表中，到執(zhí)行抽取的時(shí)間時(shí)，程序自動(dòng)從臨時(shí)表中讀取數(shù)據(jù)。占用資源較多，不建議使用。

優(yōu)點(diǎn)是下載的數(shù)據(jù)較小，速度較快，占用資源少；缺點(diǎn)是使用限制較大，有時(shí)候需要源系統(tǒng)進(jìn)行改造支持。

2轉(zhuǎn)換作業(yè)
這一步包含了數(shù)據(jù)的清洗和轉(zhuǎn)換。

2.1數(shù)據(jù)清洗
任務(wù)是過(guò)濾不符合條件或者錯(cuò)誤的數(shù)據(jù)。

這一步常常出現(xiàn)在剛剛開(kāi)始建立數(shù)據(jù)倉(cāng)庫(kù)或者源業(yè)務(wù)系統(tǒng)仍未成熟的時(shí)候，此時(shí)發(fā)現(xiàn)錯(cuò)誤數(shù)據(jù)需要聯(lián)系源業(yè)務(wù)系統(tǒng)進(jìn)行更正，部分可預(yù)期的空值或者測(cè)試用數(shù)據(jù)可以過(guò)濾掉。

2.2數(shù)據(jù)轉(zhuǎn)換
這一步是整個(gè)ETL流程中最為占用時(shí)間和資源的一步。

數(shù)據(jù)轉(zhuǎn)換包含了簡(jiǎn)單的數(shù)據(jù)不一致轉(zhuǎn)換，數(shù)據(jù)粒度轉(zhuǎn)換和耗時(shí)的數(shù)據(jù)關(guān)聯(lián)整合或拆分動(dòng)作。這里可能存在各種各樣千奇百怪的需求。對(duì)于核心數(shù)據(jù)倉(cāng)庫(kù)來(lái)說(shuō)，里面往往是對(duì)數(shù)據(jù)進(jìn)行按照主題劃分合并的動(dòng)作。同時(shí)，也會(huì)添加一些為了提升執(zhí)行效率而進(jìn)行反范式化添加的冗余字段。

根據(jù)實(shí)現(xiàn)方式的不同，可以區(qū)分為使用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程轉(zhuǎn)換和使用高級(jí)語(yǔ)言轉(zhuǎn)換

o???使用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程轉(zhuǎn)換

使用SQL開(kāi)發(fā)存儲(chǔ)過(guò)程完成轉(zhuǎn)換作業(yè)是很多銀行常用的方法。

它的優(yōu)點(diǎn)是開(kāi)發(fā)簡(jiǎn)單、能支持絕大部分轉(zhuǎn)換場(chǎng)景；缺點(diǎn)在于占用資源多且受制于單一數(shù)據(jù)庫(kù)性能，無(wú)法做到橫向擴(kuò)展。

因此，除了業(yè)務(wù)的理解能力外，對(duì)SQL海量數(shù)據(jù)處理的優(yōu)化能力在此也非常重要。比如:

·????????利用數(shù)據(jù)庫(kù)的分區(qū)性，選擇良好的分區(qū)鍵。

·????????建表時(shí)合理選擇主鍵和索引，關(guān)聯(lián)時(shí)候必須使用主鍵或索引進(jìn)行關(guān)聯(lián)。

·????????關(guān)注數(shù)據(jù)庫(kù)對(duì)SQL的流程優(yōu)化邏輯，盡量選擇拆分復(fù)雜SQL，引導(dǎo)數(shù)據(jù)庫(kù)根據(jù)你選擇流程進(jìn)行數(shù)據(jù)處理

·????????合理反范式化設(shè)計(jì)表，留出適當(dāng)?shù)娜哂嘧侄?#xff0c;減少關(guān)聯(lián)動(dòng)作。

具體的優(yōu)化根據(jù)不同的數(shù)據(jù)庫(kù)有著不同的處理方式，根據(jù)所選用的數(shù)據(jù)庫(kù)不同而定。

o???使用高級(jí)語(yǔ)言轉(zhuǎn)換

使用高級(jí)語(yǔ)言包含了常用的開(kāi)發(fā)C/C++/JAVA等程序?qū)Τ槿〉臄?shù)據(jù)進(jìn)行預(yù)處理。

自行使用高級(jí)語(yǔ)言開(kāi)發(fā)的優(yōu)點(diǎn)是運(yùn)行效率較高，可以通過(guò)橫向擴(kuò)展服務(wù)器數(shù)量來(lái)提高系統(tǒng)的轉(zhuǎn)換作業(yè)處理能力；缺點(diǎn)是開(kāi)發(fā)較為復(fù)雜，同時(shí)雖然能進(jìn)行較為復(fù)雜的邏輯的開(kāi)發(fā)，但是對(duì)于大數(shù)據(jù)量的關(guān)聯(lián)的支持能力較弱，特別是有復(fù)數(shù)的服務(wù)器并行處理的時(shí)候。

3加載作業(yè)
轉(zhuǎn)換作業(yè)生成的數(shù)據(jù)有可能直接插入目標(biāo)數(shù)據(jù)庫(kù)，一般來(lái)說(shuō)，這種情況常見(jiàn)于使用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程進(jìn)行轉(zhuǎn)換作業(yè)的方案。此時(shí)，ETL作業(yè)位于目標(biāo)數(shù)據(jù)庫(kù)上，加載作業(yè)只需要使用INSERT或者LOAD的方式導(dǎo)入目標(biāo)表即可。此時(shí)轉(zhuǎn)換作業(yè)和加載作業(yè)往往是在同一加工中完成的。

當(dāng)使用高級(jí)語(yǔ)言開(kāi)發(fā)時(shí)，ETL作業(yè)有著專(zhuān)門(mén)的ETL服務(wù)器，此時(shí)，轉(zhuǎn)換作業(yè)生成的往往是文本文件，在轉(zhuǎn)換作業(yè)完成后需要使用目標(biāo)庫(kù)特有的工具導(dǎo)入或者通過(guò)INSERT入目標(biāo)庫(kù)。

同時(shí)，根據(jù)抽取作業(yè)的數(shù)據(jù)抽取方式的不同（全量、增量），對(duì)目標(biāo)表進(jìn)行替換或者插入動(dòng)作。

4流程控制
抽取加載和轉(zhuǎn)換作業(yè)需要一個(gè)集中的調(diào)度平臺(tái)控制他們的運(yùn)行，決定執(zhí)行順序，進(jìn)行錯(cuò)誤捕捉和處理。

較為原始的ETL系統(tǒng)就是使用CRON做定時(shí)控制，定時(shí)調(diào)起相應(yīng)的程序或者存儲(chǔ)過(guò)程。但是這種方式過(guò)于原始，只能進(jìn)行簡(jiǎn)單的調(diào)起動(dòng)作，無(wú)法實(shí)現(xiàn)流程依賴(lài)行為，同時(shí)按步執(zhí)行的流程控制能力也弱，錯(cuò)誤處理能力幾乎沒(méi)有。只適合于極其簡(jiǎn)單的情況。

對(duì)于自行開(kāi)發(fā)的較為完善的ETL系統(tǒng)，往往需要具有以下幾個(gè)能力：

·????????流程步驟控制能力

調(diào)度平臺(tái)必須能夠控制整個(gè)ETL流程（抽取加載和轉(zhuǎn)換作業(yè)），進(jìn)行集中化管理，不能有流程游離于系統(tǒng)外部。

·????????系統(tǒng)的劃分和前后流程的依賴(lài)

由于整個(gè)ETL系統(tǒng)里面可能跨越數(shù)十個(gè)業(yè)務(wù)系統(tǒng)，開(kāi)發(fā)人員有數(shù)十撥人，必須支持按照業(yè)務(wù)系統(tǒng)對(duì)ETL流程進(jìn)行劃分管理的能力。

同時(shí)必須具有根據(jù)流程依賴(lài)進(jìn)行調(diào)度的能力，使得適當(dāng)?shù)牧鞒棠茉谶m當(dāng)?shù)臅r(shí)間調(diào)起。

·????????合理的調(diào)度算法

同一時(shí)間調(diào)起過(guò)多流程可能造成對(duì)源數(shù)據(jù)庫(kù)和ETL服務(wù)器還有目標(biāo)數(shù)據(jù)庫(kù)形成較大負(fù)載壓力，故必須有較為合理的調(diào)度算法。

·????????日志和警告系統(tǒng)

必須對(duì)每一步的流程記錄日志，起始時(shí)間，完成時(shí)間，錯(cuò)誤原因等，方便ETL流程開(kāi)發(fā)人員檢查錯(cuò)誤。對(duì)于發(fā)生錯(cuò)誤的流程，能及時(shí)通知錯(cuò)誤人員進(jìn)行錯(cuò)誤檢查和修復(fù)。

·????????較高可靠性

5常用商業(yè)ETL工具
常用的ETL工具有Ascential公司的Datastage、Informatica公司的Powercenter、?NCR?Teradata公司的ETL?Automation等。

·????????Datastage

是使用高級(jí)語(yǔ)言進(jìn)行開(kāi)發(fā)ETL服務(wù)器的代表。使用JAVA進(jìn)行開(kāi)發(fā)E/T/L的整個(gè)流程，同時(shí)支持平行添加服務(wù)器提升處理效率的方法。

·????????Automation

基于Teradata的TD數(shù)據(jù)庫(kù)的ETL調(diào)度框架。其ETL流程是使用DSQL的存儲(chǔ)過(guò)程進(jìn)行開(kāi)發(fā)，利用TD數(shù)據(jù)庫(kù)的海量數(shù)據(jù)處理能力，也具有一定的平行擴(kuò)展能力。
————————————————

名詞解釋：
DW(Data Warehouse) : 數(shù)據(jù)倉(cāng)庫(kù)https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93
ODS（Operational Data Store）：https://zh.wikipedia.org/wiki/ODS
CRM（customer relationship management）：客戶(hù)關(guān)系管理https://zh.wikipedia.org/wiki/%E5%AE%A2%E6%88%B7%E5%85%B3%E7%B3%BB%E7%AE%A1%E7%90%86
BI(Business Intelligence):商業(yè)智能
https://baike.baidu.com/item/BI/4579902

參見(jiàn)：
ETL流程概述及常用實(shí)現(xiàn)方法：https://blog.csdn.net/btkuangxp/article/details/48224187
ETL介紹與ETL工具比較：
https://blog.csdn.net/wl044090432/article/details/60329843

?

總結(jié)

以上是生活随笔為你收集整理的ETL流程概述及常用实现方法的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。