dblookup

Binds input record field values to SQL placeholders to query an external database, and adds the first row of the result as fields to the record.

Command properties

Item	Description
Command type	Processing query
Required permission	None
License usage	N/A
Parallel execution	Supported
Distributed execution	Runs on Data Node (mapper)

Syntax

dblookup PROFILE [bypass=EXPR] SELECT_SQL

Options

bypass=EXPR: Bypass condition expression. When the expression evaluates to true, the SQL query is not executed and the input record is passed through as-is. Typically used to check whether the field value used in the SQL condition is null.

Target

PROFILE: JDBC profile name. Can be specified as a query parameter. The JDBC profile must be registered in advance.
SELECT_SQL: SQL query starting with SELECT. Use placeholders starting with a colon (:) to bind input record field values. For example, :src_ip is replaced with the value of the src_ip field in the input record.

Error codes

Parse errors

Error code	Message	Description
12000	Invalid JDBC profile name: [name]	The specified JDBC profile does not exist.
12001	No permission to use JDBC profile.	The current user does not have permission to use the JDBC profile.
12108	Invalid query.	The SQL statement does not start with SELECT.
12109	Invalid bypass condition expression.	The condition expression in the `bypass` option is incorrect.

Runtime errors

N/A

Description

The dblookup command executes a SQL query for each input record to query an external database. Placeholders (:fieldname) in the SQL query are bound to the corresponding field values from the input record. Only the first row of the result is retrieved, and all columns of that row are added as fields to the input record.

Unlike the dbquery command, dblookup executes a SQL query per input record, making it suitable for individually querying an external database for large volumes of input.

Use the bypass option to skip SQL query execution under certain conditions. For example, you can prevent unnecessary database queries for records where the field value used in the condition is null.

If an error occurs during SQL query execution, the record is output without the lookup result.

In a distributed environment, each Data Node individually queries the external database.

Examples

Querying a database with input record field values
```
json "[{'src_ip': '192.0.2.1'}, {'src_ip': '192.0.2.2'}]"
| dblookup mydb SELECT hostname, location FROM asset_info WHERE ip = :src_ip
```
Binds the src_ip field value from each input record to the :src_ip placeholder in the SQL query to look up the asset_info table. The hostname and location columns from the result are added as fields to the input record.
Skipping null values with the bypass option
```
json "[{'user_id': 'admin'}, {'user_id': null}]"
| dblookup mydb bypass="isnull(user_id)" SELECT name, dept FROM users WHERE id = :user_id
```
Records where user_id is null are passed through without executing the SQL query. Only records with a user_id value look up name and dept from the users table and add them as fields.
Querying with multiple placeholders
```
json "[{'src_ip': '192.0.2.1', 'dst_port': 443}]"
| dblookup mydb SELECT service_name FROM service_map WHERE ip = :src_ip AND port = :dst_port
```
Binds the src_ip and dst_port field values from the input record to the respective placeholders to query the service_map table.
Looking up an external database for table data
```
table duration=1h web_logs
| dblookup mydb bypass="isnull(src_ip)" SELECT country, city FROM geoip WHERE ip = :src_ip
```
Queries web_logs data from the past hour and adds country and city information from the geoip table based on the src_ip field of each record.