Function Description of Data Comparison

Version

Function Description

• The Data Comparison operator marks data insertions, deletions, and modifications by comparing data in the source table with that in the target table.

• In data synchronization scenarios, when the source table undergoes data insertions, updates, or deletions, you can use the combination of the Data Comparison and DB Table Output operators or the Data Comparison and Jiandaoyun Output operators to achieve corresponding incremental operations on the target table.

Note:

If the data volume is large, you are advised to use the Data Pipeline function for real-time data synchronization.

1. You can adopt different solutions based on the data structure and update requirements to implement data insertion/deletion/modification.For details, see Overview of Data Synchronization Schemes.

If you need to use the Data Comparison operator to implement data insertion, deletion, and modification:

• If the source table or the target table is a Jiandaoyun form, use the combination of Data Comparison and Jiandaoyun Output.

• If both the source and target tables are database tables, use the combination of Data Comparison and DB Table Output.

2. The following scenarios (where neither the source table nor the target table is a Jiandaoyun form) can be implemented by using the Data Synchronization node:

• There is no identifier field, and you have obtained data through filtering. You want to insert the obtained data into the target table, update data in the target table using the obtained data, or remove data corresponding to the obtained data from the target table. (Only one operation type can be selected when there is no identifier field.)

• There is an identifier field and identifier values, and you need to implement data insertion/update/deletion.

For details about using the Data Synchronization node to implement the operations, see Adding/Modifying/Deleting Data Based on the Identifier Field.

3. When adding, deleting, or modifying data using the Data Comparison and DB Table Output/Jiandaoyun Output operators, you can select Logical Deletion/Physical Deletion as the method to delete data.

• Logical Deletion: Mark data as deleted without actually deleting it.

• Physical Deletion: Actually delete data.

For details about the specific effects of the two deletion methods, see Differences Between Physical Deletion and Logical Deletion.

Obtaining Data from the Source and Target Tables

Set two input operators to obtain data from the source and target tables.Note: Only two input operators are needed. Both of the operators should be configured, and their data sources cannot be the same.

The Data Comparison operator automatically treats the input operator above as the left table (source table), and the one below as the right table (target table).FineDataLink of V4.1.5.3 and later versions supports one-click swapping of the left and right tables.

The Data Comparison operator compares the data of the source table with that of the target table, and marks the added, deleted, or modified data, as shown in the following figure.

Note:

Both source and target tables must contain a unique identifier field to ensure data uniqueness. (If there is no physical primary key, you can set a logical primary key in this operator.)

Marking the Added, Deleted, and Modified Data

Use the Data Comparison operator to compare data in the two tables. The added, deleted, and modified data will be marked.

Generally, you only need to set Primary Key Mapping, Comparison Field, and Identifier when configuring the Data Comparison operator.

Comparison Source

Comparison Source is automatically configured. You can swap the left and right tables as needed.

Primary Key Mapping

Select fields for primary key mapping to ensure data uniqueness.

The following figure shows the setting page.

• If the right table has no physical primary key, click Edit next to Primary Key Mapping, and select fields from the left and right tables for logical primary key mapping.You are advised to select the NOT NULL field with unique values as the logical primary key.

• If the target table has a physical primary key, click Edit next to Primary Key Mapping. The physical primary key of the right table will be automatically filled in, and you only need to select a field in the left table for primary key mapping.

You can directly unmap fields in Primary Key Mapping.

Comparison Field

You can select multiple fields as comparison fields.The following figure shows the setting page.

You can set field pairs, based on which the corresponding values in two tables will be compared. An identifier column fdl_comparison_type will be automatically generated based on the comparison results. This column can be unmapped in Field Mapping in the subsequent DB Table Output operator.

1. A field in the right table cannot be selected multiple times.

2. If a field is selected as Comparison Field, data insertions, updates, and deletions on this field are identified.

If a field is not selected as Comparison Field, data insertions and deletions on this field are identified, but any updates on it cannot be identified.

Identifier

Set the values of the identifier column fdl_comparison_type, which are used to mark differences in values of paired fields between two tables.

The default values are Identical, Changed, Added, and Removed. You can customize the values.

Comparison Result Table Field

1. When the comparison table of the Data Comparison operator is selected via the Jiandaoyun Input operator, the comparison result table field defaults to _id, as shown in the following figure.

2. The system will automatically suffix duplicated field names (if any) in the comparison result table with an incrementing number (starting from one), such as Column, Column1, and Column2, which can be viewed in the drop-down list.

Data Preview

Click Data Preview to view the marked data.The Data Comparison operator compares the data of paired fields between two tables. An identifier column fdl_comparison_type will be automatically generated based on the comparison results, as shown in the following figure.

If you select Add Right Table Field to Comparison Result Table, the right table field will be displayed with a green background.

Comparison Detail is added since FineDataLink V4.1.5.3. Click Comparison Detail to view the comparison details of the logical primary key and the comparison field, as shown in the following figure.

Note:

Only the first 5000 rows of data will be fetched during preview on the Data Preview tab page.If the data that exists on rows within the first 5000 rows of the source table exists on rows beyond the first 5000 rows of the target table, the corresponding value of the marker column will be displayed as Added on the Data Preview tab page, but it should be Identical actually.This is because the Data Preview function reads the first 5000 rows only (which may be optimized later). You don't need to worry about it as the Data Comparison operator compares all data during actual execution.

Data Output Configuration

Configuring Data Destination and Mapping

In Data Destination and Mapping in the DB Table Output operator:

1. If you select Physical Deletion in Write Method, you can cancel the mapping for the identifier column fdl_comparison_type.

2. If you select Logical Deletion in Write Method, you need to add a field to the target table as the identifier column, or select an existing column as the identifier column. The source table field fdl_comparison_type should be mapped to the identifier column in the target table.

Write Method

In the DB Table Output operator, when selecting Add/Modify/Delete Data Based on Identifier Field as Write Method, you can select Logical Deletion or Physical Deletion as the method to delete data.Values set in Identifier Value should correspond with the values of Identifier accordingly in the Data Comparison operator.The configuration of Primary Key Mapping in the DB Table Output operator should conform to that in the Data Comparison operator.

Note: For details about the DB Table Output operator, see DB Table Output.