Database Lookup

Look up each row's keys in a database table and add the matching row's columns.

Category

Lookup

Connections

PostgreSQLSQL ServerMySQLTrino / Starburst

Also known as

lookup, dynamic input, enrich, enrichment, where in, keys, join database, sql, postgres, sql server, mysql, trino, starburst

What it does

This is a Join against a database table that never reads the whole table. Your rows come from somewhere else (a file, an API, another database) and the node sends their key values to the database, a batch at a time, and fetches only the rows that match. The matched row's columns are added to yours and leave by Matched; rows whose key the database does not hold leave by Not found, unchanged. Say a nightly file lists 3,000 orders and you need each customer's tier from a 200-million-row customers table. A database input node feeding a Join would read all 200 million rows to use 3,000 of them. This node asks for the 3,000 customers, so the database answers from its index in seconds. It reads Postgres, SQL Server, MySQL and Trino (Starburst), chosen by the connection you pick. The database itself decides which rows match, so its own rules apply: on SQL Server and MySQL's usual settings 'abc' finds 'ABC'.

Inputs and outputs

When to use it

• Add customer, product or account details to rows from a big table you don't want to read whole
• Check which incoming ids already exist in a database (Not found = the new ones)
• Fetch the rows for a list of keys that arrived in a file or from an API
• Enrich a stream from a Trino or Starburst table, sending only the keys

Examples

Table: sales.customers · Keys: customer_id = id

Keys: region = region · account_no = account_no

SQL query: SELECT * FROM customers WHERE active · Keys: id = customer_id

Wire Not found to the insert path, Matched to the update path

Tips

• Press ⤢ beside SQL query for the SQL Studio: write the lookup query with the connection's tables and columns beside it, and try it with Run to here.
• Index the database column you match on. Without an index the database scans its table for every batch of keys.
• A key that matches several rows gives one output row for each, the way Join does. The order of Matched rows is the database's.
• A row with an empty (NULL) key is never sent and goes to Not found.
• The key types must agree: a number with a number, text with text, a date with a date. Retype the input column with Manage Columns first if they don't.
• The database's own key columns are not added: they equal your keys, and your spelling is kept. Any other clashing name gets the suffix (_lookup).
• On SQL Server, a query cannot end in ORDER BY. Save it as a view, or drop the ORDER BY.
• Keys go 1,000 to a query (Trino: 256). On Trino more than 256 makes it read a range of the table instead of the keys, so keep the default.
• A Test run really queries the database, like every database input, with the 50 sample rows' keys.

Related nodes