本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 # 使用转换规则表达式定义列内容 要定义新列和现有列的内容，可以在转换规则中使用表达式。例如，使用表达式，您可以添加列或将源表标头复制到目标。您还可以使用表达式将目标表上的记录标记为在源中插入、更新或删除。 **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` 表中。它将新列名称设置为 `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 } } ``` ## 使用表达式复制源表标头 默认情况下，源表的标头不会复制到目标。要指示要复制哪些标头，请将转换规则与包含表列标头的表达式结合使用。 您可以在表达式中使用以下列标头。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/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) 接下来，您可以找到可用于构建转换规则表达式的字符串函数。 | 字符串函数 | 说明 | | --- | --- | | `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` 的绝对值。如果为空，则该`abs(x)`函数返回 N *x* ULL。如果**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)`函数返回一个浮点值，*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`:\$1\$1 小数形式的秒数 SS.SSS `%j`：一年中的第几天（001–366） `%J`：\$1\$1 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 ``` ### 示例 **Example 使用 case 条件向目标表添加新的字符串列** 以下示例转换规则将新的字符串列 `emp_seniority` 添加到目标表 `employee`。它使用薪金列上的 SQLite `round`函数，并使用案例条件来检查工资是否等于或超过 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\$1 COL2 ``` 06F6949D234911EE80670242AC120002 1A2B3C4D5E6F11EE80670242AC120003 F5E4D3C2B1A011EE80670242AC120004 ``` **目标表（PostgreSQL UUID 格式）** T\$1 \$1TMP COL2 ``` 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 } } ```