[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for Apache Kafka®](../index.md) > [Tutorials](index.md) > Delivering data using Data Transfer > Delivering data from Apache Kafka® to ClickHouse® # Delivering data to Yandex Managed Service for ClickHouse® using Yandex Data Transfer # Delivering data from an Apache Kafka® queue to ClickHouse® using Yandex Data Transfer 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`](https://clickhouse.com/docs/enen/engines/table-engines/integrations/kafka)-engine tables. To set up data delivery from Managed Service for Apache Kafka® to Managed Service for ClickHouse®: 1. [Send test data to the Managed Service for Apache Kafka® topic](#send-sample-data-to-kf). 1. [Prepare and activate your transfer](#prepare-transfer). 1. [Test the transfer](#verify-transfer). If you no longer need the resources you created, [delete them](#clear-out). ## Getting started {#before-you-begin} ### Required paid resources {#paid-resources} * Managed Service for Apache Kafka® cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for Apache Kafka® pricing](../pricing.md)). * Managed Service for ClickHouse® cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)). * Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)). ### Set up your infrastructure {#deploy-infrastructure} {% list tabs group=instructions %} - Manually {#manual} {% note info %} 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. {% endnote %} 1. [Create a Managed Service for Apache Kafka®](../operations/cluster-create.md) source cluster of any suitable [configuration](../concepts/instance-types.md). For connections to the cluster from the user's local machine, rather than the Yandex Cloud network, enable public access to the cluster when creating it. 1. [Create a topic](../operations/cluster-topics.md#create-topic) in the Managed Service for Apache Kafka® cluster. 1. To enable the [producer and consumer](../concepts/producers-consumers.md) to access the topic in the Managed Service for Apache Kafka® cluster, [create](../operations/cluster-accounts.md#create-account) the following users: * With the `ACCESS_ROLE_PRODUCER` role for the producer. * With the `ACCESS_ROLE_CONSUMER` role for the consumer. 1. Create a [Managed Service for ClickHouse® target cluster](../../managed-clickhouse/operations/cluster-create.md) of any suitable [configuration](../../managed-clickhouse/concepts/instance-types.md). For connections to the cluster from the user's local machine, rather than the Yandex Cloud network, enable public access to the cluster when creating it. 1. If using security groups, configure them to allow internet access to your clusters: * [Managed Service for Apache Kafka®](../operations/connect/index.md#configuring-security-groups). * [Managed Service for ClickHouse®](../../managed-clickhouse/operations/connect/index.md#configuring-security-groups). - Terraform {#tf} 1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform). 1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file. 1. [Configure and initialize a provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf). 1. Place the configuration file in a separate working directory and [specify the parameter values](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file. 1. Download the [data-transfer-mkf-mch.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-from-kafka-to-clickhouse/blob/main/data-transfer-mkf-mch.tf) configuration file to the same working directory. This file describes: * [Network](../../vpc/concepts/network.md#network). * [Subnet](../../vpc/concepts/network.md#subnet). * [Security group](../../vpc/concepts/security-groups.md) and rules for access to the clusters from the internet. * Managed Service for Apache Kafka® source cluster. * Topic and two Apache Kafka® users for producer and consumer access. * Managed Service for ClickHouse® target cluster. * Source and target endpoints. * Transfer. 1. In `data-transfer-mkf-mch.tf`, specify the following: * Managed Service for Apache Kafka® source cluster parameters: * `source_user_producer` and `source_password_producer`: Producer's username and password, respectively. * `source_user_consumer` and `source_password_consumer`: Consumer's username and password, respectively. * `source_topic_name`: Topic name. * Managed Service for ClickHouse® target cluster parameters to use for the [target endpoint](../../data-transfer/operations/endpoint/target/clickhouse.md#managed-service): * `target_db_name`: Managed Service for ClickHouse® database name. * `target_user` and `target_password`: Database owner username and password. * `transfer_enabled = 0` disables the creation of endpoints and transfers. They will be created during the [preparation of the transfer](#prepare-transfer). 1. Validate your Terraform configuration files using this command: ```bash terraform validate ``` Terraform will display any configuration errors detected in your files. 1. Create the required infrastructure: 1. Run this command to view the planned changes: ```bash 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. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. 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](https://console.yandex.cloud). {% endlist %} ### Configure additional settings {#additional-settings} 1. Install the following tools: * [kafkacat](https://github.com/edenhill/kcat): For data reads and writes in the Apache Kafka® topic. ```bash 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](../operations/connect/clients.md#bash-zsh). * [clickhouse-client](https://clickhouse.com/docs/enen/interfaces/cli): For connecting to a database within the Managed Service for ClickHouse® cluster. 1. Add the ClickHouse® [DEB repository](https://clickhouse.com/docs/enen/install#install-from-deb-packages): ```bash 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 ``` 1. Install the dependencies: ```bash sudo apt update && sudo apt install --yes clickhouse-client ``` 1. Download the `clickhouse-client` configuration file: ```bash 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](../../managed-clickhouse/operations/connect/clients.md) via `clickhouse-client`. * [jq](https://stedolan.github.io/jq/): For stream processing of JSON files. ```bash sudo apt update && sudo apt-get install --yes jq ``` ## Send test data to the Managed Service for Apache Kafka® topic {#send-sample-data-to-kf} Let's assume your Apache Kafka® topic receives car sensor data. This data will be transmitted as Apache Kafka® messages in JSON format: ```json { "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 [JSONEachRow data format](https://clickhouse.com/docs/enen/interfaces/formats#jsoneachrow) to insert data into `Kafka` tables. This format converts strings from Apache Kafka® messages to the relevant column values. 1. Create a `sample.json` file with test data: {% cut "sample.json" %} ```json { "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 } ``` {% endcut %} 1. Send data from `sample.json` to the Managed Service for Apache Kafka® topic using `jq` and `kafkacat`: ```bash jq -rc . sample.json | kafkacat -P \ -b :9091 \ -t \ -k key \ -X security.protocol=SASL_SSL \ -X sasl.mechanisms=SCRAM-SHA-512 \ -X sasl.username="" \ -X sasl.password="" \ -X ssl.ca.location=/usr/local/share/ca-certificates/Yandex/RootCA.crt -Z ``` ## Prepare and activate a transfer {#prepare-transfer} {% note info %} To rapidly deliver a large volume of data, use [special endpoint settings](../../data-transfer/operations/endpoint/target/clickhouse.md#recommended-settings-queue). {% endnote %} {% list tabs group=instructions %} - Manually {#manual} 1. [Create a source endpoint](../../data-transfer/operations/endpoint/index.md#create): * **Database type**: `Kafka`. * **Endpoint parameters** → **Connection settings**: * **Connection type**: `Managed Service for Apache Kafka cluster`. * **Managed Service for Apache Kafka cluster**: Select the source cluster from the list. * **Authentication**: * **Username**: Enter the consumer username. * **Password**: Enter the consumer password. * **Topic full name**: Enter the name of the topic in the Managed Service for Apache Kafka® cluster. * **Advanced settings** → **Conversion rules**: * **Data format**: `JSON`. * **Data scheme**: `JSON specification`: Copy and paste the data schema in JSON format: {% cut "Data schema" %} ```json [ { "name": "device_id", "type": "string" }, { "name": "datetime", "type": "datetime" }, { "name": "latitude", "type": "double" }, { "name": "longitude", "type": "double" }, { "name": "altitude", "type": "double" }, { "name": "speed", "type": "double" }, { "name": "battery_voltage", "type": "any" }, { "name": "cabin_temperature", "type": "double" }, { "name": "fuel_level", "type": "any" } ] ``` {% endcut %} 1. [Create a target endpoint](../../data-transfer/operations/endpoint/index.md#create): * **Database type**: `ClickHouse`. * **Endpoint parameters**: * **Connection settings**: * **Connection type**: `Managed cluster`. * **Managed cluster**: Select the source cluster from the list. * **Database**: Enter the database name. * **User** and **Password**: Enter the name and password of the user who has access to the database, e.g., the database owner. * **Advanced settings** → **Upload data in JSON format**: Enable this option if you enabled **Conversion rules** in the advanced settings of the source endpoint. 1. [Create a transfer](../../data-transfer/operations/transfer.md#create) of the **_Replication_**-type that will use the endpoints you created. 1. [Activate](../../data-transfer/operations/transfer.md#activate) the transfer. - Terraform {#tf} 1. Specify `transfer_enabled = 1` in the `data-transfer-mkf-mch.tf` file. 1. Validate your Terraform configuration files using this command: ```bash terraform validate ``` Terraform will display any configuration errors detected in your files. 1. Create the required infrastructure: 1. Run this command to view the planned changes: ```bash 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. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. Wait for the operation to complete. Endpoints and a transfer will be created. The transfer will be activated automatically as soon as it is created. {% endlist %} ## Test the transfer {#verify-transfer} 1. Wait for the transfer status to change to **Replicating**. 1. Make sure the data from the Managed Service for Apache Kafka® source cluster has been transferred to the Managed Service for ClickHouse® database: 1. [Connect to the cluster](../../managed-clickhouse/operations/connect/clients.md#clickhouse-client) using `clickhouse-client`. 1. Run this query: ```sql SELECT * FROM . ``` 1. Change values in the `sample.json` file and send data from it to the Managed Service for Apache Kafka® topic: ```bash jq -rc . sample.json | kafkacat -P \ -b :9091 \ -t \ -k key \ -X security.protocol=SASL_SSL \ -X sasl.mechanisms=SCRAM-SHA-512 \ -X sasl.username="" \ -X sasl.password="" \ -X ssl.ca.location=/usr/local/share/ca-certificates/Yandex/RootCA.crt -Z ``` 1. Check that the Managed Service for ClickHouse® database shows the new values: 1. [Connect to the cluster](../../managed-clickhouse/operations/connect/clients.md#clickhouse-client) using `clickhouse-client`. 1. Run this query: ```sql SELECT * FROM . ``` ## Delete the resources you created {#clear-out} {% note info %} Before deleting the resources, [deactivate the transfer](../../data-transfer/operations/transfer.md#deactivate). {% endnote %} Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them: {% list tabs group=instructions %} - Manually {#manual} 1. [Delete the transfer](../../data-transfer/operations/transfer.md#delete). 1. [Delete](../../data-transfer/operations/endpoint/index.md#delete) the source and target endpoints. 1. [Delete the Managed Service for Apache Kafka® cluster](../operations/cluster-delete.md). 1. [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md). - Terraform {#tf} 1. In the terminal window, go to the directory containing the infrastructure plan. {% note 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. {% endnote %} 1. Delete resources: 1. Run this command: ```bash terraform destroy ``` 1. Confirm deleting the resources and wait for the operation to complete. All the resources described in the Terraform manifests will be deleted. {% endlist %} _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._