Saving data to Yandex Managed Service for ClickHouse®
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:
-
Create a Managed Service for YDB database in any suitable configuration.
-
Create a Managed Service for ClickHouse® cluster with any suitable configuration.
-
-
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 in the
data-transfer-yds-mch.tf
file:folder_id
: ID of the folder to create resources 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
andtarget_password
: ClickHouse® database owner username and password.transfer_enabled
: Set0
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:
-
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.
-
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 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
-
-
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 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" } ]
-
- Conversion rules:
-
-
-
Create a transfer:
ManuallyTerraform- Create a transfer of the Replication type that will use the created endpoints.
- Activate your transfer.
-
Specify the variables in the
data-transfer-yds-mch.tf
file:source_endpoint_id
: ID of the source endpoint.transfer_enabled
: Set to1
to enable transfer creation.
-
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:
-
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.
-
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:
- Connect to the Managed Service for ClickHouse® target cluster.
- 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 resources depending on how they were created:
ManuallyTerraform-
In the terminal window, go to the directory containing the infrastructure plan.
-
Delete the
data-transfer-yds-mch.tf
configuration file. -
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.
-
Confirm updating the resources.
-
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.
-
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. -
-
ClickHouse® is a registered trademark of ClickHouse, Inc