Loading data from YDB to a ClickHouse® data mart

Written by

Updated at September 25, 2025

With Data Transfer, you can upload data from a Managed Service for YDB database to a Managed Service for ClickHouse® cluster.

To upload data:

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

Required paid resources

The support cost for this solution includes:

Fee for the Managed Service for YDB. The charge depends on the usage mode:
- For the serverless mode, you pay for data operations and the amount of stored data.
- For the dedicated instance mode, you pay for the use of computing resources, dedicated DBs, and disk space.
Learn more about the Managed Service for YDB pricing plans.
Managed Service for ClickHouse® cluster fee: Using computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see Managed Service for ClickHouse® pricing).
Fee for using public IP addresses for cluster hosts (see Virtual Private Cloud pricing).

Getting started

Set up the infrastructure:

Manually

Using Terraform

Create a Managed Service for YDB database named ydb1 in any suitable configuration.
Create a Managed Service for ClickHouse® cluster in any suitable configuration with publicly available hosts and the following settings:
- DB name: db1.
- Username: user1.
If using security groups in your cluster, make sure they are configured correctly and allow connecting to the Managed Service for ClickHouse® cluster.
Create a service account named ydb-account with the ydb.editor role. The transfer will use it to access the database.

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 ydb-to-clickhouse.tf configuration file to the same working directory.

This file describes:
- Network.
- Subnet.
- Security group and rules required to connect to the Managed Service for ClickHouse® cluster from the internet.
- Managed Service for YDB database.
- Target Managed Service for ClickHouse® cluster.
- Source endpoint.
- Target endpoint.
- Transfer.
In the ydb-to-clickhouse.tf file, specify the following settings:
- mch_version: ClickHouse® version.
- mch_password: User password of the ClickHouse® database owner.
Validate your Terraform configuration files using this command:
```
terraform validate
```
Terraform will display any configuration errors detected in your files.
Create the required infrastructure:
1. Run this command to view the planned changes:
```
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.
2. If everything looks correct, apply the changes:
  1. Run this command:
```
terraform apply
```
  2. Confirm updating the 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 your test data

Connect to the Managed Service for YDB database.
Create a row-oriented table named table1 with the following columns:

Name Type Primary key

Id Int64 Yes

Name Utf8

You can create a table using the following SQL query:
```
CREATE TABLE table1 (
    Id Int64,
    Name Utf8,
    PRIMARY KEY (Id)
)
```

Name	Type	Primary key
`Id`	`Int64`	Yes
`Name`	`Utf8`

Populate the table with data using the following SQL query:

INSERT INTO table1 (Id, Name)
VALUES
    (1, "Anna"),
    (2, "Robert"),
    (3, "Umar"),
    (4, "Algul"),
    (5, "Viktor");

Set up and activate the transfer

Manually

Using Terraform

Create a source endpoint of the YDB type and specify the DB connection settings in it:
- Connection settings:
  - Database: Select the Managed Service for YDB database from the list.
  - Service account ID: Select the ydb-account service account.
- Included paths list: Specify table1.
Create a target endpoint of the ClickHouse® type and specify the cluster connection parameters in it:
- Connection type: Managed cluster.
- Managed cluster: <ClickHouse®_target_cluster_name> from the drop-down list.
- Database: db1.
- User: user1.
- Password: <user_password>.
Create the Snapshot and replication type transfer and configure it to use the previously created endpoints.
Activate the transfer and wait for its status to change to Replicating.

In the ydb-to-clickhouse.tf file, set the transfer_enabled parameter to 1.
Validate your Terraform configuration files using this command:
```
terraform validate
```
Terraform will display any configuration errors detected in your files.
Create the required infrastructure:
1. Run this command to view the planned changes:
```
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.
2. If everything looks correct, apply the changes:
  1. Run this command:
```
terraform apply
```
  2. Confirm updating the resources.
  3. Wait for the operation to complete.
The transfer will activate automatically upon creation.

Test the transfer

To verify that the transfer is operational, check the copy and replication processes.

Test the copy operation

Connect to the db1 database in your target Managed Service for ClickHouse® cluster.

Run this query:

SELECT * FROM db1.table1;

Response example

# | Id  | Name     | __data_transfer_commit_time | __data_transfer_delete_time
--+-----+----------+-----------------------------+----------------------------
1 | "1" | "Anna"   | "1722023098109014844"       | "0"
2 | "2" | "Robert" | "1722023098109014844"       | "0"
3 | "3" | "Umar"   | "1722023098109014844"       | "0"
4 | "4" | "Algul"  | "1722023098109014844"       | "0"
5 | "5" | "Viktor" | "1722023098109014844"       | "0"

Test the replication process

Connect to the Managed Service for YDB database.

Add data to the table named table1:

INSERT INTO table1 (Id, Name)
VALUES
    (6, "Maria"),
    (7, "Alex");

Make sure the new data has been added to the target database:

Connect to the db1 database in your target Managed Service for ClickHouse® cluster.

Run this query:

SELECT * FROM db1.table1;

Response example

# | Id  | Name     | __data_transfer_commit_time | __data_transfer_delete_time
--+-----+----------+-----------------------------+----------------------------
1 | "1" | "Anna"   | "1722023098109014844"       | "0"
2 | "2" | "Robert" | "1722023098109014844"       | "0"
3 | "3" | "Umar"   | "1722023098109014844"       | "0"
4 | "4" | "Algul"  | "1722023098109014844"       | "0"
5 | "5" | "Viktor" | "1722023098109014844"       | "0"
6 | "6" | "Maria"  | "1722023655224000000"       | "0"
7 | "7" | "Alex"   | "1722023655225000000"       | "0"

Delete the resources you created

Note

Before deleting the resources, deactivate the transfer.

Some resources incur charges. To avoid unnecessary expenses, delete the resources you no longer need:

Manually

Using Terraform

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

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.
Delete resources:
1. Run this command:
```
terraform destroy
```
2. Confirm deleting the resources and wait for the operation to complete.
All the resources described in the Terraform manifests will be deleted.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Loading data from YDB to a ClickHouse® data mart

Required paid resourcesRequired paid resources

Getting startedGetting started

Prepare your test dataPrepare your test data

Set up and activate the transferSet up and activate the transfer

Test the transferTest the transfer

Test the copy operationTest the copy operation

Test the replication processTest the replication process

Delete the resources you createdDelete the resources you created

Was the article helpful?

Required paid resources

Getting started

Prepare your test data

Set up and activate the transfer

Test the transfer

Test the copy operation

Test the replication process

Delete the resources you created