開源ETL工具kettle系列之常見問題

1、24開源ETL工具kettle系列之常見問題kettle , ETL,工具,開源1. Join我得到A數(shù)據(jù)流（不管是基于文件或數(shù)據(jù)庫），A包含fieldl , field2 , field3字段，然后我還有一個B數(shù)據(jù)流，B包含field4 , field5 , field6 ,我現(xiàn)在想把它們加起來，應(yīng)該怎么樣做這是新手最容易犯錯的一個地方，A數(shù)據(jù)流跟B數(shù)據(jù)流能夠Join，肯定是它們包含join key ,join key可以是一個字段也可以是多個字段。如果兩個數(shù)據(jù)流沒有join key ,那么它們就是在做笛卡爾積，一般很少會這樣。比如你現(xiàn)在需要列出一個員工的姓名和他所在部門的姓名，如果這是在同

2、一個數(shù)據(jù)庫，大家都知道會在一個sql里面加上where限定條件，但是如果員工表和部門表在兩個不同的數(shù)據(jù)流里面，尤其是數(shù)據(jù)源的來源是多個數(shù)據(jù)庫的情況，我們一般是要使用Database Join 操作，然后用兩個 database table in put來表示輸入流，一個輸入是部門表的姓名，另一個是員工 表的姓名，然后我們認(rèn)為這兩個表就可以” Join ” 了，我們需要的輸出的確是這兩個字段，但是這兩個字段的輸出并不代表只需要這兩個字段的輸入，它們之間肯定是需要一個約束關(guān)系存在的。另外，無論是在做Join , Merge ,Update , Delete 這些常規(guī)操作的時候， 都是先需要做一個

3、compare操作的，這個compare操作都是針對 compare key 的，無論兩個表結(jié)構(gòu)是不是一樣的，比如employee表和department表，它們比較的依據(jù)就是employee的外鍵department_id ,沒有這個compare key這兩個表是不可能連接的起來的.對于兩個表可能還有人知道是直接sql來做連接，如果是多個輸入數(shù)據(jù)源，然后是三個表，有人就開始迷茫了，A表一個字段，B表一個字段，C表一個字段，然后就連Join操作都沒有，直接 database table output ,然后開始報錯，報完錯就到處找高手問，他們的數(shù)據(jù)庫原理老師已經(jīng)在吐血了。如果是三個表連接，一

4、個sql不能搞定，就需要先兩個表兩個表的連接，通過兩次compare key連接之后得到你的輸出，記住，你的輸出并不能代表你的輸入.下面總結(jié)一下：1. 單數(shù)據(jù)源輸入，直接用sql做連接2. 多數(shù)據(jù)源輸入，（可能是文本或是兩個以上源數(shù)據(jù)庫），用database join 操作.3. 三個表以上的多字段輸出.2. Kettle的數(shù)據(jù)庫連接模式Kettle的數(shù)據(jù)庫連接是一個步驟里面控制一個單數(shù)據(jù)庫連接，所以kettle的連接有數(shù)據(jù)庫連接池，你可以在指定的數(shù)據(jù)庫連接里面指定一開始連接池里面放多少個數(shù)據(jù)庫連接，在創(chuàng)建數(shù)據(jù)庫連接的時候就有Pooli ng選項卡，里面可以指定最大連接數(shù)和初始連接數(shù)，這可以一

5、定程度上提高速度3. tran sact ion我想在步驟A執(zhí)行一個操作（更新或者插入），然后在經(jīng)過若干個步驟之后，如果我發(fā)現(xiàn)某一個條件成立，我就提交所有的操作，如果失敗，我就回滾，kettle 提供這種事務(wù)性的操作嗎？Kettle 里面是沒有所謂事務(wù)的概念的，每個步驟都是自己管理自己的連接的，在這個步驟開始的時候打開數(shù)據(jù)庫連接，在結(jié)束的時候關(guān)閉數(shù)據(jù)庫連接，一個步驟是肯定不會跨session的（數(shù)據(jù)庫里面的session）,另外，由于kettle 是并行執(zhí)行的，所以不可能把一個數(shù)據(jù)庫連接打開很長時間不放，這樣可能會造成鎖出現(xiàn)，雖然不一定是死鎖，但是對性能還是影響太大了。ETL中的事務(wù)對性能影響

6、也很大，所以不應(yīng)該設(shè)計一種依賴與事務(wù)方式的ETL執(zhí)行順序，畢竟這不是OLTP因為你可能一次需要提交的數(shù)據(jù)量是幾百GB都有可能，任何一種數(shù)據(jù)庫維持一個幾百GB的回滾段性能都是會不大幅下降的4. 我真的需要transaction但又不想要一個很復(fù)雜的設(shè)計，能不能提供一個簡單一點的方式Kettle 在3.0.2GA版中將推出一種新功能，在一個 table output 步驟中有一個 Miscellaneous選項卡，其中有一個Use unique connections的選項，如果你選中的話就可以得到一個transaction 的簡單版，由于是使用的單數(shù)據(jù)庫連接，所以可以有錯誤的時候回滾事務(wù)，不過要

7、提醒一點是這種方式是以犧牲非常大的性能為前提條件的，對于太大的數(shù)據(jù)量是不適合的（個人仍然不建議使用這種方式）5. temporary表如何使用我要在ETL過程中創(chuàng)建一個中間表，當(dāng)某個條件成立的時候，我要把中間表的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，當(dāng)另一條件成立的時候我要對中間表進(jìn)行另一個操作，我想使用數(shù)據(jù)庫的臨時表來操作，應(yīng)該用什么步驟。首先從temp表的生命周期來分，temp分為事務(wù)臨時表和會話臨時表，前面已經(jīng)解釋過了，kettle 是沒有所謂事務(wù)的概念的，所以自然也沒有所謂的事務(wù)臨時表。Kettle的每個步驟管理自己的數(shù)據(jù)庫連接，連接一結(jié)束，kettle 也就自然丟掉了這個連接的session 的handle

8、r ,沒有辦法可以在其他步驟拿回這個session 的handler , 所以也就不能使用所謂的會話臨時表，當(dāng)你嘗試再開一個連接的時候，你可以連上這個臨時表，但是你想要的臨時表里面的數(shù)據(jù)都已經(jīng)是空的（數(shù)據(jù)不一定被清除了，但是你連不上了），所以不要設(shè)計一個需要使用臨時表的轉(zhuǎn)換之所以會使用臨時表，其實跟需要”事務(wù)”特性有一點類似，都是希望在ETL過程中提供一種緩沖。臨時表很多時候都不是某一個源表的全部數(shù)據(jù)的鏡像，很多時候臨時表都是很小一部分結(jié)果集，可能經(jīng)過了某種計算過程，你需要臨時表無非是基于下面三個特性：1. 表結(jié)構(gòu)固定，用一個固定的表來接受一部分?jǐn)?shù)據(jù)。2. 每次連接的時候里面沒有數(shù)據(jù)。你希望它

9、接受數(shù)據(jù)，但是不保存，每次都好像執(zhí)行了truncate table操作一樣3. 不同的時候連接臨時表用同一個名字，你不想使用多個連接的時候用類似與temp1 , temp2 , temp3 , temp4 這種名字，應(yīng)為它們表結(jié)構(gòu)一樣。既然臨時表不能用，應(yīng)該如何設(shè)計ETL過程呢？（可以用某種詭異的操作搞出臨時表，不過不建議這樣做罷了）如果你的ETL過程比較的單線程性，也就是你清楚的知道同一時間只有一個這樣的表需要，你可以創(chuàng)建一個普通的表，每次連接的時候都執(zhí)行truncate 操作，不論是通過 table output 的truncate table選項，還是通過手工執(zhí)行truncate tab

10、le sql 語句（在execute sql script 步驟）都可以達(dá)到目的（基于上面的 1，2特性）如果你的ETL操作比較的多線程性，同一時間可能需要多個表結(jié)構(gòu)一樣并且里面都是為空的表（基于上面1, 2，3特性），你可以創(chuàng)建一個“字符串+序列”的模式，每次需要的時候，就創(chuàng)建這樣的表，用完之后就刪除，因為你自己不一定知道你需要多少個這種類型的表，所以刪除會比trun cate 好一些。下面舉個例子怎么創(chuàng)建這種表：你可以使用某種約定的表名比如department_temp 作為department的臨時表?；蛘甙?argument 傳到表名，使用 department_$argument的語

