本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用轉換規則表達式定義資料行內容 若要定義全新和現有的欄的內容，您可以在轉換規則內使用表達式。例如，您可以使用表達式來新增欄或將來源資料表標頭複寫至目標。您也可以使用表達式在來源進行插入、更新或刪除的同時，為目標資料表上的記錄加上旗標。 **Topics** + [ ## 使用表達式新增資料行 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-adding) + [ ## 使用表達式為目標記錄加上旗標 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Flagging) + [ ## 使用表達式複寫來源資料表標頭 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Headers) + [ ## 使用 SQLite 函數來建構表達式 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite) + [ ## 使用表達式將中繼資料新增至目標資料表 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Metadata) ## 使用表達式新增資料行 若要在轉換規則中使用表達式將欄新增至資料表，請使用 `add-column` 規則動作和 `column` 規則目標。 下列範例會將新的欄新增至 `ITEM` 資料表。它會將新的欄名稱設為具有 `string` 資料類型且長度為 50 個字元的 `FULL_NAME`。表達式會串連兩個現有的欄 (`FIRST_NAME` 和 `LAST_NAME`) 的值，以得出 `FULL_NAME`。`schema-name`、`table-name` 和表達式參數是指來源資料庫資料表中的物件。`Value` 和 `data-type` 區塊是指目標資料庫資料表中的物件。 ``` { "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "Test", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "Test", "table-name": "ITEM" }, "value": "FULL_NAME", "expression": "$FIRST_NAME||'_'||$LAST_NAME", "data-type": { "type": "string", "length": 50 } } ] } ``` ## 使用表達式為目標記錄加上旗標 若要在來源資料表中進行插入、更新或刪除的同時，為目標資料表中的記錄加上旗標，請在轉換規則中使用表達式。表達式會使用 `operation_indicator` 函數來為記錄加上旗標。從來源刪除的記錄不會從目標中刪除。相反地，目標記錄會加上使用者提供的值的旗標，表示它已從來源中刪除。 **注意** `operation_indicator` 函數僅適用於在來源和目標資料庫都具有主索引鍵的資料表。 例如，下列轉換規則會先將新的 `Operation` 欄新增至目標資料表。接著，無論何時從來源資料表中刪除記錄，它都會以 `D` 值更新欄。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "add-column", "value": "Operation", "expression": "operation_indicator('D', 'U', 'I')", "data-type": { "type": "string", "length": 50 } } ``` ## 使用表達式複寫來源資料表標頭 根據預設，來源資料表的標頭不會複寫至目標。若要指出要複寫哪些標頭，請使用轉換規則搭配包含資料表欄標頭的表達式。 您可以在表達式中使用下列欄標頭。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html) 下列範例會使用來源中的串流位置值，將新的欄新增至目標。若為 SQL Server，串流位置值為來源端點的 LSN。若為 Oracle，串流位置值為來源端點的 SCN。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "add-column", "value": "transact_id", "expression": "$AR_H_STREAM_POSITION", "data-type": { "type": "string", "length": 50 } } ``` 下列範例會將新資料欄新增至目標，並具有來源的唯一遞增數字。此值代表工作層級的 35 位數唯一編號。前 16 位數字是時間戳記的一部分，後 19 位數是依 DBMS 遞增的 record\$1id 編號。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "add-column", "value": "transact_id", "expression": "$AR_H_CHANGE_SEQ", "data-type": { "type": "string", "length": 50 } } ``` ## 使用 SQLite 函數來建構表達式 您可以使用資料表設定指定任何您希望套用到特定操作所選取資料表或檢視的任何設定。資料表設定規則是選用的。 **注意** MongoDB 和 DocumentDB 資料庫不使用資料表和檢視的概念，而是將資料記錄儲存為文件，並集中在*集合*內。因此，從 MongoDB 或 DocumentDB 來源遷移時，請考慮所選*集合*平行載入設定的範圍分割類型，而不是資料表和檢視 。 **Topics** + [ ### 使用 CASE 表達式 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite.CASE) + [ ### 範例 ](#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite.Ex) 接下來將展示您可用於建立轉換規則運算式的字串函數。 | 字串函數 | Description | | --- | --- | | `lower(x)` | `lower(x)` 函數會傳回 *`x`* 字串的副本，並所有字元轉換為小寫。依預設，內建 `lower` 函數僅適用於 ASCII 字元。 | | `upper(x)` | `upper(x)` 函數會傳回字串 *`x`* 的副本，並將所有字元轉換為大寫。依預設，內建 `upper` 函數僅適用於 ASCII 字元。 | | `ltrim(x,y)` | `ltrim(x,y)` 函數會傳回從 x 左側刪除 y 中所有字元形成的字串。如果 y 沒有值，則 `ltrim(x)` 會移除 x 左側的空格。 | | `replace(x,y,z)` | `replace(x,y,z)` 函數會傳回將字串 x 中每次出現的字串 y 替換為字串 z 所形成的字串。 | | `rtrim(x,y)` | `rtrim(x,y)` 函數會傳回從 x 右側刪除 y 中出現的所有字元所形成的字串。如果 y 沒有值，則 `rtrim(x)` 會移除 x 右側的空格。 | | `substr(x,y,z)` | `substr(x,y,z)` 函數會傳回輸入字串 `x` 以第 `y` 個字元開頭的子字串，該字串長度為 *`z`* 個字元。 如果省略 *`z`*，則 `substr(x,y)` 會傳回從字串 `y` 第 `x` 個字元到結尾的所有字元。`x` 最左邊的字元是數字 1。如果 *`y`* 是負數，則建立子字串時第一個字元會從右邊開始計算，而不是左邊。如果 *`z`* 是負數，則傳回 `y` 的前 `abs(z)` 個字元。如果 `x` 是字串，那麼字元的索引是指實際的 UTF-8 字元。如果 `x` 是 BLOB，那麼索引是指位元組。 | | trim(x,y) | `trim(x,y)` 函數會傳回從 `y` 兩側刪除所有 `x` 中所含字元的字串。如果 `y` 沒有值，則 `trim(x)` 會移除 `x` 兩側的空格。 | 接下來將展示您可用於建立轉換規則運算式的 LOB 函數。 | LOB 函數 | Description | | --- | --- | | `hex(x)` | `hex` 函數會接收 BLOB 做為引數，並傳回 BLOB 內容的大寫十六進位字串版本。 | | `randomblob (N)` | `randomblob(N)` 函數會傳回一個包含偽隨機字節的 `N` 位元組 BLOB。如果 *N* 小於 1，則傳回一個 1 位元組的隨機 BLOB。 | | `zeroblob(N)` | `zeroblob(N)` 函數會傳回一個由 `N` 位元組 0x00 組成的 BLOB。 | 接下來將展示您可用於建立轉換規則運算式的數值函數。 | 數值函數 | Description | | --- | --- | | `abs(x)` | `abs(x)` 函數會傳回數值引數 `x` 的絕對值。如果 *x* 為 NULL，`abs(x)` 函數會傳回 NULL。如果 **x** 是無法轉換為數值的字串或 BLOB，則 `abs(x)` 函數會傳回 0.0。 | | `random()` | `random` 函數會傳回介於 -9,223,372,036,854,775,808 到 \$19,223,372,036,854,775,807 之間的偽隨機整數。 | | `round (x,y)` | `round (x,y)` 函數會傳回一個四捨五入到小數點右側 *y* 位的浮點值 *x*。如果 *y* 沒有值，則假定為 0。 | | `max (x,y...)` | 多參數 `max` 函數會傳回具有最大值的參數，或在任意參數為 NULL 時傳回 NULL。 `max` 函數會從左到右搜尋其參數，以尋找定義整理函數的參數。如果找到符合項目，函數將使用該整理函數進行所有字串的比較。如果 `max` 沒有定義整理函數的引數，則會使用 `BINARY` 整理函數。`max` 函數有兩個或以上的參數時為簡單函數，但如果只有一個參數，則會作為彙整函數執行。 | | `min (x,y...)` | 多參數 `min` 函數會傳回具有最小值的參數。 `min` 函數會從左到右搜尋其參數，以尋找定義整理函數的參數。如果找到符合項目，函數將使用該整理函數進行所有字串的比較。如果 `min` 沒有定義整理函數的引數，則會使用 `BINARY` 整理函數。`min` 函數有兩個或以上的參數時為簡單函數，但如果只有一個參數，則會作為彙整函數執行。 | 接下來，您可以找到可用來建立轉換規則表達式的 NULL 檢查函數。 | NULL 檢查函數 | Description | | --- | --- | | `coalesce (x,y...)` | `coalesce` 函數會傳回其第一個非 NULL 參數的副本，但如果所有參數都為 NULL，則傳回 NULL。coalesce 函數至少有兩個引數。 | | `ifnull(x,y)` | `ifnull` 函數會傳回其第一個非 NULL 參數的副本，但如果兩個參數都為 NULL，則傳回 NULL。`ifnull` 函數只有兩個參數。`ifnull` 函數與具有兩個參數的 `coalesce` 相同。 | | `nullif(x,y)` | 如果參數不同，`nullif(x,y)` 函數會傳回其第一個參數的副本，但如果參數相同，則傳回 NULL。 `nullif(x,y)` 函數會從左到右搜尋其參數，以尋找定義整理函數的參數。如果找到符合項目，函數將使用該整理函數進行所有字串的比較。如果 nullif 的參數都未定義整理函數，則會使用 `BINARY` 整理函數。 | 接下來，您可以找到可用來建立轉換規則表達式的日期和時間函數。 | 日期和時間函數 | Description | | --- | --- | | `date(timestring, modifier, modifier...)` | `date` 函數會傳回 YYYY-MM-DD 格式的日期。 | | `time(timestring, modifier, modifier...)` | `time` 函數會傳回 HH:MM:SS 格式的時間。 | | `datetime(timestring, modifier, modifier...)` | `datetime` 函數會傳回 YYYY-MM-DD HH:MM:SS 格式的日期和時間。 | | `julianday(timestring, modifier, modifier...)` | `julianday` 函數會傳回自西元前 4714 年 11 月 24 日格林威治中午以來的天數。 | | `strftime(format, timestring, modifier, modifier...)` | `strftime` 函數會使用下列其中一個變數，根據指定為第一個引數的格式字串傳回日期： `%d`：月曆日 `%H`：小時 00–24 `%f`：\$1\$1 小數秒 SS.SSS `%j`：年的第幾日 001–366 `%J`：\$1\$1 儒略日數 `%m`：月份 01–12 `%M`：分鐘 00–59 `%s`：自 1970-01-01 起的秒數 `%S`：秒 00–59 `%w`：星期幾 0–6 星期日==0 `%W`: 年的第幾週 00–53 `%Y`: 年份 0000–9999 `%%`: % | 接下來將展示您可用於建立轉換規則運算式的雜湊函數。 | 雜湊函數 | Description | | --- | --- | | `hash_sha256(x)` | `hash` 函數會產生輸入資料欄的雜湊值 (使用 SHA-256 演算法)，並傳回所產生雜湊值的十六進位值。 若要在表示式中使用 `hash` 函數，請將 `hash_sha256(x)` 新增至表達式，並以來源欄名稱取代 *`x`*。 | ### 使用 CASE 表達式 SQLite `CASE` 表達式會評估條件清單，並根據結果傳回表達式。語法如下所示： ``` CASE case_expression WHEN when_expression_1 THEN result_1 WHEN when_expression_2 THEN result_2 ... [ ELSE result_else ] END # Or CASE WHEN case_expression THEN result_1 WHEN case_expression THEN result_2 ... [ ELSE result_else ] END ``` ### 範例 **Example 使用 case 條件將新的字串欄新增到目標資料表** 例如，下列範例轉換規則會先將新的字串欄 `emp_seniority` 新增至目標資料表 `employee`。它會使用薪資欄上的 SQLite `round` 函數，透過 case 條件來檢查薪資是否等於或超過 20,000。如果是，則該資料欄會取得值 `SENIOR`，而其他情況的值為 `JUNIOR`。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "employee" }, "value": "emp_seniority", "expression": " CASE WHEN round($emp_salary)>=20000 THEN ‘SENIOR’ ELSE ‘JUNIOR’ END", "data-type": { "type": "string", "length": 50 } } ``` **Example 使用 SUBSTR 函數將新的字串資料欄新增至目標資料表** 下列範例轉換規則會使用 SQLite 運算子或函數來新增字串欄，以定義欄中的資料。此方法涉及使用 SQLite 函數，將從 Oracle 載入的 GUID 資料轉換為 UUID 格式，然後再將其插入 Postgresql 目標資料表。 下列規則使用 SQLite 子字串 (SUBSTR)、十六進位函數 (HEX) 和小寫 (LOWER) 函數，將 GUID 資料分成數個以連字號分隔的群組，特別是 8 位數的群組，後面接著 4 位數的群組，後面接著 12 位數的群組，總共 32 位數，代表 128 位元。 以下是透過轉換規則進行目標後處理時的範例來源資料和輸出： **來源資料表 (Oracle GUID 格式）** T\$1COL2 ``` 06F6949D234911EE80670242AC120002 1A2B3C4D5E6F11EE80670242AC120003 F5E4D3C2B1A011EE80670242AC120004 ``` **目標資料表 (PostgreSQL UUID 格式）** T\$1COL2\$1TMP ``` 06f6949d-2349-11ee-8067-0242ac120002 1a2b3c4d-5e6f-11ee-8067-0242ac120003 f5e4d3c2-b1a0-11ee-8067-0242ac120004 ``` ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "SPORTS", "table-name": "TEST_TBL_2" }, "value": "t_col2_tmp", "expression": "CASE LOWER(SUBSTR(HEX($T_COL2), 1, 8) || '-' || SUBSTR(HEX($T_COL2), 9, 4) || '-' || SUBSTR(HEX($T_COL2), 13, 4) || '-' || SUBSTR(HEX($T_COL2), 17, 4) || '-' || SUBSTR(HEX($T_COL2), 21, 12)) WHEN '----' THEN NULL ELSE LOWER(SUBSTR(HEX($T_COL2), 1, 8) || '-' || SUBSTR(HEX($T_COL2), 9, 4) || '-' || SUBSTR(HEX($T_COL2), 13, 4) || '-' || SUBSTR(HEX($T_COL2), 17, 4) || '-' || SUBSTR(HEX($T_COL2), 21, 12)) END", "data-type": { "type": "string", "length": 60 } } ``` **Example 將新的日期欄新增到目標資料表** 下列範例會將新的資料欄 `createdate` 新增至目標資料表 `employee`。當您使用 SQLite 日期函數 `datetime` 時，已插入每一列所新建的資料表中都會新增該日期。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "employee" }, "value": "createdate", "expression": "datetime ()", "data-type": { "type": "datetime", "precision": 6 } } ``` **Example 將新的數值欄新增至目標資料表** 下列範例會將新的數值欄 `rounded_emp_salary` 新增至目標資料表 `employee`。它使用 SQLite `round` 函數來新增四捨五入的薪資。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "employee" }, "value": "rounded_emp_salary", "expression": "round($emp_salary)", "data-type": { "type": "int8" } } ``` **Example 使用雜湊函數將新的字串欄新增至目標資料表** 下列範例會將新的字串欄 `hashed_emp_number` 新增至目標資料表 `employee`。SQLite `hash_sha256(x)` 函數會在目標上為來源資料欄 `emp_number` 建立雜湊值。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "employee" }, "value": "hashed_emp_number", "expression": "hash_sha256($emp_number)", "data-type": { "type": "string", "length": 64 } } ``` ## 使用表達式將中繼資料新增至目標資料表 您可以使用下列表達式將中繼資料資訊新增至目標資料表： + `$AR_M_SOURCE_SCHEMA` – 來源結構描述的名稱。 + `$AR_M_SOURCE_TABLE_NAME` – 來源資料表的名稱。 + `$AR_M_SOURCE_COLUMN_NAME` – 來源資料表中的資料行名稱。 + `$AR_M_SOURCE_COLUMN_DATATYPE` – 來源資料表中的資料行名稱。 **Example 使用來源中的模式名稱，新增結構描述名稱的資料欄** 下列範例會使用來源中的結構描述名稱，將名為 `schema_name` 的新欄新增至目標。 ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "add-column", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "add-column", "value":"schema_name", "expression": "$AR_M_SOURCE_SCHEMA", "data-type": { "type": "string", "length": 50 } } ```