Lookups and Joins with ksqlDB

Lookups and Joins with ksqlDB

When processing data, a common requirement is to enrich it with other data. These lookups can be done in ksqlDB using the SQL JOIN syntax. Joins can be between streams of events, between streams and tables, or between tables and tables.

Instead of making calls out to external systems to enrich the data with lookups (which introduces complexity, and can be inefficient), the data is ingested, using Kafka Connect, from the external system into an Apache Kafka topic, from which the join can be performed natively using ksqlDB.

Here’s an example of joining order events to a table that contains information about the item being ordered:

CREATE STREAM ORDERS_ENRICHED AS
SELECT O.*,
       I.*,
       O.ORDERUNITS * I.UNIT_COST AS TOTAL_ORDER_VALUE,
  FROM ORDERS O
       LEFT OUTER JOIN ITEMS I
       ON O.ITEMID = I.ID ;