The function of the Data Comparison operator is introduced in Function Description of Data Comparison. This document uses a simple example to demonstrate how to use the Data Comparison operator.
You can download the example data: Fsale.xls and Fsale_Copy.xls.
You need to periodically synchronize the data in the Fsale table to the Fsale_Copy table.
The data in the Fsale_Copy table is shown in the following figure.
Compared with the data in the Fsale_Copy table, the data of Sugarcane and Frozen pear is deleted, the data of Grape is inserted, and the data of Pineapple is updated in the Fsale table. You want to synchronize the latest data to the Fsale_Copy table.
This section extracts the data from the source and target tables to facilitate the marking of added, deleted, or modified the data in the subsequent Data Comparison operator.
Note that the Data Comparison operator treats the input operator above as the source table, and the one below as the target table.Therefore, in this example, place the input operator extracting the data from the Fsale table above and use the data in the Fsale table to compare with that in the Fsale_Copy table.
Create a scheduled task and drag a Data Transformation node onto the design page, as shown in the following figure.
1. Click the Data Transformation node, drag a DB Table Input operator onto the design page, and rename it Source Table on the Node Information tab page, as shown in the following figure.
2. Configure the data source and use the SQL statement to extract all the data from the Fsale table, as shown in the following figure.
Add another DB Table Input operator and rename it Target Table.Use the SQL statement to extract all the data from the Fsale_Copy table, as shown in the following figure.
1. Add a Data Comparison operator and connect it to the two DB Table Input operators as the downstream operator using connectors, as shown in the following figure.
2. In the Data Comparison operator, configure the logical primary key and comparison field, as shown in the following figure.
The following table describes the setting items.
Comparison Source
Auto-generated
/
Primary Key Mapping
You can select fields for logical primary key mapping to identify the same record in both tables by matching the data in the source and target tables. You are advised to select the NOT NULL field with unique values as the logical primary key.
ID
Comparison Field
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 DB Table Output operator.
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.
In this example, the column with data changes is the Sales column, so select Sales as Comparison Field.
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.
No modification is needed. Keep the default settings.
Comparison Result Table Field
1. All the fields in the comparison result table can be selected if you tick Add Right Table Field to Comparison Result Table. No field is selected by default and no configuration is required.
2. When the comparison table of the Data Comparison operator is selected via the Jiandaoyun Input operator, the comparison result table field defaults to _id.
For details about usage scenarios, see Jiandaoyun Output Operator.
No configuration is required.
The Data Preview page is shown in the following figure.
1. Add a DB Table Output operator and connect it to the Data Comparison operator using a connector.
2. Configure the DB Table Output operator to store the latest data in the target table Fsale_Copy. In Field Mapping, select Map Fields with Same Name and click the icon to unmap the target table field fdl_comparison_type, as shown in the following figure.
3. Click Write Method, select Add/Modify/Delete Data Based on Identifier Field as White Method, select Physical Deletion. Select ID as the logical primary key and select fdl_comparison_type as Identifier Field. Values set in Identifier Value should correspond with the values of Identifier accordingly in the Data Comparison operator, as shown in the following figure.
4. Click Save in the upper right corner.
Click Run in the upper right corner. If a message indicating successful task execution is displayed in Log, the task is successfully executed, as shown in the following figure.
The data in the Fsale_Copy table is consistent with that in the Fsale table, as shown in the following figure.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy