---
url: 'https://altinity.com/webinarspage/2020-1-21-fast-insight-from-fast-data-integrating-clickhouse-and-apache-kafka'
title: 'Fast Insight from Fast Data. Integrating ClickHouse® and Apache Kafka'
author:
  name: Robert Hodges
  url: 'https://altinity.com/author/robertaltinity/'
date: '2020-01-21T16:27:00-08:00'
modified: '2026-06-09T16:21:57-07:00'
type: post
summary: 'Integrate ClickHouse® and Apache Kafka for low-latency streaming: the Kafka table engine, materialized views, and production best practices.'
categories:
  - Webinars
tags:
  - ClickHouse
  - Kafka
image: 'https://altinity.com/wp-content/uploads/2020/01/Webinarsquarepicture2020Jan-2.png'
published: true
---

# Fast Insight from Fast Data. Integrating ClickHouse® and Apache Kafka

In this Altinity webinar, CEO Robert Hodges and engineer Mikhail Filimonov explain how to combine ClickHouse and Apache Kafka to turn fast-moving event streams into queryable analytics with very low latency. Robert introduces Kafka as a distributed log built around topics, partitions, brokers, and consumer groups, and ClickHouse as a fast, columnar, shared-nothing data warehouse.

The core pattern is the Kafka table engine paired with a materialized view that continuously moves rows into a MergeTree target table, demonstrated live with sensor data that becomes instantly available for query. The session also covers writing data from ClickHouse back to Kafka, using virtual columns to capture message metadata, and chaining materialized views into pipelines.

Mikhail then digs into production guidance: relying on librdkafka, sizing partitions and the background thread pool, why a single consumer per table is preferred, and how batching and commits produce at-least-once delivery. The talk closes with the roadmap, including reduced duplication, richer metrics, and planned exactly-once support, plus a long Q&A on monitoring, SSL, schema registry, clusters, and replay.

**Here are the slides:**

