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.
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:
The source table has a timestamp field.
Timed synchronization (see the "The Source Table Having a Timestamp Field" section)
The source table does not have a timestamp field but has a unique identifier.
Timed synchronization (see the "The Source Table Having No Timestamp Field but Having a Unique Identifier" section)
The source table has neither a timestamp field nor a unique identifier.
Real-time synchronization (see the "The Source Table Having Neither Timestamp Field nor Unique Identifier" section)
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.
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.
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]
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.
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.
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.
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.
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.
You are advised to use the Data Pipeline function for real-time incremental updates. For details, see Overview of Data Pipeline.
滑鼠選中內容,快速回饋問題
滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy