Configuring a Dynamic Target
You configure a Write transformation to receive columns from the target at run time when target metadata changes. Optionally, specify a parameter as the target data object to enable the assignment of different values. You can also specify whether the Write transformation uses an associated object or a mapping flow for port definitions.
When you configure the Write transformation for a dynamic mapping, you can use one or more of the following methods:
- Use a parameter as a target
- Specify a parameter as the underlying data object for the target to enable you to change the schema for the Write transformation through the parameter.
- Get data object columns from the target at run time
- Enable the option to get data object columns from the target at run time to dynamically update Write transformation ports with changes in the target schema.
- Define a DDL query to create or replace the target at run time
When you choose to create or replace the target at run time, you can define a DDL query to create the target based on the query that you define. You can define a DDL query for relational and Hive targets.
- Define Write transformation ports from mapping flow
- When you choose to define ports from mapping flow, the Data Integration Service defines Write transformation ports based on upstream column definitions. Target columns update dynamically at run time.
Using a Parameter as a Target for a Dynamic Mapping
You can use a parameter as the data object for the transformation and then change the parameter at run time.
1. Select the Write transformation in the mapping editor.
2. In the Properties view, click the Data Object tab.
3. Select Parameter in the Specify by list.
4. Select one of the following options:
- - Click New to create a parameter. Name the parameter, and then browse to select a default value for the parameter.
- - Click Browse to select an existing parameter.
The following image shows a transformation with a parameter as a data source:
Getting Target Object Columns from the Data Source at Run-Time
You can enable the option to get data object columns from the data source at run-time.
When you select the option to get data object columns from the data source at run time, the mapping fetches the data object columns to the transformation when the mapping runs. If the data source columns and metadata have changed, then the mapping fetches the changed information.
1. In the Properties view, click the Data Object tab.
2. Select At run time, get data object columns from the data source.
Defining a DDL Query to Create or Replace the Target at Run Time
When you choose to create or replace the target at run time, you can define a DDL query based on which the Data Integration Service must create or replace the target table at run time. You can define a DDL query for relational and Hive targets. You can enter placeholders and parameters in the DDL query.
1. In the Properties view, click the Advanced tab.
2. Select the Create or replace table at runtime option.
The DDL query field is available.
3. Click Edit.
The DDL query dialog box appears.
4. Enter the DDL query in the editor.
You can enter placeholders in the DDL query. The Data Integration Service substitutes the placeholders with the actual values at run time. For example, if a table contains 50 columns, instead of entering all the column names in the DDL query, you can enter a placeholder.
You can enter the following placeholders in the DDL query:
- - INFA_TABLE_NAME. Fetches the target table name at run time.
- - INFA_COLUMN_LIST. Fetches a list of columns in the target table at run time.
- - INFA_PORT_SELECTOR. Adds port selectors.
Note: The placeholder names are case sensitive. You must enclose the placeholders within two curly brackets. For example, {INFA_TABLE_NAME}.
You can also perform the following steps to define the DDL query.
- - To add a column name, double-click a column in the Columns tab.
- - To define a parameter, click the Parameters tab, and double-click a parameter name. You can also click Manage Parameters to add, edit, or delete parameters.
- - To configure a port selector, click the Port Selector tab, and double-click a port selector. You can also click New to configure a new port selector.
The following image shows a DDL query to create a Hive target table:
The DDL query in the image contains the INFA_TABLE_NAME, INFA_COLUMN_LIST, and INFA_PORT_SELECTOR placeholders. It also contains a parameter to define the storage format.
If you do not enter a DDL query, the Data Integration Service creates the target based on the mapping flow or data object.
5. Click OK to save the DDL query.
Defining Write Transformation Ports
Define target object columns by mapping flow to enable upstream mapping objects to update the incoming ports for the Write transformation.
1. In the Properties view, click the Ports tab.
2. Select Columns defined by: Mapping flow.
The following image shows the Ports tab populated with ports defined by the associated data object:
3. Enable dynamic ports and targets:
- a. Drag upstream ports to the input pane of the Write transformation.
The target gets column definitions from upstream mapping objects.
- b. In the Properties view, click the Advanced tab.
- c. Select Create or replace table at run-time.
The following image shows the
Create or replace table at run-time option in the Advanced tab of the target object:
At run-time, the Data Integration Service creates, or drops and replaces, the target table.
Note: When a mapping contains multiple targets whose columns are defined by the same physical data object, enable the Create and replace table at run-time option for only one of the targets. If you enable this option for more than one target, the metadata of the table that the mapping creates would match only one of the targets, and the mapping fails.
