Contact UsTry it for free
© 2026 Direct Cursus Technology L.L.C.

Fetching data from Yandex Managed Service for Apache Kafka® to Yandex Managed Service for ClickHouse®

Written by
Updated at February 25, 2026

A Managed Service for ClickHouse® cluster can ingest data from Apache Kafka® topics in real time. This data will be automatically inserted into ClickHouse® Kafka-engine tables.

To set up data delivery from Managed Service for Apache Kafka® to Managed Service for ClickHouse®:

  1. Set up Apache Kafka® integration for your Managed Service for ClickHouse® cluster.
  2. Create Kafka-engine tables in your Managed Service for ClickHouse® cluster.
  3. Send test data to your Managed Service for Apache Kafka® topics.
  4. Check Managed Service for ClickHouse® cluster tables for test data.

If you no longer need the resources you created, delete them.

Warning

This tutorial uses a single-host ClickHouse® cluster. If your cluster has more than one ClickHouse® host, use a distributed query by specifying your cluster name in the SQL statements below: CREATE ... ON CLUSTER '{cluster}'. Additionally, use the ReplicatedMergeTree table engine to replace MergeTree wherever it appears in the statements.

The support cost for this solution includes:

  • Managed Service for Apache Kafka® cluster fee, which covers the use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see Apache Kafka® pricing).
  • Managed Service for ClickHouse® cluster fee, which covers the use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see Managed Service for ClickHouse® pricing).
  • Fee for public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Getting started

Set up your infrastructure

Note

Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster.

  1. Create the required number of Managed Service for Apache Kafka® clusters of any suitable configuration. To be able to connect to the clusters not only from within the Yandex Cloud network but also from a local machine, enable public access when creating them.

  2. Create a Managed Service for ClickHouse® cluster with a single shard and a database named db1. To be able to connect to the cluster not only from within the Yandex Cloud network but also from your local machine, enable public access when creating it.

    Note

    Integration with Apache Kafka® is available during cluster creation. In this tutorial, however, we will configure the integration at a later step.

  3. If using security groups, configure them to allow internet access to your clusters:

  4. Create the required number of topics in Managed Service for Apache Kafka® clusters. Make sure the topic names are unique.

  5. To enable the producer and consumer to access topics, create two users in each Managed Service for Apache Kafka® cluster:

    • With the ACCESS_ROLE_PRODUCER role for the producer.
    • With the ACCESS_ROLE_CONSUMER role for the consumer.

    Usernames may be the same across clusters.

  1. If you do not have Terraform yet, install it.

  2. Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.

  3. Configure and initialize a provider. There is no need to create a provider configuration file manually, you can download it.

  4. Place the configuration file in a separate working directory and specify the parameter values. If you did not add the authentication credentials to environment variables, specify them in the configuration file.

  5. Download the data-from-kafka-to-clickhouse.tf configuration file to the same working directory.

    This file describes:

    • Network.

    • Subnet.

    • Default security group and inbound internet rules for the cluster.

    • Managed Service for Apache Kafka® cluster.

    • Topic and two Managed Service for Apache Kafka® users for producer and consumer access.

      To create multiple topics or clusters, duplicate the sections with their descriptions and provide a unique name for each. Usernames may be the same across clusters.

    • Managed Service for ClickHouse® cluster with a single shard and a database named db1.

  6. In the data-from-kafka-to-clickhouse.tf file, specify the following:

    • Managed Service for Apache Kafka® version.
    • Usernames and passwords of the accounts with the ACCESS_ROLE_PRODUCER and ACCESS_ROLE_CONSUMER roles in your Managed Service for Apache Kafka® clusters.
    • Names of topics in the Managed Service for Apache Kafka® clusters.
    • Username and password that will be used to access your Managed Service for ClickHouse® cluster.

  7. Validate your Terraform configuration files using this command:

    terraform validate

    Terraform will display any configuration errors detected in your files.

  8. Create the required infrastructure:

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

    All the required resources will be created in the specified folder. You can check resource availability and their settings in the management console.

Configure additional settings

  1. Install the following tools:

    • kafkacat: For data reads and writes in Apache Kafka® topics.

      sudo apt update && sudo apt install --yes kafkacat

      Make sure you can use it to establish SSL connections to your Managed Service for Apache Kafka® clusters.

    • clickhouse-client: For connecting to a database within the Managed Service for ClickHouse® cluster.

      1. Add the ClickHouse® DEB repository:

        sudo apt update && sudo apt install --yes apt-transport-https ca-certificates dirmngr && \
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4 && \
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
/etc/apt/sources.list.d/clickhouse.list

      2. Install the dependencies:

        sudo apt update && sudo apt install --yes clickhouse-client

      3. Download the clickhouse-client configuration file:

        mkdir -p ~/.clickhouse-client && \