11、法，如果你需要多個這種表，使用一個sequenee 操作+execute sql script 操作，execute sql script就下面這種模式Create table_?（.）在表的名字上加參數(shù)，前面接受一個sequenee或類似的輸入操作.需要注意的是這種參數(shù)表名包括database table in put 或者execute sql script，只要是參數(shù)作為表名的情況前面的輸入不能是從數(shù)據(jù)庫來的，應(yīng)為沒有辦法執(zhí)行這種preparedStatement語句，從數(shù)據(jù)庫來的值后面的操作是"值操作”，而不是字符串替換，只有argument或者sequenee操作當(dāng)作參數(shù)才

12、是字符串替換.（這一點官方FAQ也有提到）6. update table 和 execute sql script里面執(zhí)行 update 的區(qū)另U執(zhí)行update table 操作是比較慢的，它會一條一條基于compare key對比數(shù)據(jù)，然后決定是不是要執(zhí)行update sql如果你知道你要怎么更新數(shù)據(jù)盡可能的使用execute sql script操作，在里面手寫 update sql（注意源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫在哪），這種多行執(zhí)行方式（ update sql ）肯定比單行執(zhí)行方式（update table 操作）快的多。另一個區(qū)別是execute sql script操作是可以接受參數(shù)的輸

13、入的。它前面可以是一個跟它完全不關(guān)的表一個sql :select fieldl, field2 field3 from tableA后面執(zhí)行另一個表的更新操作：update tableB set field4 = ? where field5=? And field6=?然后選中execute sql script 的execute for each row .注意參數(shù)是一一對應(yīng)的.（field4 對應(yīng)fieldl 的值，field5 對應(yīng) field2 的值，field6 對應(yīng) field3 的值）7. kettle 的性能kettle本身的性能絕對是能夠應(yīng)對大型應(yīng)用的，一般的基于平均行長1

14、50的一條記錄，假設(shè)源數(shù)據(jù)庫，目標(biāo)數(shù)據(jù)庫以及kettle 都分別在幾臺機器上（最常見的桌面工作模式，雙核，1G內(nèi)存），速度大概都可以到5000行每秒左右,如果把硬件提高一些，性能還可以提升，但是ETL過程中難免遇到性能問題，下面一些通用的步驟也許能給你一些 幫助.盡量使用數(shù)據(jù)庫連接池盡量提高批處理的commit size盡量使用緩存，緩存盡量大一些（主要是文本文件和數(shù)據(jù)流） Kettle 是Java做的，盡量用大一點的內(nèi)存參數(shù)啟動Kettle.可以使用sql來做的一些操作盡量用sqlGroup , merge , stream lookup ,split field插入大量數(shù)據(jù)的時候盡量把索引

15、刪掉盡量避免使用 update , delete 操作，尤其是 update ,如果可以把 update能使用truncate table的時候，就不要使用delete all row變成先delete ,后 insert .這種類似sql這些操作都是比較慢的，想辦法避免他們，能用sql就用sql頁腳內(nèi)容8合理的分區(qū)如果刪除操作是基于某一個分區(qū)的，就不要使用delete row 這種方式（不管是delete sql 還是delete 步驟）,直接把分區(qū)drop掉，再重新創(chuàng)建盡量縮小輸入的數(shù)據(jù)集的大?。ㄔ隽扛乱彩菫榱诉@個目的）盡量使用數(shù)據(jù)庫原生的方式裝載文本文件（Oracle 的sqlload

16、er , mysql 的bulk loaderETL的那臺機器放在哪，操作步驟）盡量不要用kettle 的calculate計算步驟，能用數(shù)據(jù)庫本身的sql就用sql ,不能用sql就盡量想辦法用procedure ,實在不行才是 calculate 步驟.kettle log生成的方式要知道你的性能瓶頸在哪，可能有時候你使用了不恰當(dāng)?shù)姆绞?，?dǎo)致整個操作都變慢，觀察來了解你的ETL操作最慢的地方。遠(yuǎn)程數(shù)據(jù)庫用文件+FTP的方式來傳數(shù)據(jù) ，文件要壓縮。（只要不是局域網(wǎng)都可以認(rèn)為是遠(yuǎn)程連接）8. 描述物理環(huán)境源數(shù)據(jù)庫的操作系統(tǒng)，硬件環(huán)境，是單數(shù)據(jù)源還是多數(shù)據(jù)源，數(shù)據(jù)庫怎么分布的，做 系統(tǒng)和硬件環(huán)境

