Data Synchronization - Write Method

Version

Function Description

This document introduces the configuration items on the Write Method tab page of the Data Synchronization node and the DB Table Output operator in the Data Transformation node.

The following figure shows the Write Method tab page.

The three write methods are described in the following table.

Note:

1. Some data sources provide limited support for Write Method. For details, see Functional Limitations of Different Data Sources.

2. In V4.1.3 and later versions, partition writing is supported when the target table is in Apache Hive or Transwarp Inceptor. For details, see Partition Table Creation and Data Reading/Writing.

Primary Key Mapping

Establish the correspondence between the rows of the source table and the target table, as shown in the following figure.

1. If the target table has a physical primary key or you have set the primary key for the target table to be created, the primary key mapping relationship will be automatically generated according to the physical primary key of the target table to ensure data uniqueness.

2. If the target table has no physical primary key:

• If Target Table is set to Auto Created Table, you can manually set one or more fields as the physical primary key in Field Mapping.

• If Target Table is set to Existing Table, you can select the field as the logical primary key in Primary Key Mapping for mapping to ensure data uniqueness. If no field is selected, data will be written into the target table directly.

Strategy for Primary Key Conflict

Note:

Strategy for Primary Key Conflict is not supported when the target data source is Hive.

You need to configure Strategy for Primary Key Conflict if any of the following conditions are met:

• The target table has a physical primary key.

• The target table has no physical primary key but has been configured with logical primary key mapping.

Three strategies for the primary key conflict are available:

• Overwrite Data in Target Table If Same Primary Key Value Exists

• Ignore Source Data If Same Primary Key Value Exists

• Record as Dirty Data If Same Primary Key Value Exists

Data is written into the target table after the target table is emptied. This write method is available with or without a primary key.

Note:

In V4.0.29 and later versions, if the source table has no primary key, the target table has a primary key, and the source table contains two records with the same primary key (of the target table) value, the latter record will overwrite the previous one when you use Write Data into Target Table After Emptying It.

Function Description

Note:

1. This write method is not supported when the target data source is Hive.

2. You can select one or more options among Add, Modify, and Delete.

Identifier Field and Identifier Value

1. You can filter the data you need to add/modify/delete based on the identifier field and its value.

2. If you have used the Data Comparison operator, Identifier Field defaults to fdl_comparison_type, and Identifier Value defaults to the fdl_comparison_type value.

3. If only one option among Add, Modify, and Delete is selected, you can leave Identifier Field empty, and the selected write method will take effect on all the read data.

Primary Key Mapping

For details, see the section "Primary Key Mapping" of this document.

1. Primary Key Mapping is displayed when Modify or Delete is selected in Write Method.

2. When Modify or Delete is selected in Write Method, Primary Key Mapping must be configured.

Best Practice

1. The Data Comparison operator is usually used in combination with the DB Table Output operator. For details, see Data Comparison Example.

2. The application scenario of a single Data Synchronization node includes:

• You want to add/modify/delete data based on the filtering result.

• You want to add/modify/delete data based on the identifier field and its value.

For details, see Adding/Modifying/Deleting Data Based on the Identifier Field.

Assume that null values exist in the D column of both the source table and the target table.

You have set Write Method to Write Data into Target Table Directly, selected fields A, B, C, and D as logical primary keys, and set Strategy for Primary Key Conflict to Overwrite Data in Target Table If Same Primary Key Value Exists, as shown in the following figure.

After execution, for primary key conflicts with not-null values, the source data overwrites the target data. (See the first record in the following figure.) For primary key conflicts with null values, the source data is appended to the target table without overwriting the existing data.