wget "https://storage.yandexcloud.net/doc-files/clickhouse-client.conf.example" \
  --output-document ~/.clickhouse-client/config.xml

      Verify that you can establish an SSL connection to the Managed Service for ClickHouse® cluster via clickhouse-client.

    • jq: For stream processing of JSON files.

      sudo apt update && sudo apt-get install --yes jq

Set up Apache Kafka® integration for your Managed Service for ClickHouse® cluster

Configuration depends on how many Managed Service for Apache Kafka® clusters you have:

  • Single Apache Kafka® cluster: Provide authentication data in the ClickHouse® settings, under DBMS settings → Kafka. The Managed Service for ClickHouse® cluster will use these credentials when accessing any topic.

    Authentication data:

  • Multiple Apache Kafka® clusters: Create the required number of named collections containing authentication data for each Managed Service for Apache Kafka® topic:

    1. Connect to the db1 database on your Managed Service for ClickHouse® cluster via clickhouse-client.

    2. Run the following query as many times as needed, providing the authentication data for each topic:

      CREATE NAMED COLLECTION <collection_name> AS
    kafka_broker_list = '<broker_host_FQDN>:9091',
    kafka_topic_list = '<topic_name>',
    kafka_group_name = 'sample_group',
    kafka_format = 'JSONEachRow'
    kafka_security_protocol = 'SASL_SSL',
    kafka_sasl_mechanism = 'SCRAM-SHA-512',
    kafka_sasl_username = '<username_for_consumer>',
    kafka_sasl_password = '<user_password_for_consumer>';

  1. Configuration depends on how many Managed Service for Apache Kafka® clusters you have:

    • Single Apache Kafka® cluster: Uncomment the clickhouse.config.kafka section in the data-from-kafka-to-clickhouse.tf file:

      config {
    kafka {
        security_protocol = "SECURITY_PROTOCOL_SASL_SSL"
        sasl_mechanism    = "SASL_MECHANISM_SCRAM_SHA_512"
        sasl_username     = "<username_for_consumer>"
        sasl_password     = "<user_password_for_consumer>"
    }
}

    • Multiple Apache Kafka® clusters: Uncomment the clickhouse.config.kafka_topic section and specify the credentials for each Managed Service for Apache Kafka® topic:

      config {
    kafka_topic {
        name = "<topic_name>"
        settings {
        security_protocol = "SECURITY_PROTOCOL_SASL_SSL"
        sasl_mechanism    = "SASL_MECHANISM_SCRAM_SHA_512"
        sasl_username     = "<username_for_consumer>"
        sasl_password     = "<user_password_for_consumer>"
        }
    }
}

      If your clusters have more than one topic, duplicate the kafka_topic section for each one, specifying the relevant topic names.

  2. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  3. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Create Kafka-engine tables in your Managed Service for ClickHouse® cluster

Let's assume your Apache Kafka® topics receive some car sensor data in JSON format. This data will be transmitted as Apache Kafka® messages, each containing a string like this:

{"device_id":"iv9a94th6rzt********","datetime":"2020-06-05 17:27:00","latitude":"55.70329032","longitude":"37.65472196","altitude":"427.5","speed":"0","battery_voltage":"23.5","cabin_temperature":"17","fuel_level":null}

For Kafka table inserts, the Managed Service for ClickHouse® cluster will use the JSONEachRow format, which converts rows from Apache Kafka® messages into the required column values.

For each Apache Kafka® topic, create a separate table for incoming data in your Managed Service for ClickHouse® cluster:

  1. Connect to the db1 database on your Managed Service for ClickHouse® cluster via clickhouse-client.

  2. Run this query:

    CREATE TABLE IF NOT EXISTS db1.<table_name_for_topic>
