Data Sync Map Objects

The target object a.b.c is structured as follows: a represents the Database, b is the Schema, and c is the Table.

Object mapping shows how each leaf object (like a Table) on the target side corresponds to all leaf objects (also like a Table) from the source node that require synchronization. Each job type has its own default mapping rules, which can be customized by adjusting the mapping rules on the node.

Edit Mapping Rules

Edit mappings rules for each node, such as changing node attributes and utilizing inheritance and overwrite modes.

Use the "Global Edit" button to apply mapping rules to sibling nodes simultaneously.

Mapping Rules Syntax

Currently, the system supports three expressions:

1. constants

Enclosed in single quotes, with the target object expressed inside, for example: 'a.b.c'

2. concat function

Functions accommodate both constants and source variables. Constants are defined easily. Source variables don’t require quotation marks; for instance, SOURCE_DATABASE denotes the source database name, SOURCE_SCHEMA signifies the source schema name, and SOURCE_OBJECT refers to the name of the source object, whether it be a table, view, or function. For example, the expression concat(SOURCE_DATABASE, '.', 'public', '.', SOURCE_OBJECT) illustrates how to synchronize a table from the source database to a table named similarly in the public namespace of the target database.

3. case when

To handle complex mappings like combining sub-databases and sub-tables into a single target table, use the case when expression structured as follows:

case
when ${condition} then ${expression}
...
when ${condition} then ${expression}
else ${expression}
end

When the expression is a constant, variable, or concat function mentioned above, the condition currently supports two types: like and regexp_match.

case
 when ${expression} like ${expression} then ${expression}
 when regexp_match(${expression}, ${regex}) like then ${expression}
 ...
 when ${condition} then ${expression}
 else ${expression}
end

Regex is a standard regular expression that allows data extraction from parentheses as a group. The extracted variable names are denoted as $1, $2, ..., $n, which can be utilized in expressions. For example, map source t1_000, t1_001, ..., t1_009 to target t1, and source t2_000, t2_001, ..., t2_009 to target t2. Configure table mapping using a similar approach.

case
when SOURCE_OBJECT like 't1_%' then concat(SOURCE_DATABASE, '.public.t1')
when SOURCE_OBJECT like 't2_%' then concat(SOURCE_DATABASE, '.public.t2')
else concat(SOURCE_DATABASE, '.', 'public', '.', SOURCE_OBJECT)
end

By configuring the table mapping with regexp_match

case
 when regexp_match(SOURCE_OBJECT, '(.*)_[0-9]{3}$') then concat(SOURCE_DATABASE, '.public.', $1)
 else concat(SOURCE_DATABASE, '.', 'public', '.', SOURCE_OBJECT)
end

Example: From MySQL to Tacnode

Change default mappings for database and schema

Refer to the mapping rules below for the example database.

concat('another_database', '.', 'another_schema', '.', SOURCE_OBJECT)

Edit the mapping for tables

Refer to the mapping rules below for the table.

concat(SOURCE_DATABASE, '.', 'public', '.', 'another_table')

Map the sharded tables to target table

Refer to the figure below to apply the mapping rules to the test table. In the case when statement, use the like clause to match the sub-library and sub-table.

case
    when SOURCE_OBJECT like 't1_%' then concat(SOURCE_DATABASE, '.', 'public', '.', 't1')
    when SOURCE_OBJECT like 't2_%' then concat(SOURCE_DATABASE, '.', 'public', '.', 't2')
    else concat(SOURCE_DATABASE, '.', 'public', '.', SOURCE_OBJECT)
end

Utilize regexp_match with regular expressions for matching:

case
    when regexp_match(SOURCE_OBJECT, '(.*)_[0-9]{3}') then concat(SOURCE_DATABASE, '.', 'public', '.', $1)
    else concat(SOURCE_DATABASE, '.', 'public', '.', SOURCE_OBJECT)
end

Field Pruning, Mapping, and Conversion

Column mapping allows for field pruning, mapping, and conversion.

For tables already present on the target end, only field pruning and mapping are available:

• Field pruning in Action
• Choose the target table field in the Target

Trim or convert the fields if the target table is absent.

• Field pruning in Action
• Target is the default field name; type serves as a target and can be altered through input.