数据仓库之 ETL漫谈
http://superlxw1234.iteye.com/blog/1666960
ETL，Extraction-Transformation-Loading的縮寫，中文名稱為數據抽取、轉換和加載。
?
大多數據倉庫的數據架構可以概括為：
數據源-->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. 數據加載：
?????????? 個人認為，每insert數據到一張表，都可以稱為數據加載，至于是delete+insert、truncate+insert、
?????????? 還是merge，這個是由業務規則決定的，這些操作也都是嵌入到數據抽取、轉換的程序中的。
?
四、ETL工具：
????? ? 在傳統行業的數據倉庫項目中，大多會采用一些現成的ETL工具，如Informatica、Datastage、微軟SSIS等。
??????? 這三種工具我都使用過，優點有：圖形界面，開發簡單，數據流向清晰；缺點：局限性，不夠靈活，
??????? 處理大數據量比較吃力，查錯困難，昂貴的費用；
??????? 選擇ETL工具需要充分考慮源系統和數據倉庫的環境，當然還有成本，如果源數據系統和數據倉庫都采用
??????? ORACLE，那么我覺得所有的ETL，都可以用存儲過程來完成了。。
??????? 在大一點的互聯網公司，由于數據量大，需求特殊，ETL工具大多為自己開發，
??????? 或者在開源工具上再進行一些二次開發，在實際工作中，
??????? 一個存儲過程，一個shell/perl腳本，一個java程序等等，都可以作為ETL工具。
?
五、ETL過程中的元數據：
?????? 試想一下，你作為一個新人接手別人的工作，沒有文檔，程序沒有注釋，
??????????? 數據庫中的表和字段也沒有任何comment，你是不是會罵娘了？
?????? 業務系統發生改變，刪除了一個字段，需要數據倉庫也做出相應調整的時候，
?????????? 你如何知道改這個字段會對哪些程序產生影響？
?????? 。。。。
?????? 源系統表的字段及其含義，源系統數據庫的IP、接口人，數據倉庫表的字段及其含義，
?????? 源表和目標表的對應關系，一個任務對應的源表和目標表，任務之間的依賴關系，
?????? 任務每次執行情況等等等等，這些元數據如果都能嚴格的管控起來，上面的問題肯定不會是問題了。。。
?
總結
以上是生活随笔為你收集整理的数据仓库之 ETL漫谈的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Cascading——针对Hadoop
- 下一篇： 关联挖掘算法Apriori和FP-Tre