Flagging Rows Within a Mapping
Add an Update Strategy transformation to a mapping to flag individual rows for insert, update, delete, or reject.
Define an update strategy expression to test each row to see if it meets a particular condition. Then, assign each row a numeric code to flag the row for a particular database operation.
The following table lists the constants for each database operation and their numeric equivalent:
Operation | Constant | Numeric Value |
|---|
Insert | DD_INSERT | 0 |
Update | DD_UPDATE | 1 |
Delete | DD_DELETE | 2 |
Reject | DD_REJECT | 3 |
The Data Integration Service treats any other value as an insert.
Update Strategy Expressions
Enter an update strategy expression in the Expression Editor.
The update strategy expression uses the IIF or DECODE function from the transformation language to test each row. For example, the following IIF statement flags a row for reject if the entry date is after the apply date. Otherwise, the statement flags the row for update:
IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE)
You can configure parameters in an update strategy expression. Create parameters or browse parameters in the Expression Editor.
If the transformation is in a dynamic mapping, the generated fields in the transformation might change. You can parameterize the complete update strategy expression. If you use a parameter to define the expression, the Developer tool cannot validate the expression. An expression parameter cannot contain another parameter.
Update Strategy Transformation Advanced Properties
Configure advanced properties to help determine how the Data Integration Service processes data for the Update Strategy transformation.
You can define the following advanced properties for the Update Strategy transformation on the Advanced tab:
- Forward Rejected Rows
Determines whether the Update Strategy transformation passes rejected rows to the next transformation or drops rejected rows. By default, the Data Integration Service forwards rejected rows to the next transformation. The Data Integration Service flags the rows for reject and writes them to the reject file. If you do not select Forward Rejected Rows, the Data Integration Service drops rejected rows and writes them to the mapping log file.
- Use Hive Merge
- Determines whether the Update Strategy transformation uses Hive MERGE to perform updates on Hive targets when the mapping runs on Spark. When a query uses a MERGE statement instead of INSERT, UPDATE or DELETE statements, processing is more efficient.
The mapping ignores the Hive MERGE option and the Data Integration Service uses INSERT, UPDATE and DELETE to perform the operation under the following scenarios:
- - The mapping runs on Blaze or Hive.
- - In scenarios where MERGE is restricted by Hive implementation on particular Hadoop distributions.
The mapping log contains results of the operation, including whether restrictions affected results.
When the update affects partitioning or bucketing columns, updates to the columns are omitted.
Note: The Developer tool and the Data Integration Service do not validate against this restriction. If the Update Strategy expression violates these restrictions, the mapping might produce unexpected results.
- Tracing Level
- Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Aggregator and Update Strategy Transformations
When you connect Aggregator and Update Strategy transformations as part of the same pipeline, put the Aggregator before the Update Strategy transformation. In this order, the Data Integration Service performs the aggregate calculation, and then flags rows that contain the results of this calculation for insert, update, delete, or reject.
If you put the Update Strategy before the Aggregator transformation, you must consider how the Aggregator transformation handles rows flagged for different operations. In this order, the Data Integration Service flag rows for insert, update, delete, or reject before it performs the aggregate calculation. How you flag a row determines how the Aggregator transformation treats values in that row used in the calculation. For example, if you flag a row for delete and then use the row to calculate the sum, the Data Integration Service subtracts the value in this row. If you flag a row for reject and then use the row to calculate the sum, the Data Integration Service does not include the value in this row. If you flag a row for insert or update and then use the row to calculate the sum, the Data Integration Service adds the value in this row to the sum.