Using Data Flow Parameters

In Data Integration, the types of parameters you can use in data flows are user-defined or system-generated.

Data Integration generates the system parameters at runtime, such as SYS.TASK_START_TIME. The values of system parameters can be used in expressions to log system information. Currently, you can use system-generated parameters with the expression operator only.

User-defined parameters give you the flexibility to use tasks with different configurations of sources, targets, and transformations at design time and runtime. To use parameters, you start by creating parameters for operators in a data flow. For example, while designing a data flow, you use the Details tab of an operator's Properties panel to add and assign parameters for the following resource types:

Data asset (source and target)
Connection (source and target)
Schema or bucket (source and target)
Data entity (source and target)
Condition (filter, join, lookup, split)

With a data entity, instead of parameterizing the entire entity name, you can include one or more parameters in the data entity name. See About Parameters in Data Entity Names.

For data flow operators that use expressions in the configuration, such as the expression and aggregate operators, and the filter, join, lookup, and split operators, you can use parameters in the expressions (also known as scalar parameters). See About Expression Parameters.

When you use user-defined parameters and expression parameters in a data flow, you must specify default values for the parameters. Then when you create an integration task for the parameterized data flow, you can change one or more of those default values on the Configure Parameters page, or you can change the values at runtime. If you don't change a parameter during design time or at runtime, the default value set in the data flow is used when you run the task.

Parameters can also be assigned to resources in tasks such as SQL, OCI Data Flow, and REST tasks. To use parameters for the source and target resources in a data loader task, see Using Parameters for Source and Target.

When you add a parameterized task to a pipeline, you can reconfigure any of the values of the incoming parameters that are defined at the task level or at the associated data flow level. You can also reconfigure expression parameter values. See Using Parameters in Pipelines.

Considerations When Using Parameters

Using parameters keeps data integration resources and values from being permanently bound to an integration task. If you don't assign parameters, then the chosen resources and values are used each time you run the task. Parameters give you the flexibility to reuse a data flow design with different data integration resources and values. To change values at design time, you use the Parameters panel or the Configure Parameters page when you configure the data flow in an integration task.

Although parameters are designed to give you flexibility when you run the data flow in an integration task, it can quickly become complex and confusing. For example, when you add a source operator to a data flow, you select the data asset, connection, schema, and then the data entity that serves as the data flow's source of data. These four values are the operator's default values. Parameters can be assigned for each of these values. If you assign parameters, you need to take special care and pay close attention to the values you assign.

Suppose you assign parameters to the data entity and data asset values, but not the connection nor schema values, then you risk running into errors when you change a parameter value. For example, if you change the data asset value, the new data asset might not have the same connection, schema, or data entity values you originally set. Data Integration warns you when you change a value, but you must review the relationships yourself and ensure that the data flow still works with the changed values. While designing the data flow, within an integration task, and when you run the task, be mindful of the parameter values everywhere.

Note

Parameters are local to the data flow you add them for, so you cannot share or reuse parameters across data flows.

Also be mindful of cases where you add a parameter for a new target data entity. New data entities are shapeless, meaning they don't contain any attributes yet. To avoid encountering errors at design time or runtime, be careful how you reuse parameters that are assigned for new objects. For example, don't assign the parameter whose default value is a new target data entity to a source data entity or an existing target data entity.

About Expression Parameters

Parameters in expressions give you the flexibility to reuse expressions with different values at design time or runtime.

You add parameters to expressions at the data flow level when you configure operators that use expressions. Expression parameters (also known as scalar parameters) are applicable to filter, join, lookup, split, expression, and aggregate operators. Depending on the operator, you use either the Expression Builder or Condition builder to add expression parameters.

The expression parameters you add to an operator are saved in the data flow, and are available to other applicable operators within the data flow.

An expression parameter has a name and a default value, and is associated with a data type. The following types are supported:

NUMERIC
VARCHAR
DATE
DATETIME
DECIMAL
DOUBLE
FLOAT
INTEGER
TIMESTAMP
TINYINT
BOOLEAN
LONG
STRING

After you create an expression parameter, you cannot change the name or type, or any other properties the parameter might have depending on the type. Only the default value can be modified. You can change the default value at design time and runtime.

For example, consider the following expression for a condition filter in a data flow:

FILTER_1.COUNTRIES.CITY='Redwood Shores'

