Incremental Data Updates and Synchronization

Application Scenario

This document applies to scenarios where new data is generated. For details about modified or deleted data, see Data Comparison Example.

For more details about data synchronization schemes, see Overview of Data Synchronization Schemes.

Scheme Introduction

During data synchronization, sometimes you need to perform incremental data updates periodically to ensure data timeliness. For example, update new data (in the source table) to the target table every day.

Incremental data updates can be divided into three types based on the actual data details:

If the source table has a timestamp field, you can add a Parameter Assignment node to obtain the maximum timestamp value in the target table, and then add a Data Synchronization node to obtain incremental data for updates.

Example

The source table is DEMO_ODS_Contract, and the target table is DW_Contract, both containing the timestamp field Order_Creation_Time, as shown in the following figure.

If the latest time of the Order_Creation_Time field in the target table is earlier than that in the source table, it indicates that new data is generated in the source table and needs to be synchronized to the target table.

Obtaining the Latest Time in the Target Table

Add a Parameter Assignment node to extract the maximum value of the Order_Creation_Time field in the target table and set it as the Time parameter, as shown in the following figure.

select max(Order_Creation_Time) from [DW_Contract]

Updating the New Data to the Target Table

1. Drag in a Data Synchronization node and connect it to the Parameter Assignment node.

2. In the Data Synchronization node, extract data from the source table where the value of the Order_Creation_Time field is greater than that of the Time parameter and synchronize it to the target table, as shown in the following figure.

SELECT * FROM [DEMO_ODS_Contract] where Order_Creation_Time > ${Time}

3. Since the data to be synchronized is new, it can be appended to the target table directly. To implement it, you can leave the logical primary key unset and set Write Method to Write Data into Target Table Directly, as shown in the following figure.

Note:

For more details about write methods, see Data Synchronization - Write Method.

4. You can set the execution frequency of scheduled tasks. For example, if you want to synchronize new data to the target table on a daily basis, you can set it to execute once a day.

Click the  icon in the upper right corner to set the execution frequency, as shown in the following figure.

Applicable scenario: The source table has a primary key or logical primary key, but does not have a timestamp field, based on which incremental updates cannot be performed.

Note:

You need to modify the SQL statement according to actual scenario.

Example

The source table is JDY_Order, and the target table is Order_Target.

The source table lacks a timestamp field or the timestamp field cannot be used as an update identifier. However, the source table has a logical primary key field Order_ID, as shown in the following figure.

Description of the incremental synchronization scheme:

• Add a Parameter Assignment node to obtain the unique identifier Order_ID in the target table.

• Add a Data Synchronization node to obtain the corresponding data of the Order_ID field that exists in the source table but not in the target table (that is, the new data to be synchronized), and use the Data Synchronization node to synchronize the data to the target table, thus completing the incremental updates.

Obtaining the Unique Identifier Field in the Target Table

Add a Parameter Assignment node to retrieve the unique identifier Order_ID in the target table Order_Target and set Order_ID as a parameter, as shown in the following figure.

Updating New Data to the Target Table

1. Drag in a Data Synchronization node and connect it to the Parameter Assignment node.

2. In the Data Synchronization node, obtain the corresponding data of the Order_ID field that exists in the source table but not in the target table (that is, the new data to be synchronized), and use the Data Synchronization node to synchronize the data to the target table, thus completing the incremental updates, as shown in the following figure.

.3. Since the data to be synchronized is new, it can be appended to the target table directly. To implement it, you can leave the logical primary key unset and set Write Method to Write Data into Target Table Directly, as shown in the following figure.

Note:

For more details about write methods, see Data Synchronization - Write Method.

4. You can set the execution frequency of scheduled tasks. For example, if you want to synchronize new data to the target table on a daily basis, you can set it to execute once a day.

Click the  icon in the upper right corner to set the execution frequency, as shown in the following figure.

You are advised to use the Data Pipeline function for real-time incremental updates. For details, see Overview of Data Pipeline.