1、kettle 增量更新設計etl 中增量更新是一個比較依靠與工具和設計方法的過程, kettle 中主要供應 sert/ update 步驟, delete 步驟和 database lookup 步驟來支持增量更新增量更新的設計方法也是依據應用場景來選取的,雖然本文爭辯的是 kettle 的實現方式,但或許對其他工具也有一些掛念。1、增量分類應用場景增量更新依據數據種類的不同或許可以分成：1、 增加,不更新2、 更新,不增加3、 即增加也更新4、 有刪除,有增加,有更新其中 1,2,3 種或許都是相同的思路,使用的步驟可能略有不同,通用的方法是在原數據庫增加一個時間戳,然后在轉換之后的對應表

2、保留這個時間戳,然后每次抽取數據的時候,先讀取這個目標數據庫表的時間戳的最大值,把這個值當作參數傳給原數據庫的相應表,依據這個時間戳來做限定條件來抽取數據,抽取之后同樣要保留這個時間戳,并且原數據庫的時間 戳肯定是指定默認值為 date 當前(以原數據庫的時間為標準),抽取之后的目標數據庫的時戩要保留原來的時間戳,而不是抽取時候的時間。對于第一種狀況 ,可以使用 kettle 的 insert/ update 步驟,只是可以勾選 don” t perform any update 選項,這個選項可以告知 kettle 你只會執行 insert 步驟。對于其次種情況可能比較用在數據消滅錯誤然后原

3、數據庫有一些更新,相應的標數據庫也要更新,這時可能不是更新全部的數據 ,而是有一些限定條件的數排你可以使用 kettle 的 update 步驟來只執行更新。關于如何動態的執行限定條件,可以參考前一篇文章。第三種狀況是最為常見的一種情況,使用的同樣是 kettle 的 insert/ update 步驟,只是不要勾選don” t perform any update 選項第四種情況有些簡單,后面特地爭辯：對于第 1,2,3 種個以參考下面的例子這個例子假設原數據庫表為 customers,含有一個id, firstname,1 astnameage 字段,主鍵為 id,然后還加默認值為 sys

4、date 的時間戳字段。轉換之后的結果類似:id, firstname,1 astname,age, updatedate.整個設計流程或許如：其中第一個步驟的sql 或許如下模式：select max(updatedate) from target_customer ;你會留意到其次個步驟和第一個步驟的連接是黃色的線，這是由于其次個 table input 步驟把前面一個步驟的輸出當作一個參數來用，全部 kettle 用黃色的線來表示，其次個table input 的 sql 模式或許如下：select field1 , field2 , field3 from customers wher

5、eupdatedate > ? 后面的一個問號就是表示它需要接受一個參數，你在這個 tableinput 下面需要指定 replace variable in script 選項和 execute for each row 為選中狀態，這樣，kettle 就會循環執行這個sql , 執行的次數為前面參數步驟傳入的數據集的大小。關于第三個步驟執行insert / update 步驟需要特別解釋一下,kettle 執行這個步驟是需要兩個數據流對比，其中一個是目標數據庫，你在 target table 里面指定的，它放在 the keys to look up the values(s) 左邊

6、的 table field 里面的，另外一個數據流就是你在前一個步驟傳進來的，它放在the keys to look up the value(s) 的右邊，kettle 首先用你傳進來的 key 在數據庫中查詢這些記錄，假如沒有找到，它就插入一條記錄，全部的值都跟你原來的值相同，假如依據這個 key 找到了這條記錄，kettle 會比較這兩條記錄，依據你指定update field 來比較，假如數據完全一樣，kettle 就什么都不做，假如記錄不完全一樣，kettle 就執行一個update 步驟。所以首先你要確保你指定的 key 字段能夠唯一確定一條記錄，這個時候會有兩種狀況： 1.維表

7、2.事實表維表大都是通過一個主鍵字段來推斷兩條記錄是否匹配，可能我們的原數據庫的。主鍵記錄不肯定對應目標數據庫中相應的表的主鍵，這個時候原數據庫的主鍵就變成了業務主鍵，你需要依據某種條件推斷這個業務主鍵是否相等，想象一下假如是多個數據源的話，業務主鍵可能會有重復，這個時候你需要比較的是依據你自定義生成的新的實際的主鍵， 這種主鍵可能是依據某種類似與sequence 的生成方式生成的，事實表在經過轉換之后，進目標數據庫之前往往都是通過多個外鍵約束來確定唯一一條記錄的，這個時候比較兩條記錄是否相等都是通過全部的維表的外鍵打算的，你在比較了記錄相等或不等之后，還要自己推斷是否需要添加一個新的主鍵給這

