DB Table Output

Version

Function Description

• You can use the DB Table Output operator to output the data processed through a series of steps to the target database.

• You can use the DB Table Output operator with Data Comparison operator to add, delete, or update the increment.

• You can set the primary key if Target Table is set to Auto Created Table.

• You can output data to a table in the specified schema, or create a table in the specified schema to store the processed data.

1. Functions on the Data Destination and Mapping tab page and the Write Method tab page of DB Table Output are the same as those of Data Synchronization. For details, see Overview of Data Synchronization.

2. When adding, deleting, or modifying data using Data Comparison and DB Table Output, you can choose the data deletion method between Logical Deletion and Physical Deletion.

• Logical Deletion: Mark data as deleted without actually deleting it.

• Physical Deletion: Actually delete data.

For the specific effects of the two deletion methods, see Differences Between Physical Deletion and Logical Deletion.

Scene Simulation

You can download the example data:test_mubiao.xlstests.xls

Data in the source table tests is shown in the following figure.

Data in the target table is shown in the following figure.

Data with orderid values of 10345 and 10348 has been deleted from the source table. You want to delete the corresponding data in the target table as well.

Note:

Since Logical Deletion is to be used, you need to add an identifier column to the target table. The identifier column in this example is named fdl_comparison_type.

Configuring the Input Operator

This example uses the DB Table Input operator. Drag in two operators, and rename them Source Table and Target Table, respectively. Configure the Source Table operator first, as shown in the following figure.

Configure the Target Table operator, as shown in the following figure.

Configuring the Data Comparison Operator

1. Add a Data Comparison operator and connect it to the two input operators.

2. Select the logical primary key (orderid in this example), which is used to identify the same record in the source and target tables. You can skip the Comparison Field setting since this example requires marking the deleted data only, as shown in the following figure.

Note:

1. Inserts and deletions of fields not selected as Comparison Field can be detected, but any updates to these fields cannot be detected.

2. For details about using Data Comparison, see Data Comparison Example.

Click Comparison Result. You can see that the deleted data has been marked, as shown in the following figure.

Configuring the DB Table Output Operator

1. Add a DB Table Output operator and connect it to the Data Comparison operator.

2. Configure the target table and the write method, as shown in the following figure.

• Select Add/Modify/Delete Data Based on Identifier Field as Write Method, select Delete only, and select Logical Deletion.

• Set Identifier Field to fdl_comparison_type, which is the identifier column generated by default.

• Set Identifier Value of Delete to Removed, which should correspond to the value of Identifier in the Data Comparison operator.

• Select orderid as the logical primary key in Primary Key Mapping, which should be the same as that in the Data Comparison operator.

Note:

After logical deletion, the deleted data is marked in the identifier column. If you tick Perform Logical Deletion on Data Whose Identifier Value is Null Only, only the deleted data whose identifier value is null is marked. Otherwise, all deleted data is marked.

3. Click the Save button in the upper right corner.

Running the Task

Click Run in the upper right corner. The message indicating successful execution is displayed in Log if the task is successfully executed, as shown in the following figure.

You can see that the deleted data in the target table has been marked, as shown in the following figure.

Example Two: Physical Deletion

For details, see Data Comparison Example.