Learn Apache Kafka - ksqlDB: SQL for Stream Processing

#Introduction

Not everyone wants to write Kafka Streams topologies in Java code. ksqlDB provides a SQL layer on top of Kafka Streams: you describe streams and tables, then write SQL queries for filtering, aggregation, and joins — and ksqlDB turns them into Streams applications running on a server.

ksqlDB's value lies in its ease of use: queries that need dozens of lines of Java can be written as a single SQL statement. The concepts you learned in episode 13 still apply — KStream becomes a stream, KTable becomes a table — but now they're operated with a familiar syntax.

Episode 14 covers the fundamentals of ksqlDB, the difference between streams and tables, query types (persistent, push, pull), aggregations and joins, and how to run its server and CLI.

#ksqlDB Fundamentals

#A SQL Interface for Kafka Streams

ksqlDB translates SQL statements into Kafka Streams topologies. You don't write processors — you declare what you want, and the engine generates the Streams code behind the scenes. This dramatically lowers the barrier to entry for teams already familiar with SQL.

#Stream vs Table

Two core entities, aligned with KStream and KTable:

• STREAM: an immutable stream of events; every record is a new fact. Similar to KStream.
• TABLE: a per-key view storing the latest value. Similar to KTable, backed by a compacted changelog.

#Persistent vs Push vs Pull Queries

Three query types you must be able to distinguish:

• Persistent query: a query that runs continuously in the background and writes results to a topic. Begins with CREATE STREAM AS SELECT or CREATE TABLE AS SELECT.
• Push query: a query that subscribes and streams results to the client continuously. Begins with SELECT ... EMIT CHANGES.
• Pull query (3.0+): a one-time query that reads the current value from a materialized view. Begins with SELECT ... FROM table WHERE ... without EMIT CHANGES.

#Working with Streams and Tables

#Creating Streams and Tables

Streams are created from Kafka topics with CREATE STREAM, tables with CREATE TABLE:

CREATE STREAM orders (
  order_id VARCHAR,
  user_id VARCHAR,
  amount DOUBLE
) WITH (
  KAFKA_TOPIC = 'orders',
  VALUE_FORMAT = 'JSON'
);
 
CREATE TABLE user_profiles (
  user_id VARCHAR PRIMARY KEY,
  name VARCHAR
) WITH (
  KAFKA_TOPIC = 'user-profiles',
  VALUE_FORMAT = 'AVRO'
);

KAFKA_TOPIC='orders' connects the stream to the physical topic, and VALUE_FORMAT='JSON' determines the serialization format. Notice: tables use PRIMARY KEY, streams don't.

#INSERT INTO

To write records to a stream, use INSERT INTO — ksqlDB doesn't support plain INSERT on streams:

INSERT INTO orders (order_id, user_id, amount)
VALUES ('order-005', 'user-3', 150.50);

INSERT INTO orders writes one event to the orders topic through that stream. This is a quick way to test a pipeline without an external producer.

#Stream Processing with SQL

#Filtering and Projection

Simple transformations are written like ordinary SQL:

CREATE STREAM paid_orders AS
  SELECT order_id, user_id, amount * 1.11 AS amount_with_tax
  FROM orders
  WHERE amount > 100
  EMIT CHANGES;

CREATE STREAM AS SELECT defines a persistent query that continuously streams results to the new paid_orders topic — every new record in orders is processed immediately.

#Aggregation and Grouping

Windowed aggregations produce materialized views that are always up to date:

CREATE TABLE total_per_user AS
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WINDOW TUMBLING (SIZE 1 MINUTE)
  GROUP BY user_id
  EMIT CHANGES;

WINDOW TUMBLING (SIZE 1 MINUTE) creates a one-minute window like the tumbling window in episode 13, and GROUP BY user_id aggregates the total per user. The result is a KTable stored as a topic.

#Joins Between Entities

ksqlDB supports the same three join patterns as Kafka Streams:

CREATE STREAM orders_enriched AS
  SELECT o.order_id, o.amount, u.name
  FROM orders o
  LEFT JOIN user_profiles u ON o.user_id = u.user_id
  EMIT CHANGES;

LEFT JOIN user_profiles enriches every order with the user's name from the table — the stream-table join pattern most often used for real-time data enrichment.

#Deploying ksqlDB

#Server and CLI

ksqlDB runs as a server that executes all queries. A CLI connects to the server to write statements:

ksql-server-start config/ksql-server.properties
ksql http://localhost:8088

The server opens port 8088 for CLI connections and the REST API. All persistent queries are stored on the server, so restarting it doesn't remove them.

#REST API and Scaling

The REST API enables integration with applications:

curl -X POST http://localhost:8088/query \
  -H "Content-Type: application/vnd.ksql.v1+json" \
  -d '{"ksql": "SELECT * FROM total_per_user EMIT CHANGES;", "streamsProperties": {}}'

curl -X POST http://localhost:8088/query sends a push query and receives a stream of results. For scaling, a ksqlDB cluster consists of several servers that share work through internal topics — add nodes to add capacity, with state stores distributed automatically.

#Closing

In this episode 14 you've understood ksqlDB as SQL for Kafka Streams, the difference between streams and tables, persistent, push, and pull queries, filtering, aggregation, and join operations, and deploying the server, CLI, and REST API.

The key takeaways:

• ksqlDB translates SQL into Kafka Streams topologies.
• STREAM is for events, TABLE is for per-key state.
• Persistent queries keep writing to topics; push queries stream to clients; pull queries read once.
• Windowing aggregations with WINDOW TUMBLING produce materialized views.
• Stream-table joins for real-time data enrichment.
• The ksqlDB server executes queries and persists them across restarts.

In the next episode 15 we'll discuss network configuration and multi-datacenter — advertised listeners, the PLAINTEXT and SSL protocols, inter-broker communication, and active-active and active-passive patterns across datacenters. Prepare your networking understanding, because security in episodes 16-18 depends on this foundation.