ksqlDB and Stream Processing Options: SQL on Streams, Flink and Choosing a Tool

Apache Kafka 16 min min read Updated: Nov 17, 2026 Intermediate
ksqlDB and Stream Processing Options: SQL on Streams, Flink and Choosing a Tool
Intermediate Topic 4 of 4

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

TypeBehaviourSyntax
Push queryRuns forever and streams new results to the clientSELECT ... FROM orders EMIT CHANGES;
Pull queryReads 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 StreamsksqlDBApache Flink
FormJava/Scala library in your appSQL server on top of Kafka StreamsDistributed processing cluster (or managed service) with SQL and Java/Python APIs
SourcesKafka onlyKafka onlyKafka and many others (files, databases, queues)
Best forMicroservices that own their processing logicQuick SQL pipelines, simple enrichment and aggregationsLarge, complex, multi-source pipelines, advanced event-time features, batch and stream unification
OperationsDeploy like any appRun and scale a ksqlDB clusterRun a Flink cluster (or use a managed one)
SkillsJava developersSQL analysts and developersData 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.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators