編輯導(dǎo)語：通過ETL，我們可以將分散且凌亂的數(shù)據(jù)整合到一起，進而幫助企業(yè)利用已有數(shù)據(jù)驅(qū)動決策。而ETL產(chǎn)品的關(guān)鍵點就在于抽取、轉(zhuǎn)換與加載。具體應(yīng)該如何設(shè)計一款ETL產(chǎn)品？本篇文章里，作者介紹了從0-1搭建ETL產(chǎn)品的策略方法，一起來看一下。

領(lǐng)導(dǎo)提了個小需求，公司做的系統(tǒng)越來越多了，數(shù)據(jù)也越來越多，想做一個屬于公司內(nèi)部的ETL工具做數(shù)據(jù)清洗并歸集。你無從下手，或許本文可以幫助你。

一、ETL是什么？

ETL（ Extract-Load-Transform）是將業(yè)務(wù)系統(tǒng)的數(shù)據(jù)抽取到一個中間數(shù)據(jù)庫里，在里面經(jīng)過各種規(guī)則的轉(zhuǎn)換之后，裝載到數(shù)據(jù)倉庫的過程。目的是將分散、凌亂、標(biāo)準(zhǔn)不統(tǒng)一的數(shù)據(jù)整合到一起，幫助企業(yè)將沉睡的數(shù)據(jù)最大價值利用起來。

一般常見的做法包括ETL或者ELT，一個是先抽取到中間庫轉(zhuǎn)換好后再裝載到目標(biāo)數(shù)據(jù)庫，另一個是將數(shù)據(jù)抽取并裝載到目的端，利用目的端的數(shù)據(jù)處理能力完成數(shù)據(jù)轉(zhuǎn)換工作。

通常越大量的數(shù)據(jù)、復(fù)雜的轉(zhuǎn)換邏輯、目的端為較強運算能力的數(shù)據(jù)庫，越偏向使用ELT，以便運用目的端數(shù)據(jù)庫的處理能力。

二、怎么設(shè)計ETL？

其實這款產(chǎn)品的設(shè)計很簡單，根據(jù)該產(chǎn)品的名稱，我們就可以把功能模塊分為三部分：抽取、轉(zhuǎn)換、加載。

1. 功能點一：抽取

設(shè)計數(shù)據(jù)抽取功能，需要解決幾個問題：一是需要確定從哪些源系統(tǒng)進行數(shù)據(jù)抽取；二是數(shù)據(jù)抽取的方法，是主動抽取還是由源系統(tǒng)推送？是增量抽取還是全量抽?。咳菙?shù)據(jù)抽取的頻次，是按照每日抽取還是按照每月抽取。

1）數(shù)據(jù)庫連接

首先我們需要把數(shù)據(jù)從業(yè)務(wù)系統(tǒng)中抽取出來，從哪些源系統(tǒng)抽取，就要知道這個源業(yè)務(wù)系統(tǒng)放數(shù)據(jù)的數(shù)據(jù)庫是哪一個，想要人家的東西就要知道人家放東西的地址在哪，也就是數(shù)據(jù)庫連接。

數(shù)據(jù)庫的類型大致可分為三種：關(guān)系型數(shù)據(jù)庫、非關(guān)系型數(shù)據(jù)庫（NoSQL）、鍵值數(shù)據(jù)庫，目前企業(yè)最常用的關(guān)系型數(shù)據(jù)庫Oracle、MySQL，非關(guān)系型數(shù)據(jù)庫BIgTable、MongoDB等，可根據(jù)業(yè)務(wù)情況增添所需要的數(shù)據(jù)庫種類。

• 數(shù)據(jù)源名稱：幫助用戶在操作數(shù)據(jù)源連接時，給所連接的數(shù)據(jù)源起個名稱，便于后續(xù)的選擇使用；
• 數(shù)據(jù)源描述：便于管理和記憶數(shù)據(jù)源的信息；
• 數(shù)據(jù)庫地址：想要連接數(shù)據(jù)庫的地址；
• 端口號：一臺計算機可以提供多個服務(wù)，端口號就類似于這些服務(wù)的門牌號。例如：在一臺電腦上，瀏覽網(wǎng)頁服務(wù)、連接服務(wù)器服務(wù)、微信服務(wù)、釘釘服務(wù)等，每一項都對應(yīng)一個端口號，只有通過這些端口號，客戶端才能真正的訪問到這些服務(wù)。MySQL端口號一般默認(rèn)是3306；
• 數(shù)據(jù)庫名稱：同一個數(shù)據(jù)庫地址可以包括很多數(shù)據(jù)庫，每個數(shù)據(jù)庫都有自己的名字，每個數(shù)據(jù)庫里面包含了很多張數(shù)據(jù)表；
• 用戶名和密碼：由業(yè)務(wù)方來分配，他們會把你可操作的數(shù)據(jù)權(quán)限放權(quán)給該賬戶。

