大多資料倉庫的資料架構可以概括為:
資料源-->ODS(操作型資料存儲)-->DW-->DM(data mart)
ETL貫穿其各個環節。
一、資料抽取:
可以了解為是把源資料的資料抽取到ODS或者DW中。
1. 源資料類型:
關系型資料庫,如Oracle,Mysql,Sqlserver等;
文本檔案,如使用者浏覽網站産生的日志檔案,業務系統以檔案形式提供的資料等;
其他外部資料,如手工錄入的資料等;
2. 抽取的頻率:
大多是每天抽取一次,也可以根據業務需求每小時甚至每分鐘抽取,當然得考慮源資料庫系統能否承受;
3. 抽取政策:
個人感覺這是資料抽取中最重要的部分,可分為全量抽取和增量抽取。
全量抽取适用于那些資料量比較小,并且不容易判斷其資料發生改變的諸如關系表,次元表,配置表等;
增量抽取,一般是由于資料量大,不可能采用全量抽取,或者為了節省抽取時間而采用的抽取政策;
如何判斷增量,這是增量抽取中最難的部分,一般包括以下幾種情況:
a) 通過時間辨別字段抽取增量;源資料表中有明确的可以辨別當天資料的字段的流水表,
如createtime,updatetime等;
b) 根據上次抽取結束時候記錄的自增長ID來抽取增量;無createtime,但有自增長類型字段的流水表,
如自增長的ID,抽取完之後記錄下最大的ID,
下次抽取可根據上次記錄的ID來抽取;
c) 通過分析資料庫日志擷取增量資料,無時間辨別字段,無自增長ID的關系型資料庫中的表;
d) 通過與前一天資料的Hash比較,比較出發生變化的資料,這種政策比較複雜,在這裡描述一下,
比如一張會員表,它的主鍵是memberID,而會員的狀态是有可能每天都更新的,
我們在第一次抽取之後,生成一張備用表A,包含兩個字段,第一個是memberID,
第二個是除了memberID之外其他所有字段拼接起來,再做個Hash生成的字段,
在下一次抽取的時候,将源表同樣的處理,生成表B,将B和A左關聯,Hash字段不相等的
為發生變化的記錄,另外還有一部分新增的記錄,
根據這兩部分記錄的memberID去源表中抽取對應的記錄;
e) 由源系統主動推送增量資料;例如訂單表,交易表,
有些業務系統在設計的時候,當一個訂單狀态發生變化的時候,是去源表中做update,
而我們在資料倉庫中需要把一個訂單的所有狀态都記錄下來,
這時候就需要在源系統上做文章,資料庫觸發器一般不可取。我能想到的方法是在業務系統上做些變動,
當訂單狀态發生變化時候,記一張流水表,可以是寫進資料庫,也可以是記錄日志檔案。
當然肯定還有其他抽取政策,至于采取哪種政策,需要考慮源資料系統情況,
抽取過來的資料在資料倉庫中的存儲和處理邏輯,抽取的時間視窗等等因素。
二、資料清洗:
顧名思義,就是把不需要的,和不符合規範的資料進行處理。資料清洗最好放在抽取的環節進行,
這樣可以節約後續的計算和存儲成本;
當源資料為資料庫時候,其他抽取資料的SQL中就可以進行很多資料清洗的工作了。
資料清洗主要包括以下幾個方面:
1. 空值處理;根據業務需要,可以将空值替換為特定的值或者直接過濾掉;
2. 驗證資料正确性;主要是把不符合業務含義的資料做一處理,比如,把一個表示數量的字段中的字元串
替換為0,把一個日期字段的非日期字元串過濾掉等等;
3. 規範資料格式;比如,把所有的日期都格式化成YYYY-MM-DD的格式等;
4. 資料轉碼;把一個源資料中用編碼表示的字段,通過關聯編碼表,轉換成代表其真實意義的值等等;
5. 資料标準,統一;比如在源資料中表示男女的方式有很多種,在抽取的時候,直接根據模型中定義的值做轉化,
統一表示男女;
6. 其他業務規則定義的資料清洗。。。
三、資料轉換和加載:
很多人了解的ETL是在經過前兩個部分之後,加載到資料倉庫的資料庫中就完事了。
資料轉換和加載不僅僅是在源資料-->ODS這一步,ODS-->DW, DW-->DM包含更為重要和複雜的ETL過程。
1. 什麼是ODS?
ODS(Operational Data Store)是資料倉庫體系結構中的一個可選部分,
ODS具備資料倉庫的部分特征和OLTP系統的部分特征,
它是“面向主題的、內建的、目前或接近目前的、 不斷變化的”資料。---摘自百度百科
其實大多時候,ODS隻是充當了一個資料臨時存儲,資料緩沖的角色。一般來說,
資料由源資料加載到ODS之後,會保留一段時間,當後面的資料處理邏輯有問題,需要重新計算的時候,
可以直接從ODS這一步擷取,而不用再從源資料再抽取一次,減少對源系統的壓力。
另外,ODS還會直接給DM或者前端報表提供資料,比如一些維表或者不需要經過計算和處理的資料;
還有,ODS會完成一些其他事情,比如,存儲一些明細資料以備不時之需等等;
2. 資料轉換(重新整理):
資料轉換,更多的人把它叫做資料重新整理,就是用ODS中的增量或者全量資料來重新整理DW中的表。
DW中的表基本都是按照事先設計好的模型建立的,如事實表,次元表,彙總表等,
每天都需要把新的資料更新到這些表中。
更新這些表的過程(程式)都是剛開始的時候開發好的,每天隻需要傳一些參數,如日期,來運作這些程式即可。
3. 資料加載: