Overview
Version
FineDataLink Version | Functional Change |
4.1.5.2 | |
4.1.5.5 | Allowed you to select Overwrite Data in Target Table If Same Primary Key Value Exists as Strategy for Primary Key Conflict when Write Method was set to Write Data into Target Table Directly. |
4.1.8.1 | Repaired: When multiple Data Synchronization nodes were executed in parallel, no errors occurred if the data was output to Oracle through a transaction and written to the same table concurrently. |
4.1.10.2 |
|
4.1.11.1 | When you used the Transaction Output operator in Greenplum, the parallel loading phase was much faster if the target table had a primary key and Write Method was set to Overwrite Data in Target Table If Same Primary Key Value Exists. |
Application Scenario
1. You want to load all the data from the single current source into the same transaction when writing data to the target database through scheduled synchronization:
Either all the data is written successfully. (The transaction is committed normally, and the target table is updated.)
Or all the data fails to be written. (The transaction is rolled back, and the target table remains unchanged.)
2. After the synchronization of the scheduled task is complete, the statistical data of the current write operation can be written to a custom table for subsequent data verification.
Function Description
When data is written to the database through a scheduled task, failure rollback is supported, that is, the target table remains unchanged if the task fails.
Pre-processing and post-processing are supported when data is written to the database through a scheduled task. This processing logic can be encapsulated along with core write operations as transactions to support failure rollback.
Prerequisite
To use this operator, you need to add the FineDB configuration in the fine_conf_entity data table.
You can send an email to international@fanruan.com or click at https://help.fanruan.com/finedatalink-en/.
Restriction on Use
Supported Range | Write Method | Primary Key Mapping | Strategy for Primary Key Conflict |
Oracle | Write Data into Target Table Directly (supported in 4.1.5.2 and later versions) Write Data into Target Table After Emptying It (supported in 4.1.5.2 later versions) Append/Update/Delete Data Based on Identifier Field (supported in 4.1.10.2 and later versions) | If the physical primary key is configured, use the physical primary key directly. If no physical primary key is configured, select a field as the logical primary key (supported in 4.1.10.2 and later versions). | Overwrite Data in Target Table If Same Primary Key Value Exists (supported in 4.1.10.2 and later versions) Ignore Target Table Data if Same Primary Key Value Exists (Not supported.) An error will occur when the node runs if the same primary key value exists. |
Greenplum Data Source (Greenplum only supports parallel loading mode.) | Write Data into Target Table Directly Write Data into Target Table after Emptying it Append/Update/Delete Data Based on Identifier Field (supported in 4.1.10.2 and later versions) | If the physical primary key is configured, use the physical primary key directly. If no physical primary key is configured, select a field as the logical primary key (supported in 4.1.10.2 and later versions). | Overwrite Data in Target Table If Same Primary Key Value Exists Ignore Target Table Data if Same Primary Key Value Exists (Not supported.) An error will occur when the node runs if the same primary key value exists. |
Gbase8a (Supported in 4.1.10.2 and later versions) | Write Data into Target Table Directly Write Data into Target Table after Emptying it Add/Modify/Delete Data Based on Identifier Field | If no physical primary key is configured, select a field as the logical primary key (supported in 4.1.10.2 and later versions). | Overwrite Data in Target Table If Same Primary Key Value Exists Ignore Target Table Data if Same Primary Key Value Exists (Not supported.) An error will occur when the node runs if the same primary key value exists. |
Currently, this can be used in the Write Method operator under Data Synchronization and the DB Table Output (Transaction) operator under Data Transformation.
You can select Node Excution Error Is Reported If Same Primary Key Value Exists and Overwrite Data in Target Table If Same Primary Key Value Exists as the Strategy for Primary Key Conflict when Write Method is set to Write Data into Target Table Directly.
Function Description
Transaction Control
Setting items of Transaction Control are shown as follows.
Function | Description |
Pre-SQL | You can use @ temp_tbl_nm_full to reference the intermediate table name used during loading. You can use this parameter to define SQL queries. When this table name parameter is referenced in other transactions, its value will be an empty string. |
Post SQL | You can use @ temp_tbl_nm_full to reference the intermediate table name used during loading. You can use this parameter to define SQL queries. When this table name parameter is referenced in other transactions, its value will be an empty string. |
Supported parameters:
Parameter | Description |
@temp_tbl_nm_full | Intermediate table name with quotes and full SCHEMA |
@temp_schema_nm | Intermediate SCHEMA name without quotes |
@temp_tbl_nm | Intermediate table name without quotes |
@target_tbl_nm_full | Target table name with quotes and full SCHEMA |
@target_schema_nm | Target SCHEMA name without quotes |
@target_tbl_nm | Target table name without quotes |
Parallel Read
In 4.1.10.2 and later versions, parallel read will be executed by default if Data Synchronization - Data Source (section 3.2.4) is selected on the source side, and Transaction Output is selected on the target side.
Procedure
For example, you want to synchronize data to Oracle with the support of failure rollback, and need to record the maximum loading time to the specified data table when the synchronization is successful for subsequent incremental synchronization or data verification.
Data Reading
Create a scheduled task, drag a DB Table Input operator onto the Data Transformation editing page, and set required items for Data Source, as shown in the following figure.
Click Data Preview, and you can see the read data, as shown in the following figure.
Configuring the Pre- and Post-SQL of the Written Database
Drag a DB Table Output (Transaction) operator onto the page, and configure the relevant information of the target database, as shown in the following figure.
Set Write Method and Primary Key, and set the pre-SQL and post-SQL under the Transaction Control, as shown in the following figure.
In this example, since you want to record the maximum loading time to the specified data table when the synchronization is successful, the post-SQL is set as follows:
INSERT INTO "ANONYMOUS"."duandian"(TIME)
SELECT MAX(Contract Signing Time) AS TIME FROM @temp_tbl_nm_full;
1. The SQL statement means to fetch the maximum Contract Signing Time from the intermediate table for the current execution and write it into the TIME field of the specified data table duanian.
2. The intermediate table has the same fields as the target table, serving as an intermediate storage table for the rollback of the written data. Here, @temp_tbl_nm_full refers to the intermediate table.
Running the Task
After running the task, if the execution is successful, you can see the specified data is synchronized and written to the target database, as shown in the following figure.
At the same time, the maximum contract signing time will be written into the specified table duandian, as shown in the following figure.
If the execution fails, the entire batch of data will not be written to the specified database, and the failure rollback will be performed.
Previous：API Output Example