17、是什么，目標(biāo)數(shù)據(jù)倉庫的數(shù)據(jù)庫是什么，操作系統(tǒng)，硬件環(huán)境，數(shù)據(jù)庫的字符集怎么選，數(shù)據(jù)傳輸方式是什么，開發(fā)環(huán)境，測試環(huán)境和實際的生產(chǎn)環(huán)境有什么區(qū)別，是不是需要一個中間數(shù)據(jù)庫（staging 數(shù)據(jù)庫），源數(shù)據(jù)庫的數(shù)據(jù)庫版本號是多少，測試數(shù)據(jù)庫的版本號是多少，真正的目標(biāo)數(shù)據(jù)庫的版本號是多少.這些信息也許很零散，但是都需要一份專門的文檔來描述這些信息，無論是你遇到問題需要別人幫助的時候描述問題本身,還是發(fā)現(xiàn)測試環(huán)境跟目標(biāo)數(shù)據(jù)庫的版本號不一致，這份專門的文檔都能提供一些基本的信息9. procedure為什么我不能觸發(fā) procedure?這個問題在官方FAQ里面也有提到，觸發(fā)procedure和http

18、 clie nt都需要一個類似與觸發(fā)器的條件，你可以使用gen erate row步驟產(chǎn)生一個空的row ,然后把這條記錄連上 procedure 步驟，這樣就會使這條沒有記錄的空行觸發(fā)這個procedure （如果你打算使用無條件的單次觸發(fā)），當(dāng)然procedure 也可以象table in put里面的步驟那樣傳參數(shù)并且多次執(zhí)行另外一個建議是不要使用復(fù)雜的procedure來完成本該ETL任務(wù)完成的任務(wù)，比如創(chuàng)建表，填充數(shù)據(jù)，創(chuàng)建物化視圖等等10. 字符集Kettle使用Java通常使用的UTF8來傳輸字符集，所以無論你使用何種數(shù)據(jù)庫，任何數(shù)據(jù)庫種類的字符集，kettle都是支持的，如果你

19、遇到了字符集問題，也許下面這些提示可以幫助你：1. 單數(shù)據(jù)庫到單數(shù)據(jù)庫是絕對不會出現(xiàn)亂碼問題的，不管原數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫是何種種類，何種字符集2. 多種不同字符集的原數(shù)據(jù)庫到一個目標(biāo)數(shù)據(jù)庫，你首先需要確定多種源數(shù)據(jù)庫的字符集的最大兼容字符集是什么，如果你不清楚，最好的辦法就是使用UTF8來創(chuàng)建數(shù)據(jù)庫.3. 不要以你工作的環(huán)境來判斷字符集：現(xiàn)在某一個測試人員手上有一個oracle的基于xxx字符集的已經(jīng)存在的數(shù)據(jù)庫，并且非常不幸的是xxx字符集不是utf8類型的，于是他把另一個基于yyy字符集的oracle 數(shù)據(jù)庫要經(jīng)過某一個ETL過程轉(zhuǎn)換到oracle ,后來他發(fā)現(xiàn)無論怎么樣設(shè)置都會出現(xiàn)亂碼，

20、這是因為你的數(shù)據(jù)庫本身的字符集不支持，無論你怎么設(shè)置都是沒用的.測試的數(shù)據(jù)庫不代表最后產(chǎn)品運行的數(shù)據(jù)庫，尤其是有時候為了省事把多個不同的項目的不相關(guān)的數(shù)據(jù)庫裝在同一臺機器上，測試的時候又沒有分析清楚這種環(huán)境，所以也再次強調(diào)描述物理環(huán)境的重要性4. 你所看到的不一定代表實際儲存的：mysql處理字符集的時候是要在 jdbc連接的參數(shù)里面加上字符集參數(shù)的，而oracle則是需要服務(wù)器端和客戶端使用同一種字符集才能正確顯示，所以你要明確你所看到的字符集亂碼不一定代表真的就是字符集亂碼，這需要你檢查在轉(zhuǎn)換之前的字符集是否會出現(xiàn)亂碼和轉(zhuǎn)換之后是否出現(xiàn)亂碼，你的桌面環(huán)境可能需要變動一些參數(shù)來適應(yīng)這種變動5

