使用转换规则表达式定义列内容

要定义新列和现有列的内容，可以在转换规则中使用表达式。例如，使用表达式，您可以添加列或将源表标头复制到目标。您还可以使用表达式将目标表上的记录标记为在源中插入、更新或删除。

使用表达式添加列

要使用转换规则中的表达式向表添加列，请使用 add-column 规则操作和 column 规则目标。

以下示例将新列添加到 ITEM 表中。它将新列名称设置为 FULL_NAME，数据类型为 string，长度为 50 个字符。此表达式连接两个现有列 LAST_NAME 和 FIRST_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 源迁移时，对于所选集合（而不是表和视图），请考虑范围分段类型的并行加载设置。

主题

• 使用 CASE 表达式
• 示例

接下来，您可以找到可用于构建转换规则表达式的字符串函数。

字符串函数	描述
lower(x)	lower(x) 函数返回字符串 x 的副本，且所有字符都转换为小写。默认情况下，内置的 lower 函数仅适用于 ASCII 字符。
upper(x)	upper(x) 函数返回字符串 x 的副本，且所有字符都转换为大写。默认情况下，内置的 upper 函数仅适用于 ASCII 字符。
ltrim(x,y)	ltrim(x,y) 函数返回一个字符串，删除了 y 中出现的 x 左侧的所有字符。如果 y 没有值，则 ltrim(x) 删除 x 左侧的空格。
replace(x,y,z)	replace(x,y,z) 函数返回一个字符串，将字符串 x 中每次出现的字符串 y 替换为字符串 z。
rtrim(x,y)	rtrim(x,y) 函数返回一个字符串，删除了 y 中出现的 x 右侧的所有字符。如果 y 没有值，则 rtrim(x) 删除 x 右侧的空格。
substr(x,y,z)	substr(x,y,z) 函数返回输入字符串 x 的子字符串，该子字符串从第 y 个字符开始，长度为 z 个字符。 如果省略 z，则 substr(x,y) 返回字符串 x 中从第 y 个字符到结尾的全部字符。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) 函数返回一个浮点值，将 x 四舍五入到小数点右边第 y 位。如果 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) 函数从左到右搜索其参数，以寻找定义排序函数的参数。如果找到一个，则使用该排序函数对所有字符串进行比较。如果两个参数都没有定义排序函数，则使用 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：** Julian 天数 %m：月（01–12） %M：分钟（00–59） %s：自 1970 年 1 月 1 日以来的秒数 %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
      }
  }