Delivering data from a YDS queue to ClickHouse®

Written by

Yandex Cloud

Updated at November 6, 2024

Getting started
Prepare a Data Streams data stream
Prepare and activate the transfer
Test the transfer
Delete the resources you created

With Data Transfer, you can deliver data from a data stream Data Streams to Managed Service for ClickHouse®.

To transfer data:

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

Getting started

Prepare the infrastructure:

Manually

Terraform

Create a Managed Service for YDB database in any suitable configuration.
Create a Managed Service for ClickHouse® cluster with any suitable configuration.
Create a target endpoint:
- 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 (for example, the database owner).

If you do not have Terraform yet, install it.
Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.
Configure and initialize a provider. There is no need to create a provider configuration file manually, you can download it.
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.
Download the data-transfer-yds-mch.tf configuration file to the same working directory.

This file describes:
- Managed Service for YDB database.
- Service account with the yds.editor role.
- Network.
- Subnet.
- Security group and rules required to connect to the Managed Service for ClickHouse® cluster from the internet.
- Managed Service for ClickHouse® target cluster.
- Target endpoint.
- Transfer.
Specify the following in the data-transfer-yds-mch.tf file:
- folder_id: ID of the folder the resources will be created in.
- sa_name: Name of the service account to be used in endpoints.
- source_db_name: Managed Service for YDB database name.
- target_db_name: ClickHouse® database name.
- target_user and target_password: Username and password of the ClickHouse® database owner.
- transfer_enabled: 0 to ensure that no transfer is created before a source endpoint is created manually.
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.
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.

Prepare a Data Streams data stream

Create a Data Streams data stream.
Send test data to the data stream. Use data from the vehicle sensors in JSON format as a message:

{
    "device_id":"iv9a94th6rzt********",
    "datetime":"2022-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
}

Prepare and activate the transfer

Note

To rapidly deliver a large volume of data, use special endpoint settings.

Create a source endpoint:

Database type: Yandex Data Streams.

Endpoint parameters:

Connection settings:
- Database: Select the Managed Service for YDB database from the list.
- Stream: Specify the name of the Data Streams data stream.
- Service account: Select or create a service account with the yds.editor role.

Advanced settings:

Conversion rules: JSON.

Data scheme: You can specify a schema in two ways:

Field list.

Set a list of topic fields manually:

Name	Type	Key
`device_id`	`STRING`	Yes
`datetime`	`DATETIME`
`latitude`	`DOUBLE`
`longitude`	`DOUBLE`
`altitude`	`DOUBLE`
`speed`	`DOUBLE`
`battery_voltage`	`ANY`
`cabin_temperature`	`DOUBLE`
`fuel_level`	`ANY`

JSON specification.

Create and upload a json_schema.json data schema file in JSON format:

json_schema.json

[
    {
        "name": "device_id",
        "type": "string",
        "key": true
    },
    {
        "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"
    }
]

Create a transfer:
Manually

Terraform
1. Create a transfer of the Replication type that will use the created endpoints.
2. Activate your transfer.
1. In the data-transfer-yds-mch.tf file, specify these variables:
  - source_endpoint_id: ID of the source endpoint.
  - transfer_enabled: 1 to create a transfer.
2. 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.
3. 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:
    
    Run the command:
    
    terraform apply
    
    Confirm the update of resources.
    
    Wait for the operation to complete.
  Once created, your transfer will be activated automatically.

Test the transfer

Wait for the transfer status to change to Replicating.

Send a new message to the data stream Data Streams:

{
    "device_id":"rhibbh3y08qm********",
    "datetime":"2022-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
}

Make sure the data from the Data Streams stream has been moved to the Managed Service for ClickHouse® cluster database:
1. Connect to the Managed Service for ClickHouse® target cluster.
2. Check that the ClickHouse® database contains a table with the name of the created Data Streams data stream with the same columns as the data schema in the source endpoint and the sent test data.

Delete the resources you created

Note

Before deleting the created resources, deactivate the transfer.

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

Delete the transfer.
Delete the resources depending on how they were created:
Manually

Terraform
1. In the terminal window, go to the directory containing the infrastructure plan.
2. Delete the data-transfer-yds-mch.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:
    
    Run the command:
    
    terraform apply
    
    Confirm the update of resources.
    
    Wait for the operation to complete.
  All the resources described in the data-transfer-yds-mch.tf configuration file will be deleted.
Delete the source endpoint.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Delivering data from a YDS queue to ClickHouse®

Getting startedGetting started

Prepare a Data Streams data streamPrepare a Data Streams data stream

Prepare and activate the transferPrepare and activate the transfer

Test the transferTest the transfer

Delete the resources you createdDelete the resources you created

Was the article helpful?

Getting started

Prepare a Data Streams data stream

Prepare and activate the transfer

Test the transfer

Delete the resources you created