Overview
In this article, we will be using high watermark change replication for incremental synchronization between two databases.
Process
Step 1. Create source and destination database Connections.
Step 2. Start creating a change replication Flow in the Flows
window by clicking +
, and typing in change replication
. Select Change replication using high watermark
.
Step 3. Continue by defining Flow transformations, Mapping, and parameters as you would for any other Flow where:
Step 4. When configuring parameters for transformations, set the High Watermark Field
and enable change replication
.
Since we will be using a High Watermark Field
in the WHERE
clause, it is important to have an index for this field in the source database unless your table is very small.
It is recommended to use the SQL query to calculate the value of the High Watermark Field
as explained in this article.
Step 5. Optionally, define the Source query
.
Typically the extract SQL used for change replication
is generated automatically. If you have entered a custom SQL in the Source query
field, Etlworks will be able to automatically modify it by adding a condition (in a WHERE
clause) for the High Watermark Field
.
Step 6. If needed, you can configure Etlworks to perform MERGE
(UPSERT) on the destination database (the default is INSERT
):
1. On the Parameters
screen, set the Action
to MERGE
. MERGE
is supported for Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase databases. This action can also be conditional, which requires entering conditions using JavaScript in the Action Conditions fields.
2. Set the Lookup
fields which is a comma-separated list of fields used to uniquely identify a record for the MERGE
action.
For PostgreSQL and MySQL, there must be a unique index that includes all Lookup
fields.
3. If needed, specify Action Conditions. When a conditional action
is selected, use this field to enter a JavaScript expression that identifies which action (INSERT
, UPDATE
, DELETE
, MERGE
) should actually be used. You can reference field values from the current source record as {"field name"}
, as in this example:
-
{Types}.equals('U') ? 'update' : 'insert';
Comments
0 comments
Please sign in to leave a comment.