(
    device_id String,
    datetime DateTime,
    latitude Float32,
    longitude Float32,
    altitude Float32,
    speed Float32,
    battery_voltage Nullable(Float32),
    cabin_temperature Float32,
    fuel_level Nullable(Float32)
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = '<broker_host_FQDN>:9091',
    kafka_topic_list = '<topic_name>',
    kafka_group_name = 'sample_group',
    kafka_format = 'JSONEachRow';

    CREATE TABLE IF NOT EXISTS db1.<table_name_for_topic>
(
    device_id String,
    datetime DateTime,
    latitude Float32,
    longitude Float32,
    altitude Float32,
    speed Float32,
    battery_voltage Nullable(Float32),
    cabin_temperature Float32,
    fuel_level Nullable(Float32)
) ENGINE = Kafka(<name_of_collection_with_authentication_data>);

These tables will be automatically populated with messages consumed from Managed Service for Apache Kafka® topics. When reading data, Managed Service for ClickHouse® uses the previously configured settings for users with the ACCESS_ROLE_CONSUMER role.

For more information about creating Kafka tables, see this ClickHouse® article.

Send test data to your Managed Service for Apache Kafka® topics

  1. Create a file named sample.json with test data:

    {
    "device_id": "iv9a94th6rzt********",
    "datetime": "2020-06-05 17:27:00",
    "latitude": 55.70329032,
    "longitude": 37.65472196,
    "altitude": 427.5,
    "speed": 0,
    "battery_voltage": 23.5,
    "cabin_temperature": 17,
    "fuel_level": null
}

{
    "device_id": "rhibbh3y08qm********",
    "datetime": "2020-06-06 09:49:54",
    "latitude": 55.71294467,
    "longitude": 37.66542005,
    "altitude": 429.13,
    "speed": 55.5,
    "battery_voltage": null,
    "cabin_temperature": 18,
    "fuel_level": 32
}

{
    "device_id": "iv9a94th6rzt********",
    "datetime": "2020-06-07 15:00:10",
    "latitude": 55.70985913,
    "longitude": 37.62141918,
    "altitude": 417.0,
    "speed": 15.7,
    "battery_voltage": 10.3,
    "cabin_temperature": 17,
    "fuel_level": null
}

  2. Send data from sample.json to each Managed Service for Apache Kafka® topic using jq and kafkacat:

    jq -rc . sample.json | kafkacat -P \
   -b <broker_host_FQDN>:9091 \
   -t <topic_name> \
   -k key \
   -X security.protocol=SASL_SSL \
   -X sasl.mechanisms=SCRAM-SHA-512 \
   -X sasl.username="<username_for_producer>" \
   -X sasl.password="<user_password_for_producer>" \
   -X ssl.ca.location=/usr/local/share/ca-certificates/Yandex/RootCA.crt -Z

Data is sent using the credentials of users with the ACCESS_ROLE_PRODUCER role. To learn more about setting up an SSL certificate and using kafkacat, see Connecting to an Apache Kafka® cluster from applications.

Check Managed Service for ClickHouse® cluster tables for test data

To access the data, use a materialized view. Once a materialized view is attached to a Kafka table, it starts collecting data in the background automatically. This enables the system to continuously consume messages from Apache Kafka® and convert them to the required format using SELECT.

Note

We do not recommend reading data directly from the table because ClickHouse® can read a message from a topic only once.

To create a materialized view:

  1. Connect to the db1 database on your Managed Service for ClickHouse® cluster via clickhouse-client.

  2. Run the following queries for each Kafka table:

    CREATE TABLE db1.temp_<table_name_for_topic>
(
    device_id String,
    datetime DateTime,
    latitude Float32,
    longitude Float32,
    altitude Float32,
    speed Float32,
    battery_voltage Nullable(Float32),
    cabin_temperature Float32,
    fuel_level Nullable(Float32)
) ENGINE = MergeTree()
ORDER BY device_id;

    CREATE MATERIALIZED VIEW db1.<view_name> TO db1.temp_<table_name_for_topic>
    AS SELECT * FROM db1.<table_name_for_topic>;

To get all data from the materialized view:

  1. Connect to the db1 database on your Managed Service for ClickHouse® cluster via clickhouse-client.

  2. Run this query:

    SELECT * FROM db1.<view_name>;

This query will return a table with data sent to the respective Managed Service for Apache Kafka® topic.

To learn more about working with data received from Apache Kafka®, see this ClickHouse® article.

Delete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

  1. In the terminal window, go to the directory containing the infrastructure plan.

    Warning

    Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.

  2. Delete resources:

    1. Run this command:

      terraform destroy

    2. Confirm deleting the resources and wait for the operation to complete.

    All the resources described in the Terraform manifests will be deleted.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Configuring Managed Service for ClickHouse® for Graphite
Next
Fetching data from Managed Service for Apache Kafka® to ksqlDB