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 within a specified datasource.

The mapping table is accessed via the Datasource parameter, which defaults to the datasource of the data location. It is defined for the enricher in one of two ways:

  • Using a mapping table: specify a mapping table, a lookup column, and up to 20 output columns from the table. The input lookup value is searched in the lookup column, and corresponding values from the output columns are returned.

  • Using a custom SQL query: provide a custom SQL SELECT statement that runs on the datasource. The query must return columns aliased as LOOKUP_COLUMN for the lookup column and OUTPUT_COLUMN1, …​, OUTPUT_COLUMN20 for the output columns.

You must configure either the Mapping Table, Lookup Column, and Output Columns parameters, or the Custom SQL parameter exclusively. The Mapping Table, Lookup Column, and Output Columns parameters are mandatory unless the Custom SQL parameter is specified.

The lookup operation can use an optional memory cache, configured via the Cache Lookup Data parameter.

When the input lookup value is null or no matching value is found in the lookup column, the enricher returns either the fallback value or the lookup value, depending on the Fallback Behavior parameter.

The enricher expects string values as input lookup values and emits string values as outputs. Non-string input values must be converted to strings using SemQL. Outputs must be mapped to string attributes, as mapping to non-string attributes relies on implicit database conversions, which may produce 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

Configures an optional memory cache for the lookup process. Possible values are:

  • NO_CACHE: the mapping table is queried for each lookup.

  • LOAD_ON_START (default): caches all lookup data in memory at initialization. Lookups use the memory cache exclusively.

  • LOAD_ON_DEMAND: caches data after it is accessed. Lookups check the memory cache first, then query the mapping table if needed.

Use the cache only when processing batches of records. For instance, set this parameter to NO_CACHE for enrichers running in steppers, as caching in such scenarios may cause performance issues due to repeated cache loading.

Custom SQL

No

String

Defines the lookup dataset using a custom SQL query instead of the Mapping Table, Lookup Column, and Output Columns parameters. Leave this parameter empty to use a generated query. The query must follow this structure:

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

The query can define up to 20 output columns (OUTPUT_COLUMN1 to OUTPUT_COLUMN20).

The query must return columns aliased as LOOKUP_COLUMN and OUTPUT_COLUMN1 to OUTPUT_COLUMNn. These are used as lookup and output columns.

Datasource

No

String

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

Fallback Behavior

No

String

Defines the 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.

If multiple output columns are defined, the same value (i.e., the fallback or lookup value) is sent to all these columns.

Fallback Value

No

String

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

Lookup Column

No

String

The physical name of the column containing lookup values. Default: NONE.

Mapping Table

No

String

The physical name of the mapping table containing lookup and output columns. Default: NONE.

Output Columns

No

String

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

The single 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

The value to search 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

The Nth value returned by the lookup.