ksqlDB is an event streaming database for Kafka. It uses SQL to define continuously running stream-processing applications, with Kafka Streams performing the processing underneath. Queries can produce ongoing results instead of returning once and stopping. section 1.4.6 section 11.0
Create STREAMs for unbounded independent events and TABLEs for keyed update streams. Source queries bind these objects to Kafka topics; persistent queries write results to topics for sharing, while push queries stream results to clients and pull queries run once. section 11.7
Use ksqlDB to:
- Build real-time applications with SQL rather than application code or configuration files. section 1.6 section 11.0
- Aggregate, window, and join event streams, including stream–table lookups for enrichment. section 11.0 section 11.6
- Work with Kafka topic schemas and serialization formats, including Avro, Protobuf, JSON Schema, and format changes through WITH clauses. section 11.7
Customer rewards from correlated purchases
Define coffee and store purchase streams keyed by customer ID, then join purchases occurring within 30 minutes. A CASE expression assigns reward points from the store total; a LEFT OUTER stream–table join can then add member details. The joined topics must be co-partitioned with matching key types and partition counts. section 11.6 section 11.7
Listing 11.34 Creating a stream-stream join for potential customer rewards
CREATE STREAM customer-rewards-stream AS
SELECT c.custId AS customerId, #1
s.total as amount, #2
CASE #3
WHEN s.total < 25.00 THEN 15
WHEN s.total < 50.00 THEN 50
ELSE 75
END AS reward_points
FROM coffee-purchase-stream c
INNER JOIN store-purchase-stream s
WITHIN 30 MINUTES GRACE PERIOD 2 MINUTES #4
ON c.custId = s.custId #5
This SQL listing shows a persistent stream–stream join with a time window, grace period, key condition, selected fields, and conditional reward calculation. section 11.6