8、個新記錄。上面兩種狀況都是針對特定的應用的，假如你的轉換過程比較簡潔，只是一個原數據庫對應一個目標數據庫，業務主鍵跟代理主鍵完全相同的時候完全可以不用考慮這么多。有刪除,有增加,有更新。首先你需要推斷你是否在處理一個維表,假如是一個維表的話,那么這可能況,可以使用步驟來解決這個問題,假如你要處理的是事實表方法就可能有所不同,它們之間的主要區分 是主鍵的推斷方式不一樣事實表一般都數據量很大,需要先確定是否有變動的數據處在某一個明確的限定條件之下,比如時間上處在某個特定區間,或者某些字段有某種限定條件,盡量最大程度的先限定要處理的結果集,然后需要留意的是要先依據 id 來推斷記錄的狀態,是不存在要

9、插入新紀錄還是已存在要更新,還是記錄不存在要刪除,分別對于 id 的狀態來進行不同的操作處理刪除的狀況使用步驟,它的原理跟 inse 只不過在找到匹配的id 之后執行的是刪除操作而不是更新操作,然后處理 insee 操作,你可能需要重新創建一個轉換過程,然后在里面定義這兩個轉換之間的執行挨次假如你的數據變動量比較大的話,比如超過了肯定的百分比,假如執行效率比較低適當考慮重新建表。另外需要考慮的是維表的數據刪除了,對應的事實表或其他依靠于此維表的表的數據如何處理,外鍵約東可能不太簡潔去掉,或者說一旦去掉了就可能再加上去了,這可能需要先處理好事實表的依靠數據,主要是看你如何應用,假如只是簡潔的刪除

10、事實表數據的話還比較 簡潔,但是假如需要保留事實表相應記錄,可以在維表中增加一條記錄,這條記錄只有一個主鍵,其他字段為空,當我們刪除了維表數據后,事實表的數據就更新指向這條空的維表記錄。定時執行增量更新可能有時候我們就是定時執行更新操作，比如每天或者一個星期一次，這個時候可以不需要在目標表中增加一個時間戳字段來推斷etl 進行的最大時間，直接在取得原數據庫的時間加上限定條件比如：startdate > ? and enddate < ?或者只有一個startdatestartdate > ?(昨天的時間或者上個星期的時間)這個時候需要傳一個參數，用 get system in

11、fo 步驟來取得，而且你還可以把握時間的精度，比如到天而不是到秒的時間。固然，你也需要考慮一下假如更新失敗了怎么處理，比如某一天由于某種緣由沒有更新， 這樣可能這一天的記錄需要手工處理回來，假如失敗的狀況經常可能發生，那還是使用在目 標數據庫中增加一個時間字段取最大時間戳的方式比較通用，雖然它多了一個很少用的字段。執行效率和簡單度刪除和更新都是一項比較耗費時間的操作，它們都需要不斷的在數據庫中查詢記錄，執行刪除操作或更新操作，而且都是一條一條的執行，執行效率低下也是可以預見的，盡量可 能的縮小原數據集大小。削減傳輸的數據集大小，降低etl 的簡單程度時間戳方法的一些優點和缺點優點: 實現方式簡

12、潔，很簡潔就跨數據庫實現了，運行起來也簡潔設計缺點：鋪張大量的儲存空間，時間戳字段除etl 過程之外都不被使用，假如是定時運行的，某一次運行失敗了，就有可能造成數據有部分丟失. 其他的增量更新方法：增量更新的核心問題在與如何找出自上次更新以后的數據，其實大多數數據庫都能夠有方法捕獲這種數據的變化，比較常見的方式是數據庫的增量備份和數據復制，利用數據庫的 治理方式來處理增量更新就是需要有比較好的數據庫治理力量，大多數成熟的數據庫都供應了增量備份和數據復制的方法，雖然實現上各不一樣，不過由于 etl 的增量更新對數據庫的要求是只要數據，其他的數據庫對象不關懷，也不需要完全的備份和完全的stand

13、by 數據庫，所以實現方式還是比較簡潔的.，只要你創建一個與原表結構類似的表結構，然后創建一個三種類型的觸發器，分別對應 insert , update , delete 操作，然后維護這個新表，在你進行 etl 的過程的時候，將增量備份或者數據復制停止，然后開頭讀這個新表， 在讀完之后將這個表里面的數據刪除掉就可以了，不過這種方式不太簡潔定時執行，需要一 定的數據庫特定的學問。假如你對數據的實時性要求比較高可以實現一個數據庫的數據復制方案，假如對實時性的要求比較低，用增量備份會比較簡潔一點。2、留意地方1.觸發器無論是增量備份還是數據復制，假如原表中有觸發器，在備份的數據庫上都不要保留觸發器，由于我們需要的不是一個備份庫，只是需要里面的數據，最好全部不需要的數據庫對 象和一些比較小的表都不用處理。 2.規律全都和物理全都數據庫在數據庫備份和同步上有所謂規律全都和物理全都的區分，簡潔來說就是同一個 查詢在備份數據庫上和主數據庫上得到的總的數據是一樣的，但是里面每一條的數據排列方式可能不一樣， 只要沒有明顯的排序查詢都可能有這種狀況（ 包括 group by , distinct , union 等 ），