開源ETL工具kettle系列之增量更新設(shè)計(jì)技巧

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

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

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

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

5、0;                                                 圖1其中第一個(gè)步驟

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

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

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

9、;                                                  

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

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

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

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

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

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

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

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

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