8. Golden Gate 進階設定

本章節介紹 Golden Gate 所提供的一些函數來作為進階的設定。

在設定欄位的匹配上，最常用的就是 keycols 與 colmap :

• KEYCOLS : 用來指定 Table 的 key 欄位，可設定於 Extract 或是 Replicate ， OGG 在同步的過程中，預設會抓取 Table 的 PK 或是 Unique Index 的欄位作為 Key 來同步差異，如果兩者都沒有，預設會使用 Table 的全欄位作為 Key ，而 keycols 則是用來指定 Key 欄位所用，例如指定 regions 這個 table 的 region_id 為 key :

map hr.regions, target hr.regions, keycols(region_id);

• COLMAP : 當 Source Table 與 Target Table 欄位數量或名稱不同時，可以使用 colmap 來匹配欄位，例如 :

map hr.regions, target hr.regions, colmap(usedefaults, rgname=region_name);

usedefaults 表示欄位使用預設的匹配方式，就是假定 Source 與 Target 欄位相同來匹配，而只有 Target 的 rgname 這個欄位匹配到 Source 的 region_name 這個欄位。

用來篩選資料的函數常用的有 Where 、 Filter 與 Range 。

• WHERE : 使用 where 條件來篩選資料，例如只選擇 price > 5000 的資料來進行同步就好 :

map sa.product, target sa.product, where (price > 5000);

• FILTER : 同樣是用來篩選資料的函數，不同於 where 的是， filter 可以進行運算，例如只選擇總金額大於 10000 的資料來進行同步 :

map sa.product, target sa.product, where (price * amount > 10000);

• RANGE : 這是一個能夠將資料分割為多份來進行同步的函數，早期只有 Classic Replicate 可以使用的時候，如果表格的交易量大，只有單線程的 Classic Replicate 可能會來不及同步，此時就可以透過 Range 這個函數將資料進行分割，然後使用多個 Replicate 來同時進行同步，例如將 regions 這個 Table 分割為三份，然後建立三個 Replicate 來同步這個 Table :

# Replicate 1 參數 : map hr.regions, target hr.regions, filter (@range (1,3, region_id); # Replicate 2 參數 : map hr.regions, target hr.regions, filter (@range (2,3, region_id); # Replicate 3 參數 : map hr.regions, target hr.regions, filter (@range (3,3, region_id);

@range (1, 3, region_id) 表示總共分割為 3 份並使用 region_id 這個欄位當為 key 來分割，這個 replicate 同步第一份，以此類推。

 

如今 Integrated Replicate 、 Parallel Replicate 都具有 parallel threads 的功能， Range 這個函數已不太會使用。

在篩選資料的函數中，更進階一點可以使用 IF 與 CASE 這個兩條件判斷式來篩選資料 。

• IF : if 判斷式用來判斷當資料符合條件與不符條件時的處理方式，例如 Total 欄位在 amount 大於 0 時使用 amount 的數值，其餘則設為 0 :

map sa.product, target sa.product, colmap(usedefaults, total = @if (amount > 0, amount, 0));

• CASE : 同樣是用來判定資料符合與不符合的處理方式，不同於 IF 的是， IF 只能處理一個條件，而 CASE 可以處理多個條件，例如 country 欄位為 United Kingdom 時設定為 UK 、 欄位為 America 時設定為 USA ，其餘的設定為 unknow :

map hr.regions, target hr.regions, colmap(usedefaults, country = @case("United Kingdom","UK","America","USA","unknow"));

在日期的處理上可以使用 DATE 函數，例如 Source Table 是把年、月、日分開為三個欄位存放，而同步到 Target 希望只存在一個時間欄位，那麼就可以使用 DATE 來組合 :

map sa.product, target sa.product, colmap(usedefaults, sales_date = @DATE("YYYY-MM-DD", "YY", year, "MM", month, "DD", day));

sales_date 格式設定為 "YYYY-MM-DD" ，年的欄位 "YY" 匹配到 Source 的 year 欄位 、 月的欄位 "MM" 匹配到 Source 的 month ， 日的欄位 "DD" 則是匹配到 day 。

對於資料的處理上，可以使用 STRCAT 來組合多個欄位的資料，以及 STREXT 來截取某一部分的資料 :

• STRCAT : 用來組合欄位的資料所用，例如匹配名字 (NAME) 欄位為 "姓" + "名" 的組合 :

map hr.emp, target hr.emp, colmap(usedefaults, name = @strcat (last_name, "-", first_name));

• STREXT : 用來截取一部分的資料所用，例如區域號碼 (area_code) 為電話號碼的第一碼到第三碼 :

map hr.emp, target hr.emp, colmap(usedefaults, area_code = @strext (phone_num,1,3));

如果有使用變數的需求，那麼可以使用 GETENV 或者是 TOKENS 函數 。

• GETENV : 用來抓取現有環境變數的數值所用，例如我希望為一筆資料的異動加上時間戳記，讓使用者可以知道此筆資料何時被異動過，那麼就可以透過 GETENV 來實現 :

map hr.emp, target hr.emp, colmap(usedefaults, mtime = @GetEnv('GGHEADER','COMMITTIMESTAMP'));

mtime 為 Target 用來表示時間戳記的欄位，內容則是截取環境變數 'COMMITTIMESTAMP' 標示資料當初被 commit 的時間，來源由 Trail File Header 截取 (GGHEADER) 。

• TOKENS : 用來設定使用者自行定義的變數，同樣的要記錄時間戳記的欄位，可以利用 TOKENS 函數在 Source Extract 的時候就先設定好，此時這個變數就會寫入 Trail File 中，例如於 Extract 中設定 modify_date 環境變數 :

table hr.emp, tokens (modify_date = @GetEnv('GGHEADER','COMMITTIMESTAMP'));

 

到了 Target 端匹配的時候，就可以抓取 TOKEN 中 modify_date 這個環境變數 :

map hr.emp, target hr.emp, colmap(usedefaults, mtime = @token ('modify_date');

而預設已經有幾個事先設定好的 TOKEN ，這些 TOKEN 所設定的變數我們不用再自行定義就可以直接使用 :

TK_HOST 代表作業系統的 Hostname 、 TK_GROUP 代表作業系統使用者的 Group 、 TK_OSUSER - 作業系統的使用者名稱 、 TK_DOMAIN - 作業系統的 Domain 名稱 、 TK_COMMIT_TS - Transaction 的 commit sequence number 、 TK_POS - 當前讀取 Trail File 的 Position 位址 、 TK_RBA - 當前讀取 Trail File 的 RBA 位址 、 TK_TABLE - Table 名稱 、 TK_OPTYPE - 交易的類型 (Insert 、 Delete or Update) 。

例如上述為資料的異動加上時間戳記外，我們還想記錄資料異動的種類，那麼就可以利用 TK_OPTYPE 這個預設的 Token 變數來達成 :

map hr.emp, target hr.emp, colmap(usedefaults, mtime = @GetEnv('GGHEADER','COMMITTIMESTAMP'), optype = @TOKEN ('TK_OPTYPE'));

OGG 除了可以透過這些函數在同步的過程來過濾或異動資料之外，也可以用來執行 SQL 語法，只要於 Extract 或是 Replicate 的參數檔中有進行 dblogin 的行為，那麼就可以使用 SQLEXEC 參數來執行 SQL 命令 ，例如 :

EXTRACT EXT1 SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") SETENV (ORACLE_SID="ORCL") SETENV (ORACLE_HOME="/user1/OraHome/db") USERID ogg, PASSWORD ogg EXTTRAIL ./dirdat/E1   DISCARDFILE ./dirrpt/ext1.dsc, PURGE, MEGABYTES 100 SQLEXEC "SELECT 1 FROM DUAL" EVERY 10 MINUTES (每 10 分鐘執行一個 select 1 from dual ，用來檢查 DB 連線是否正常) SQLEXEC "TRUNCATE TABLE HR.TMP" EVERY 1 DAYS (每日 truncate HR.TMP 這個暫存的 Table) TABLE HR.*;

最後來提及一下 OGG 的事件觸發器 (Event Marker Mechanism) ，類似資料庫中 Trigger 的功能，當符合某些事件時便進行相對應的動作，透過參數 EVENTACTIONS 來進行設定。 EVENTACTIONS 可以用來設定當資料即將被寫入前所要進行的動作 (Before) 或是資料已經被寫入後所要進行的動作 (After) ，對於資料被寫入前可設定的 EVENTACTIONS 有 : Trace 、 Log 、 Checkpoint Before 、 Ignore 、 Discard 、 Shell ， Rollover ；對於資料被寫入後可設定的 EVENTACTIONS 有 : Report 、 Abort 、 Checkpoint After 、 Forcestop ， Stop 。例如 :

MAP oe.customer, TARGET oe.customer; TABLE oe.customer, FILTER (@GETENV('GGHEADER','OPTYPE') = 'DELETE'), EVENTACTIONS (LOG INFO, IGNORE); (當 oe.customer 進行 delete 操作時， ignore 這項操作並且將它 log 下來) MAP sales.account, TARGET sales.account; TABLE sales.account, FILTER (balance < 0), EVENTACTIONS (ABORT); (當 sales.account 的 balance 小於 0 時，可能是資料有問題，進行 abort 動作)

經由這些函數的介紹，可以知道 OGG 不僅僅只可以同步資料，同時還可以進行資料的處理，進階的使用上可以做到類似 ETL 的功能。