Get Started Free
‹ Back to courses
course: ksqlDB 101

Lookups and Joins with ksqlDB

2 min
Allison

Allison Walther

Integration Architect (Presenter)

Robin Moffatt

Robin Moffatt

Principal Developer Advocate (Author)

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.

performed-natively

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 ;

Use the promo code KSQLDB101 to get $25 of free Confluent Cloud usage

Get Started

Get started with Confluent Cloud

Try free

Total 28

  1. Introduction to ksqlDB
    2 min
  2. Interacting with ksqlDB
    2 min
  3. Hands On: Interacting with ksqlDB
    5 min
  4. Creating, Exporting, and Importing Data
    2 min
  5. Filtering with ksqlDB
    1 min
  6. Hands On: Filtering with ksqlDB
    1 min
  7. Lookups and Joins with ksqlDB
    2 min
  8. Hands On: Lookups and Joins with ksqlDB
    2 min
  9. Transforming Data with ksqlDB
    2 min
  10. Hands On: Transforming Data with ksqlDB
    2 min
  11. Flatten Nested Records with ksqlDB
    1 min
  12. Hands On: Flatten Nested Records with ksqlDB
    2 min
  13. Converting Data Formats with ksqlDB
    2 min
  14. Hands On: Converting Data Formats with ksqlDB
    2 min
  15. Merging Two Streams with ksqlDB
    1 min
  16. Hands On: Merging Two Streams with ksqlDB
    2 min
  17. Splitting Two Streams with ksqlDB
    2 min
  18. Hands On: Splitting Two Streams with ksqlDB
    2 min
  19. Streams and Tables
    2 min
  20. Stateful Aggregations (Materialized Views)
    2 min
  21. Hands On: Stateful Aggregations (Materialized Views)
    2 min
  22. Push Queries and Pull Queries
    2 min
  23. Hands On: Push Queries and Pull Queries
    2 min
  24. Under the Covers of ksqlDB
    2 min
  25. ksqlDB Code Lifecycle
    2 min
  26. Apply Lambda Functions to Arrays and Maps
    2 min
  27. Hands On: Apply Lambda Functions to Arrays and Maps
    2 min
  28. Monitoring ksqlDB
    1 min

Related Resources

Presentation

Inside ksqlDB

Docs

Joining Collections

Tutorial

How to Join a Table and a Table Using ksqlDB

Be the first to get updates and new content

We will only share developer content and updates, including notifications when new content is added. We will never send you sales emails. 🙂 By subscribing, you understand we will process your personal information in accordance with our Privacy Statement.

Lookups and Joins with ksqlDB

Hi, I'm Allison Walther with Confluent. Let's talk about lookups and joins in ksqlDB. Let's say we've got an inbound stream of raw order events. Each one has a code that denotes information about the item that was bought. In relational terms, this is the foreign key. To make the stream of events useful, we want to add in the information about the item to each order as the event is received in Kafka. The item data includes the primary key and several attributes of the item. This information is in a separate topic and could be populated directly by a producer, but more likely it's been ingested into a Kafka topic using Kafka Connect, which we will cover in more detail in another course. Connect brings in the information from an external system, such as a relational database. We can use ksqlDB to do a join between the two topics in Kafka. The orders are treated as a stream whilst the items are a table, both are Kafka topics. Here, we're using a left outer join, which means that any order that's received with an item code that isn't found on the item data will still be processed, but written with no values for the item information. The enriched order information is written to a new Kafka topic. ksqlDB can process every existing event in the orders topic, and then every single new order event that arrives in near real-time. That's it for lookups and joins. We're gonna move on to an exercise so you can further learn this information.