Instead of specifying an explicit value for CITY, you can use a parameter in the expression. Suppose you create a VARCHAR expression parameter, say P_VARCHAR, and set the default value to Redwood Shores. Then you can edit the condition filter to use the expression parameter:

FILTER_1.COUNTRIES.CITY=$P_VARCHAR

At design time, you can change the default value of P_VARCHAR when you create an integration task that uses that data flow. Then at runtime, you can use the default value set in the integration task or you can edit the value.

All expression parameters that you add to a data flow are available in the Parameters panel.

About System Parameters

A system parameter has a name and type. You can bind the value of a system parameter but you cannot modify the value.

When adding an expression to an expression operator in a data flow, you use the Parameters tab in the Expression Builder to see the list of system parameters. You can use system parameters in expressions to log system information.

The following table shows the system parameters that you can use.


Name	Type	Description
SYS.TASK_START_TIME	TIMESTAMP	Task start time in UTC.
SYS.TASK_RUN_KEY	OCID	Internal identifier of the task run.
SYS.TASK_RUN_NAME	STRING	Name of the task run.
SYS.TASK_SCHEDULE_TRIGGER_TIME	TIMESTAMP	The trigger time for the schedule (for a task run that is initiated from a schedule). The scheduled time might be different from the task start time.
SYS.TASK_SCHEDULE_TIMEZONE	STRING	The time zone corresponding to the scheduled time, which represents the time zone time as defined in the task schedule.
SYS.RETRY_ATTEMPT	INTEGER	The number of run attempts of a task that's configured for retries when a run fails.

Note: An OCID value is a STRING.

About Parameters in Data Entity Names

When configuring a source or target operator in a data flow, you can include one or more parameters in the data entity name, using the syntax ${PARAMETER_NAME}.

A parameter name is case-sensitive, and each parameter you use must have a default value.

