ODI中刪除資料的處理

標籤：

 

 

一、前提知識：
資料從源資料庫向資料倉儲抽取時，一般採用以下幾種方式：

• 全抽模數式
  如果表的資料量較小，則可以採取全表抽取方式，以TRUNCATE/INSERT方式進行資料幫浦。
• 基於時間戳記的抽模數式
  如果來源資料表是不可更新的資料（如大多數交易處理資料）或者是不可刪除資料（只能失效記錄的情況），則根據變更時間戳記，抽取最新變更的資料進行同步。
• 日誌分析
  如果沒有更新時間戳記，或者來源資料存在刪除的情況，則可以進行日誌分析，來執行最新資料變更的同步。

說明：時間戳記的方式如果要處理刪除資料的情況，需要在源表建立觸發器來捕獲刪除的記錄。
ODI在資料幫浦方面，添加了CDC（Changed Data Capture）的功能，並且包含兩種方式，一種是在來源資料庫表上增加觸發器來捕獲新增、修改和刪除的資料到日誌表中。另一種是通過對日誌的挖掘（Oracle的Log Miner和IBM DB2/400）。但是ODI的CDC，必須要求源表有主鍵。
二、刪除資料的問題引入
當我們開始使用ODI來進行CDC方式的資料同步時，一切都正常，刪除的資料也可以正確的同步到目標資料庫。接著，因為業務需求，我們只需要同步部分資料到目標資料庫，也就是為來源資料表加上過濾，這樣問題就出來了，新增和修改的資料都能正確的同步到目標資料庫，而來源資料表刪除的資料，經過ODI Interface執行之後，目標資料表中還存在。
經過檢查，Interface在裝載資料時查詢日誌視圖，而日誌視圖是日誌表與源表的外串連，其結果是刪除的記錄在視圖中只有主鍵，其餘的欄位都為空白，這樣基於日誌視圖上的過濾，必然導致刪除的資料被過濾掉。以下是ODI建立的對象指令碼樣本：

資料來源日誌視圖jv$qp_list_lines
create or replace view soau.jv$qp_list_lines as
SELECT decode(targ.ROWID, NULL, ‘D‘, ‘I‘) jrn_flag,
       jrn.jrn_subscriber jrn_subscriber,
       jrn.jrn_date jrn_date,
       jrn.list_line_id list_line_id,
       targ.creation_date creation_date,
       targ.created_by created_by,
       ...
FROM (SELECT l.jrn_subscriber jrn_subscriber, l.list_line_id list_line_id, MAX(l.jrn_date) jrn_date
FROM soau.j$qp_list_lines l
WHERE l.jrn_consumed = ‘1‘
GROUP BY l.jrn_subscriber, l.list_line_id) jrn,
soau.qp_list_lines targ
WHERE jrn.list_line_id = targ.list_line_id(+)

ODI Interface建立用於裝載資料的的臨時視圖
create or replace view
SOAU.C$_0QP_LIST_LINES
(
C1_LIST_LINE_ID,
C2_CREATION_DATE,
C3_CREATED_BY,
...
JRN_SUBSCRIBER,
JRN_FLAG,
JRN_DATE
)
as select
QP_LIST_LINES.LIST_LINE_ID,
QP_LIST_LINES.CREATION_DATE,
QP_LIST_LINES.CREATED_BY,
...
JRN_SUBSCRIBER,
JRN_FLAG,
JRN_DATE
from SOAU.JV$QP_LIST_LINES QP_LIST_LINES
where (1=1)
And (QP_LIST_LINES.LIST_LINE_TYPE_CODE IN (‘PLL‘, ‘PBH‘))
And (QP_LIST_LINES.END_DATE_ACTIVE is null or trunc(QP_LIST_LINES.END_DATE_ACTIVE) > trunc(sysdate))
And (QP_LIST_LINES.PRICING_PHASE_ID = 1)
And (QP_LIST_LINES.QUALIFICATION_IND IN (4, 6, 20, 22))
AND JRN_SUBSCRIBER = ‘ERP-FK‘ /* AND JRN_DATE < sysdate */

當Interface在執行整合時，將資料從視圖SOAU.C$_0QP_LIST_LINES插入到flow table（flow table是Interface處理的位於目標中間表，資料的同步最終從flow table到目標資料表），由於該視圖已經執行了過濾，刪除的資料就無法插入到flow table，導致刪除的資料最終無法寫入目標。
三、問題解決過程
因為資料在源頭就被過濾了，所以必須保證資料在源頭不被過濾，而Interface可以支援在源、Staging、目標來執行處理，那麼就來做各種測試：
1、將資料過濾移到Staging
經過測試發現，雖然在裝載資料時，包含了刪除的記錄，但是在整合階段，資料插入flow table時，由於除了主鍵，其餘欄位都為空白，所以刪除的資料同樣被過濾掉了。
2、將資料過濾移到目標
經過測試，當我們將過濾移到目標時，刪除的資料被正確的同步到了目標表。那隻要將過濾移到目標，就可以解決問題了？
3、新問題的出現
實際上資料插入flow table後，從flow table到目標表，沒有執行任何過濾處理，所有資料都會被同步到目標資料表。這樣我們需要排除的資料也寫入了目標表，說明這個方式失敗。
4、啟用模型中目標表的過濾
在ODI Designer中，編輯Model下的表，添加過濾，然後將Interface中對來源資料的過濾移除，執行Interface發現，資料正確的插入到了目標表。但是這種方式和前一種方式相同，都是把所有變化的資料都從源取到目標中，存在一定的效能問題。
5、最終的方式
經過研究，還是覺得修改ODI原來的LKM最為實際，只需要把從日誌視圖的取數的視圖代碼修改，修改為原來的視圖代碼UNION刪除的記錄即可。
修改後的Create view on source代碼如下：

create or replace view <%=odiRef.getObjectNameDefaultPSchema("L", "" , "W")%><%=odiRef.getInfo("COLL_NAME")%>
(
<%=odiRef.getColList("", "[CX_COL_NAME]", ",/n/t", "", "")%>
)
as select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]", ",/n/t", "", "")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getFilter()%>
<%=odiRef.getJrnFilter()%>
<%=odiRef.getJoin()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

<%if(!odiRef.getJrnFilter().equals("")){%>

UNION

select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]", ",/n/t", "", "")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJrnFilter()%>
AND JRN_FLAG=‘D‘
<%=odiRef.getJoin()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

<%}%>
四、插曲
在問題有瞭解決方案之後，向Oracle提Tar，經過數次溝通，並最終在OWC示範的情況下，確認為KM的Bug。
不過Oracle又提供了一種處理方法，這種方法不需要修改LKM，但是看上去感覺總有點不那麼好：在Interface中的每個Filter代碼中，加入" OR JRN_FLAG = ‘D‘"，這樣就可以保證日誌表中刪除的記錄一定不會被過濾掉。

ODI中刪除資料的處理