Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 39 ยท Data Formats and the Ecosystem

ksqlDB: SQL over Kafka

Process Kafka topics with SQL in ksqlDB: streams versus tables, persistent, push and pull queries, windowed aggregates and joins, with runnable stand-ins.

Kafka Streams is a Java library. ksqlDB offers the same ideas through SQL: you write CREATE STREAM and SELECT statements, and a ksqlDB server turns them into Kafka Streams topologies that run continuously. There is no Java project to build. Analysts, and developers who just want a quick filter or aggregate, can create a pipeline with a few lines.

ksqlDB is a separate server (it is source-available from Confluent, not part of the Apache Kafka download) that talks to your Kafka cluster. Neither can run on this site, so the SQL below is shown read-only. The TryIt blocks are plain JavaScript stand-ins that copy the behavior of the main query types.

Real versus stand-in. The SQL is real ksqlDB syntax. The JavaScript only imitates how a stream, a table and a windowed count behave; it is not ksqlDB and does not parse SQL.

Streams and tables

The distinction from Kafka Streams carries over directly.

  • A stream is an unbounded series of immutable events: every record counts.
  • A table keeps the latest value per key, like a KTable: a new record for a key replaces the old one.

You declare them on top of Kafka topics, giving column names and a value format (JSON, Avro, Protobuf; see Schemas, Avro, Protobuf and JSON):

SQL
CREATE STREAM page_views (
  user_id VARCHAR KEY,
  page    VARCHAR,
  ms      BIGINT
) WITH (
  KAFKA_TOPIC = 'page-views',
  VALUE_FORMAT = 'JSON',
  PARTITIONS = 2
);

CREATE TABLE users (
  user_id VARCHAR PRIMARY KEY,
  country VARCHAR
) WITH (
  KAFKA_TOPIC = 'users',
  VALUE_FORMAT = 'JSON'
);

If the topic does not exist yet, ksqlDB creates it with the PARTITIONS you give. KEY and PRIMARY KEY name the column held in the record key, which is what partitioning and joins depend on.

Three kinds of query

Three kinds of query

Persistent CREATE ... AS SELECT. Runs forever, writes a topic.
Push SELECT ... EMIT CHANGES. Streams results to you.
Pull SELECT on a table. One answer, then done.

Persistent queries are your pipelines. The other two are for looking.

1 / 2

Persistent queries are the pipelines. They keep running on the server, read their inputs and write results to a new topic that other applications can consume:

SQL
CREATE STREAM slow_views AS
  SELECT user_id, page, ms
  FROM page_views
  WHERE ms > 1000
  EMIT CHANGES;

CREATE TABLE views_per_page AS
  SELECT page, COUNT(*) AS views
  FROM page_views
  GROUP BY page
  EMIT CHANGES;

Push queries subscribe to a stream or table and keep sending rows to the client as they change, until you cancel. They are ideal for dashboards and debugging:

SQL
SELECT * FROM page_views EMIT CHANGES;

Pull queries read the current state of a table, like a normal database lookup, and return once. They need a materialized table with a known key:

SQL
SELECT views FROM views_per_page WHERE page = '/pricing';

Because tables are materialized in state stores (see state stores in Kafka Streams), pull queries are fast key lookups, but they are not a general-purpose database: filter on the key, not on arbitrary columns.

A stand-in for stream, table and queries

The stand-in keeps an event list (the stream) and derives a table of counts (the persistent query). A push query is a subscriber called per event; a pull query is a lookup in the table.

JS Persistent, push and pull queries

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The stream keeps all four events, the table keeps one row per page, the push query printed only the slow views as they arrived, and the pull query answered once.

Windowed aggregates

Counting forever is rarely what you want. Add a window to count per time period, using the same window types as Kafka Streams:

SQL
CREATE TABLE views_per_minute AS
  SELECT page, COUNT(*) AS views, WINDOWSTART AS window_start
  FROM page_views
  WINDOW TUMBLING (SIZE 1 MINUTE, GRACE PERIOD 10 SECONDS)
  GROUP BY page
  EMIT CHANGES;

TUMBLING windows do not overlap, HOPPING windows overlap (a size and an advance interval), and SESSION windows close after a gap of inactivity. Records are assigned to windows by their event timestamp, and the grace period decides how long late records are still accepted. EMIT FINAL (in recent versions) emits a single result when a window closes instead of an update per event.

JS Tumbling-window counts by event time

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Joins

ksqlDB supports the join types you know from Kafka Streams:

SQL
-- enrich each event with the user's country (stream-table join)
CREATE STREAM views_enriched AS
  SELECT v.user_id, v.page, u.country
  FROM page_views v
  LEFT JOIN users u ON v.user_id = u.user_id
  EMIT CHANGES;

-- match two streams that happen close together (stream-stream join needs WITHIN)
CREATE STREAM orders_with_payments AS
  SELECT o.order_id, o.total, p.method
  FROM orders o
  INNER JOIN payments p WITHIN 15 MINUTES ON o.order_id = p.order_id
  EMIT CHANGES;

A stream-table join looks up the table's value at the moment the event arrives, so it needs both sides co-partitioned: same key and same partition count. A stream-stream join keeps both sides in a time window, which costs state.

ksqlDB or Kafka Streams?

ksqlDB Kafka Streams
Language SQL Java (or Kotlin, Scala)
Runs as its own server cluster a library inside your service
Best for quick pipelines, filters, enrichment, aggregates, exploration complex logic, custom state, unit tests, tight app integration
Escape hatch user-defined functions in Java the full Processor API

A common path: prototype in ksqlDB, then move heavy or intricate logic to Streams or Kafka Connect plus a service when SQL gets awkward. For heavy multi-source analytics, see also Spark and Flink with Kafka.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which ksqlDB statement creates a pipeline that keeps running and writes results to a new topic?

    CREATE STREAM/TABLE ... AS SELECT is a persistent query: it runs on the server continuously and writes its output to a Kafka topic.
  2. 2. What does a pull query return?

    A pull query is a point-in-time lookup on a materialized table. Push queries are the ones that keep streaming.
  3. 3. Why does a stream-table join require co-partitioning?

    The lookup is local to a partition, so both inputs must use the same key and the same number of partitions.
  4. 4. A stream-stream join in ksqlDB needs which extra clause?

    WITHIN defines how close in time two events must be to match, which bounds the state the join must keep.

Summary

  • ksqlDB runs SQL statements as continuous Kafka Streams topologies on its own server.
  • Streams hold every event; tables hold the latest value per key.
  • Persistent queries are pipelines, push queries stream results to a client, pull queries look up the current state.
  • Windows (tumbling, hopping, session) bucket aggregates by event time; joins need co-partitioned inputs, and stream-stream joins need WITHIN.
  • Choose ksqlDB for quick SQL pipelines and Kafka Streams when you need code-level control.

Next: Kafka with Spark Structured Streaming and Flink, for engines that run outside the Kafka ecosystem.