Saving a Yandex Data Streams data stream in Yandex Managed Service for ClickHouse

Written by

Yandex Cloud

Before you begin
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 Streams data stream to Managed Service for ClickHouse.

To transfer data:

If you no longer need these resources, delete them.

Before you begin

Prepare the infrastructure:

Manually

Using 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 settings: Managed Service for ClickHouse cluster.
      - Managed Service for ClickHouse cluster: Select the source cluster from the list.
    - Database: Enter the name of the database.
    - User and Password: Enter the name and password of the user who has access to the database, for example, the database owner.

If you don't have Terraform, install and configure it.
Download the file with provider settings. Place it in a separate working directory and specify the parameter values.
Download the configuration file data-transfer-yds-mch.tf 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 in the file data-transfer-yds-mch.tf:
- folder_id: ID of the folder to create resources in.
- sa_name: The 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: ClickHouse database owner username and password.
- transfer_enabled: Set 0 to ensure that no transfer is created before a source endpoint is created manually.
Run the command terraform init in the directory with the configuration file. This command initializes the provider specified in the configuration files and enables you to use the provider resources and data sources.
Make sure the Terraform configuration files are correct using the command:
```
terraform validate
```
If there are errors in the configuration files, Terraform will point to them.
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's sensors in JSON format as a message:

{
    "device_id":"iv9a94th6rztooxh5ur2",
    "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

Create a source endpoint:

Database type: Yandex Data Streams.

Endpoint parameters:

Connection settings:
- Database: Select a Managed Service for YDB database from the list.
- Data 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:

Data format: JSON.

Data schema: 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 the json_schema.json 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

Using Terraform
1. Create a transfer of the Increment type that will use the created endpoints.
2. Activate it.
1. Specify the variables in the file data-transfer-yds-mch.tf:
  - source_endpoint_id: ID of the source endpoint.
  - transfer_enabled: Set 1 to enable transfer creation.
2. Make sure the Terraform configuration files are correct using the command:
  terraform validate
  If there are errors in the configuration files, Terraform will point to them.
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, a transfer is 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":"rhibbh3y08qmz3sdbrbu",
    "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 that the data from the Data Streams data 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, disable the transfer.

If you no longer need these resources, delete them:

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

Using Terraform
1. In the terminal window, change to the directory containing the infrastructure plan.
2. Delete the configuration file data-transfer-yds-mch.tf.
3. Make sure the Terraform configuration files are correct using the command:
  terraform validate
  If there are errors in the configuration files, Terraform will point to them.
4. Confirm the update of 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 configuration file data-transfer-yds-mch.tf will be deleted.
Delete the source endpoint.