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

Data delivery in Managed Service for ClickHouse®

Written by
Updated at December 3, 2025

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. Verify that the test data appears in the Managed Service for ClickHouse® cluster tables.

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, arrange the SQL queries below as distributed queries with your cluster name in curly brackets: CREATE ... ON CLUSTER '{cluster}'. Additionally, in queries with the MergeTree table engine specified, use ReplicatedMergeTree instead.

The support cost for this solution includes:

  • Managed Service for Apache Kafka® cluster fee: Covers the use of computational resources allocated to hosts (including ZooKeeper hosts) and disk storage (see Apache Kafka® pricing).
  • Managed Service for ClickHouse® cluster fee: Covers the use of computational resources allocated to hosts (including ZooKeeper hosts) and disk storage (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 the infrastructure

  1. Create the required number of Managed Service for Apache Kafka® clusters with your preferred configuration. Enable public access to your clusters during creation so you can connect to them from your local machine. Connections from within the Yandex Cloud network are enabled by default.

  2. Create a Managed Service for ClickHouse® cluster with a single shard and a database named db1. Enable public access to the cluster during creation so you can connect to it from your local machine. Connections from within the Yandex Cloud network are enabled by default.

    Note

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

  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 access to topics for producers and consumers, create two users in each Managed Service for Apache Kafka® cluster:

    • Producer: A user assigned the ACCESS_ROLE_PRODUCER role.
    • Consumer: A user assigned the ACCESS_ROLE_CONSUMER role.

    Usernames are not required to be unique 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 your current 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 these resource blocks and provide a unique name for each. Usernames are not required to be unique 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. Make sure the Terraform configuration files are correct using this command:

    terraform validate

    Terraform will show any errors found in your configuration 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 reading from and writing to Apache Kafka® topics.

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

      Verify that 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 for all topics.

    Authentication data:

  • Multiple Apache Kafka® clusters: Create enough 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 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>"
        }
    }
}

      For multiple topics, duplicate the kafka_topic section for each one, specifying the relevant topic names.

  2. Check if 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

Suppose your Apache Kafka® topics receive car sensor data in JSON format. This data will be transmitted as Apache Kafka® messages, each containing a string in the following format:

{"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 that parses 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 credentials for the ACCESS_ROLE_CONSUMER role account.

For more details on creating Kafka-engine 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 the account with the ACCESS_ROLE_PRODUCER role. To learn more about setting up an SSL certificate and using kafkacat, see Connecting to a Apache Kafka® cluster from applications.

Verify that the test data appears in the Managed Service for ClickHouse® cluster tables

To access the data, use a materialized view. Once a materialized view is attached to a Kafka table, it starts gathering 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 retrieve all data from your 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 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
Delivering data from MySQL® using Debezium
Next
Delivering data to ksqlDB