1. Join
我得到A 數據流（不管是基于文件或數據庫），A包含field1 , field2 , field3 字段，然后我還有一個(gè)B數據流，B包含field4 , field5 , field6 , 我現在想把它們 ‘加’ 起來(lái), 應該怎么樣做.
這是新手最容易犯錯的一個(gè)地方，A數據流跟B數據流能夠Join，肯定是它們包含join key ,join key 可以是一個(gè)字段也可以是多個(gè)字段。如果兩個(gè)數據流沒(méi)有join key ,那么它們就是在做笛卡爾積，一般很少會(huì )這樣。比如你現在需要列出一個(gè)員工的姓名和他所在部門(mén)的姓名，如果這是在同一個(gè)數據庫，大家都知道會(huì )在一個(gè)sql 里面加上where 限定條件，但是如果員工表和部門(mén)表在兩個(gè)不同的數據流里面，尤其是數據源的來(lái)源是多個(gè)數據庫的情況，我們一般是要使用Database Join 操作，然后用兩個(gè)database table input 來(lái)表示輸入流，一個(gè)輸入是部門(mén)表的姓名，另一個(gè)是員工表的姓名，然后我們認為這兩個(gè)表就可以 ”Join” 了，我們需要的輸出的確是這兩個(gè)字段，但是這兩個(gè)字段的輸出并不代表只需要這兩個(gè)字段的輸入，它們之間肯定是需要一個(gè)約束關(guān)系存在的。另外，無(wú)論是在做 Join , Merge , Update , Delete 這些常規操作的時(shí)候，都是先需要做一個(gè)compare 操作的，這個(gè)compare 操作都是針對compare key 的，無(wú)論兩個(gè)表結構是不是一樣的，比如employee 表和department 表，它們比較的依據就是employee 的外鍵department_id , 沒(méi)有這個(gè)compare key 這兩個(gè)表是不可能連接的起來(lái)的.. 對于兩個(gè)表可能還有人知道是直接sql 來(lái)做連接，如果是多個(gè)輸入數據源，然后是三個(gè)表，有人就開(kāi)始迷茫了，A表一個(gè)字段，B表一個(gè)字段，C表一個(gè)字段，然后就連Join操作都沒(méi)有，直接 database table output , 然后開(kāi)始報錯，報完錯就到處找高手問(wèn)，他們的數據庫原理老師已經(jīng)在吐血了。如果是三個(gè)表連接，一個(gè)sql 不能搞定，就需要先兩個(gè)表兩個(gè)表的連接，通過(guò)兩次compare key 連接之后得到你的輸出，記住，你的輸出并不能代表你的輸入. 下面總結一下:
1. 單數據源輸入，直接用sql 做連接
2. 多數據源輸入，（可能是文本或是兩個(gè)以上源數據庫），用database join 操作.
3. 三個(gè)表以上的多字段輸出.
2. Kettle的數據庫連接模式
Kettle的數據庫連接是一個(gè)步驟里面控制一個(gè)單數據庫連接，所以kettle的連接有數據庫連接池，你可以在指定的數據庫連接里面指定一開(kāi)始連接池里面放多少個(gè)數據庫連接，在創(chuàng )建數據庫連接的時(shí)候就有Pooling 選項卡，里面可以指定最大連接數和初始連接數，這可以一定程度上提高速度.
3. transaction
我想在步驟A執行一個(gè)操作（更新或者插入），然后在經(jīng)過(guò)若干個(gè)步驟之后，如果我發(fā)現某一個(gè)條件成立，我就提交所有的操作，如果失敗，我就回滾，kettle提供這種事務(wù)性的操作嗎？
Kettle 里面是沒(méi)有所謂事務(wù)的概念的，每個(gè)步驟都是自己管理自己的連接的，在這個(gè)步驟開(kāi)始的時(shí)候打開(kāi)數據庫連接，在結束的時(shí)候關(guān)閉數據庫連接，一個(gè)步驟是肯定不會(huì )跨session的（數據庫里面的session）, 另外，由于kettle是并行執行的，所以不可能把一個(gè)數據庫連接打開(kāi)很長(cháng)時(shí)間不放，這樣可能會(huì )造成鎖出現，雖然不一定是死鎖，但是對性能還是影響太大了。ETL中的事務(wù)對性能影響也很大，所以不應該設計一種依賴(lài)與事務(wù)方式的ETL執行順序，畢竟這不是OLTP，因為你可能一次需要提交的數據量是幾百 GB都有可能，任何一種數據庫維持一個(gè)幾百GB的回滾段性能都是會(huì )不大幅下降的.
4. 我真的需要transaction 但又不想要一個(gè)很復雜的設計，能不能提供一個(gè)簡(jiǎn)單一點(diǎn)的方式
Kettle 在3.0.2GA版中將推出一種新功能，在一個(gè)table output 步驟中有一個(gè)Miscellaneous 選項卡，其中有一個(gè)Use unique connections 的選項，如果你選中的話(huà)就可以得到一個(gè)transaction 的簡(jiǎn)單版，
由于是使用的單數據庫連接，所以可以有錯誤的時(shí)候回滾事務(wù)，不過(guò)要提醒一點(diǎn)是這種方式是以犧牲非常大的性能為前提條件的，對于太大的數據量是不適合的(個(gè)人仍然不建議使用這種方式)
5. temporary 表如何使用
我要在ETL過(guò)程中創(chuàng )建一個(gè)中間表，當某個(gè)條件成立的時(shí)候，我要把中間表的數據進(jìn)行轉換，當另一條件成立的時(shí)候我要對中間表進(jìn)行另一個(gè)操作，我想使用數據庫的臨時(shí)表來(lái)操作，應該用什么步驟。
首先從temp 表的生命周期來(lái)分，temp分為事務(wù)臨時(shí)表和會(huì )話(huà)臨時(shí)表，前面已經(jīng)解釋過(guò)了，kettle是沒(méi)有所謂事務(wù)的概念的，所以自然也沒(méi)有所謂的事務(wù)臨時(shí)表。Kettle的每個(gè)步驟管理自己的數據庫連接，連接一結束，kettle也就自然丟掉了這個(gè)連接的session 的handler , 沒(méi)有辦法可以在其他步驟拿回這個(gè)session 的handler , 所以也就不能使用所謂的會(huì )話(huà)臨時(shí)表，當你嘗試再開(kāi)一個(gè)連接的時(shí)候，你可以連上這個(gè)臨時(shí)表，但是你想要的臨時(shí)表里面的數據都已經(jīng)是空的(數據不一定被清除了，但是你連不上了)，所以不要設計一個(gè)需要使用臨時(shí)表的轉換
之所以會(huì )使用臨時(shí)表，其實(shí)跟需要 ”事務(wù)” 特性有一點(diǎn)類(lèi)似，都是希望在ETL過(guò)程中提供一種緩沖。臨時(shí)表很多時(shí)候都不是某一個(gè)源表的全部數據的鏡像，很多時(shí)候臨時(shí)表都是很小一部分結果集，可能經(jīng)過(guò)了某種計算過(guò)程，你需要臨時(shí)表無(wú)非是基于下面三個(gè)特性：
1. 表結構固定，用一個(gè)固定的表來(lái)接受一部分數據。
2. 每次連接的時(shí)候里面沒(méi)有數據。你希望它接受數據，但是不保存，每次都好像執行了truncate table 操作一樣
3. 不同的時(shí)候連接臨時(shí)表用同一個(gè)名字，你不想使用多個(gè)連接的時(shí)候用類(lèi)似與temp1 , temp2 , temp3 , temp4 這種名字，應為它們表結構一樣。
既然臨時(shí)表不能用，應該如何設計ETL過(guò)程呢？（可以用某種詭異的操作搞出臨時(shí)表，不過(guò)不建議這樣做罷了）
如果你的ETL過(guò)程比較的單線(xiàn)程性，也就是你清楚的知道同一時(shí)間只有一個(gè)這樣的表需要，你可以創(chuàng )建一個(gè)普通的表，每次連接的時(shí)候都執行truncate 操作，不論是通過(guò)table output 的truncate table 選項，還是通過(guò)手工執行truncate table sql 語(yǔ)句（在execute sql script 步驟）都可以達到目的（基于上面的1，2 特性）
如果你的ETL操作比較的多線(xiàn)程性，同一時(shí)間可能需要多個(gè)表結構一樣并且里面都是為空的表（基于上面1，2，3特性），你可以創(chuàng )建一個(gè) “字符串+序列” 的模式，每次需要的時(shí)候，就創(chuàng )建這樣的表，用完之后就刪除，因為你自己不一定知道你需要多少個(gè)這種類(lèi)型的表，所以刪除會(huì )比truncate 好一些。
下面舉個(gè)例子怎么創(chuàng )建這種表：
你可以使用某種約定的表名比如department_temp 作為department 的臨時(shí)表?；蛘?br>把argument 傳到表名，使用 department_${argument} 的語(yǔ)法，
如果你需要多個(gè)這種表，使用一個(gè)sequence 操作+execute sql script 操作，execute sql script 就下面這種模式
Create table_? (…………..)
在表的名字上加參數，前面接受一個(gè)sequence 或類(lèi)似的輸入操作.
需要注意的是這種參數表名包括database table input 或者execute sql script ，只要是參數作為表名的情況前面的輸入不能是從數據庫來(lái)的,應為沒(méi)有辦法執行這種preparedStatement 語(yǔ)句，從數據庫來(lái)的值后面的操作是 “值操作” ,而不是字符串替換，只有argument 或者sequence 操作當作參數才是字符串替換. (這一點(diǎn)官方FAQ也有提到)
6. update table 和execute sql script 里面執行update 的區別
執行update table 操作是比較慢的，它會(huì )一條一條基于compare key 對比數據，然后決定是不是要執行update sql , 如果你知道你要怎么更新數據盡可能的使用execute sql script 操作，在里面手寫(xiě)update sql （注意源數據庫和目標數據庫在哪），這種多行執行方式（update sql）肯定比單行執行方式（update table 操作）快的多。
另一個(gè)區別是execute sql script 操作是可以接受參數的輸入的。它前面可以是一個(gè)跟它完全不關(guān)的表一個(gè)sql :
select field1, field2 field3 from tableA
后面執行另一個(gè)表的更新操作：
update tableB set field4 = ? where field5=? And field6=?
然后選中execute sql script 的execute for each row .注意參數是一一對應的.(field4 對應field1 的值，
field5 對應field2 的值， field6 對應field3 的值)
7. kettle的性能
kettle本身的性能絕對是能夠應對大型應用的，一般的基于平均行長(cháng)150的一條記錄，假設源數據庫，目標數據庫以及kettle都分別在幾臺機器上（最常見(jiàn)的桌面工作模式，雙核，1G內存），速度大概都可以到5000 行每秒左右，如果把硬件提高一些，性能還可以提升 , 但是ETL 過(guò)程中難免遇到性能問(wèn)題，下面一些通用的步驟也許能給你一些幫助.
盡量使用數據庫連接池
盡量提高批處理的commit size
盡量使用緩存，緩存盡量大一些（主要是文本文件和數據流）
Kettle 是Java 做的，盡量用大一點(diǎn)的內存參數啟動(dòng)Kettle.
可以使用sql 來(lái)做的一些操作盡量用sql
Group , merge , stream lookup ,split field 這些操作都是比較慢的，想辦法避免他們.，能用sql 就用sql
插入大量數據的時(shí)候盡量把索引刪掉
盡量避免使用update , delete 操作，尤其是update , 如果可以把update 變成先delete , 后insert .
能使用truncate table 的時(shí)候，就不要使用delete all row 這種類(lèi)似sql
合理的分區
如果刪除操作是基于某一個(gè)分區的，就不要使用delete row 這種方式（不管是delete sql 還是delete 步驟）,直接把分區drop 掉，再重新創(chuàng )建
盡量縮小輸入的數據集的大?。ㄔ隽扛乱彩菫榱诉@個(gè)目的）
盡量使用數據庫原生的方式裝載文本文件(Oracle 的sqlloader , mysql 的bulk loader 步驟)
盡量不要用kettle 的calculate 計算步驟，能用數據庫本身的sql 就用sql ,不能用sql 就盡量想辦法用procedure , 實(shí)在不行才是calculate 步驟.
要知道你的性能瓶頸在哪，可能有時(shí)候你使用了不恰當的方式，導致整個(gè)操作都變慢，觀(guān)察kettle log 生成的方式來(lái)了解你的ETL操作最慢的地方。
遠程數據庫用文件+FTP 的方式來(lái)傳數據 ，文件要壓縮。（只要不是局域網(wǎng)都可以認為是遠程連接）
8. 描述物理環(huán)境
源數據庫的操作系統，硬件環(huán)境，是單數據源還是多數據源，數據庫怎么分布的，做ETL的那臺機器放在哪，操作系統和硬件環(huán)境是什么，目標數據倉庫的數據庫是什么，操作系統，硬件環(huán)境，數據庫的字符集怎么選，數據傳輸方式是什么，開(kāi)發(fā)環(huán)境，測試環(huán)境和實(shí)際的生產(chǎn)環(huán)境有什么區別，是不是需要一個(gè)中間數據庫(staging 數據庫) ，源數據庫的數據庫版本號是多少，測試數據庫的版本號是多少，真正的目標數據庫的版本號是多少……. 這些信息也許很零散，但是都需要一份專(zhuān)門(mén)的文檔來(lái)描述這些信息，無(wú)論是你遇到問(wèn)題需要別人幫助的時(shí)候描述問(wèn)題本身，還是發(fā)現測試環(huán)境跟目標數據庫的版本號不一致,這份專(zhuān)門(mén)的文檔都能提供一些基本的信息
9. procedure
為什么我不能觸發(fā)procedure?
這個(gè)問(wèn)題在官方FAQ里面也有提到，觸發(fā)procedure 和 http client 都需要一個(gè)類(lèi)似與觸發(fā)器的條件，你可以使用generate row 步驟產(chǎn)生一個(gè)空的row ,然后把這條記錄連上procedure 步驟，這樣就會(huì )使這條沒(méi)有記錄的空行觸發(fā)這個(gè)procedure (如果你打算使用無(wú)條件的單次觸發(fā)) ，當然procedure 也可以象table input 里面的步驟那樣傳參數并且多次執行.
另外一個(gè)建議是不要使用復雜的procedure 來(lái)完成本該ETL任務(wù)完成的任務(wù)，比如創(chuàng )建表，填充數據，創(chuàng )建物化視圖等等.
10. 字符集
Kettle使用Java 通常使用的UTF8 來(lái)傳輸字符集，所以無(wú)論你使用何種數據庫，任何數據庫種類(lèi)的字符集，kettle 都是支持的，如果你遇到了字符集問(wèn)題，也許下面這些提示可以幫助你：
1. 單數據庫到單數據庫是絕對不會(huì )出現亂碼問(wèn)題的，不管原數據庫和目標數據庫是何種種類(lèi)，何種字符集
2. 多種不同字符集的原數據庫到一個(gè)目標數據庫，你首先需要確定多種源數據庫的字符集的最大兼容字符集是什么，如果你不清楚，最好的辦法就是使用UTF8來(lái)創(chuàng )建數據庫.
3. 不要以你工作的環(huán)境來(lái)判斷字符集：現在某一個(gè)測試人員手上有一個(gè)oracle 的基于xxx 字符集的已經(jīng)存在的數據庫，并且非常不幸的是xxx 字符集不是utf8 類(lèi)型的，于是他把另一個(gè)基于yyy字符集的oracle 數據庫要經(jīng)過(guò)某一個(gè)ETL過(guò)程轉換到oracle , 后來(lái)他發(fā)現無(wú)論怎么樣設置都會(huì )出現亂碼，這是因為你的數據庫本身的字符集不支持，無(wú)論你怎么設置都是沒(méi)用的. 測試的數據庫不代表最后產(chǎn)品運行的數據庫，尤其是有時(shí)候為了省事把多個(gè)不同的項目的不相關(guān)的數據庫裝在同一臺機器上，測試的時(shí)候又沒(méi)有分析清楚這種環(huán)境，所以也再次強調描述物理環(huán)境的重要性.
4. 你所看到的不一定代表實(shí)際儲存的：mysql 處理字符集的時(shí)候是要在jdbc 連接的參數里面加上字符集參數的，而oracle 則是需要服務(wù)器端和客戶(hù)端使用同一種字符集才能正確顯示，所以你要明確你所看到的字符集亂碼不一定代表真的就是字符集亂碼，這需要你檢查在轉換之前的字符集是否會(huì )出現亂碼和轉換之后是否出現亂碼，你的桌面環(huán)境可能需要變動(dòng)一些參數來(lái)適應這種變動(dòng)
5. 不要在一個(gè)轉換中使用多個(gè)字符集做為數據源.
11. 預定義時(shí)間維
Kettle提供了一個(gè)小工具幫助我們預填充時(shí)間維，這個(gè)工具在kettle_home / samples / transformations / General – populate date dimension. 這個(gè)示例產(chǎn)生的數據不一定能滿(mǎn)足各種需要，不過(guò)你可以通過(guò)修改這個(gè)示例來(lái)滿(mǎn)足自己的需求.
12. SQL tab 和 Options tab
在你創(chuàng )建一個(gè)數據庫連接的時(shí)候除了可以指定你一次需要初始化的連接池參數之外（在 Pooling 選項卡下面），還包括一個(gè)Options 選項卡和一個(gè) SQL 選項卡, Options 選項卡里面主要設置一些連接時(shí)的參數，比如autocommit 是on 還是off , defaultFetchSize , useCursorFetch (mysql 默認支持的)，oracle 還支持比如defaultExecuteBatch , oracle.jdbc.StreamBufferSize, oracle.jdbc.FreeMemoryOnEnterImplicitCache ,你可以查閱對應數據庫所支持的連接參數，另外一個(gè)小提示：在創(chuàng )建數據庫連接的時(shí)候，選擇你的數據庫類(lèi)型，然后選到Options 選項卡，下面有一個(gè)Show help text on options usage , 點(diǎn)擊這個(gè)按鈕會(huì )把你帶到對應各個(gè)數據庫的連接參數的官方的一個(gè)參數列表頁(yè)面，通過(guò)查詢(xún)這個(gè)列表頁(yè)面你就可以知道那種數據庫可以使用何種參數了.
對于SQL 選項卡就是在你一連接這個(gè)Connection 之后，Kettle 會(huì )立刻執行的sql 語(yǔ)句，個(gè)人比較推薦的一個(gè)sql 是執行把所有日期格式統一成同一格式的sql ，比如在oracle 里面就是:
alter session set nls_date_format = xxxxxxxxxxxxx
alter session set nls_xxxxxxxxx = xxxxxxxxxxxx
這樣可以避免你在轉換的時(shí)候大量使用to_date() , to_char 函數而僅僅只是為了統一日期格式，對于增量更新的時(shí)候尤其適用.
13. 數據復制
有的時(shí)候可能我們需要的是類(lèi)似數據復制或者一個(gè)備份數據庫，這個(gè)時(shí)候你需要的是一種數據庫私有的解決方案，Kettle 也許并不是你的第一選擇，比如對于Oracle 來(lái)說(shuō)，可能rman , oracle stream , oracle replication 等等， mysql 也有mysql rmaster / slave 模式的replication 等私有的解決方法，如果你確定你的需求不是數據集成這方面的，那么也許kettle 并不是一個(gè)很好的首選方案，你應該咨詢(xún)一下專(zhuān)業(yè)的DBA人士也會(huì )會(huì )更好.
14. 如何控制版本變更
Kettle 的每一個(gè)transformation 和job 都有一個(gè)version 字段（在你保存的時(shí)候）, 不過(guò)這個(gè)功能還不實(shí)用，如果你需要版本控制的話(huà)，還是建議你將transformation 和job 轉換成文本文件保存，然后用svn 或cvs 或任意你熟悉的版本控制系統將其保存，kettle 將在下一個(gè)版本加入版本控制的功能（做的更易用）.
15. 支持的數據源
Kettle 支持相當廣的數據源，比如在數據庫里面的一些不太常見(jiàn)的Access , MaxDB (SAP DB) , Hypersonic , SAP R/3 system , Borland Interbase , Oracle RDB , Teradata和3.0新加入的Sybase IQ .
另外還包括Excel , CSV , LDAP ,以及OLAP Server Mondrian , 目前支持Web Service 不過(guò)暫時(shí)還不支持SOAP.
16. 調試和測試
當ETL轉換出現不可預知的問(wèn)題時(shí)，或是你不清楚某個(gè)步驟的功能是什么的情況下，你可能需要創(chuàng )建一個(gè)模擬環(huán)境來(lái)調適程序，下面一些建議可能會(huì )有所幫助：
盡量使用generate row 步驟或者固定的一個(gè)文本文件來(lái)創(chuàng )建一個(gè)模擬的數據源
模擬的數據源一定要有代表性，數據集一定盡量?。榱诵阅芸紤]）但是數據本身要足夠分散.
創(chuàng )建了模擬的數據集后你應該清楚的知道你所要轉換之后的數據時(shí)什么樣的.
17. 錯誤處理
在ETL任務(wù)中由于數據問(wèn)題出現轉換錯誤是一件非常正常的事情，你不應該設計一個(gè)依賴(lài)于臨時(shí)表或者擁有事務(wù)特點(diǎn)的ETL過(guò)程，面對數據源質(zhì)量問(wèn)題的巨大挑戰，錯誤處理是并不可少的，kettle同樣提供非常方便的錯誤處理方式，在你可能會(huì )出錯的步驟點(diǎn)擊右鍵選擇Define Error handing , 它會(huì )要求你指定一個(gè)處理error 的步驟，你可以使用文本文件或者數據庫的表來(lái)儲存這些錯誤信息，這些錯誤信息會(huì )包含一個(gè)id 和一個(gè)出錯的字段，當你得到這些錯誤信息之后就需要你自己分析出錯的原因了，比如違反主鍵約束可能是你生成主鍵的方式有錯誤或者本身的數據有重復，而違反外鍵約束則可能是你依賴(lài)的一些表里面的數據還沒(méi)有轉換或者外鍵表本身過(guò)濾掉了這些數據. 當你調整了這些錯誤之后，確定所有依賴(lài)的數據都被正確的處理了.kettle user guide 里面有更詳細的解釋?zhuān)锩孢€附帶了一個(gè)使用javascript 來(lái)處理錯誤的示例，這種方式可以作為處理簡(jiǎn)單數據質(zhì)量的方式.
18. 文檔，文檔，文檔
Kettle 提供了豐富的文檔和使用手冊，小到一個(gè)數據庫連接怎么連，大到一個(gè)功能怎么實(shí)現，所有的參數列表，對話(huà)框的每一個(gè)輸入輸出代表什么意思都有解釋?zhuān)援斈阌龅絾?wèn)題你應該第一時(shí)間翻閱這些文檔，也許上面已經(jīng)告訴你怎么做了. 另外kettle 還有一個(gè)非?；钴S的社區，你可以到上面提問(wèn)，但是記住在你提問(wèn)之前先搜索一下論壇看有沒(méi)有類(lèi)似的問(wèn)題已經(jīng)問(wèn)過(guò)了，如果沒(méi)有記得描述清楚你的問(wèn)題
總結
本系列文章主要討論了如何使用kettle 來(lái)處理數據倉庫中的緩慢增長(cháng)維，動(dòng)態(tài)ETL如何設計，增量更新的一些設計技巧，在應用程序中如何集成kettle 以及在使用kettle 時(shí)的一些常見(jiàn)問(wèn)題. 如果你正在尋找一個(gè)工具來(lái)幫助你解決數據庫的集成問(wèn)題或是你打算建立一個(gè)商業(yè)智能項目的數據倉庫，那么kettle是一個(gè)不錯的選擇，你不用支付任何費用就可以得到很多很多數據集成的特性，大量文檔和社區支持. 難道這些不就是你希望從一個(gè)商業(yè)工具上的到的嗎？還在等什么 ，開(kāi)始你的數據集成之旅吧
聯(lián)系客服
