JDBC Lookup

Supported pipeline types:

  • Data Collector
The JDBC Lookup processor uses a JDBC connection to perform lookups in a database table and pass the lookup values to fields. For information about supported versions, see Supported Systems and VersionsSupported Systems and Versions in the Data Collector documentation.

Use the JDBC Lookup to enrich records with additional data. For example, you can configure the processor to use a department_ID field as the column to look up department name values in a database table, and pass the values to a new department_name output field.

Important: This stage does not support connecting to non-RDBMS systems, including Hive, Impala, Kudu, or Snowflake. Support for untested systems is not guaranteed. For a list of tested systems, see "Database Vendors and Drivers". Use the Kudu Lookup processor to look up Kudu data.

When a lookup results in multiple matches, the JDBC Lookup processor can return the first matching value, all matching values in a list in a single record, or all matching values in separate records.

When you configure JDBC Lookup, you specify connection information and custom JDBC configuration properties to determine how the processor connects to the database. You configure the SQL query to define the data to look up in the database, specify the output fields to write the lookup values to, and choose the multiple match behavior.

You can specify the behavior for when the lookup returns no values and optionally configure a default value for the same situation. You can also specify the behavior for when the processor encounters data of an unexpected type.

You can configure the processor to locally cache the lookup values to improve performance. When caching lookup values, you can also enable using additional threads to prepopulate the lookup cache and further increase performance.

By default, the JDBC Lookup processor requires all table columns defined in the column mappings to exist when the pipeline starts. You can configure the processor to skip this validation.

The processor generates JDBC field attributes that provide additional information about each field.

To use a JDBC version older than 4.0, you can specify the driver class name and define a health check query.

You can also use a connectionconnection to configure the processor.

When you monitor a pipeline that includes the JDBC Lookup processor, you can view stage statistics about the number of queries the processor makes and the average time of the queries.