開源ETL工具kettle系列之增量更新設計技巧

1、開源etl工具kettle系列之增量更新設計技巧作者：it168 gemini5201314etl中增量更新是一個比較依賴與工具和設計方法的過程，kettle中主要提供insert / update 步驟，delete 步驟和database lookup 步驟來支持增量更新，增量更新的設計方法也是根據(jù)應用場景來選取的，雖然本文討論的是kettle的實現(xiàn)方式，但也許對其他工具也有一些幫助。本文不可能涵蓋所有的情況，歡迎大家討論。應用場景增量更新按照數(shù)據(jù)種類的不同大概可以分成：1. 只增加，不更新，2. 只更新，不增加3. 即增加也更新4. 有刪除，有增加

2、，有更新其中1 ，2， 3種大概都是相同的思路，使用的步驟可能略有不同，通用的方法是在原數(shù)據(jù)庫增加一個時間戳，然后在轉(zhuǎn)換之后的對應表保留這個時間戳，然后每次抽取數(shù)據(jù)的時候，先讀取這個目標數(shù)據(jù)庫表的時間戳的最大值，把這個值當作參數(shù)傳給原數(shù)據(jù)庫的相應表，根據(jù)這個時間戳來做限定條件來抽取數(shù)據(jù)，抽取之后同樣要保留這個時間戳，并且原數(shù)據(jù)庫的時間戳一定是指定默認值為sysdate當前時間（以原數(shù)據(jù)庫的時間為標準），抽取之后的目標數(shù)據(jù)庫的時間戳要保留原來的時間戳，而不是抽取時候的時間。   對于第一種情況，可以使用kettle的insert / update 步驟，只是可以勾選dont

3、perform any update選項，這個選項可以告訴kettle你只會執(zhí)行insert 步驟。對于第二種情況可能比較用在數(shù)據(jù)出現(xiàn)錯誤然后原數(shù)據(jù)庫有一些更新，相應的目標數(shù)據(jù)庫也要更新，這時可能不是更新所有的數(shù)據(jù)，而是有一些限定條件的數(shù)據(jù)，你可以使用kettle的update 步驟來只執(zhí)行更新。關(guān)于如何動態(tài)的執(zhí)行限定條件，可以參考前一篇文章。第三種情況是最為常見的一種情況，使用的同樣是 kettle的insert / update 步驟，只是不要勾選dont perform any update 選項。第四種情況有些復雜，后面專門討論。對于第1，2，3種情況，可以參考下面的例子。這個例子假設

4、原數(shù)據(jù)庫表為customers , 含有一個id , firstname , lastname , age 字段，主鍵為id , 然后還加上一個默認值為sysdate的時間戳字段。轉(zhuǎn)換之后的結(jié)果類似：id , firstname , lastname , age , updatedate . 整個設計流程大概如下：                       

5、0;                                                 圖1其中第一個步驟

6、的sql 大概如下模式：select max(updatedate) from target_customer ;你會注意到第二個步驟和第一個步驟的連接是黃色的線，這是因為第二個table input 步驟把前面一個步驟的輸出當作一個參數(shù)來用，所有kettle用黃色的線來表示，第二個table input 的sql 模式大概如下：select field1 , field2 , field3 from customers where updatedate > ? 后面的一個問號就是表示它需要接受一個參數(shù)，你在這個table input 下面需要指定replace variable in

7、script 選項和execute for each row 為選中狀態(tài)，這樣，kettle就會循環(huán)執(zhí)行這個sql , 執(zhí)行的次數(shù)為前面參數(shù)步驟傳入的數(shù)據(jù)集的大小。                                    &#

8、160;                                        圖2關(guān)于第三個步驟執(zhí)行insert / update 步驟需要特別解釋一下，    

9、;                                                  

10、;                               圖3kettle執(zhí)行這個步驟是需要兩個數(shù)據(jù)流對比，其中一個是目標數(shù)據(jù)庫，你在target table 里面指定的，它放在the keys to look up the values(s) 左邊的table field 里面的，另外一個數(shù)

