Dynamic Targets
A dynamic target is a target that can change at run time. You can configure flat files and relational targets to be dynamic.
When you run a mapping, a dynamic target can get metadata changes from physical data targets, including relational tables, flat files, and customized data objects. It can also generate columns based on the upstream column definitions.
You can configure dynamic run-time functionality for a target in the following ways:
- Get columns from the data source.
- When you expect small changes to the target, you can configure the Write transformation to get relational object columns at run time. When you configure the Write transformation to get metadata from targets, you can configure the Write transformation to update dynamically and remain synchronized with target objects.
- Define target columns based on the mapping flow.
- When you define columns based on the mapping flow, target columns are determined by upstream transformations.
- Define target columns based on the data object.
- When you define columns based on the data object, target columns are determined by the associated data object.
- Create or replace relational target tables at run time.
- By default, when you configure the Write transformation to create or replace the target at run time, the Data Integration Service creates the target based on the data object. You can also choose to create the target based on the mapping flow, or you can define a DDL query to create the target based on the query.
- Assign a parameter to determine the resource, table owner, or directory of a relational data object.
- When the relational targets are similar, you can assign a parameter to get the resource, connection, and table owner properties.
- Assign a parameter to determine the data object to use for a file or relational target.
You can create a customized data object as a Write transformation, and specify a parameter value as the target for the transformation. When you change the value of the parameter, the target changes for all objects that use the parameter.
The following table shows where you can configure the dynamic run-time functionality of a target:
Dynamic Run-time Target Functionality | Configuration |
|---|
Get columns from the data source. | Configure the Data Object tab on the Write transformation for the following target type: |
Define target columns based on the data object or the mapping flow. | Configure the Ports tab on the Write transformation for the following target types: |
Create or replace the table at run time. | Configure the Advanced tab on the physical data object for the following target type: |
Define a DDL query to create the target table at run time. | Configure the Advanced tab on the physical data object for the following target types: |
Assign a parameter to determine the connection, owner, or resource. | Configure the Run-Time tab on the Write transformation for the following target type: |
Assign a parameter to determine the data object. | Configure the Data Object tab on the Write transformation for the following target types: |
Get Columns from the Data Source
You can update the ports in a Write transformation at run time based on the structure of the relational data source.
Update the columns at run time if you expect small changes to the target columns. When you get the data object columns from the data source at run time, the Data Integration Service creates a run-time instance of the data object based on the structure of the target. It does not update metadata in the Model repository. To view the run-time instance of the data object in the Developer tool, view the mapping with resolved parameters.
Note: If you configure the Write transformation to get columns form the data source and to define targets based on mapping flow, the mapping fails.
Configure this functionality on the Data Object tab of the Write transformation for relational targets.
Define Targets Based on the Mapping Flow
When you define columns by mapping flow, target columns are determined by upstream transformations. When an upstream transformation changes the port order and metadata, the Write transformation picks up the changes.
You can retain keys when you create or replace the target if the key columns in the upstream transformation match the key column names in the target.
The following image shows how the Write transformation appears when you define target columns based on mapping flow:
Note: To avoid unexpected results, do not configure a run-time link to a Write transformation that defines targets based on the mapping flow.
Configure this functionality on the Ports tab of the Write transformation for flat file and relational targets.
Define Targets Based on the Data Object
You can configure the Write transformation to define target columns based on the associated data object.
When you define target columns based on the data object, the Write transformation contains dynamic and generated ports.
You can choose to create or replace the target at run time. You can retain target keys when you create or replace the target if the column names match. You can configure rules to ensure that column names match.
Configure this functionality on the Ports tab of the Write transformation for flat file and relational targets.
Create or Replace the Target at Run Time
At run time, the Data Integration Service can create or it can drop and replace the table. The Data Integration Service creates or replaces the table based on the mapping flow or the associated data object.
When you configure the Write transformation to create or replace the target, the Data Integration Service drops any existing target table associated with the write object and creates a table based on the configuration to use the data object or to use the mapping flow.
When the Data Integration Service creates a table based on the data object, the table contains columns that match the ports in the data object. If you create or replace the target at run time with a customized data object, the Data Integration Service creates a table with the name referenced in the data object connection.
When the Data Integration Service creates a table based on the mapping flow, the table contains columns that match generated ports in the Write transformation.
Configure this functionality on the Advanced tab of the data object.
Define a DDL Query
At run time, the Data Integration Service can create or replace relational and Hive target tables based on a DDL query that you define.
When you configure the Write transformation to create or replace the target, by default, the Data Integration Service drops any existing target table associated with the write object. The Data Integration Service then creates a table based on the mapping flow or data object.
If you want to customize the table or specify additional parameters such as partitions, you can define a DDL query based on which the Data Integration Service must create or replace the target table. The table contains the columns that you define in the DDL query.
The following image shows the DDL query field:
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 a port selector.
You must enclose the placeholders within two curly brackets. For example, {INFA_TABLE_NAME}.
Configure this functionality on the Advanced tab of the data object.
Rules and Guidelines for Creating or Replacing the Target at Run Time
Consider the following rules and guidelines when you create or replace the target at run time:
- •If the target table has a cyclic dependency among other tables in the database, the database cannot execute the command to drop or create the table, and the mapping fails.
- •When the Data Integration Service replaces a target, it does not retain the indexes and permissions for the target table.
- •If you do not configure the Write transormation to have dynamic ports, the Data Integration Service creates the target with linked and unlinked ports based on the data object. It writes data to the linked ports.
- •The Data Integration Service creates a table even if the the resource in the data object is a synonym or view. While each connection can point to different database instances, all the connections in a dynamic mapping must all be of the same database type.
Assign a Parameter to Relational Target Properties
To run a dynamic mapping with similar relational targets, you can assign a parameter the resource, connection, and table owner properties in the Write transformation.
Use a parameter for the resource to run a mapping with different but similar tables in the same database. When you use a parameter for the resource, you do not need to create a data object for each target. Use a parameter for the connection to access a different database.
You cannot parameterize these properties in the physical data object. When you create parameters for the properties in the Write transformation, you create mapping parameters.
By default, you create a connection type parameter for the connection. You configure a resource type parameter for the table name and a string parameter for the table owner.
Configure this functionality on the Run-Time tab of the Write transformation for a relational target.
Assign a Parameter to the Target Data Object
You can assign a parameter to a customized data object and change the source for the Write transformation at run time.
Parameterize the data object when you have a customized data object in the Model repository for more than one target data source. When you change the value of the parameter, the target changes for all objects that uses the parameter.
When you create a Write transformation from a customized data object, information about the data object appears on the Data Object tab of the transformation properties. You can click the data object name to view the definition from the Model repository. To parameterize the data object, create a resource type parameter or browse for a resource parameter. The parameter default value is the name of customized data object in the Model repository. When you create a default parameter value, you select a customized data object name from a list of data objects in the repository.
When you change the data object, the transformation ports change. You can view the ports on the Ports tab in the transformation properties.
The following table describes the parameter options on the Data Object tab:
Parameter Options | Description |
|---|
Parameter | The name of a resource parameter that you configured as the data object. Read-only. |
Description | The description of the parameter. Read-only. |
New | Create a resource parameter. Browse for and select a data object in the Model repository for the parameter default value. |
Browse | Browse for a resource parameter and select the parameter. |
Default Value | The default value of the resource parameter that you configured for the data object. The default value is a customized data object name. Read-only. |
Configure this functionality on the Data Object tab of the Write transformation for a relational target.
Rules and Guidelines for Dynamic Targets
Consider the following rules and guidelines when you work with dynamic targets:
- • When you preview a dynamic target, the Developer tool does not refresh the schema definition. If there is a mismatch due to changes in the schema such as a configuration to get columns from the data source or to replace the target at run time, the data preview fails. Manually synchronize the Read or Write transformation. If you continue to get an error, run the mapping to see the results.
- •If the dynamic target is too small for the incoming data, the mapping fails with a message indicated that the value is too large for the column.
- •The data types in the target table might be different from the data types in the Write transformation. When the Data Integration Service runs the mapping, it might change data types between upstream transformations and the target table.
