ksqlDB HOWTO: Joins

Using ksqlDB you can enrich messages on a Kafka topic with reference data held in another topic. This could come from a database, message queue, producer API, etc.

ksqlDB uses SQL to describe the stream processing that you want to do.
With JOIN clause you can define relationships between streams and/or tables in ksqlDB (which are built on topics in Kafka)

For example:

CREATE STREAM ORDERS_ENRICHED AS
SELECT O.ORDERTIME AS ORDER_TIMESTAMP,
O.ORDERID,
I.MAKE,
O.ORDERUNITS,
O.ORDERUNITS * I.UNIT_COST AS TOTAL_ORDER_VALUE,
FROM ORDERS O
INNER JOIN ITEM_REFERENCE_01 I
ON O.ITEMID = I.ITEM_ID
PARTITION BY ORDERID;
-----

💾 Run ksqlDB yourself: https://ksqldb.io?utm_source=youtube&...
☁️ Use ksqlDB as a managed service: https://www.confluent.io/confluent-cl...

👾 Demo code: https://github.com/confluentinc/demo-...

🤔 Questions? Join the Confluent Community at https://confluent.io/community/ask-th...

-----

⏱ Time codes

00:00:00 Introduction
00:00:29 Listing streams in ksqlDB
00:00:38 Querying the orders stream
00:00:58 Foreign keys in a stream
00:01:25 Storing reference data in a Kafka topic
00:02:12 The importance of message keys in topics used as tables
00:03:20 Declaring a table in ksqlDB over a topic holding reference data
00:03:40 Streams and tables in ksqlDB
00:04:21 Defining the primary key on a ksqlDB table
00:04:47 Examining a table in ksqlDB
00:05:14 Stream-table joins in ksqlDB
00:05:46 INNER JOIN vs LEFT OUTER JOIN
00:06:45 Sampling the results of the join
00:07:25 Writing the results of the join to a new Kafka topic
00:08:14 Inspecting the derived stream
00:08:30 Viewing the enriched data stream
00:08:49 NULLs in join results
00:09:18 Viewing the enriched topic
00:09:43 Summary