通過這樣幾項內(nèi)容的設(shè)計，用戶就可以連接到一個確定的數(shù)據(jù)庫，并使用里面的數(shù)據(jù)表。

數(shù)據(jù)庫連接好之后，還需要連接測試一下是否能連通，以防因為網(wǎng)絡(luò)或者信息填寫錯誤等原因?qū)е聸]有連上，那就是表面看著連好了，實際上是無效的數(shù)據(jù)庫。

2）數(shù)據(jù)抽取

連接好源數(shù)據(jù)庫后，就可以開始抽取工作了。這時需要解決剛提出的第二個問題，如何抽取？

ETL的工作一般都是主動抽取，能推送的話那就太好了，不過源系統(tǒng)推送的可能性不大，因為這增加了源業(yè)務(wù)方的工作量，本來抽取數(shù)據(jù)都會多多少少影響業(yè)務(wù)的運行情況，現(xiàn)在還來增加工作量，這不是給人家添堵嘛。所以不推送也沒關(guān)系，能讓主動抽就可以。

是增量抽取還是全量抽取呢？這個是根據(jù)業(yè)務(wù)需求而定，全量抽取比較好操作，每次做數(shù)據(jù)清洗任務(wù)時，把需要的表全部抽取過來就可以了。而增量抽取是只抽取新增部分的數(shù)據(jù)，要實現(xiàn)增量抽取，就要準(zhǔn)確地捕獲到數(shù)據(jù)庫中數(shù)據(jù)源表的變化。

數(shù)據(jù)的變化無非是增、刪、改，只要能監(jiān)測到數(shù)據(jù)有這三種形式的變化，并對變化做一些處理，就能實現(xiàn)增量抽取了。增量抽取有四種方式：

① 觸發(fā)器方式

在被抽取的數(shù)據(jù)源表上建立插入、修改、刪除3個觸發(fā)器，當(dāng)源表中的數(shù)據(jù)發(fā)生變化（是新增、修改，還是刪除了），可以指定一個或多個具備唯一性的字段來監(jiān)測，對應(yīng)的觸發(fā)器就將變化的數(shù)據(jù)寫入一個增量日志表，抽取時則從增量日志中抽取，同時，增量日志表中抽取過的數(shù)據(jù)要及時被標(biāo)記或刪除。

② 時間戳方式

增量抽取時，通過比較指定抽取時間與抽取源表的時間戳字段的值決定抽取哪些數(shù)據(jù)。這種方式需要在源表中增加一個時間戳字段，源表數(shù)據(jù)更新或修改時，同時也會修改時間戳字段的值，這樣就知道源表發(fā)生了哪些變化，將變化的數(shù)據(jù)重新抽取即可。

③ 全表比對方式

增量抽取時，ETL任務(wù)會逐條比較源表和目標(biāo)表的記錄，將新增或修改等變化的記錄過濾讀取出來，這種方式就比較考驗硬件環(huán)境了。

④ 日志表方式

在數(shù)據(jù)庫中創(chuàng)建業(yè)務(wù)日志表，增量抽取時，通過讀日志表數(shù)據(jù)決定加載哪些數(shù)據(jù)，日志表的維護需要由業(yè)務(wù)系統(tǒng)程序編寫代碼完成。

以上四種增量抽取方式?jīng)]有一種方式具有絕對優(yōu)勢，不同的方式在不同的企業(yè)中表現(xiàn)大體平衡。通常根據(jù)企業(yè)的業(yè)務(wù)需求和硬件環(huán)境來選擇。

根據(jù)這四種方式描述，可以看出，我們比較好設(shè)計到產(chǎn)品中的就是觸發(fā)器方式、時間戳方式、全表比對方式。

3）任務(wù)調(diào)度

如果需求是按日、按周或者按月抽取數(shù)據(jù)，并執(zhí)行清洗任務(wù)，怎么辦呢？可以通過任務(wù)調(diào)度功能來實現(xiàn)。

