Learning Objectives
- Design the idiomatic three-part log ingestion pipeline — a Kafka engine table, a materialized view, and a MergeTree target table — and explain what job each piece does
- Explain why a materialized view fires per-insert into its source table rather than on a schedule, and why that's what makes the Kafka engine table usable as a pipeline stage at all
- Reason about batching a high-volume log stream so it lands in a MergeTree table without producing too many small parts
- Recognize the anti-pattern of querying a Kafka engine table directly, and explain concretely why it silently loses data
Why This Matters
Logs don't arrive the way this course's earlier examples have inserted data — one clean, deliberate INSERT at a time. They arrive continuously, in bursts, from hundreds of producers, at a rate that has nothing to do with what makes a good MergeTree part. Module 3 already showed you that every INSERT creates a part, and that too many small parts is a real operational cost, not a cosmetic one. Point a naive one-row-at-a-time pipeline at ClickHouse and you'll learn that the hard way, in production, during your first traffic spike. This lesson is about the pattern nearly every ClickHouse log pipeline actually uses to avoid that: a durable buffer in front of ClickHouse, and a specific mechanism — the materialized view — that pulls from it on ClickHouse's own terms rather than the producer's.
Why something needs to sit between the log stream and the table
A queue like Kafka is a natural fit here for a reason that has nothing to do with ClickHouse specifically: it decouples the rate logs are produced at from the rate they're consumed at. Producers can burst as hard as they want; the topic just holds the backlog. What ClickHouse needs on its side of that queue is a way to pull from it in batches it controls — sized for good parts, not sized for whatever a single log line's producer happened to send.
The Kafka engine table: a consumer, not a store
ClickHouse's Kafka table engine looks like an ordinary table in CREATE TABLE syntax, but it isn't one — it's a live connection to a Kafka topic. A SELECT against it doesn't return "whatever's currently in the topic" the way a SELECT against a MergeTree table returns "whatever's currently on disk." It consumes messages — reading them off the topic and advancing the consumer group's offset (Kafka's own bookmark of how far a given group of readers has progressed through the topic, shared across every reader in that group) — which means the same SELECT run twice does not return the same rows twice. Nothing about a Kafka engine table is meant to be queried casually; it exists to be read by exactly one thing, continuously, in the background.
CREATE TABLE logs_queue
(
log_line String
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = 'kafka-broker:9092',
kafka_topic_list = 'app-logs',
kafka_group_name = 'clickhouse-log-ingest',
kafka_format = 'JSONEachRow';
Materialized views: the piece that makes this a pipeline, not a dead end
A materialized view in ClickHouse isn't a saved query re-run on a schedule, the way "materialized view" means in most other databases — it's a trigger that fires on every INSERT into its source table, running its SELECT over just the newly inserted rows and writing the result into a target table. That per-insert trigger is exactly the mechanism that turns a Kafka engine table from a dead end into a pipeline stage: attach a materialized view to logs_queue, and every batch the Kafka engine table's background consumer pulls in becomes an automatic INSERT into a real, durable MergeTree table — no cron job, no polling query, no separate consumer process to operate.
CREATE TABLE logs
(
log_line String,
ingested_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY ingested_at;
CREATE MATERIALIZED VIEW logs_mv
TO logs
AS SELECT log_line
FROM logs_queue;
Three tables, three distinct jobs: logs_queue (the Kafka engine table) is the consumer, logs_mv is the trigger that moves each consumed batch into a real INSERT, and logs (an ordinary MergeTree table) is the durable, queryable store everything downstream actually reads from. Nobody ever queries logs_queue for real work — its only reader is logs_mv, running automatically, in the background, for as long as the pipeline is deployed.
Real-world case study
Kafka in front of ClickHouse, joined by a materialized view, is common enough that it's ClickHouse's own documented reference architecture for streaming ingestion.
Idiom vs. anti-pattern
-- Kafka engine table: consumed only by logs_mv, never queried directly
CREATE TABLE logs_queue (log_line String)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka-broker:9092',
kafka_topic_list = 'app-logs',
kafka_group_name = 'clickhouse-log-ingest',
kafka_format = 'JSONEachRow';
CREATE TABLE logs (log_line String, ingested_at DateTime DEFAULT now())
ENGINE = MergeTree ORDER BY ingested_at;
CREATE MATERIALIZED VIEW logs_mv TO logs AS
SELECT log_line FROM logs_queue;
-- Dashboards and ad-hoc queries always read `logs`, never `logs_queue`.
-- A well-intentioned "quick check" on what's coming through the topic:
SELECT log_line FROM logs_queue WHERE log_line LIKE '%error%' LIMIT 100;
-- This consumes those messages from the consumer group. logs_mv will
-- never see them. They will not appear in `logs`. They are gone.
The anti-pattern rarely looks reckless when someone does it — it looks like a debugging convenience, a one-off SELECT to sanity-check that messages are flowing. But because a Kafka engine table has no independent storage of its own, that one query and the pipeline's materialized view are competing consumers in the same consumer group, and whichever one reads a given message first is the only one that gets it — a direct consequence of "each message can be read only once." The fix isn't "be careful" — it's structural: nobody should have standing permission to run ad-hoc SELECTs against a Kafka engine table at all. If you need to inspect what's flowing through, query the downstream MergeTree table the materialized view already populated.
Design exercise
A team needs to ingest roughly 50,000 log lines per second from a Kafka topic with 12 partitions into a searchable ClickHouse log store. Logs need to be queryable within a few seconds of being produced. The team has already seen, in Module 3, what happens to a table fed by many tiny, single-row inserts.
Reasoning Prompt
Design this pipeline. What do the Kafka engine table, materialized view, and target table look like? What would you tune to make sure the materialized view's inserts into the target table produce reasonably sized parts rather than one part per message, without pushing "queryable within a few seconds" out of reach? Write your approach before revealing the model answer.
Model Answer⌄
The three-table shape is the same as this lesson's running example — a Kafka engine table, a MergeTree target table, and a materialized view connecting them — but two things need deliberate tuning given the volume here, both aimed at the same underlying tension: batch size vs. freshness.
- Matching consumers to partitions. A Kafka engine table can run multiple internal consumers (
kafka_num_consumers, default1) so that ClickHouse's read throughput can scale with the topic's partition count instead of being bottlenecked by a single consumer reading all 12 partitions serially. The reasonable target here is enough consumers to spread the 12 partitions across, without exceeding partition count (a consumer with no partition assigned does nothing). - Batch size for the materialized view's inserts. The materialized view fires per block of consumed messages, not per individual message, and how large that block is — governed by
kafka_max_block_size(defaults tomax_insert_block_size) andkafka_flush_interval_ms(defaults tostream_flush_interval_ms, the timeout that flushes a batch even if it hasn't filled) — directly determines how many rows land in a singleINSERT, and therefore how many rows end up in each resulting part. Too small a batch reproduces the one-row-per-part problem from Module 3 at a smaller but still real scale; too large a batch trades freshness for part size, since the view won't fire until a batch fills or a timeout elapses. At 50,000 lines/second, a batch large enough to produce parts of a healthy size (comfortably above a few thousand rows) still fills in well under a second, so there's real room to favor bigger batches here without meaningfully hurting the "queryable within a few seconds" requirement — this wouldn't be true at a much lower message rate, where the timeout, not the batch size, would end up doing most of the work. - What doesn't need designing around: background merges. Even with reasonably sized parts from a well-tuned batch, a 50,000/second stream will still produce many parts over a day; that's exactly the problem Module 3's background merge process exists to solve on its own, and this pipeline doesn't need to do anything special to make merges happen.
The one thing that isn't a matter of tuning, and doesn't have a "just size it right" answer: nobody queries logs_queue directly, ever, regardless of how the consumers or batching are configured — that failure mode is structural, not a knob to turn.
Summary
ClickHouse's idiomatic log ingestion pipeline is three tables with three distinct jobs: a Kafka engine table that consumes messages from a topic (and is never queried directly — a SELECT against it consumes and advances offsets rather than returning a stable snapshot), a materialized view that fires automatically on every batch the Kafka engine table consumes, and an ordinary MergeTree table that the materialized view's INSERTs land in and that everything downstream actually reads from. This works because a ClickHouse materialized view is a per-insert trigger, not a periodically refreshed snapshot — that's what turns "messages arriving on a topic" into "rows appearing in a real table" without a separate consumer process to operate. The tuning knobs that matter at high volume are how many Kafka consumers run in parallel (matched to the topic's partition count) and how large a batch the materialized view fires on (balancing part size against freshness) — but the rule against querying the Kafka engine table directly isn't a tuning knob at all; it's structural, because doing so competes with the pipeline's own consumer for the same messages and silently drops whatever it reads.
Quiz
1. Why does running SELECT log_line FROM logs_queue directly (where logs_queue is a Kafka engine table) risk losing data, when querying an ordinary MergeTree table never does?
2. What specifically makes a materialized view suitable as the connector between a Kafka engine table and a MergeTree target table?
3. A team's materialized-view batch size is tuned very small, so it fires (and inserts) after just a handful of messages each time. What problem does this most directly risk, given what Module 3 established about MergeTree?
4. In the design exercise, why was increasing the materialized view's batch size considered a reasonable trade at 50,000 log lines/second, when the same choice might not be reasonable at a much lower message rate?