DatabaseRecordLookupService¶

Description¶

A relational-database-based lookup service. When the lookup key is found in the database, the specified columns (or all if Lookup Value Columns are not specified) are returned as a Record. Only one row will be returned for each lookup, duplicate database entries are ignored.

Tags¶

cache, database, enrich, join, key, lookup, rdbms, record, reloadable, value

Properties¶

In the list below required Properties are shown with an asterisk (*). Other properties are considered optional. The table also indicates any default values, and whether a property supports the NiFi Expression Language.

Display Name	API Name	Default Value	Allowable Values	Description
Cache Expiration	Cache Expiration			Time interval to clear all cache entries. If the Cache Size is zero then this property is ignored.
Default Decimal Precision *	dbf-default-precision	10		When a DECIMAL/NUMBER value is written as a ‘decimal’ Avro logical type, a specific ‘precision’ denoting number of available digits is required. Generally, precision is defined by column data type definition or database engines default. However undefined precision (0) can be returned from some database engines. ‘Default Decimal Precision’ is used when writing those undefined precision numbers.
Default Decimal Scale *	dbf-default-scale	0		When a DECIMAL/NUMBER value is written as a ‘decimal’ Avro logical type, a specific ‘scale’ denoting number of available decimal digits is required. Generally, scale is defined by column data type definition or database engines default. However when undefined precision (0) is returned, scale can also be uncertain with some database engines. ‘Default Decimal Scale’ is used when writing those undefined numbers. If a value has more decimals than specified scale, then the value will be rounded-up, e.g. 1.53 becomes 2 with scale 0, and 1.5 with scale 1.
Cache Size *	dbrecord-lookup-cache-size	0		Specifies how many lookup values/records should be cached. The cache is shared for all tables and keeps a map of lookup values to records. Setting this property to zero means no caching will be done and the table will be queried for each lookup value in each record. If the lookup table changes often or the most recent data must be retrieved, do not use the cache.
Clear Cache on Enabled *	dbrecord-lookup-clear-cache-on-enabled	true	true false	Whether to clear the cache when this service is enabled. If the Cache Size is zero then this property is ignored. Clearing the cache when the service is enabled ensures that the service will first go to the database to get the most recent data.
Database Connection Pooling Service *	dbrecord-lookup-dbcp-service			The Controller Service that is used to obtain connection to database
Lookup Key Column *	dbrecord-lookup-key-column			The column in the table that will serve as the lookup key. This is the column that will be matched against the property specified in the lookup processor. Note that this may be case-sensitive depending on the database.
Table Name *	dbrecord-lookup-table-name			The name of the database table to be queried. Note that this may be case-sensitive depending on the database.
Lookup Value Columns	dbrecord-lookup-value-columns			A comma-delimited list of columns in the table that will be returned when the lookup key matches. Note that this may be case-sensitive depending on the database.

State management¶

This component does not store state.

Restricted¶

This component is not restricted.

System Resource Considerations¶

This component does not specify system resource considerations.