11、據(jù)流就是你在前一個步驟傳進來的，它放在the keys to look up the value(s) 的右邊，kettle首先用你傳進來的key 在數(shù)據(jù)庫中查詢這些記錄，如果沒有找到，它就插入一條記錄，所有的值都跟你原來的值相同，如果根據(jù)這個key找到了這條記錄，kettle會比較這兩條記錄，根據(jù)你指定update field 來比較，如果數(shù)據(jù)完全一樣，kettle就什么都不做，如果記錄不完全一樣，kettle就執(zhí)行一個update 步驟。所以首先你要確保你指定的key字段能夠唯一確定一條記錄，這個時候會有兩種情況：1.維表2.事實表維表大都是通過一個主鍵字段來判斷兩條記錄是否匹配，可能我們

12、的原數(shù)據(jù)庫的主鍵記錄不一定對應目標數(shù)據(jù)庫中相應的表的主鍵，這個時候原數(shù)據(jù)庫的主鍵就變成了業(yè)務主鍵，你需要根據(jù)某種條件判斷這個業(yè)務主鍵是否相等，想象一下如果是多個數(shù)據(jù)源的話，業(yè)務主鍵可能會有重復，這個時候你需要比較的是根據(jù)你自定義生成的新的實際的主鍵，這種主鍵可能是根據(jù)某種類似與sequence 的生成方式生成的，事實表在經(jīng)過轉(zhuǎn)換之后，進目標數(shù)據(jù)庫之前往往都是通過多個外鍵約束來確定唯一一條記錄的，這個時候比較兩條記錄是否相等都是通過所有的維表的外鍵決定的，你在比較了記錄相等或不等之后，還要自己判斷是否需要添加一個新的主鍵給這個新記錄。上面兩種情況都是針對特定的應用的，如果你的轉(zhuǎn)換過程比較簡單，只

13、是一個原數(shù)據(jù)庫對應一個目標數(shù)據(jù)庫，業(yè)務主鍵跟代理主鍵完全相同的時候完全可以不用考慮這么多。有刪除，有增加，有更新首先你需要判斷你是否在處理一個維表，如果是一個維表的話，那么這可能是一個scd情況，可以使用kettle的dimension lookup 步驟來解決這個問題，如果你要處理的是事實表，方法就可能有所不同，它們之間的主要區(qū)別是主鍵的判斷方式不一樣。事實表一般都數(shù)據(jù)量很大，需要先確定是否有變動的數(shù)據(jù)處在某一個明確的限定條件之下，比如時間上處在某個特定區(qū)間，或者某些字段有某種限定條件，盡量最大程度的先限定要處理的結(jié)果集，然后需要注意的是要先根據(jù)id 來判斷記錄的狀態(tài)，是不存在要插入新紀錄，

14、還是已存在要更新，還是記錄不存在要刪除，分別對于id 的狀態(tài)來進行不同的操作。處理刪除的情況使用delete步驟，它的原理跟insert / update 步驟一樣，只不過在找到了匹配的id之后執(zhí)行的是刪除操作而不是更新操作，然后處理insert / update 操作，你可能需要重新創(chuàng)建一個轉(zhuǎn)換過程，然后在一個job 里面定義這兩個轉(zhuǎn)換之間的執(zhí)行順序。如果你的數(shù)據(jù)變動量比較大的話，比如超過了一定的百分比，如果執(zhí)行效率比較低下，可以適當考慮重新建表。另外需要考慮的是維表的數(shù)據(jù)刪除了，對應的事實表或其他依賴于此維表的表的數(shù)據(jù)如何處理，外鍵約束可能不太容易去掉，或者說一旦去掉了就可能再加上去了，這

15、可能需要先處理好事實表的依賴數(shù)據(jù)，主要是看你如何應用，如果只是簡單的刪除事實表數(shù)據(jù)的話還比較簡單，但是如果需要保留事實表相應記錄，可以在維表中增加一條記錄，這條記錄只有一個主鍵，其他字段為空，當我們刪除了維表數(shù)據(jù)后，事實表的數(shù)據(jù)就更新指向這條空的維表記錄。定時執(zhí)行增量更新可能有時候我們就是定時執(zhí)行更新操作，比如每天或者一個星期一次，這個時候可以不需要在目標表中增加一個時間戳字段來判斷etl進行的最大時間，直接在取得原數(shù)據(jù)庫的時間加上限定條件比如：startdate > ? and enddate < ? 或者只有一個startdatestartdate > ? &#