[Fast-Intro-to-Fast-Query-with-ClickHouse-1](https://altinity.com/wp-content/uploads/2024/05/Fast-Intro-to-Fast-Query-with-ClickHouse-1.pdf)[Download](https://altinity.com/wp-content/uploads/2024/05/Fast-Intro-to-Fast-Query-with-ClickHouse-1.pdf)

## **Key Moments (Timestamps)**

Key moments generated with AI assistance.

- 0:08 – Welcome, recording, and Q&A logistics

- 1:36 – Speaker introductions

- 3:37 – What Apache Kafka is

- 5:47 – ClickHouse as a fast data warehouse

- 9:12 – Reasons to pair Kafka with ClickHouse

- 12:08 – Standard flow and table setup for reading

- 16:44 – Live demo streaming sensor data

- 21:18 – Writing data back to Kafka

- 24:17 – Kafka engine internals and librdkafka

- 26:21 – Production best practices

- 32:10 – Clusters and chained materialized views

- 35:54 – How batching and flushing work

- 39:15 – Client loading and roadmap

- 47:59 – Audience Q&A begins

---

## **Webinar Transcript**

### **0:08 – Welcome, recording, and speaker introductions**

**Robert:** Hello, everybody. This is Robert Hodges, and I’ll be your host today at our webinar on ClickHouse and Apache Kafka: getting fast insight from fast data. This webinar is being recorded, so we will post a copy of it afterward. You’ll actually receive an email message with a link to the recording as well as the slides, so you don’t have to take notes. We’ll make the content available afterward.

**Robert:** Before I get too deep into the content itself, I’d like to discuss a couple of key points that will help you as the webinar proceeds. We have a question and answer box that you can use to type in questions as the webinar is proceeding. If it’s something relevant to the topic we’re discussing, we may stop and address the question right then and there. Otherwise, we have time at the end to answer questions. Our goal is to make the webinar relatively short, hopefully around 40 or 45 minutes, and leave plenty of time for your questions.

**Robert:** The second thing is that we have a poll, which we run at the end of every webinar. It helps us get a little bit of feedback on how we’re doing and helps make the webinars better in the future. We’d appreciate it if you wait around and fill that out. We’ll do it after the last slide runs. Okay, let’s dive into the content.

**Robert:** I’d like to do a couple of quick introductions. My name is Robert Hodges. As I said, I’m the CEO of Altinity. I’ve been working on database systems of one kind or another for well over 30 years, plus I’ve done a fair amount of work on virtualization and security. Today I’ll have with me Mikhail Filimonov, who is one of our engineers. He is a committer to the ClickHouse open source project, and most importantly for this discussion, he is now the Kafka engine maintainer. We took over responsibility for Kafka engine support about a month and a half ago, and we are now responsible for both fixing bugs and adding new features.

**Robert:** Just a little bit about Altinity: we are the enterprise provider for ClickHouse. We do software and services of various kinds. They include management software, for example the Altinity Kubernetes Operator for ClickHouse, to run cloud-native data warehouses. We also do a variety of services that include support, engineering of features in ClickHouse (also known as NRE), and things like proof-of-concept support and training. We are a major committer, in fact the number two committer to ClickHouse worldwide, and we are a community sponsor in the US and Western Europe. We have a deep interest in ensuring that you can use ClickHouse easily, and this webinar is part of our efforts to make that easier for everyone.

### **3:37 – What Apache Kafka is and how it works**

**Robert:** Let’s dive straight into the content. I’d like to talk a little bit about what Kafka is and why use it with ClickHouse. We won’t go too far into this, because we assume from the very large number of people who signed up that many of you are already using Kafka and know about it, so we won’t bore you with a lot of details about Kafka itself. Let’s just have a quick look in case you’re not terribly familiar with it or this issue is new to you.

**Robert:** Kafka is a messaging system. I call it messaging on steroids. The way it works is that the heart of Kafka is a distributed log. You can have producers that push data into that distributed log, which can be spread across one or more nodes. In large installations it is many nodes. Then you have consumers who read out of the log at the other end.

**Robert:** The log is divided into what we call topics, which you can think of as queues. You can push a message into a topic, and then somebody else can come along and read it. In fact, many people can read it. The topics themselves are stored in what are called partitions. You can think of the queue being divided up into pieces. For example, if we have three partitions, one third of the messages typically go in each partition, and they are properly ordered, so that if somebody reads from the partition they will see the messages in the same order they arrived.

**Robert:** Kafka also replicates the data very efficiently. You have what are called Kafka brokers that hold the data, and in a production system you’ll typically have multiple brokers that contain replicas of the data. That prevents it from being lost if one of them dies. One final thing in this model is that we have what’s called a consumer group. That is a kind of catch-all for a group of consumers, applications that are reading from Kafka, so that they all properly collect messages from the topic.

**Robert:** I’m not going to go any deeper into this. I’ve worked with replication for a really long time, so I could talk about it for a while, but it’s a great system, and most importantly it’s extremely popular. It’s worth talking about why it is so popular. Excuse me, before I do that, let me talk a little bit about ClickHouse.

### **5:47 – ClickHouse as a fast columnar data warehouse**

**Robert:** For those of you who already know ClickHouse, you don’t need to be told this, but for those who are new to it, let’s talk a little bit about what it does.

**Robert:** ClickHouse is an extremely fast data warehouse. It understands SQL, and it’s very portable. It runs on bare metal and in the cloud. It uses what’s called a shared-nothing architecture, so you have a bunch of ClickHouse servers, anywhere from one to many, and those have storage attached to them. When you run a query on a single server, it’ll go straight to the locally attached storage. Of course it could be mounted over a network, but the processing and the storage are closely coupled. This is a standard architecture for data warehouses.

**Robert:** Speaking of storage, we store the data in columns. Instead of storing it as a row, as you would in a row-oriented database where all the data for a single accounting record would be in the same place in storage, we instead put all the values for a column in the same place. Columns are highly compressed, and they’re also sorted. When we read the columns, we take advantage of what’s called vectorized execution, where we break the column up into pieces and then assign the duty of reading the column to as many cores as we have available or are allowed to use. That allows us to read extremely efficiently on a single machine by using the cores in parallel. We also have what’s called MPP, massively parallel processing, which allows us to run in parallel across multiple servers. Between these two things, this gives us tremendously good query speed, and every query is going to be a sequential read. Similarly, when we write, we always write sequentially.

**Robert:** The system scales to many petabytes. I run it on this laptop I’m using right now. This is a six-year-old Dell, so it’s really about as weak a machine as you could get. I once looked up the SSD’s rating on a website somewhere, and it’s rated as a zero, so the performance is supposedly very poor. But I can still get pretty good performance on it. Then we have people using ClickHouse that go all the way out to clusters including hundreds of nodes. For example, Cloudflare has over 100 nodes. Cloudflare is a big user, not an Altinity customer, but they have over a hundred nodes and ingest enormous amounts of data. One final thing about ClickHouse: it’s really great that it’s open source, like Kafka. So if you’re looking to build a business that depends on open source, or you just want easy access, it makes it very easy to use.

### **9:12 – Why combine Kafka and ClickHouse**

**Robert:** It seems I’m muted. I apologize. Let me back up. You have Kafka, which is very capable, and you have ClickHouse, which is very capable, and there are a bunch of reasons to use them together. In fact, many customers do that.

**Robert:** My apologies for that. The meeting software is a little complex and sometimes doesn’t work correctly. So, the reasons to use Kafka and ClickHouse together as data sources:

**Robert:** Kafka supports an enormous number of producers and consumers. We had a conversation with the Confluent folks, who offer enterprise support, and they mentioned that there are over 800 connectors available to either put data in or take it out. So you can fetch data from Hadoop, you can fetch data from Spark, and it’s very easy to construct your own applications that push data into Kafka or take it out. That’s really important.

**Robert:** Another reason is that Kafka has very low latency. You put something in and you can read it out, often within seconds or even fractions of a second. This is an important capability.

**Robert:** Another thing is that Kafka scales very well. It allows a very high message throughput, and that’s great because ClickHouse scales equally well. ClickHouse can scale linearly as you add more shards across servers. What you want is a messaging system where you can balance the load coming in from the messaging system against the load that the data warehouse is capable of ingesting.

**Robert:** A final reason, among many, that people choose to couple Kafka and ClickHouse is that you can replay messages in Kafka. You can deliver them, of course, but what’s maybe not obvious if you haven’t done this before is that if you have a problem with ClickHouse and you need to restore data, one simple way is to replay the messages out of Kafka again and reload the data. That’s actually a very common approach among many of our users.

**Robert:** Okay, these are all good reasons and give you the overview of these systems. Let’s jump into the details.

### **12:08 – Reading data from Kafka: tables and the standard flow**

**Robert:** What I’d like to do here is talk about reading data from Kafka, and we’ll use a simple example. I have a very short demo that shows it in action. The flow you get is this: you have a topic on Kafka that has messages coming in. The way we’re going to extract them is to use what’s called a Kafka table engine. This is [a specialized table engine inside ClickHouse that encapsulates one or more topics](https://altinity.com/blog/2020-5-21-clickhouse-kafka-engine-tutorial) and automatically fetches data out of them.

**Robert:** Next, we’re going to have a materialized view. This is a great feature in ClickHouse. We’ve done webinars and blog articles on it, but a [materialized view will automatically fetch data](https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1) out of one table as it arrives and put it in another one. So the materialized view fetches the rows and stores them in a target table. That’s the basic flow.

**Robert:** Let’s show how we set this up. We can do many variations on this. If you’re using Kafka right now, you’ll think, okay, this is kind of first grade. We’ll get to some of the more advanced use cases in just a minute.

**Robert:** What we’re going to do is create the inbound Kafka topic. The example I’m showing is a typical kafka-topics command, which will create the topic at the command line. If you’re familiar with the Kafka command line, you’ll notice I’m using a bootstrap server to locate Kafka, and I’m using a funny Kafka headless DNS name. That’s because all of these examples are being run on Kubernetes. If you’re running in a different environment, your DNS names will obviously have a different form. We’re going to create a topic called readings, and it’s going to have six partitions, so these are breaking up the queue into six shards, and they’re going to be replicated three times, so they’ll be present on three servers. That command runs almost instantly.

**Robert:** The next thing we’ll do is set up a target table. We’d like our target table to contain temperature readings. This is from a benchmark we recently did that hit Hacker News, where we showed how you can read very large quantities of data just using an Intel NUC. This is that same table. It’s a vanilla, garden-variety ClickHouse table. It uses MergeTree, it’s partitioning by time, and it has four columns. We won’t spend any further time on that.

**Robert:** Now we’ll look at the most interesting part, the Kafka engine. We have a new table we call readings_queue. It has three columns in it. If you remember, the previous table had an alias in it, so we create some of the data automatically. We’re going to use the Kafka engine, and then we’re going to have a set of settings. These settings are very simple. They just tell the Kafka table engine where the topic is located. We have a broker list, which can be one or more hostnames. We have a topic list, which can be one or more topics. We have a group name that groups all the consumers, so if there are other servers, they’ll be grouped in this. Then we specify the format, the input format of the data, so we know how to parse it. Finally, we have this kafka_num_consumers setting, which just says, for this table, use a single consumer. That’s actually a best practice for Kafka, and we’ll talk about it later.

**Robert:** So we have the Kafka engine, which encapsulates the topic, and we have the target table, which is where we want the data to go. Let’s now create a materialized view that’s going to fetch the data out of the Kafka table engine and put it in the target table. This is a relatively simple materialized view. We just select the columns and put them into the readings table.

**Robert:** We actually have a question about Kafka engine development. That’s one we’ll defer until later.

### **16:44 – Live demo: streaming sensor data into ClickHouse**

**Robert:** Meanwhile, what I’d like to do is get in and do a very simple demo. Let’s have a look at this in action. I’ve got a demo where I set up exactly those tables I just showed you, and I have a simple Python client that is generating sensor data for me. You see it on the bottom. In the bottom left-hand corner, the yellow screen is attached to my ClickHouse server, and it’s showing the data being read in after it’s fetched out of the Kafka table engine and put in my MergeTree table. I have a query here that does some aggregations. I’m going to run it again, and it will show me, in fact, let’s put a limit on this.

**Robert:** What you can see is that the numbers change each time I run this. Let’s run it a couple of times. We see 27 is 1,169, now we’re running again and it’s 1,192. The data is being fetched from the topic as it’s arriving.

**Robert:** Let’s also look at proof that the data is arriving in order. That’s something we want to make sure of, that we’re not missing anything. Let’s run a query that shows the latest data coming from sensor 27. We see that it’s coming in. We run it again, there are no gaps in the data. It’s being generated every second. We run it again and we again get the latest data.

**Robert:** That’s showing that the data is feeding in. One thing that’s kind of fun is that now we can add more sensors to this. This is the joy of working with Kafka: you can have as many upstream producers as you want pushing into the same topic, and they will all arrive in ClickHouse without ClickHouse doing anything special. So I’m going to add a new sensor. I’m going to call it 99 for this demo, and I’m starting my client. You can now see data from sensor 99 showing up. Let’s run our query with the counts, and now you can see 99 at the bottom. It’s now showing up in ClickHouse and is available for query.

**Robert:** That’s basically showing the versatility of this. One thing I want to make completely apparent is that this is very low latency, in the sense that the data is put into Kafka, it’s buffered a little bit inside the table engine, and Misha may talk more about that, but as soon as it arrives in ClickHouse it’s basically instantly available for query. This is really important if you have data being generated upstream and you quickly want to be able to run analytics on it. Kafka plus ClickHouse is a great combination.

**Robert:** Let me show you one more interesting thing: it’s possible to get a little bit of metadata about what’s arriving from Kafka, for example the topic name. Let’s say we’d like to know where the data came from. I should have the query here. I’ve actually defined a second materialized view that reads off that same table engine and puts the data into another table. This is something you can do with ClickHouse very easily: you can take the data coming from a Kafka topic and send it to multiple places. I’ve sent it to a table called readings_extra, and this table uses what are called virtual columns. These are data that come from Kafka itself and tell us, for example, that this came from the readings topic on Kafka. If there were a key being used, or a different partition, we would see that in the data.

**Robert:** That’s this demo. What we’re going to do now is move to another question, which is how to write data from ClickHouse back into Kafka.

### **21:18 – Writing data from ClickHouse back to Kafka**

**Robert:** Let’s talk about writing data to Kafka. This is a much simpler flow. We define the Kafka table engine and we just insert into it. It’s as simple as that. We have a topic that has the messages, the Kafka table engine points to it, and anything we insert into that will appear in the messages.

**Robert:** Let’s show how we set that up. In this case we’re going to create the outbound Kafka topic, which I created much as we did the previous inbound topic, with a very simple command. Then we’re going to create the Kafka table engine. Here we have a table called events and a few settings. The Kafka format in this example shows CSV. I think I may have used JSON; we’ll see what comes out. At this point we can go ahead and insert some data into events.

**Robert:** Instead of focusing on the slide, we’ll just demonstrate it. Let’s stop the consumer that was reading, and we’re going to start reading from the events topic. We’re now waiting for data to show up in Kafka. Let’s pull up the insert command. I’m going to insert into the events table, into the Kafka table engine, just like that, and there you go, it shows up as CSV data, exactly what we put in. If we create another kind of message and insert that, it will likewise appear in the topic.

**Robert:** At this point, what we’ve seen is the simple setup to read data from Kafka and also write it back out again. This gives you the ability to develop a number of configurations that allow ClickHouse to share data into Kafka topics. At this point I’d like to turn things over to my colleague Mikhail. He’s going to talk about tips and tricks, some advanced procedures, and additional things you’re going to need to know when you’re actually running this in production. So, Misha, go ahead and take over.

### **24:17 – Kafka engine internals and production best practices**

**Misha:** Hello, everybody. I will talk about how to use it in production: what is required in real use cases, which problems you can face, how to avoid them, and how to use it in the best way.

**Misha:** First of all, let me talk a bit about how it’s built. When you create a ClickHouse table that uses the Kafka engine, under the hood it uses librdkafka, which is one of the most advanced and most performant C libraries for accessing data from Kafka. The library creates a consumer, and that consumer fetches the data from a topic. As Robert showed in his sample, when you create the table you provide some basic settings inside the CREATE TABLE statement. But besides those, there are more advanced settings you can reach through config.xml. For example, you can enable debugging for Kafka by putting “debug” (for example “debug all”) inside the Kafka section in the XML. That is a very high-level look at how it works.

**Misha:** Let me share a few tips and tricks that can help you use it in the most efficient way. First of all, we highly recommend you use[ the latest stable version](https://docs.altinity.com/altinitystablebuilds/), which is now 19.16. It contains a few important fixes for Kafka that at this point exist only in that version, so if you use other versions you can face some issues. This version is really proven; we know it works.

**Misha:** When you use Kafka in production, of course you need high availability. To reach a high-availability state you usually need at least two in-sync brokers. To have high availability you need one extra, so you have three brokers, replication factor three, and at least two should be in sync.

**Misha:** One typical problem when you start using Kafka is that you should understand how to consume the topic in parallel. You need to have enough partitions for the Kafka consumers. For example, if you need to consume the same topic with three ClickHouse nodes, you need to have at least three partitions in that topic. If you have only two partitions, one of the ClickHouse servers will do nothing, because it will have no assigned partition and will not be able to consume anything.

**Misha:** As Robert already said, there is a nice debugging feature to get the coordinates of the messages that were consumed. You can get a topic name, partition, timestamp, key, and offset, and you don’t need to declare them inside the table. You can just select them in your materialized view.

**Misha:** One important piece of information here: when you create a ClickHouse table, it uses a separate thread to copy the data from the topic to the destination table. Those threads use a background schedule pool, and by default it has 16 threads. So if you have a lot of Kafka tables in your environment, you usually need to increase that number; otherwise you can have delays, because there will not be enough threads for everybody. You can monitor how many threads are used through the system metric called BackgroundSchedulePoolTask.

**Misha:** About num_consumers: this was one of the ideas for how to improve performance. When you reach the maximum performance of consuming and you are not satisfied, one of our ideas was to create one more consumer that would be used in the same thread, in the same context, and would consume more data and copy it with the same block into the target table. But that approach didn’t really work. The setting exists, but we don’t recommend it. Instead, you can create a separate table using the Kafka engine and a separate materialized view streaming data to the same target. In that case it will be parallelized, it will run in separate threads, and you will roughly double the performance. With num_consumers, for example with 10 consumers, it will consume in parallel with 10 consumers, but parsing and flushing will still be done in a single thread.

**Misha:** There are a lot of settings in librdkafka. You can [fine-tune some of the timeouts](https://altinity.com/blog/clickhouse-kafka-engine-faq), set up different logins, different compressions, and various default settings. The full list is available, so when you get the presentation you can check it and follow that.

### **32:10 – Clusters, chained materialized views, and replaying topics**

**Misha:** When you have a cluster of ClickHouse and a cluster of Kafka, the usual best practice is that every ClickHouse server consumes some partitions and flushes rows locally to its local replicated table. After that, replication does its work and copies the data to the other ClickHouse servers, but you don’t push the data through a distributed table. The only exception to that rule is when you have to redistribute the data according to some sharding key that is not available for some reason in our options. In that case, when you need to shard by some key that exists in the data, you put the data through a distributed table and it gets redistributed according to that key.

**Misha:** You can also build chains of materialized views, so you can build quite complicated pipelines. For example, from Kafka you copy data to table A, from table A you copy data to tables B and C, from table C you copy data to D and E, and from E, for example, you push the data back to another Kafka topic. So you can use ClickHouse as a stream engine. For now, we actually don’t recommend that, because ClickHouse is not a transactional database, and those pushes of data through the pipeline are not 100 percent reliable. We don’t have atomic properties, so some data can reach deeper stages while another block of data does not, and you will have some inconsistencies. Work on atomic materialized views has already started, and we expect to have an implementation in a future ClickHouse release.

**Misha:** What if you need to rewind or fast-forward some topic? It is quite easy to do. We detach the tables so they stop being used by ClickHouse, and then, using the official Kafka tool called kafka-consumer-groups, you can reset the offsets to latest, for example, or to a specific list, or shift by some amount, and execute that. Your consumer group gets shifted forward or back. After that, you attach the Kafka tables back and consumption continues from the new position. Also, when you start consuming data with a consumer group that does not exist yet, it uses the auto.offset.reset setting, so you can set it to smallest to read from the beginning of a topic, or to latest to read from the end.

### **35:54 – How batching, flushing, and at-least-once delivery work**

**Misha:** How does it work internally? People often wonder whether it flushes by rows or by blocks. As you know, ClickHouse accepts data only one way, inserting in bulk. So the Kafka engine forms the blocks for you.

**Misha:** First, it executes a batch poll, where we extract some messages from Kafka. This is limited: we don’t spend more than half a second on that extraction, and we don’t extract more than 65,000 messages at once. Once the messages are extracted, we go to a second phase, where we parse them. Each message can actually have more than one row. You can have hundreds or thousands of rows in one message. For example, if you read a natural batch of data, you can put the whole batch together in one message, and it will be split by ClickHouse into rows. After we have collected enough data, configured by the max block size (around 65,000 rows), or if we have already waited more than one and a half seconds, we flush our data to a target materialized view. If we don’t reach those limits, the limit by rows or the limit by time, we go back to the first stage and poll another batch.

**Misha:** At some point we go to step three. After we collect enough data, we push that data to the materialized view and commit after the flush. Because the commit happens after writing, it means we use an at-least-once scenario. If something goes wrong in between, during the flushing of data to the materialized view, such as running out of disk space, it will not be committed and you will get back to the previous position, and you will start getting duplicates. If some error happens, the error processing is quite simple: the Kafka client is restarted, and that leads to getting back to the last committed stage.

### **39:15 – Client-side loading, alternative approaches, and roadmap**

**Misha:** The Kafka engine is not the only option for putting data into ClickHouse. ClickHouse has plenty of interfaces and a lot of different integrations, so you can talk to it in a variety of ways. For example, if you already have tools like Flink, NiFi, or Kafka Streams, you can integrate the inserts into your existing pipeline. Instead of the Kafka engine forming the blocks, you form the blocks and insert them into ClickHouse from your existing tools. For example, if you use Flink and you are familiar with Java and that whole stack, you can use the official ClickHouse JDBC driver and put data from Flink, which reads data from Kafka, into ClickHouse. Of course, you can also create your own client that reads the data with whatever technology stack you are most familiar with, for example Golang or Python, and put the data into ClickHouse.

**Robert:** Misha, if I could just add a word here. One of the biggest reasons we see people build their own client, or use something external, is to get things like exactly-once semantics, which the Kafka table engine does not currently support. That’s a pretty common approach to working around that.

**Misha:** Yes, there are reasons when it can help. As I said, right now we have an at-least-once scenario, so we can have more copies of the same data. Right now I’m working on improvements. First of all, we will improve the corner cases when those duplicates appear, namely when a consumer group rebalance happens. Right now we get duplicates in that situation, and we can avoid that. We also have more plans in our roadmap.

**Misha:** Right now I’m working on eliminating duplicates during topic rebalancing. We are planning to add the ability to provide a key and timestamp when producing data from ClickHouse. We have some plans to make error processing better, and also to introduce exactly-once semantics. It is actually part of the Kafka protocol, since around version 1.10 or so. Kafka in recent versions has the ability to guarantee exactly-once semantics, but it should be supported by both sides, by the producer and by the consumer. In that case you can have real guarantees that what you put in is exactly what you get out. From our side, a format that is very popular in the Kafka world has already been merged, as I remember, and we have plans to add some introspection tables, like system.kafka, and to introduce more metrics and events to get feedback on how the engine is working.

**Robert:** Just a quick question on systems: will the additional metrics on Kafka allow you to see lag and things like that?

**Misha:** Yes, that is one of the important features, because at this point you can see that lag, but you need to use external tools that show you how far your consumer group is behind the top of a topic.

**Misha:** We also have some longer-term work. Those plans I mentioned before are really short-term plans; they will be implemented in the next months. For the longer term, we are planning to redesign the issue with num_consumers a bit. At this point I think num_consumers gives nothing, or even makes things worse in some cases. It really is possible to parallelize the parsing and consuming to improve performance, and of course that should be done, but for now it’s not yet planned. I also know there are plans to add more messaging systems, not only Kafka, but also things like RabbitMQ, Pulsar, Redis Pub/Sub, and so on. We are open to discussing it. Implementation will probably happen in the middle of 2020. Of course, we are open to your thoughts and ideas. If you have feature requests or bug reports, you certainly can discuss them and we can make them happen.

**Robert:** At this point we’re actually at the end of what we wanted to present. I just want to say a couple of things and then we’ll answer a bunch of questions. As Misha says, let us know what you want. File issues on GitHub if you want to run it through the community process. If you have a project where some aspect of Kafka support is blocking you from solving a business problem, contact us directly. We can also arrange to do NRE to make things better or to address a specific feature you would like.

**Robert:** With that, we’d like to say thank you. Please do give us feedback on this. I’m going to kick off the poll in just a second. We also have an offer: if you’re using Kafka, or actually using ClickHouse in any way whatsoever, and you’d like an hour of consulting just to understand and evaluate the system you have and talk about different ways to improve it, feel free to contact us. We gladly do a one-hour free consultation to help you understand how to use ClickHouse more effectively, as well as give you ideas about how we can help you. Our contact information is here. I’m going to start the poll. If you would be so kind as to fill in the information, it’s a fairly short series of questions. We use this to decide which webinars to do next and how to make them better.

### **47:59 – Audience questions and answers**

**Robert:** Let’s address the questions. The first one that’s open: Kafka engine was developed by Cloudflare four years ago. What’s the problem with their implementation? Do you have a comment on that?

**Misha:** Cloudflare did a good job, but it was mostly addressed as an internal product. After about a year and a half in ClickHouse, a few more use cases were found. For example, they used only one format, Cap’n Proto. When you start using it for more cases, some problems appear. The implementation wasn’t really perfect in the beginning of 2019. Cloudflare decided they would not continue supporting it, because it was already done for their needs. They made a big chunk of work and put it open source, and just expected that other people would keep it going and make it better. At the beginning of 2019, someone from Yandex picked up the work, but unfortunately it wasn’t a very successful project, because Yandex doesn’t use Kafka internally. When you start doing things you are not really familiar with, more and more problems appear, and there were really big stability issues with Kafka for the first half of 2019. After that we started making it better, and last month we picked it up.

**Robert:** Here’s another one. Is it a good idea to use a buffer table between the materialized view and the storage table?

**Misha:** Usually it’s not necessary. The only exception I can imagine is if you really have a lot of tiny inserts, or, for example, if you use GROUP BY and it creates only a few rows in the materialized view. In those cases it can make sense to use a buffer, but usually not.

**Robert:** I think we covered the topic about num_consumers and why a single consumer is the best practice. To be clear, that is only for ClickHouse. For Kafka in general, and with librdkafka, you can use whatever you want.

**Robert:** The next one: what metrics are available now to monitor the Kafka table, or the lag between the last written offset?

**Misha:** It’s enough to use whatever tool allows you to see the consumer group lag, because a consumer group is not an abstraction of ClickHouse; it’s an abstraction of Kafka. So whatever tools you use for monitoring Kafka should allow you to monitor the lag of your consumer groups. If you use Prometheus or Grafana, or whatever, it should have those metrics.

**Robert:** Exactly-once is in the plan. I think you covered that. In fact, that was a project funded by one of our customers, so we’re really grateful they’re helping. I think we can pass over that one.

**Robert:** There’s a question here about whether our average support includes communication with a schema registry, and what about SSL support for brokers?

**Misha:** For SSL support, you can configure it through the librdkafka settings in the XML file. For the schema registry, that is actually a really hot subject right now. A pull request was merged, as I remember, yesterday. The schema registry part was already in the original pull request, but due to some small issues it was postponed, so probably it will go in as another pull request. It may be delivered a bit later, maybe weeks after. Certainly we need to have support for SSL and for schema keys.

**Robert:** We have another question about SSL in general. SSL support for brokers is covered by the librdkafka settings you discussed, correct?

**Misha:** Yes. librdkafka is extremely capable. As I said at the beginning, it’s an outstanding client and does a lot of stuff.

**Robert:** Here’s a great one. What happens with ClickHouse if the Kafka leader is lost or another error occurs?

**Misha:** If you have a high-availability cluster, nothing bad will happen. It will just connect to another broker and continue. On the Kafka side there will be a rebalance, the leader will shift, and it continues. That’s just normal high-availability behavior. Of course, in some corner cases, I’m not really ready to say what will happen in a particular case, but we can expect duplicates on the ClickHouse side right now.

**Robert:** One thing we should mention is that, besides doing pull requests in the code, we do a lot of test development. That’s because there are so many corner cases. It was somewhat difficult earlier to test the Kafka support effectively, before we could write from ClickHouse back out to Kafka. Now that this is a recent feature addition, it makes it easier for us to construct tests that, for example, show data round-tripping, and it allows us to have more confidence that we’re getting the corner cases.

**Robert:** Here’s one which is not strictly a Kafka question, but I think it’s good. Our main concern while using the ClickHouse JDBC driver is writing data in batches. When writing in batches, we don’t know, based on the exception thrown by the JDBC driver, which record in the batch has a problem.

**Misha:** You can look into the logs. In cases when you need to debug something, you should always look in the logs. The exception messages were improved in recent versions, so in a simple text format you can see the exact position where parsing broke. Still, it’s not a typical use case. Once you get through the initial setup and configure everything to work smoothly, you don’t really get those errors often. It usually happens only during the development process, when you need to adjust things to the requirements of ClickHouse.

**Robert:** The next one: are there plans for creating a connector for an AWS Kinesis broker? That’s a request I’ve heard. Is there any roadmap plan for that?

**Misha:** No, I don’t know. You need to tell us about that.

**Robert:** If that’s something you need, make sure it’s in as an issue, and if you need it accelerated, give us a call. That’s an interesting one, because I know Kinesis is pretty popular.

**Robert:** In the case of several servers of ClickHouse, how are the consumers distributed across servers?

**Misha:** That comes down to consumer group balancing. When you have more consumers attached to a group, it gets rebalanced, and the partitions get distributed between the group. The distribution tries to be as equal as possible. For example, if you have only two servers but three partitions, one server will consume two partitions and the second one will have half the data, because it will only consume one partition. Because of that, you should always have enough partitions for everybody.

**Robert:** Is that protocol supported with Kafka?

**Misha:** Yes, it is.

**Robert:** That’s what I figured. Okay, is there support for a Kafka-ClickHouse connector by Altinity? I’m glad somebody asked that. We’ve actually talked with the Confluent folks about developing this. We don’t have one right now, and I have not seen one in open source that looks really usable. That’s something we’re thinking about. If you have a question about that, please contact us directly. We’d be happy to talk about it.

**Robert:** When using a cluster as well as distributed tables, what is the best practice for resilient client communications? For example, put the cluster behind an F5, or have each client configured to communicate with just one server with failover to a second server?

**Misha:** It’s a bit complicated. You can use load balancers like F5. In general, for high availability, keep in mind that a single load balancer is a single point of failure, so with F5 you need two of them. If you don’t have those F5s and try to build your own load balancing, usually it’s simpler to just load-balance the connections. Also, when a Kafka client connects to a broker, it initially connects to one broker and gets information about the other brokers from that one. That also means each ClickHouse server should have connections to all Kafka brokers.

**Robert:** Here’s an interesting one: an interesting use case of reading Kafka from ClickHouse.

**Misha:** Yes, we have interesting use cases. Right now one of our clients is going to production soon using really complicated pipelines, reading from Kafka and writing to Kafka. I’m not sure how many details I can share here.

**Robert:** I’ve got one which is okay to share, and that is [log management](https://altinity.com/observability-and-logging/). For people who use ClickHouse, you know that materialized views are an incredibly useful feature for aggregating data, but they can also be used, as Misha says, to construct pipelines and pull things out of tables. For example, if you have log management and you want to detect unusual conditions or things that require immediate attention, you can build a materialized view that queries the incoming records and, when it finds something, writes it back out to Kafka, generating events. That’s a good use case.

**Robert:** Here’s another one: if some imported records are corrupted, is there any way to track it, or will the whole batch fail?

**Misha:** There is a feature, which I don’t really like, to skip broken messages, but it’s better not to have broken messages. What will happen is that the whole batch will fail, and it will stop consuming at that point. It will retry, continuing to try to consume the same block every time, and it will stop at the same message. Currently, what you need to do is either fast-forward or enable the setting to skip broken messages, in which case it will go forward.

**Robert:** What’s the limit on chaining materialized views? I think you touched on this, but it’s a good question.

**Misha:** There isn’t any physical limit, like ten or five or seven. It’s always a matter of being performant and getting optimal usage of your server resources. So there is no concrete limit.

**Robert:** I think, as you pointed out, the real limit is that they’re not transactional and can be inconsistent at this time. So if you chain materialized views, you have to be tolerant of having parts inconsistently written across the chain. That’s an important consideration.

**Robert:** Here’s a question: how do I sync the offsets with snapshots in ClickHouse for backup, or should I just use both?

**Misha:** You should just store the virtual columns with your data. When you store the topics and partitions you already consumed inside your data, it’s possible to get back to a position using the detach approach I mentioned.

**Robert:** Then there’s a slightly off-topic question: what is the way to add more shards to the ClickHouse cluster without taking downtime? I can answer that one, because I do a lot of work with Kubernetes. It’s already supported. If you just need to add a shard, which is to say make a server available that’s marked as belonging to one or more shards, all you have to do is update the config.xml and update the cluster definition. Shards and replicas are defined in config.xml, and you can make those changes live, and ClickHouse will pick them up automatically. We use this a lot. We use this capability in Kubernetes because it allows us to add a shard automatically through the Altinity Kubernetes Operator.

**Robert:** The question you might really be asking is how to actually move the data over to that shard, and that’s trickier. If you need to physically rebalance data, taking it from one place and putting it somewhere else, that’s something ClickHouse does not do automatically, so you’ll have to build an application and move the data yourself. Depending on your tolerance for things like duplicates, you may or may not have to take downtime to do that. What many people do when adding shards, if it’s time-series data and they’re only holding it for a week, is just put the shard in and then distribute data evenly across all shards. After seven days, your data will already be distributed, so there’s a natural rebalancing that happens in a lot of time-series cases.

**Robert:** We’re over time at this point by quite a bit. I’d like to thank everybody for attending, and also Misha. We’ve got some questions in the chat section. I’m going to stop recording here, because we’re well over. Actually, we might as well keep recording. Let’s have a quick look at the chat section. Let me pull up the chat. There’s a question in the case of several servers. Okay, I think we’ve answered all the questions.

**Robert:** I would like to thank, in particular, Misha Filimonov for joining us. His expertise is very helpful on this topic. We will be presenting additional webinars on this topic. We’re also going to be putting out a blog article on our blog, so keep an eye on the Altinity blog. Most of the materials you see here will be discussed in a one- or two-part blog article, and we’ll continue to build this out to make it easier for people to understand how Kafka works. Thanks again, everybody, have a great day. We’re delighted that you could join us. Thanks, and see you soon.

## **FAQ**

**What is the ClickHouse Kafka table engine, and how does it load data?**

The Kafka table engine is a special ClickHouse table engine that connects to one or more Kafka topics and fetches messages from them. It is paired with a materialized view that reads rows out of the Kafka engine table and writes them into a target MergeTree table. Together they form a continuous, low-latency pipeline from Kafka into ClickHouse.

**Can ClickHouse write data back to Kafka, not just read from it?**

Yes. You define a Kafka table engine pointing at an outbound topic and simply INSERT into it. Anything inserted into that table appears as messages in the topic. This makes it possible to build pipelines where ClickHouse both consumes and produces Kafka messages, for example generating alert events from incoming logs.

**How many partitions and consumers should I use with ClickHouse and Kafka?**

You need at least as many partitions as the ClickHouse servers that will consume the topic, otherwise some servers sit idle with no assigned partition. Within a single table, a single consumer is the recommended best practice. To add parallelism, create separate Kafka engine tables and materialized views writing to the same target rather than raising num_consumers.

**Does the ClickHouse Kafka engine guarantee exactly-once delivery?**

Not at the time of this webinar. The engine commits offsets after flushing data, which gives at-least-once semantics, so duplicates can appear, for example during a consumer group rebalance. Teams that need exactly-once often load data with an external client instead. Exactly-once support and reduced duplication were on the roadmap.

**How do I replay or reset Kafka messages for ClickHouse?**

Detach the Kafka tables so ClickHouse stops consuming, then use the official kafka-consumer-groups tool to reset offsets, either to latest, to a specific position, or shifted by an amount. Reattach the tables and consumption resumes from the new offset. For new consumer groups, the auto.offset.reset setting controls whether reading starts at the beginning or the end of the topic.

**What are virtual columns in the ClickHouse Kafka engine?**

Virtual columns are metadata fields that come from Kafka itself, such as the topic name, partition, timestamp, key, and offset. You do not declare them in the table definition; you can simply select them in a materialized view. This is useful for tracking where each message came from and for backup or replay scenarios.

## **Copyright disclaimer**

© 2026 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.