起點課堂會員權益
職業體系課特權
線下行業大會特權
個人IP打造特權
30+門專項技能課
1300+專題課程
12場職場軟技能直播
12場求職輔導直播
12場專業技能直播
會員專屬社群
榮耀標識如何從0-1搭建ETL？
編輯導語：通過ETL，我們可以將分散且凌亂的數據整合到一起，進而幫助企業利用已有數據驅動決策。而ETL產品的關鍵點就在于抽取、轉換與加載。具體應該如何設計一款ETL產品？本篇文章里，作者介紹了從0-1搭建ETL產品的策略方法，一起來看一下。
領導提了個小需求，公司做的系統越來越多了，數據也越來越多，想做一個屬于公司內部的ETL工具做數據清洗并歸集。你無從下手，或許本文可以幫助你。
一、ETL是什么？
ETL（ Extract-Load-Transform）是將業務系統的數據抽取到一個中間數據庫里，在里面經過各種規則的轉換之后，裝載到數據倉庫的過程。目的是將分散、凌亂、標準不統一的數據整合到一起，幫助企業將沉睡的數據最大價值利用起來。
一般常見的做法包括ETL或者ELT，一個是先抽取到中間庫轉換好后再裝載到目標數據庫，另一個是將數據抽取并裝載到目的端，利用目的端的數據處理能力完成數據轉換工作。
通常越大量的數據、復雜的轉換邏輯、目的端為較強運算能力的數據庫，越偏向使用ELT，以便運用目的端數據庫的處理能力。
二、怎么設計ETL？
其實這款產品的設計很簡單，根據該產品的名稱，我們就可以把功能模塊分為三部分：抽取、轉換、加載。
1. 功能點一：抽取
設計數據抽取功能，需要解決幾個問題：一是需要確定從哪些源系統進行數據抽?。欢菙祿槿〉姆椒ǎ侵鲃映槿∵€是由源系統推送？是增量抽取還是全量抽??？三是數據抽取的頻次，是按照每日抽取還是按照每月抽取。
1）數據庫連接
首先我們需要把數據從業務系統中抽取出來，從哪些源系統抽取，就要知道這個源業務系統放數據的數據庫是哪一個，想要人家的東西就要知道人家放東西的地址在哪，也就是數據庫連接。
數據庫的類型大致可分為三種：關系型數據庫、非關系型數據庫（NoSQL）、鍵值數據庫，目前企業最常用的關系型數據庫Oracle、MySQL，非關系型數據庫BigTable、MongoDB等，可根據業務情況增添所需要的數據庫種類。
- 數據源名稱：幫助用戶在操作數據源連接時，給所連接的數據源起個名稱，便于后續的選擇使用；
- 數據源描述：便于管理和記憶數據源的信息；
- 數據庫地址：想要連接數據庫的地址；
- 端口號：一臺計算機可以提供多個服務，端口號就類似于這些服務的門牌號。例如：在一臺電腦上，瀏覽網頁服務、連接服務器服務、微信服務、釘釘服務等，每一項都對應一個端口號，只有通過這些端口號，客戶端才能真正的訪問到這些服務。MySQL端口號一般默認是3306；
- 數據庫名稱：同一個數據庫地址可以包括很多數據庫，每個數據庫都有自己的名字，每個數據庫里面包含了很多張數據表；
- 用戶名和密碼：由業務方來分配，他們會把你可操作的數據權限放權給該賬戶。
通過這樣幾項內容的設計，用戶就可以連接到一個確定的數據庫，并使用里面的數據表。
數據庫連接好之后，還需要連接測試一下是否能連通，以防因為網絡或者信息填寫錯誤等原因導致沒有連上，那就是表面看著連好了，實際上是無效的數據庫。
2）數據抽取
連接好源數據庫后，就可以開始抽取工作了。這時需要解決剛提出的第二個問題，如何抽??？
ETL的工作一般都是主動抽取，能推送的話那就太好了，不過源系統推送的可能性不大，因為這增加了源業務方的工作量，本來抽取數據都會多多少少影響業務的運行情況，現在還來增加工作量，這不是給人家添堵嘛。所以不推送也沒關系，能讓主動抽就可以。
是增量抽取還是全量抽取呢？這個是根據業務需求而定，全量抽取比較好操作，每次做數據清洗任務時，把需要的表全部抽取過來就可以了。而增量抽取是只抽取新增部分的數據，要實現增量抽取，就要準確地捕獲到數據庫中數據源表的變化。
數據的變化無非是增、刪、改，只要能監測到數據有這三種形式的變化，并對變化做一些處理，就能實現增量抽取了。增量抽取有四種方式：
① 觸發器方式
在被抽取的數據源表上建立插入、修改、刪除3個觸發器，當源表中的數據發生變化（是新增、修改，還是刪除了），可以指定一個或多個具備唯一性的字段來監測，對應的觸發器就將變化的數據寫入一個增量日志表，抽取時則從增量日志中抽取，同時，增量日志表中抽取過的數據要及時被標記或刪除。
② 時間戳方式
增量抽取時，通過比較指定抽取時間與抽取源表的時間戳字段的值決定抽取哪些數據。這種方式需要在源表中增加一個時間戳字段，源表數據更新或修改時，同時也會修改時間戳字段的值，這樣就知道源表發生了哪些變化，將變化的數據重新抽取即可。
③ 全表比對方式
增量抽取時，ETL任務會逐條比較源表和目標表的記錄，將新增或修改等變化的記錄過濾讀取出來，這種方式就比較考驗硬件環境了。
④ 日志表方式
在數據庫中創建業務日志表，增量抽取時，通過讀日志表數據決定加載哪些數據，日志表的維護需要由業務系統程序編寫代碼完成。
以上四種增量抽取方式沒有一種方式具有絕對優勢，不同的方式在不同的企業中表現大體平衡。通常根據企業的業務需求和硬件環境來選擇。
根據這四種方式描述，可以看出，我們比較好設計到產品中的就是觸發器方式、時間戳方式、全表比對方式。
3）任務調度
如果需求是按日、按周或者按月抽取數據，并執行清洗任務，怎么辦呢？可以通過任務調度功能來實現。
任務調度功能的設計就是來自于cron表達式。cron，是計劃任務，指在約定的時間執行已經計劃好的工作，是用代碼來實現任務調度的用法。
任務調度的運行頻次一般包括分、時、日、周、月，為什么沒有秒調度，這玩意兒要求太高了，比較耗費資源，能滿足到分的定時任務就可以了。調度任務的配置邏輯就是任務在什么時間開始運行，多久運行一次，什么時間結束運行。
2. 功能點二：轉換
數據的清洗轉換，主要任務是過濾掉不符合要求的數據，不符合要求的數據主要有不完整的數據、錯誤的數據、重復的數據三大類。
根據數據清洗的業務需求，在ETL產品中設計各種類型的清洗組件，在組件設置頁面由用戶配置清洗規則，完成數據清洗任務的設計。
例如，根據抽取、轉換、加載的流程，組件可以分為三大類：輸入、轉換、輸出。
- 輸入組件包括數據表輸入、Excel文件輸入、SQL輸入等；
- 轉換組件包括過濾、聚合、合并、排序、數據脫敏、增加計算字段、行轉列等；
- 輸出組件包括數據表輸出、Excel文件輸出等。
3. 功能點三：加載
數據加載的主要任務是將數據從臨時數據表或文件中加載到指定的數據倉庫中。如果是全量方式則采用LOAD方式，如果是增量則根據業務規則，使用SQL語句MERGE進數據庫。
對于一個ETL任務流，一般會在數據抽取時進行增量操作，將增量抽取的數據清洗之后再統一加載到目標表中。
三、如何使用ETL
業務場景：抽取商品銷售訂單數據表，商品數據表，計算出每日的銷售訂單總金額并入庫。
第一步：制作ETL任務流程，抽取商品銷售訂單數據，商品數據表，通過商品ID關聯成一張表。
第二步：計算每日銷售訂單總金額。增加一個訂單總金額字段，并添加計算公式訂單總金額=訂單金額*數量。
第三步：設置調度任務，定時執行ETL任務。選擇運行頻次“天”，設定任務在哪一段時間內執行，也可以指定幾個不在這段時間內的單個時間執行任務。
四、寫在結尾
本文只是教大家如何搭建一個ETL產品的基礎功能，搭建出來的產品要能真正的上線使用起來，除了需要考慮到：產品運作流程要使用的方案，是ETL還是ELT？如果要與BI產品共同使用，如何與其交互？
還需要認真思考一下，我們到底需要什么樣的ETL？只是想做數據采集，支撐數據倉庫的建設，還是作為一個數據交換平臺，賦予其更多的應用場景。
這些大家在設計產品的時候都要考慮清楚，和相關開發團隊以及領導多討論溝通，有疑問的地方歡迎留言。
本文由 @金豌豆?原創發布于人人都是產品經理，未經許可，禁止轉載
題圖來自Pexels，基于 CC0 協議
您好，不知道您是否有輸出課程的想法呢？如果有歡迎添加微信溝通（微信號：www-627715182）
專業
????