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&utm_medium=video&utm_campaign=tm.devx_ch.rmoff_youtube__0Ktp2eB-as&utm_term=rmoff-devx ☁️ Use ksqlDB as a managed service: https://www.confluent.io/confluent-cloud/tryfree?utm_source=youtube&utm_medium=video&utm_campaign=tm.devx_ch.rmoff_youtube__0Ktp2eB-as&utm_term=rmoff-devx 👾 Demo code: https://github.com/confluentinc/demo-scene/blob/master/introduction-to-ksqldb/demo_introduction_to_ksqldb_02.adoc 🤔 Questions? Join the Confluent Community at https://confluent.io/community/ask-the-community/?utm_source=youtube&utm_medium=video&utm_campaign=tm.devx_ch.rmoff_youtube__0Ktp2eB-as&utm_term=rmoff-devx ----- ⏱ 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