21、. 不要在一個轉(zhuǎn)換中使用多個字符集做為數(shù)據(jù)源.11. 預(yù)定義時間維Kettle 提供了一個小工具幫助我們預(yù)填充時間維，這個工具在kettle_home / samples / transformations/ General-populate date dimension.這個示例產(chǎn)生的數(shù)據(jù)不一定能滿足各種需要，不過你可以通過修改這個示例來滿足自己的需求.12. SQL tab 和 Optio ns tab在你創(chuàng)建一個數(shù)據(jù)庫連接的時候除了可以指定你一次需要初始化的連接池參數(shù)之外（在Pooli ng 選項卡下面），還包括一個 Options 選項卡和一個 SQL選項卡，Options 選項卡里面

22、主要設(shè)置一些連接時的參數(shù)，比如autocommit是 on 還是 off, defaultFetchSize , useCursorFetch （mysql默認(rèn)支持的）,oracle 還支持比如defaultExecuteBatch , oracle.jdbc.StreamBufferSize, oracle.jdbc.FreeMemory OnEn terImplicitCache ,你可以查閱對應(yīng)數(shù)據(jù)庫所支持的連接參數(shù)，另外一個小提示：在創(chuàng)建數(shù)據(jù)庫連接的時候，選擇你的數(shù)據(jù)庫類型，然后選到Options 選項卡，下面有一個 Show help text on options usage ,點

23、擊這個按鈕會把你帶到對應(yīng)各個數(shù)據(jù)庫的連接參數(shù)的官方的一個參數(shù)列表頁面，通過查詢這個列表頁面你就可以知道那種數(shù)據(jù)庫可以使用何種參數(shù)了.對于SQL選項卡就是在你一連接這個Conn ection 之后，Kettle 會立刻執(zhí)行的sql語句，個人比較推薦的一個sql是執(zhí)行把所有日期格式統(tǒng)一成同一格式的sql ，比如在oracle 里面就是：alter sessi on set nls_date_format = xxxxxxxxxxxxxalter sessi on set nl s_xxxxxxxxx = xxxxxxxxxxxx這樣可以避免你在轉(zhuǎn)換的時候大量使用to_date（） , to_cha

24、r函數(shù)而僅僅只是為了統(tǒng)一日期格式，對于增量更新的時候尤其適用13. 數(shù)據(jù)復(fù)制有的時候可能我們需要的是類似數(shù)據(jù)復(fù)制或者一個備份數(shù)據(jù)庫，這個時候你需要的是一種數(shù)據(jù)庫私有的解決方案，Kettle 也許并不是你的第一選擇，比如對于Oracle 來說，可能rman , oracle stream , oracle replication等等，mysql也有mysql rmaster / slave 模式的replication等私有的解決方法，如果你確定你的需求不是數(shù)據(jù)集成這方面的，那么也許kettle并不是一個很好的首選方案，你應(yīng)該咨詢一下專業(yè)的DBA人士也會會更好.14. 如何控制版本變更Kettl

25、e的每一個transformation和job都有一個version 字段（在你保存的時候），不過這個功能還不實用，如果你需要版本控制的話，還是建議你將 transformation 和job轉(zhuǎn)換成文本文件保存，然后用 svn或cvs或任意你 熟悉的版本控制系統(tǒng)將其保存， kettle 將在下一個版本加入版本控制的功能（做的更易用）15. 支持的數(shù)據(jù)源Access , MaxDB (SAP DB) , Hyperso nic , SAP和3.0新加入的Sybase IQ .目前支持Web Service 不過暫時還不支持 SOAP.Kettle支持相當(dāng)廣的數(shù)據(jù)源，比如在數(shù)據(jù)庫里面的一些不太常見

26、的R/3 system , Borla nd In terbase , Oracle RDB , Teradata 另外還包括 Excel , CSV , LDAP , 以及 OLAP Server Mondrian ,16. 調(diào)試和測試當(dāng)ETL轉(zhuǎn)換出現(xiàn)不可預(yù)知的問題時，或是你不清楚某個步驟的功能是什么的情況下，你可能需要創(chuàng)建一個模擬環(huán)境 來調(diào)適程序，下面一些建議可能會有所幫助：盡量使用gen erate row步驟或者固定的一個文本文件來創(chuàng)建一個模擬的數(shù)據(jù)源模擬的數(shù)據(jù)源一定要有代表性，數(shù)據(jù)集一定盡量?。榱诵阅芸紤]）但是數(shù)據(jù)本身要足夠分散創(chuàng)建了模擬的數(shù)據(jù)集后你應(yīng)該清楚的知道你所要轉(zhuǎn)換之后的數(shù)據(jù)時什么樣