Semarchy Lookup enricher

The Semarchy Lookup enricher performs a data lookup on a mapping table.

Plugin ID

Semarchy Lookup Enricher - com.semarchy.engine.plugins.convergence.text

Description

This enricher performs a data lookup on a mapping table accessed via a datasource.

The mapping table is located in a datasource provided using the Datasource parameter, which defaults to the data location’s datasource. The mapping table is declared to the enricher:

  • By giving a Mapping Table as well as a Lookup Column and a list of (up to 20) Output Columns from this table. The input lookup value is searched in the Lookup Column and the corresponding values from the Output Columns are returned.

  • By giving a Custom SQL select statement executed on the datasource, which must return columns aliased LOOKUP_COLUMN and OUTPUT_COLUMN1, …​, OUTPUT_COLUMN20. These columns will be used as the lookup and output columns.

You must either set Mapping Table, Lookup Column and Output Columns, or only set Custom SQL. The Mapping Table, Lookup Column, and Output Columns parameters are mandatory unless the Custom SQL parameter is set instead.

The lookup is performed on the mapping table with an optional memory cache configured with the Cache Lookup Data parameter.

When a null value is passed as the Lookup Value or when the lookup finds no matching value in lookup column, the enricher returns the Fallback Value or the Lookup Value, depending on the Fallback Behavior parameter.

The lookup value expected and output values emitted by this plugin are string values. Any other datatype passed as the input should be converted to a string using SemQL, and outputs should be mapped to string attributes. Output values mapped to non-string output attributes rely on the database implicit conversion, which may give unexpected results.
This plugin is thread-safe and supports parallel execution.

Plugin parameters

The following table lists the plugin parameters.

Parameter name Mandatory Type Description

Cache Lookup Data

No

String

Use this parameter to optionally use a memory cache for the lookup process. Possible values are:

  • NO_CACHE: Do not use a cache, the mapping table is queried for each lookup.

  • LOAD_ON_START (Default): Cache all lookup data in memory at initialization. All lookups are made using the memory cache.

  • LOAD_ON_DEMAND : Cache data after it is looked for. Lookups are first attempted on the memory cache, then on the mapping table if the lookup value is not present in the cache.

Use the cache only to process batches of records. Do not use it when processing one record at a time. For example, it is recommended to set this parameter to NO_CACHE for enrichers running in steppers. If you configure the cache in such situation, it would load everytime the stepper triggers the enricher, causing bad performances.

Custom SQL

No

String

Leave this parameter empty to use a generated SQL query. Use this parameter instead of Mapping Table, Lookup Column and Output Columns to define the lookup dataset with a select statement in the following form:

select
    <lookup_column> LOOKUP_COLUMN,
    <output_column> OUTPUT_COLUMN1,
    <output_column> OUTPUT_COLUMN2,
    <output_column> OUTPUT_COLUMN3,
	...
from <mapping_table>
where...

The number of OUTPUT_COLUMN<N> is limited to 20 (from `OUTPUT_COLUMN1 to OUTPUT_COLUMN20)

This query must return a dataset with n+1 columns aliased LOOKUP_COLUMN and OUTPUT_COLUMN1 to OUTPUT_COLUMNn. These columns are used instead of the Lookup Column and Output Columns.

Datasource

No

String

Name of datasource containing the lookup data. This datasource must be configured in the platform. If this parameter is not defined, the enricher uses the data location datasource.

Fallback Behavior

No

String

Behavior when the lookup value is not found in the lookup column. Possible values are:

  • USE_FALLBACK (default): returns the fallback value or null if the fallback value is not specified

  • USE_LOOKUP_VALUE: returns the lookup value.

When multiple output columns are specified, the same value - the fallback or lookup value - is sent to all these columns.

Fallback Value

No

String

Value to return if the lookup value is not found in the lookup column. Default value: NULL.

Lookup Column

No

String

Physical name of the column containing the lookup values. Default value: NONE.

Mapping Table

No

String

Physical name of the mapping table containing the lookup and output columns. Default value: NONE.

Output Columns

No

String

Comma-separated list of the physical names of the columns containing the values returned by the enricher. Default value: NONE.

The (singular) Output Column parameter available in previous versions of this plugin is deprecated and replaced by this parameter.

Plugin inputs

The following table lists the plugin inputs.

Input name Mandatory Type Description

Lookup Value

Yes

String

Value to look for in the mapping table’s lookup column.

Plugin outputs

The following table lists the plugin outputs.

Output name Type Description

Output Value<N>

String

Nth Value returned by the lookup.