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

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

一、ETL是什么？

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

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

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

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

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

1. 功能點(diǎn)一：抽取

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

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

首先我們需要把數(shù)據(jù)從業(yè)務(wù)系統(tǒng)中抽取出來，從哪些源系統(tǒng)抽取，就要知道這個(gè)源業(yè)務(wù)系統(tǒng)放數(shù)據(jù)的數(shù)據(jù)庫是哪一個(gè)，想要人家的東西就要知道人家放東西的地址在哪，也就是數(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í)，給所連接的數(shù)據(jù)源起個(gè)名稱，便于后續(xù)的選擇使用；
• 數(shù)據(jù)源描述：便于管理和記憶數(shù)據(jù)源的信息；
• 數(shù)據(jù)庫地址：想要連接數(shù)據(jù)庫的地址；
• 端口號(hào)：一臺(tái)計(jì)算機(jī)可以提供多個(gè)服務(wù)，端口號(hào)就類似于這些服務(wù)的門牌號(hào)。例如：在一臺(tái)電腦上，瀏覽網(wǎng)頁服務(wù)、連接服務(wù)器服務(wù)、微信服務(wù)、釘釘服務(wù)等，每一項(xiàng)都對(duì)應(yīng)一個(gè)端口號(hào)，只有通過這些端口號(hào)，客戶端才能真正的訪問到這些服務(wù)。MySQL端口號(hào)一般默認(rèn)是3306；
• 數(shù)據(jù)庫名稱：同一個(gè)數(shù)據(jù)庫地址可以包括很多數(shù)據(jù)庫，每個(gè)數(shù)據(jù)庫都有自己的名字，每個(gè)數(shù)據(jù)庫里面包含了很多張數(shù)據(jù)表；
• 用戶名和密碼：由業(yè)務(wù)方來分配，他們會(huì)把你可操作的數(shù)據(jù)權(quán)限放權(quán)給該賬戶。

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

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

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

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

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

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

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

① 觸發(fā)器方式

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

② 時(shí)間戳方式

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

③ 全表比對(duì)方式

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

④ 日志表方式

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

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

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

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

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

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

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

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

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

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

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

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

3. 功能點(diǎn)三：加載

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

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

三、如何使用ETL

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

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

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

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

四、寫在結(jié)尾

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

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

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

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

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