What Is ksqlDB?
ksqlDB is a streaming database that lets you process Kafka topics with SQL-like statements. Under the hood it runs Kafka Streams. Statements become continuously running queries that read topics and write results to new topics.
Check availability and support: ksqlDB is developed by Confluent under the Confluent Community License. Before choosing it for a new system, check the project's current status and your support needs, and compare with Kafka Streams and Flink (see the end of this lesson).
Run ksqlDB Locally
yaml
services:
ksqldb-server:
image: confluentinc/ksqldb-server:latest
ports: ["8088:8088"]
environment:
KSQL_BOOTSTRAP_SERVERS: broker:9092
KSQL_LISTENERS: http://0.0.0.0:8088
KSQL_KSQL_SERVICE_ID: shop_ksql_
ksqldb-cli:
image: confluentinc/ksqldb-cli:latest
entrypoint: /bin/sh
tty: true
bash
docker compose exec ksqldb-cli ksql http://ksqldb-server:8088
ksql> SHOW TOPICS;
ksql> SET 'auto.offset.reset' = 'earliest';
Streams and Tables
sql
-- a STREAM is an unbounded series of events on a topic
CREATE STREAM orders (
order_id VARCHAR KEY,
customer_id VARCHAR,
total DOUBLE,
region VARCHAR,
created_at BIGINT
) WITH (KAFKA_TOPIC='shop.orders.created.v1', VALUE_FORMAT='JSON', PARTITIONS=3, TIMESTAMP='created_at');
-- a TABLE holds the latest value per key
CREATE TABLE customers (
customer_id VARCHAR PRIMARY KEY,
name VARCHAR,
tier VARCHAR
) WITH (KAFKA_TOPIC='shop.customers', VALUE_FORMAT='JSON');
Push Queries and Pull Queries
| Type | Behaviour | Syntax |
| Push query | Runs forever and streams new results to the client | SELECT ... FROM orders EMIT CHANGES; |
| Pull query | Reads current state once, like a normal DB lookup (on tables created by aggregations) | SELECT ... FROM table WHERE key = 'x'; |
sql
SELECT order_id, total FROM orders WHERE total > 500 EMIT CHANGES; -- push: live feed of big orders
Persistent Queries: CSAS and CTAS
sql
-- CREATE STREAM AS SELECT: continuous transformation into a new stream and topic
CREATE STREAM big_orders AS
SELECT order_id, customer_id, total FROM orders WHERE total >= 1000 EMIT CHANGES;
-- CREATE TABLE AS SELECT: continuous aggregation into a table
CREATE TABLE revenue_by_customer AS
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders GROUP BY customer_id EMIT CHANGES;
-- pull query on that table
SELECT * FROM revenue_by_customer WHERE customer_id = 'C42';
Windowed Aggregations
sql
CREATE TABLE orders_per_region_5m AS
SELECT region, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
WINDOW TUMBLING (SIZE 5 MINUTES, GRACE PERIOD 1 MINUTE)
GROUP BY region
EMIT CHANGES;
-- other windows: HOPPING (SIZE 10 MINUTES, ADVANCE BY 1 MINUTE), SESSION (30 MINUTES)
-- EMIT FINAL gives a single result after the window closes (instead of EMIT CHANGES)
Joins
sql
-- stream-table enrichment
CREATE STREAM orders_enriched AS
SELECT o.order_id, o.total, c.name, c.tier
FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id
EMIT CHANGES;
-- stream-stream join within a time window
CREATE STREAM orders_paid AS
SELECT o.order_id, p.amount
FROM orders o INNER JOIN payments p WITHIN 15 MINUTES ON o.order_id = p.order_id
EMIT CHANGES;
The same rules as Kafka Streams apply: joined sources need the same key and partition count (ksqlDB can repartition with PARTITION BY).
Useful Functions and Formats
- Functions:
CONCAT, SUBSTRING, ROUND, TIMESTAMPTOSTRING, EXTRACTJSONFIELD, COALESCE, CASE WHEN, EARLIEST_BY_OFFSET, LATEST_BY_OFFSET, COLLECT_LIST
- Formats: JSON, AVRO, PROTOBUF, DELIMITED (CSV), KAFKA (primitive keys). Use Avro or Protobuf with Schema Registry in production
- Manage queries:
SHOW QUERIES;, EXPLAIN <id>;, TERMINATE <id>;, DROP STREAM ... DELETE TOPIC;
Production Notes
- Statements are stored in a command topic. Run several ksqlDB servers with the same service id for scaling and failover
- Use headless mode with a SQL file for locked-down production deployments
- Resource use is that of Kafka Streams: state stores, repartition topics, changelog topics
- Version control your SQL and test it. Schema changes of sources need care
Choosing a Stream Processing Tool
| Kafka Streams | ksqlDB | Apache Flink |
| Form | Java/Scala library in your app | SQL server on top of Kafka Streams | Distributed processing cluster (or managed service) with SQL and Java/Python APIs |
| Sources | Kafka only | Kafka only | Kafka and many others (files, databases, queues) |
| Best for | Microservices that own their processing logic | Quick SQL pipelines, simple enrichment and aggregations | Large, complex, multi-source pipelines, advanced event-time features, batch and stream unification |
| Operations | Deploy like any app | Run and scale a ksqlDB cluster | Run a Flink cluster (or use a managed one) |
| Skills | Java developers | SQL analysts and developers | Data engineers |
Rule of thumb: Start with Kafka Streams for service-level processing, use SQL tools (ksqlDB or Flink SQL) for analyst-friendly pipelines, and choose Flink when you integrate many systems or need very large, complex stateful jobs.
Conclusion
ksqlDB gives you SQL over Kafka: streams, tables, push and pull queries, persistent CSAS and CTAS statements with windows and joins. Compare it with Kafka Streams (library in your service) and Flink (separate processing platform) before choosing.
Quick Summary: CREATE STREAM/TABLE; push vs pull; CSAS/CTAS; windows and joins; Streams vs ksqlDB vs Flink.