任務(wù)調(diào)度功能的設(shè)計就是來自于cron表達式。cron，是計劃任務(wù)，指在約定的時間執(zhí)行已經(jīng)計劃好的工作，是用代碼來實現(xiàn)任務(wù)調(diào)度的用法。

任務(wù)調(diào)度的運行頻次一般包括分、時、日、周、月，為什么沒有秒調(diào)度，這玩意兒要求太高了，比較耗費資源，能滿足到分的定時任務(wù)就可以了。調(diào)度任務(wù)的配置邏輯就是任務(wù)在什么時間開始運行，多久運行一次，什么時間結(jié)束運行。

2. 功能點二：轉(zhuǎn)換

數(shù)據(jù)的清洗轉(zhuǎn)換，主要任務(wù)是過濾掉不符合要求的數(shù)據(jù)，不符合要求的數(shù)據(jù)主要有不完整的數(shù)據(jù)、錯誤的數(shù)據(jù)、重復(fù)的數(shù)據(jù)三大類。

根據(jù)數(shù)據(jù)清洗的業(yè)務(wù)需求，在ETL產(chǎn)品中設(shè)計各種類型的清洗組件，在組件設(shè)置頁面由用戶配置清洗規(guī)則，完成數(shù)據(jù)清洗任務(wù)的設(shè)計。

例如，根據(jù)抽取、轉(zhuǎn)換、加載的流程，組件可以分為三大類：輸入、轉(zhuǎn)換、輸出。

1. 輸入組件包括數(shù)據(jù)表輸入、Excel文件輸入、SQL輸入等；
2. 轉(zhuǎn)換組件包括過濾、聚合、合并、排序、數(shù)據(jù)脫敏、增加計算字段、行轉(zhuǎn)列等；
3. 輸出組件包括數(shù)據(jù)表輸出、Excel文件輸出等。

3. 功能點三：加載

數(shù)據(jù)加載的主要任務(wù)是將數(shù)據(jù)從臨時數(shù)據(jù)表或文件中加載到指定的數(shù)據(jù)倉庫中。如果是全量方式則采用LOAD方式，如果是增量則根據(jù)業(yè)務(wù)規(guī)則，使用SQL語句MERGE進數(shù)據(jù)庫。

對于一個ETL任務(wù)流，一般會在數(shù)據(jù)抽取時進行增量操作，將增量抽取的數(shù)據(jù)清洗之后再統(tǒng)一加載到目標(biāo)表中。

三、如何使用ETL

業(yè)務(wù)場景：抽取商品銷售訂單數(shù)據(jù)表，商品數(shù)據(jù)表，計算出每日的銷售訂單總金額并入庫。

第一步：制作ETL任務(wù)流程，抽取商品銷售訂單數(shù)據(jù)，商品數(shù)據(jù)表，通過商品ID關(guān)聯(lián)成一張表。

第二步：計算每日銷售訂單總金額。增加一個訂單總金額字段，并添加計算公式訂單總金額=訂單金額*數(shù)量。

第三步：設(shè)置調(diào)度任務(wù)，定時執(zhí)行ETL任務(wù)。選擇運行頻次“天”，設(shè)定任務(wù)在哪一段時間內(nèi)執(zhí)行，也可以指定幾個不在這段時間內(nèi)的單個時間執(zhí)行任務(wù)。

四、寫在結(jié)尾

本文只是教大家如何搭建一個ETL產(chǎn)品的基礎(chǔ)功能，搭建出來的產(chǎn)品要能真正的上線使用起來，除了需要考慮到：產(chǎn)品運作流程要使用的方案，是ETL還是ELT？如果要與BI產(chǎn)品共同使用，如何與其交互？

還需要認(rèn)真思考一下，我們到底需要什么樣的ETL？只是想做數(shù)據(jù)采集，支撐數(shù)據(jù)倉庫的建設(shè)，還是作為一個數(shù)據(jù)交換平臺，賦予其更多的應(yīng)用場景。

這些大家在設(shè)計產(chǎn)品的時候都要考慮清楚，和相關(guān)開發(fā)團隊以及領(lǐng)導(dǎo)多討論溝通，有疑問的地方歡迎留言。

本文由 @金豌豆 原創(chuàng)發(fā)布于人人都是產(chǎn)品經(jīng)理，未經(jīng)許可，禁止轉(zhuǎn)載

題圖來自Pexels，基于 CC0 協(xié)議