使用轉換規則表達式定義資料行內容 - AWS 資料庫遷移服務
使用表達式新增資料行使用表達式為目標記錄加上旗標使用表達式複寫來源資料表標頭使用 SQLite 函數來建構表達式 使用表達式將中繼資料新增至目標資料表

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

使用轉換規則表達式定義資料行內容

若要定義全新和現有的欄的內容，您可以在轉換規則內使用表達式。例如，您可以使用表達式來新增欄或將來源資料表標頭複寫至目標。您也可以使用表達式在來源進行插入、更新或刪除的同時，為目標資料表上的記錄加上旗標。

使用表達式新增資料行

若要在轉換規則中使用表達式將欄新增至資料表，請使用 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
      }
}

使用表達式複寫來源資料表標頭

根據預設，來源資料表的標頭不會複寫至目標。若要指出要複寫哪些標頭，請使用轉換規則搭配包含資料表欄標頭的表達式。

您可以在表達式中使用下列欄標頭。

標頭 進行中複寫中的值 完全載入中的值 資料類型
AR_H_STREAM_POSITION 來源中的串流位置值。根據來源端點，這個值可能是系統變更編號 (SCN) 或日誌序號 (LSN)。 空白字串。 STRING
AR_H_TIMESTAMP 代表變更時間的時間戳記。 表示目前時間資料到達目標的時間戳記。 DATETIME (小數位數=7)
AR_H_COMMIT_TIMESTAMP 代表遞交時間的時間戳記。 代表目前時間的時間戳記。 DATETIME (小數位數=7)
AR_H_OPERATION INSERT、UPDATE 或 DELETE INSERT STRING
AR_H_USER 使用者名稱、ID 或由來源提供有關進行此變更之使用者的任何其他資訊。

只有 SQL Server 和 Oracle (11.2.0.3 版和更新版本) 來源端點才能支援此標頭。

 您要套用至物件的轉換。轉換規則動作會區分大小寫。 STRING
AR_H_CHANGE_SEQ 來自來源資料庫的唯一遞增數字，由時間戳記和自動遞增的數字組成。此值取決於來源資料庫系統。 空白字串。 STRING

下列範例會使用來源中的串流位置值，將新的欄新增至目標。若為 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_id 編號。

{
"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 來源遷移時，請考慮所選集合平行載入設定的範圍分割類型，而不是資料表和檢視 。

接下來將展示您可用於建立轉換規則運算式的字串函數。

字串函數 描述

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 函數 描述

hex(x)

hex 函數會接收 BLOB 做為引數，並傳回 BLOB 內容的大寫十六進位字串版本。

randomblob (N)

randomblob(N) 函數會傳回一個包含偽隨機字節的 N 位元組 BLOB。如果 N 小於 1，則傳回一個 1 位元組的隨機 BLOB。

zeroblob(N)

zeroblob(N) 函數會傳回一個由 N 位元組 0x00 組成的 BLOB。

接下來將展示您可用於建立轉換規則運算式的數值函數。

數值函數 描述

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 到 +9,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 檢查函數 描述

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 整理函數。

接下來，您可以找到可用來建立轉換規則表達式的日期和時間函數。

日期和時間函數 描述

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：** 小數秒 SS.SSS

%j：年的第幾日 001–366

%J：** 儒略日數

%m：月份 01–12

%M：分鐘 00–59

%s：自 1970-01-01 起的秒數

%S：秒 00–59

%w：星期幾 0–6 星期日==0

%W: 年的第幾週 00–53

%Y: 年份 0000–9999

%%: %

接下來將展示您可用於建立轉換規則運算式的雜湊函數。

雜湊函數 描述

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

範例

範例 使用 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
      }

  }
範例 將新的日期欄新增到目標資料表

下列範例會將新的資料欄 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
      }
  }
範例 將新的數值欄新增至目標資料表

下列範例會將新的數值欄 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"
      }
  }
範例 使用雜湊函數將新的字串欄新增至目標資料表

下列範例會將新的字串欄 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 – 來源資料表中的資料行名稱。

範例 使用來源中的模式名稱，新增結構描述名稱的資料欄

下列範例會使用來源中的結構描述名稱，將名為 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
      }
  }