Where to Assign Parameters
You can assign user-defined parameters and system parameters to fields. You must create the user-defined parameters before you can assign them to fields.
You can parameterize some properties in objects and transformations. If you assign a parameter to a property, the option to assign a parameter appears when you configure the property value.
For example, you can create a Read transformation in a mapping. The Read transformation is a non-reusable transformation that you create based on a physical data object. You can assign parameters to the transformation properties, or you can assign parameters to the physical data object.
You cannot nest user-defined parameters within a parameterized source. If the source data object is parameterized, you cannot expose a user-defined parameter as a mapping parameter to override the parameter values at run time. The mapping uses the default value instead.
The following table lists the objects and the fields where you can assign parameters:
Object | Field |
|---|
All transformations | Link resolution order |
Association transformation | Cache file directory Cache file size |
Address Validator transformation | Casing style Default country Geocode data type Global maximum field length Line separator Maximum result count Optimization level Standardize invalid addresses |
Aggregator transformation | Cache directory Expression elements. Not the full expression. Group By |
Bad Record Exception transformation | Lower Threshold Upper Threshold |
Case Converter transformation | Reference table. |
Consolidation transformation | Cache file directory Cache file size |
Customized data object | Connection Data object Owner SQL Query elements Table name |
Customized data object read operation | Custom query Filter condition Join condition PreSQL PostSQL |
Customized data object write operation | PreSQL PostSQL Update override |
Decision transformation | Decision script. |
Duplicate Record Exception transformation | Cache file directory Lower Threshold Upper Threshold |
Expression transformation | Expression elements. Not the full expression. Port selector Sort key list. Windowing only. |
Filter transformation | Filter condition elements Filter condition. Full expression. |
Flat file data object | Compression codec Compression format Control file directory Control file name Connection name Default scale Flat file delimiter Merge file directory Source file directory Source file name Output file name Output file directory Reject file directory Target directory |
Joiner transformation | Cache directory Join condition elements Port selector |
Key Generator transformation | Cache file directory Cache file size |
Labeler transformation | Reference table |
Lookup transformation | Custom query. Relational only. |
Lookup transformation, excluding the physical data objects for the lookup source | Data object. Nonreusable transformation. Dynamic port rules. Nonreusable transformation. Lookup condition. Full expression, nonreusable transformation. Port selector. Nonreusable transformation. |
Mapping | Hive version Run-time environment Maximum parallelism |
Match transformation | Cache directory on the Match Output tab Cache directory on the Match Type tab Index directory on the Match Type tab Persistence method Threshold |
Nonrelational data object | Connection |
Rank transformation | Cache directory Expression elements. Not the full expression. Group by ports Rank port |
Read transformation | Connection Custom query. Relational only. Data object Filter condition. Relational only. Join condition. Relational only. Owner name. Relational only. PreSQL. Relational only. PostSQL. Relational only. Resource/table name. Relational only. |
Relational Data Object | Filter condition elements Join condition elements PreSQL query elements PostSQL query elements SQL override elements |
Router transformation | Group filter condition elements. Group filter condition. Full expression. |
Sorter transformation | Sort key Group by Work directory |
SQL transformation | Connection |
Standardizer transformation | Reference table |
Token Parser transformation | Reference table |
Update Strategy transformation | Update strategy expression elements. Update strategy expression. Full expression. |
Write transformation | Data object Link resolution order PreSQL. Relational only. PostSQL. Relational only. Reject directory Reject file name Update override. Relational only. |
Parameters for Compression Formats
You can assign a parameter to the compression format and the compression codec for an HDFS flat file target.
You must assign a parameter to the compression format before you can assign a parameter to the compression codec. If you assign a parameter to the compression format, you must assign a parameter to the compression codec.
The following image shows the advanced properties for an HDFS flat file where you can configure the compression format and the compression codec:
The following image shows the dialog box that you can use to assign a parameter to the compression format and the compression codec:
Parameters in Custom Queries for Hive Sources
When you use a string parameter in a SQL override, a join expression, or a filter query for a Hive source, you need to add quotes around the parameter reference if the parameter represents a literal value. You can use single or double quotes. This requirement is for Hive sources in mappings that run in the native execution environment or in the Hadoop execution environment.
For example, you need to create a filter that selects Hive source rows with a specific department name. You create a string parameter that represents the department name. You assign a default value of R&D for the department name parameter.
The following image shows the string parameter:
When you use the parameter in a filter query for a Hive source, you must include quotes around the parameter name. Otherwise the mapping fails at run time with a SQL parser error.
The following image shows the filter query for the Hive source on the Query view of the Properties tab:
Note: By default, the Expression editor does not add the quotes around the parameter. You must manually add them.
You do not need to add single or double quotes around the parameter name if the parameter contains a column name or a sub query name.
The following image show a string parameter with a default value that is a column name:
The following image shows a filter query that uses the parameter:
Parameters in Custom Queries for Relational Sources
You can assign a parameter to the custom query for a customized data object read operation, or a relational data object in a Read transformation.
When you configure the custom query, you must create an advanced query and specify the query using a parameter.
The following image shows where you can parameterize the custom query:
Parameters in Expressions
You can configure parameters in expressions or conditions in transformations, such as the Aggregator transformation, Lookup transformation, Expression transformation, and the Filter transformation.
For example, configure a filter condition in the Filter transformation. Choose the ports and the parameters to include in the condition. Select the system parameters or the user-defined parameters to include in the filter condition.
The following image shows a Filter condition that includes the Employee port and the Employee_Filter parameter:
You can use parameters in expressions in the same arguments that accept port names as arguments. You cannot use a parameter to replace a constant argument in an expression.
For example, consider the TO_DECIMAL expression that converts a string to a decimal value:
TO_DECIMAL( value [, scale] )
The scale argument must be a constant value in the expression.
The following valid expression contains a constant argument for scale:
TO_DECIMAL( Input_Port,10 )
The following expression is not valid because it contains a user-defined parameter for the scale argument :
TO_DECIMAL( Input_Port,$Scale_Param )
A parameter cannot contain another parameter. For example, if you configure Parameter1 and Parameter2 in a transformation, you cannot set the default value of Parameter1 to $Parameter2. If you nest the parameters, the mapping fails with a validation error at runtime.
Expression Parameters
You can configure an expression parameter type. An expression parameter is a parameter that contains a complete expression. You can use an expression parameter in a Filter transformation and a Lookup transformation.
Define an expression parameter in the Expression Editor. Select Specify by Parameter to indicate that the complete expression is parameterized.
The following image shows the Specify by Parameter option for the filter condition:
When you use an expression parameter, you can create the expression parameter or you can select an existing expression parameter to use in the transformation. An expression parameter can contain ports, operators, and constants. It cannot contain other parameters.
For example, in a Filter transformation you might create a filter expression parameter with the following default value: EmployeeID > 100. In the mapping, you might create a different expression parameter with the following default value: Dept < 2000. If you bind the mapping parameter to the transformation parameter, you can override the mapping expression parameter at run time. You might create expression parameters with different port names and operators for dynamic mappings.
Parameters for Fields and Property Values
You can configure parameters for some field or property values in transformations and physical data objects.
You can configure connection names for relational data objects, customized data objects, and Lookup transformations. In a flat file data object, you can configure parameters for input and output file directories and the reject file directory. You can also configure a parameter to change the flat file delimiter type.
The following image shows the parameter for the flat file delimiter on the Advanced tab of the physical data object:
Parameters for Relational Table Resources
You can parameterize the resource name, the table owner, and the connection for a Read transformation. The resource is the table, view, or synonym name of the relational data object.
You might parameterize the resource name if you need to process multiple tables from the same database in a dynamic mapping.
Select the Read transformation in the mapping. In the Run-time tab of the Properties view, click the Value column to assign a parameter for a connection, table owner, or resource.
The following image shows where to assign the parameters for the connection, the resource name, and the table owner for the Read transformation:
Parameters in SQL Statements
You can include parameters in SQL statements that you add to relational data objects, customized data objects, or to Read and Lookup transformations that use relational or customized data objects.
The following image shows how you can parameterize an SQL query that reads from a relational source:
Parameters in Filter and Join Conditions
You can assign a parameter to the SQL statement that you add to the filter or join condition. You can assign the parameter in a customized data object read operation; or in a relational data object in a Read transformation.
The following image shows where you can assign a SQL parameter to a filter or join condition:
When you edit the join or the filter condition, you can use a parameter to specify the condition.
For example, the following image shows the dialog box that you can use to assign a parameter to the SQL statement when you edit the filter condition:
String Parameters in SQL Statements
When you define a string parameter in an SQL statement, you must add single quotes (' ') to the parameter in the query. If single quotes are part of the default value instead of the query, the Data Integration Service escapes the data in each parameter with single quotes and adds additional single quotes for every single quote that appears in the default value.
Example - Single Quotes in the Query
For example, you have an SQL statement with a date parameter $date_parm. The SQL statement appears as the following expression:
select * from <table_name> where <date_port> > $date_parm
Since the parameter $date_parm is a string, add single quotes to the parameter in the query. The following expression shows the query where single quotes are added to the parameter:
select * from <table_name> where <date_port> > '$date_parm'
If the default value of the parameter $date_parm is 01/31/2000 00:00:00, the following expression shows how the Data Integration Service expands the query:
select * from <table_name> where <date_port> > '01/31/2000 00:00:00'
Example - Single Quotes in the Default Value
You use the same SQL statement with a date parameter $date_parm. The SQL statement appears as the following expression:
select * from <table_name> where <date_port> > $date_parm
You add single quotes to the default value '01/31/2000 00:00:00'. The following expression shows the expanded query:
select * from <table_name> where <date_port> > ''01/31/2000 00:00:00''
Since single quotes are part of the default value, the Data Integration Service escapes the data with additional single quotes. Because the string parameter contains double quotes in the expanded query, the query might fail or produce no result.
Tips for Using Parameters in SQL Statements
You can use tips to use parameters in SQL statements more effectively.
- •For a string parameter, use single quotes when you define the parameter in the query.
- •Do not use single quotes for a parameter if the parameter is not a string. You might get unexpected results.
- •For a string parameter, if the SQL query and the default value of the parameter do not have single quotes, you can add an upstream Filter transformation in the mapping. In the Filter transformation, edit the filter condition to include single quotes around the parameter.
- •A parameter name cannot contain a period (.) . An SQL query is not valid if it has a parameter that contains a period. For example, the following SQL statement has a parameter name that contains a period:
SELECT $tname.ID,"MY_SOURCE"."NAME" FROM "MY_SOURCE" where FIELDX=1
When you validate the query, the Data Integration Service returns an error that it cannot find the tname.ID parameter.
Parameters for Port Lists
You can create parameters that contain lists of ports. You can reference these parameters in transformations such as the Sorter transformation, Rank transformation, Joiner transformation, and Expression transformation.
You can configure the following types of parameters that contain multiple port names:
- Port list
- A list of port names separated by commas. A port list parameter has the following syntax: Port1,Port2,Port3
- Sort list
- A list of port names and the sort type for each port. The sort list parameter has the following syntax: Port1:A,Port2:A,Port3:D
- Input linkset
- A set of ports to link at run time. The link set parameter contains name-value pairs in the following syntax: Port1>:=Port2, Port3>:=Port4
