Contact UsGet started
© 2024 Direct Cursus Technology L.L.C.

Data delivery in Managed Service for ClickHouse®

Written by
Updated at November 27, 2024

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

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

  1. Set up integration with Apache Kafka® for the Managed Service for ClickHouse®.
  2. Create Kafka tables in the Managed Service for ClickHouse® cluster.
  3. Send test data to Managed Service for Apache Kafka® topics.
  4. Check that the test data is present in the Managed Service for ClickHouse® cluster tables.

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

Getting started

Prepare the infrastructure

  1. Create the required number of Managed Service for Apache Kafka® clusters in any suitable configuration. To connect to clusters from a user's local machine instead of the Yandex Cloud cloud network, enable public access to clusters when creating them.

  2. Create a Managed Service for ClickHouse® cluster with a single shard and a database named db1. To connect to the cluster from the user's local machine rather than doing so from the Yandex Cloud cloud network, enable public access to the cluster when creating it.

    Note

    You can set up Apache Kafka® integration when creating a cluster. In this tutorial, integration will be set up later.

  3. If you are using security groups, configure them to enable connecting to the clusters from the internet:

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

  5. To enable producers and consumers to work with topics, create two users per Managed Service for Apache Kafka® cluster:

    • User with the ACCESS_ROLE_PRODUCER role for the producer.
    • User with the ACCESS_ROLE_CONSUMER role for the consumer.

    Users in different clusters may have the same names.

  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 rules required to connect to the clusters from the internet.

    • Managed Service for Apache Kafka® cluster.

    • Topic and two Managed Service for Apache Kafka® users on whose behalf the producer and consumer will connect to the topic, respectively.

      To create multiple topics or clusters, duplicate blocks with their description and specify new unique names. Users in different clusters may have the same names.

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

  6. Specify the following in the data-from-kafka-to-clickhouse.tf file:

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

  7. Make sure the Terraform configuration files are correct using this command:

    terraform validate

    If there are any errors in the configuration files, Terraform will point them out.

  8. Create the required infrastructure:

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of 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 utilities:

    • kafkacat to read and write data to Apache Kafka® topics.

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

      Check that you can use it to connect to Managed Service for Apache Kafka® clusters over SSL.

    • clickhouse-client: To connect to the database in the Managed Service for ClickHouse® cluster.

      1. Connect 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 configuration file for clickhouse-client:

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

      Check that you can use it to connect to the Managed Service for ClickHouse® cluster over SSL.

    • jq for JSON file stream processing.

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

Set up integration with Apache Kafka® for the Managed Service for ClickHouse® cluster

Depending on the number of Managed Service for Apache Kafka® clusters:

  • If there is a single Apache Kafka® cluster, specify authentication data under DBMS settings → Kafka. In this case, the Managed Service for ClickHouse® cluster will use these authentication credentials to access any topic.
  • If there are multiple Apache Kafka® clusters, specify authentication data for each Managed Service for Apache Kafka® topic in the Managed Service for ClickHouse® cluster settings under DBMS settings → Kafka topics.

Authentication data:

  1. Depending on the number of Managed Service for Apache Kafka® clusters:

    • If there is a 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>"
    }
}

    • If there are multiple Apache Kafka® clusters, uncomment the clickhouse.config.kafka_topic section and specify the authentication 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 there are multiple topics in the clusters, duplicate the kafka_topic section as many times as required stating the relevant topic names.

  2. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  3. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

Create Kafka tables in the Managed Service for ClickHouse® cluster

For example, Apache Kafka® topics receive some data from car sensors in JSON format. This data will be sent 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}

The Managed Service for ClickHouse® cluster will use the JSONEachRow data format to insert data into Kafka tables. This format converts strings from Apache Kafka® messages into the appropriate column values.

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

  1. Connect to the db1 database in the Managed Service for ClickHouse® cluster using clickhouse-client.

  2. Run this request:

    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';

The created tables will be automatically populated with messages that are read from Managed Service for Apache Kafka® topics. To read data, Managed Service for ClickHouse® uses the settings configured earlier for users with the ACCESS_ROLE_CONSUMER role.

To learn more about creating Kafka tables, see the ClickHouse® documentation.

Send test data to Managed Service for Apache Kafka® topics

  1. Create a sample.json file 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 on behalf of users with the ACCESS_ROLE_PRODUCER role. To learn more about setting up an SSL certificate and working with kafkacat, see Connecting to a Apache Kafka® cluster from applications.

Check that the test data is present in the Managed Service for ClickHouse® cluster tables

To access the data, use a materialized view. When a materialized view is added to a Kafka table, it starts collecting data in the background. This allows you to continuously receive messages from Apache Kafka® and convert them to the required format using SELECT.

Note

Since ClickHouse® can read a message from a topic only once, we do not recommend reading data directly from the table.

To create a materialized view:

  1. Connect to the db1 database in the Managed Service for ClickHouse® cluster using 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 the data from the appropriate materialized view:

  1. Connect to the db1 database in the Managed Service for ClickHouse® cluster using clickhouse-client.

  2. Run this request:

    SELECT * FROM db1.<view_name>;

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

To learn more about how to work with data received from Apache Kafka®, see the ClickHouse® documentation.

Delete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

To delete the infrastructure created with Terraform:

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

  2. Delete the data-from-kafka-to-clickhouse.tf configuration file.

  3. Make sure the Terraform configuration files are correct using this command:

    terraform validate

    If there are any errors in the configuration files, Terraform will point them out.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

    All the resources described in the data-from-kafka-to-clickhouse.tf configuration file will be deleted.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Delivering data from MySQL® using Debezium
Next
Delivering data to ksqlDB