摘要：本文主要介紹使用kettle 來建立一個Type 2的Slowly Changing Dimension 以及其中一些細(xì)節(jié)問題
1. Kettle 簡介
Kettle 是一個強(qiáng)大的，元數(shù)據(jù)驅(qū)動的ETL工具被設(shè)計用來填補(bǔ)商業(yè)和IT之前的差距，將你公司的數(shù)據(jù)變成可增長的利潤.
我們先來看看Kettle能做什么：
1. Data warehouse population with built-in support for slowly changing dimensions, junk dimensions and much, much more.
2. Export of database(s) to text-file(s) or other databases
3. Import of data into databases, ranging from text-files to excel sheets
4. Data migration between database applications
5. Exploration of data in existing databases. (tables, views, synonyms, )
6. Information enrichment by looking up data in various information stores (databases, text-files, excel sheets, )
7. Data cleaning by applying complex conditions in data transformations
8. Application integration
本系列文章主要介紹如下幾點：
1. 數(shù)據(jù)倉庫內(nèi)建支持緩慢增長維SCD ,
2. 在數(shù)據(jù)轉(zhuǎn)換中使用復(fù)雜條件判斷來清理數(shù)據(jù)
3. 如何使用kettle 來處理增量更形
4. 將Kettle 集成到你的應(yīng)用程序里
5. 使用kettle中應(yīng)該注意的一些地方
2. Kettle 文檔
最好的kettle教程就在你身邊，我們下載的kettle-version. zip 文件里其實已經(jīng)包括了非常多的示例和文檔，在你的kettle文件夾下，docs 文件夾下包含了所有的文檔，samples文件夾下包含了一些示例，后面的介紹中一部分示例都來自kettle自帶的這個示例文件夾下。docs里面最主要的是Spoon-version-User-Guide. zip ,里面記錄了kettle 的技術(shù)性文檔，包括支持的操作系統(tǒng)，數(shù)據(jù)庫平臺，文本格式，圖形化的界面，其中最重要的是所有的轉(zhuǎn)換對象(Transformation Core Objects) 和Job對象(Job Core Objects) 的解釋，包括截圖和每一個參數(shù)的解釋。
3. Kettle與Slowly Changing Dimension
我們使用kettle自帶的samples文件下的示例，來看kettle如何支持SCD的。
打開samples / jobs / Slowly Changing Dimension 文件夾，發(fā)現(xiàn)里面有三個文件，
create - populate - update slowly changing dimension.kjb
DimensionLookup - update dimension table 2.ktr
DimensionLookup - update dimension table.ktr
其中后綴以 .kjb 結(jié)尾的是kettle 的job 文件導(dǎo)出的格式，而以ktr 結(jié)尾的是kettle 的transformation 導(dǎo)出的格式,打開其中的DimensionLookup - update dimension table.ktr , 出現(xiàn)如下所示 ：
圖1
1. 最左邊的是產(chǎn)生測試數(shù)據(jù)，如果是實際環(huán)境的話應(yīng)該是連接真實的數(shù)據(jù)庫，產(chǎn)生的真實數(shù)據(jù)格式打開如下：
圖2
2 第二個步驟Dummy 就是把前面的數(shù)據(jù)合并起來，Dummy 步驟本身不做任何事情，不過由于前面有四個輸入指向它，所以它在第二步的作用等同于數(shù)據(jù)合并。
3 第三個步驟是取得系統(tǒng)參數(shù)(get system date) , 它取得當(dāng)前系統(tǒng)時間的日期，并且格式是當(dāng)天的 00:00:00 , 如圖所示
4. 最后一步是真正的重點，執(zhí)行Dimension Lookup / Update 步驟來更新和插入數(shù)據(jù)，以此來實現(xiàn)Type 1 ,2 ,3 的不同Slowly Changing Dimension
圖4
圖5
在開始介紹Dimension Lookup / Update 之前，先看看在執(zhí)行這個步驟之前的輸入和輸入:
輸入:
|
字段名
|
數(shù)據(jù)類型
|
說明
|
|
id
|
int
|
前面步驟的輸入
|
|
name
|
Varchar(50)
|
前面步驟的輸入
|
|
firstname
|
Varchar(50)
|
前面步驟的輸入
|
|
updated
|
time
|
從第三步來的時間參數(shù)
|
輸出:
|
字段名
|
數(shù)據(jù)類型
|
說明
|
|
id
|
INT
|
來自輸入
|
|
name
|
varchar(50)
|
來自輸入
|
|
firstname
|
varchar(50)
|
來自輸入
|
|
customer_tk
|
BIGINT
|
代理主鍵
|
|
version
|
INT
|
版本變更號
|
|
Date_from
|
datetime
|
有效期起始日期
|
|
Date_to
|
Datetime
|
有效期失效日期
|
注意: 上圖中所使用的是mysql 5 數(shù)據(jù)庫做測試，所以數(shù)據(jù)類型一欄都是mysql 的數(shù)據(jù)類型，如果你使用其他數(shù)據(jù)庫，可能數(shù)據(jù)類型會有所不同，其中的datetime 的格式 yyyy/mon/day hh:mm:ss:sss
我們再來看看當(dāng)我們第一次運(yùn)行以后出現(xiàn)的數(shù)據(jù)輸出:
圖6
注意圖6中所有的 version 值都是 1
Date_from 都是 1900/01/01 00:00:00.000
Date_to 都是 2199/12/31 23:59:59.000 這兩列都是根據(jù)圖4下面部分定義的
Id , name , firstname 都是測試數(shù)據(jù)，從前面步驟來的.
然后我們修改圖1中g(shù)enerate row 的部分?jǐn)?shù)據(jù)（一共兩條），并且只有測試數(shù)據(jù)變了的情況下，我們再次運(yùn)行轉(zhuǎn)換，查看數(shù)據(jù)輸出:
圖7
注意到其中customer_tk 并沒有什么變化，仍然在產(chǎn)生類似序列的輸出
Version 的值中出現(xiàn)了 2 , 并且只有在我們改變的數(shù)據(jù)中
在出現(xiàn)了改變的行中的date_from 變成了2007/11/28/ 00:00:00.000
在出現(xiàn)了改變的行中原來數(shù)據(jù)的date_to 變成了 2007-11-28 00:00:00.000
Id 列沒有變化，（變化了也沒用，圖5中的中間部分 Field 選項卡沒有選id）
Name , firstname 有兩個值變了（我們手工改變的）
Dimension Lookup / Update 參數(shù)解釋
|
Step name
|
步驟的名稱，在一個轉(zhuǎn)換中必須是唯一的
|
|
Update the dimension?
|
當(dāng)找到符合條件記錄的時候更新這條記錄，如果這個復(fù)選框沒有選擇，找到了符合條件記錄的時候就是插入新紀(jì)錄而不是更新
|
|
Connection
|
數(shù)據(jù)庫連接的名字
|
|
Target schema
|
|
|
Target table
|
要更新的維表的名稱
|
|
Commit size
|
批處理更新的記錄數(shù)
|
|
Cache size in rows
|
這是把維表的數(shù)據(jù)放在緩存中用來提高數(shù)據(jù)查找速度從而減少數(shù)據(jù)庫查詢的次數(shù)
注意只有最近一次的記錄會被放在緩存中，如果記錄數(shù)超過緩存大小，最有最有關(guān)的最近的最高版本號記錄會被放在緩存中
如果把cache size 設(shè)置成0 ，kettle會一直把記錄放在緩存中直到JVM沒有內(nèi)存了，如果你這樣設(shè)置要確保維的記錄數(shù)不要太大
設(shè)置成 1 表示不使用緩存
|
|
Keys tab
|
設(shè)置在流中的主鍵和目標(biāo)維表的業(yè)務(wù)主鍵，當(dāng)兩個鍵相等時認(rèn)為這條記錄匹配
|
|
Fields tab
|
設(shè)定要更新的字段，當(dāng)主鍵記錄匹配的時候，只有設(shè)定更新的字段不一樣才認(rèn)為是這條記錄是不一樣的，需要更新或者插入（注意圖5的中間部分，F(xiàn)ields tab 右邊設(shè)定的是Insert ,所以實現(xiàn)的是Type2 的SCD）
|
|
Technical key field
|
維的主鍵，也可以叫做代理主鍵(Surrogate Key)
|
|
Creation of technical key
|
指定技術(shù)主鍵的生成方式，對于你數(shù)據(jù)庫連接不適合的方式會自動被去掉，一共有三種：
1 .Use table maximum + 1 : 使用當(dāng)前表最大記錄數(shù)加一的方式產(chǎn)生新主鍵，注意新的最大值會被緩存，所以不用每次需要產(chǎn)生新記錄的時候就計算
2 . Use sequence : 使用一個數(shù)據(jù)庫支持的序列來產(chǎn)生技術(shù)主鍵（比如Oracle ,你也可以看到圖4中這一條是灰色的因為使用的是mysql 數(shù)據(jù)庫）
3. Use auto increment field : 使用一個數(shù)據(jù)庫支持的自動增長來產(chǎn)生技術(shù)主鍵（比如DB2）
|
|
Version field
|
使用這個字段來儲存版本號
|
|
Stream Datafield
|
你可以指定維記錄最后一次被更改的時間，它能指定你要更新的維的精度，如果不指定，就會默認(rèn)是系統(tǒng)時間
|
|
Date range start field
|
維記錄其實有效時間
|
|
Table daterange end
|
維記錄失效時間
|
|
Get Fields button
|
指定所有你想要更新的字段，除了你指定的主鍵
|
|
SQL button
|
產(chǎn)生sql 來創(chuàng)建維表
|
官方文檔中提到的注意事項:
1. Stream date field : 如果你不想每次都改變時間的范圍，你需要添加一個額外的這個字段，比如你打算每天的午夜來進(jìn)行ETL過程，可以考慮加一個Join 步驟”Yesterday 23:59:59” 作為輸入的時間字段.
2. 這必須是一個Date 字段(不能是轉(zhuǎn)換后的字符串，即使他們有相同的格式也不行),我們（Kettle 的開發(fā)小組）把功能實現(xiàn)隔離出來，如果你需要的話自己要先轉(zhuǎn)換.
3. 對于Date range start and end fields : 你只能指定一個表示年的數(shù)據(jù)，而不是時間戳，如果你輸入YYYY(比如2100) ,這將會被當(dāng)成一個時間戳來用: YYYY-01-01 00:00:00.000 ,（注意圖6中的格式）
另外需要注意的地方:
1. Technical key field : 其他一些ETL工具（比如OWB）也許叫做代理主鍵，只是名字上不同而已.
2. SQL Button : 當(dāng)你在目標(biāo)數(shù)據(jù)庫中還沒有建立維表的時候，你點擊SQL Button ,Kettle 會彈出如下對話框幫你建立維表，你會發(fā)現(xiàn)它默認(rèn)幫你在代理主鍵和業(yè)務(wù)主鍵上建立索引。
圖8
3 Creation of technical key ： 在這個選項的第二種實現(xiàn)方式上，Use sequence ，這個要視你數(shù)據(jù)庫支持而定，mysql 就不支持，Oracle 支持sequence , 但是你要自己創(chuàng)建和管理這個sequence , 如果這個sequence 的值因某種外部因素改變了，你要自己確定sequence 產(chǎn)生的值處于何種狀態(tài)，如果可以的話盡量不要用，盡量用第一種：table maximum + 1 ,這種方式永遠(yuǎn)不要擔(dān)心數(shù)據(jù)庫的不同和實現(xiàn)方式的不同，而且簡單易懂。
4 Stream Datefield
4.1 這個選項是用來控制時間的精度的，有的時候我們可能只是一個月進(jìn)行一次ETL，這個時候Datefield 顯然沒有必要到秒的精度，而且這個選項嚴(yán)重影響你后面如果使用緩慢增長維的sql 的復(fù)雜度，因為你需要先把時間的精度調(diào)到你需要的精度，比如你使用的數(shù)據(jù)是到秒的精度，但是你實際需要的只是天的精度，你在sql 里面有大量的時間都浪費(fèi)在toString( stream date field) ,然后把這個字符串substring() ,執(zhí)行效率會低一些.
4.2 不要輕易改這個精度，一旦你確定了精度問題，不要嘗試改變它，尤其是當(dāng)精度變細(xì)的時候，你可能會損失掉已經(jīng)存在與數(shù)據(jù)庫中的數(shù)據(jù)的精度，如果你只是從 “Today 00:00:00.000” 改成 “Today 23.59.59.000” 的情況，需要手動處理好已經(jīng)存在的數(shù)據(jù)格式問題.
4.3 執(zhí)行ETL的時間可能決定這個值，如果你一天可能存在5次執(zhí)行ETL過程（包括自動執(zhí)行或者手工執(zhí)行）那么你顯然不希望時間的精度是按天來計算的（比如Today 00.00.00這種格式）
4.4 精度的損失并不可怕：考慮一下你的應(yīng)用場景，比如我們要做表，列出2006年11月份和2006年12月份的所有銷售總和，結(jié)合上圖中的customer 的例子，假設(shè)是按客戶聚合的, 我們對于customer 的精度要求只要求到月，沒有要求到天，如果我們執(zhí)行ETL的過程是一個星期執(zhí)行一次，可能一個客戶在一個星期內(nèi)改變了三次他的名字（雖然不是個好例子，完全是為了配合上面的圖），而只有最后一次的改變被記錄了下來，這完全跟你執(zhí)行ETL的頻度有關(guān)，但是考慮到用戶需求，只要精度到月就夠了，即使這種精度有數(shù)據(jù)損失也完全沒關(guān)系，所以你如何指定你的Stream date field 的精度主要是看用戶需求的精度。
4.5 如果以上四點你覺得只是一堆讓你頭疼的字符串，那你完全可以把stream date field 設(shè)置成空（默認(rèn)的到時間戳的精度）
執(zhí)行Type 2 SCD
1. “Update the dimension?” 選中
2. 在Field tabs 里面，對于每一個你想要保持全部記錄的字段都要選擇Insert 方式.
錯誤處理和依賴問題
如果你運(yùn)行了這個轉(zhuǎn)換，你會發(fā)現(xiàn)你的輸出中有一條customer_tk為1，version為1的數(shù)據(jù)，你在圖6和圖7中沒有看到這條數(shù)據(jù)是因為我不想一開始把這條數(shù)據(jù)跟SCD的實現(xiàn)混在一起，SCD的實現(xiàn)本身并不會告訴你要添加這條數(shù)據(jù)，這完全是跟數(shù)據(jù)建模有關(guān)系，為了理解這個問題，我們看一下如下情況該如何處理：
一個產(chǎn)品銷售的記錄是作為一個立方體的主要事實表，它包括一個客戶維，現(xiàn)在因為某種原因客戶維需要刪除掉一部分?jǐn)?shù)據(jù)，但是對映的產(chǎn)品銷售記錄卻要保存起來，該如何處理外鍵約束的問題?
SCD實現(xiàn)本身并不會考慮這個問題，因為它跟維表沒有什么關(guān)系，你要處理的是事實表里面那些引用了維表的記錄，如果你沒有這個空行（它唯一的一個值就是id ,而且是為了滿足主鍵約束，version那個字段有沒有值不重要），事實表中的記錄就不好處理這種情況，因為你把它賦予任何一個值都是不合適的。這種方法是為了處理像數(shù)據(jù)依賴（外鍵的關(guān)系）和錯誤處理比較常見的方法。