SQL Lookup

Connection

The database connection to use.

Evaluated for each input row

Schema

The database schema of the table.

Evaluated for each input row

Table

Name of the dimension table in the database.

Evaluated for each input row

Keys

Key fields of table and corresponding values used to look up records.

Evaluated for each input row

Data fields

The fields retrieved from looked up records.

Evaluated for each input row

Use Cache

If true, the step stores looked up rows in an in-memory cache to reduce database roundtrips.

Evaluated once when step initializes

Cache Size

Number of records cached

• When set to a number greater than 0, creates a lookup cache of the given size.
• When set to 0 all records are cached.
• Any other value produces an error.

Evaluated once when step initializes

Pre-load Cache

If true, the step pre-fills the cache with the contents of the table.

Evaluated once when step initializes

Pre-load SQL Filter

If non-empty, this query text is appended as an AND condition to the cache pre-loading query.

It should contain a predicate to append to the where clause of the query, for example: (country = 'FR')

This is useful to selectively pre-warm the cache with a specific subset of records.

Evaluated once when step initializes

Pre-load SQL Parameters

If the pre-load cache filter contains ? parameter placeholders, they are bound to this list of values in order of appearance.

Evaluated once when step initializes

Use Bloom Filter

If true, the step places all distinct keys from the database table in a bloom filter during initialization.

During processing, the bloom filter is then used to determine whether any records with given keys are present in the table.

The filter is only useful when a substantial portion of the looked up records are not actually present in the table. It allows to skip the lookup query in such cases.

Evaluated once when step initializes

Bloom Filter Size

The expected number of distinct keys the bloom filter should be able to handle. This number is used to ensure the bloom filter operates with an expected false positive rate of 5%. That is 5% of the time the filter might indicate that a set of keys might be present in the table, when in fact it is not.

This number should be close to the number of distinct keys in the fully loaded table.

Evaluated once when step initializes

lookup

lookup_rows