Update Strategy Transformation Overview
The Update Strategy transformation is an active transformation that flags a row for insert, update, delete, or reject. Use an Update Strategy transformation to control changes to existing rows in a target based on a condition that you apply.
As an active transformation, the Update Strategy transformation might change the number of rows passed through it. The Update Strategy transformation tests each row to see if it meets a particular condition, and then flags the row accordingly. The transformation passes rows that it flags for insert, update, or delete to the next transformation. You can configure the transformation to pass rows flagged for reject to the next transformation or to drop rows flagged for reject.
For example, you might use the Update Strategy transformation to flag all customer rows for update when the mailing address has changed. Or, you might flag all employee rows for reject for people who no longer work for the organization.
Note: The Update Strategy transformation is supported only on the Hadoop distributions that support Hive ACID.
Setting the Update Strategy
To define an update strategy, complete the following steps:
- 1. To control how rows are flagged for insert, update, delete, or reject within a mapping, add an Update Strategy transformation to the mapping. Use an Update Strategy transformation to flag rows destined for the same target for different database operations, or to reject rows.
- 2. Define insert, update, and delete options for individual targets when you configure the mapping. On a target-by-target basis, you can allow or disallow inserts and deletes for all rows flagged for insert or delete. You can choose different ways to handle updates for all rows flagged for update.
- 3. If the mapping runs on the Spark run-time engine, you can choose Use Hive Merge in Update Strategy transformation advanced properties. When a query uses a MERGE statement instead of INSERT, UPDATE or DELETE statements, processing is usually more efficient.