For example, CUSTOMERS_${COUNTRY} might return the database table CUSTOMERS_USA, and BANK_${COUNTRY}/* might return the Object Storage files in BANK_EU.

To use parameters in data entity names, you can add the parameter to the data flow before you add the data operator. You can also add the parameter at the time you're configuring the data entity for the operator.

To add parameters to a data flow, in the data flow, select Parameters from the View menu on the canvas toolbar to open the Parameters panel.

In the parameters panel, click Config and then click Add.

In the Add parameter panel, configure a parameter of the appropriate data type, for example VARCHAR or NUMERIC, and add a default value.
To add a parameter at the time of data entity configuration, in the Browse data entities panel:
- From the More actions menu, select Add data flow parameter.
- In the search field, start typing ${ followed by any character. If the front part of the parameter name matches existing parameters in the data flow, a list of suggested names displays. To select a parameter from the list, click the parameter name, complete the syntax by adding }, then press Enter.
- In the search field, enter the parameter name. If the parameter name does not yet exist in the data flow, and you press Enter, Data Integration displays the Add data flow parameter panel. Alternatively, after entering the parameter name, select Add data flow parameter from the More actions menu.
  
  In the Add data flow parameter panel, specify the data type, default value, and other properties for the parameter you want to add and use.
For Object Storage on a source operator only: When configuring the data entity on a source operator that uses an Object Storage data asset, you can use parameters and file patterns. In the Browse data entities by pattern panel, enter an expression using parameters and patterns to select one or more entities. For example: ADDRESSES_${STATE}/*

Note

For a data entity that includes a data flow parameter in the name, you cannot bind the entity resource to another parameter. The Assign parameter option is unavailable.

About the Parameters Panel

The Parameters panel in a data flow shows the parameters that have been defined in the entire data flow.

Use the Parameters panel to view:

All the parameters created for data assets, connections, schemas or buckets, and data entities used in the data flow.
All the parameters created for shaping operators such as filter, join, and lookup.
All the expression parameters created for expression and aggregate operators, and shaping operators such as filter, join, lookup, and split.
All the data flow parameters created for data entity names in a data flow.

To access the Parameters panel when the data flow is opened in the designer canvas, select Parameters from the View menu on the canvas toolbar.

The Parameters panel has two tabs, View and Config. In either tab, you can search the list by entering the full name of a parameter.

On the View tab, you can:

See the parameters that have been created in the data flow, regardless of whether a parameter is assigned to an object. A parameter is assigned when you see a value next to Used in.
Collapse and expand the details of each parameter.
Click the Used in link for an assigned parameter. The operator referencing that parameter is brought into focus on the canvas and in the Properties panel (Details tab).

On the Config tab, you can:

Add expression parameters and data flow parameters. Click Add, then use the Add parameter panel that displays to define the parameter.
Change the default value of a parameter. From the Actions menu () for the parameter, select Edit to use the Edit parameter panel to edit the value.
Delete one or more parameters. See Deleting a Parameter.

Adding a Parameter

You add and define parameters on operators associated with data sources in a data flow.

A parameter in a data flow can be reused to represent the default value for another resource of the same type within the same data flow.

How you define a parameter depends on the type of resource you're adding the parameter to. For all parameter types, you provide a name and an optional description, and set a default value for the parameter. You cannot change the name and description after the parameter is created.

Note

The following procedure does not apply to adding parameters in expressions and conditions. See Adding an Expression Parameter. To add system parameters to the expression operator, see Adding and configuring an expression operator.

To add a parameter to a data flow:

On the canvas of a data flow, select the operator for the resource type that you want to add a parameter.
For example, select a source or target operator to add a parameter for a data asset, connection, schema, or data entity. To add a parameter for a condition, select a filter, join, or lookup operator.
On the Details tab of the Properties panel, click Assign parameter next to the resource (for example, Data Asset or Filter Condition).
On the Assign parameter page, click Add parameter.
In the Add parameter panel, enter a name for the parameter in the Identifier field, or use the default value.
(Optional) Enter a Description to help identify the purpose of the parameter to other users.
The Type field shows the resource type for which you're adding the parameter. You cannot change the type.
Set the default value for the parameter.
How you set the default value depends on the resource type. For example, for a data entity, you use menus to select a data asset, connection, and schema, and then you select the entity.
Click Add.
You're returned to the Assign parameter page.
You can repeat steps 3 through 8 to add more parameters of the same resource type, if needed.

Viewing System and User-Defined Parameters

You can view the system parameters that are available in a data flow at runtime, and the user-defined parameters that you have created.

For user-defined parameters, the list includes assigned and unused parameters at the data flow level for data assets, connections, schemas or buckets, and data entities. For assigned parameters, you can see where the parameter is used in the data flow.

The user-defined list also includes used and unused expression parameters. Expression parameters are parameters that are added at the data flow level using the Condition Builder (filter, join, lookup, and split operators) or Expression Builder (expression and aggregate operators).

Use the Parameters tab on the Properties panel of the data flow to view the system and user-defined parameters.

On the designer canvas of a data flow, click any blank area.
In the Properties panel for the data flow, click Parameters.
Select User defined or System defined.

Currently, you can use system parameters with the expression operator only.
Review the lists.
The Used in column indicates where the parameter is used in the data flow.

Assigning a Parameter

You can assign parameters to different resource types, such as data entities in source and target operators, and conditions in filter, join, and lookup operators.

In a data flow, you can select from existing parameters, or you can add and then assign a parameter.

You use the Details tab of the Properties panel of a data flow operator to assign parameters. The following procedure can be used to assign parameters to source, target, filter, join, and lookup operators.

To assign a parameter for an operator in a data flow:

On the canvas of a data flow, select the operator for the resource type that you want to assign a parameter.
For example, select a source or target operator to add a parameter for a data asset, connection, schema, or data entity. To add a parameter for a condition, select a filter, join, or lookup operator.
On the Details tab of the Properties panel, click Assign parameter next to the resource (for example, Data Asset or Filter Condition).
On the Assign parameter page, review the list of existing parameters, if any.
Only parameters of the same resource type appear in the list.
To assign a parameter from the existing list, select the parameter and click Assign.
To create a parameter and then assign, click Add parameter. Define the parameter in the Add parameter panel:
1. Enter a name in the Identifier field, or use the default value.
2. (Optional) Enter a Description to help identify the purpose of the parameter to other users.
3. The Type field shows the resource type for which you're adding the parameter. You cannot change the type.
4. Set the default value for the parameter.
  How you set the default value depends on the resource type. For example, for a data entity, you use menus to select a data asset, connection, and schema, and then you select the entity.
5. Click Add.
  You're returned to the Assign parameter page. The parameter you added is selected by default.
6. Click Assign.

Editing a Parameter

You edit the default value of an assigned parameter in the data flow where the parameter is used.

You can also change the parameter value when you create an integration task using the data flow, or when you run the task. The following procedure describes how to edit a parameter value while designing the data flow.

Note

The following procedure does not apply to editing expression parameters in expressions or conditions.

To edit an assigned parameter:

On the canvas of a data flow, select the operator whose parameter value you want to edit.
On the Details tab of the Properties panel, for the parameter resource type you want to edit, click Edit parameter.
In the Edit parameter panel, edit the fields.
When editing values for data asset, connection, schema, and data entity resource types, you must select the values in the order as shown in the panel.
Click Save changes.

Note

When editing the parameter for a data asset, connection, schema, or data entity resource, ensure that you maintain the proper relationships between the values. For example, if you change the schema value, check that the new schema value contains the data entity you originally selected. If the edited value does not contain the original data entity, you risk running into errors with the data flow at runtime. Data Integration warns you of potential issues, but you're responsible for setting the proper values for the parameters.

Removing a Parameter

Removing a parameter from an operator removes only the association of the operator to the parameter.

The parameter is not deleted from the data flow when you remove a parameter. You can still use the parameter for another operator of the same resource type.

When you remove a parameter, the value that was assigned to the parameter becomes the default value of the operator.

Note

The following procedure does not apply to removing or deleting expression parameters from expressions or conditions.

To remove an assigned parameter:

On the canvas of a data flow, select the operator whose parameter you want to remove.
On the Details tab of the Properties panel, for the parameter resource type you want to remove, click Remove parameter.
In the Remove parameter dialog box, review the list of operators that are impacted if you remove this parameter, and then click Remove.

Deleting a Parameter

When you delete a parameter from a data flow, the parameter is no longer available for you to use in the data flow.

If the parameter you delete is associated with an operator in the data flow, the value assigned to the parameter then becomes the operator's default value.

To delete a parameter:

On the canvas of a data flow, select Parameters from the View menu on the canvas toolbar.
The View tab in the Parameters panel shows all the parameters that are created in the data flow. If a parameter is assigned to a resource in the data flow, the Used in link shows the name of the operator where the parameter is used.
In the Parameters panel, select Config.
Select one or more parameters you want to delete, and then click Delete.
Caution

By deleting a parameter, you're also removing all associations to the parameter. The value assigned to the parameter then becomes the operator's default value.
In the Delete parameter dialog box, review the list of operators that are impacted if you delete this parameter.
Click Delete.

Adding an Expression Parameter

You use the Expression Builder or Condition Builder to add expression parameters in a data flow.

An expression parameter has a name, type, and default value. Only the default value can be changed after the parameter is created.

To add an expression parameter:

On the canvas of a data flow, select the operator that you want to add or edit an expression.
For example, select a filter operator or an expression operator.
Depending on the operator you selected, do one of the following:
- For an expression or aggregate operator, click Add expression or select Edit from the Actions menu () for an existing expression.
- For a filter, join, or lookup operator, click Create or Edit .
- For a split operator, click Add or Edit from the Actions menu () for an existing condition.
In the add or edit panel that displays, do the following:
1. In the builder section, click Parameters.
2. Click Add.
3. In the Add parameter panel, enter a name for the parameter in the Identifier field.
4. (Optional) Enter a Description to help identify the purpose of the parameter to other users.
5. From the Data type menu, select a type for this parameter.
6. Set the Default value for the parameter.
  
  How you set the default value depends on the data type. For example, a Varchar type has the Length field.
7. Click Add.
  The parameter is added to the Parameters list.
8. Repeat the steps to create more expression parameters, if needed.

The expression parameters you add are saved in the data flow. You can view and edit the default values of expression parameters in the Parameters panel or the Parameters tab on the Properties panel of the data flow.

Editing an Expression Parameter

You can edit the default values of expression parameters only. You cannot modify the name, type, or other type-specific properties of an expression parameter.

You can use the Parameters panel or the Parameters tab on the Properties panel of the data flow to edit the default values.

The following procedure uses the Parameters tab on the Properties panel of the data flow.

On the canvas of a data flow, click any blank area.
In the Properties panel for the data flow, click Parameters.
Click User defined.
Review the parameters listed by name.

You can search the list by entering the full name of a parameter.
In the row of the expression parameter you want to change, select Edit from the Actions menu ().
In the Edit parameter panel, change the Value of the expression parameter.
Click Save.

Oracle Cloud Infrastructure Documentation