16、160; (昨天的時間或者上個星期的時間)這個時候需要傳一個參數(shù)，用get system info 步驟來取得，而且你還可以控制時間的精度，比如到天而不是到秒的時間。當然，你也需要考慮一下如果更新失敗了怎么處理，比如某一天因為某種原因沒有更新，這樣可能這一天的記錄需要手工處理回來，如果失敗的情況經(jīng)常可能發(fā)生，那還是使用在目標數(shù)據(jù)庫中增加一個時間字段取最大時間戳的方式比較通用，雖然它多了一個很少用的字段。執(zhí)行效率和復雜度刪除和更新都是一項比較耗費時間的操作，它們都需要不斷的在數(shù)據(jù)庫中查詢記錄，執(zhí)行刪除操作或更新操作，而且都是一條一條的執(zhí)行，執(zhí)行效率低下也是可以預見的，盡量可能的縮小原數(shù)據(jù)集大小。

17、減少傳輸?shù)臄?shù)據(jù)集大小，降低etl的復雜程度時間戳方法的一些優(yōu)點和缺點優(yōu)點:  實現(xiàn)方式簡單，很容易就跨數(shù)據(jù)庫實現(xiàn)了，運行起來也容易設計缺點： 浪費大量的儲存空間，時間戳字段除etl過程之外都不被使用，如果是定時運行的，某一次運行失敗了，就有可能造成數(shù)據(jù)有部分丟失.其他的增量更新辦法：增量更新的核心問題在與如何找出自上次更新以后的數(shù)據(jù)，其實大多數(shù)數(shù)據(jù)庫都能夠有辦法捕捉這種數(shù)據(jù)的變化，比較常見的方式是數(shù)據(jù)庫的增量備份和數(shù)據(jù)復制，利用數(shù)據(jù)庫的管理方式來處理增量更新就是需要有比較好的數(shù)據(jù)庫管理能力，大多數(shù)成熟的數(shù)據(jù)庫都提供了增量備份和數(shù)據(jù)復制的方法，雖然實現(xiàn)上各不一樣，不過由于etl的增量更

18、新對數(shù)據(jù)庫的要求是只要數(shù)據(jù)，其他的數(shù)據(jù)庫對象不關(guān)心，也不需要完全的備份和完全的stand by 數(shù)據(jù)庫，所以實現(xiàn)方式還是比較簡單的.，只要你創(chuàng)建一個與原表結(jié)構(gòu)類似的表結(jié)構(gòu)，然后創(chuàng)建一個三種類型的觸發(fā)器，分別對應insert , update , delete 操作，然后維護這個新表，在你進行etl的過程的時候，將增量備份或者數(shù)據(jù)復制停止，然后開始讀這個新表，在讀完之后將這個表里面的數(shù)據(jù)刪除掉就可以了，不過這種方式不太容易定時執(zhí)行，需要一定的數(shù)據(jù)庫特定的知識。如果你對數(shù)據(jù)的實時性要求比較高可以實現(xiàn)一個數(shù)據(jù)庫的數(shù)據(jù)復制方案，如果對實時性的要求比較低，用增量備份會比較簡單一點。幾點需要注意的地方：1.觸發(fā)器無論是增量備份還是數(shù)據(jù)復制，如果原表中有觸發(fā)器，在備份的數(shù)據(jù)庫上都不要保留觸發(fā)器，因為我們需要的不是一個備份庫，只是需要里面的數(shù)據(jù)，最好所有不需要的數(shù)據(jù)庫對象和一些比較小的表都不用處理。2.邏輯一致和物理一致數(shù)據(jù)庫在數(shù)據(jù)庫備份和同步上有所謂邏輯一致和物理一致的區(qū)別，簡單來說就是同一個查詢在備份數(shù)據(jù)庫上和主數(shù)據(jù)庫上得到的總的數(shù)據(jù)是一樣的，但是里面每一條的數(shù)據(jù)