[Yandex Cloud documentation](../../../../index.md) > [Yandex Data Transfer](../../../index.md) > [Step-by-step guides](../../index.md) > [Configuring endpoints](../index.md) > ClickHouse > Source # Transferring data from a ClickHouse® source endpoint Yandex Data Transfer enables you to migrate data from a ClickHouse® database and implement various data transfer, processing, and transformation scenarios. To implement a transfer: 1. [Explore possible data transfer scenarios](#scenarios). 1. [Prepare the ClickHouse®](#prepare) database for the transfer. 1. [Set up a source endpoint](#endpoint-settings) in Yandex Data Transfer. 1. [Set up one of the supported data targets](#supported-targets). 1. [Create](../../transfer.md#create) a transfer and [start](../../transfer.md#activate) it. 1. [Perform the required operations with the database](#db-actions) and [see how the transfer is going](../../monitoring.md). 1. In case of any issues, [use ready-made solutions](#troubleshooting) to resolve them. ## Scenarios for transferring data from ClickHouse® {#scenarios} Migration: Moving data from one storage to another. Migration often means migrating a database from obsolete local databases to managed cloud ones. * [Migrating a ClickHouse® cluster](../../../tutorials/managed-clickhouse.md). For a detailed description of possible Yandex Data Transfer scenarios, see [Tutorials](../../../tutorials/index.md). ## Preparing the source database {#prepare} {% note info %} Yandex Data Transfer cannot transfer a ClickHouse® database if its name contains a hyphen. If transferring tables with engines other than `ReplicatedMergeTree` and `Distributed` in a ClickHouse® multi-host cluster, the transfer will fail with the following error: `the following tables have not Distributed or Replicated engines and are not yet supported`. {% endnote %} {% list tabs %} * Managed Service for ClickHouse® 1. Make sure the tables you are transferring use the `MergeTree` family engines. Only these tables and [materialized views](https://clickhouse.com/docs/enen/materialized-views) (MaterializedView) will be transferred. In case of a multi-host cluster, only tables and materialized views with the `ReplicatedMergeTree` or `Distributed` engines will be transferred. Make sure these tables and views are present on all the cluster hosts. 1. [Create a user](../../../../managed-clickhouse/operations/cluster-users.md) with access to the source database. In the user settings, specify a value of at least `1000000` for the **Max execution time** [parameter](../../../../managed-clickhouse/concepts/settings-list.md#setting-max-execution-time). * ClickHouse® 1. Make sure the tables you are transferring use the `MergeTree` family engines. Only these tables and [materialized views](https://clickhouse.com/docs/enen/materialized-views) (MaterializedView) will be transferred. In case of a multi-host cluster, only tables and materialized views with the `ReplicatedMergeTree` or `Distributed` engines will be transferred. Make sure these tables and views are present on all the cluster hosts. 1. If not planning to use [Cloud Interconnect](../../../../interconnect/concepts/index.md) or [VPN](https://en.wikipedia.org/wiki/Virtual_private_network) for connections to an external cluster, make such cluster accessible from the Internet from [IP addresses used by Data Transfer](../../../../overview/concepts/public-ips.md#virtual-private-cloud). For details on linking your network up with external resources, see [this concept](../../../concepts/network.md#source-external). 1. [Configure access to the source cluster from Yandex Cloud](../../../concepts/network.md#source-external). 1. Create a user with access to the source database. In the user settings, specify a value of at least `1000000` for the **Max execution time** parameter. {% endlist %} ## Configuring the ClickHouse® source endpoint {#endpoint-settings} When [creating](../index.md#create) or [updating](../index.md#update) an endpoint, you can define: * [Yandex Managed Service for ClickHouse® cluster](#managed-service) connection or [custom installation](#on-premise) settings, including those based on Yandex Compute Cloud VMs. These are required parameters. * [Additional parameters](#additional-settings). ### Managed Service for ClickHouse® cluster {#managed-service} {% note warning %} To create or edit an endpoint of a managed database, you will need the [`managed-clickhouse.viewer`](../../../../managed-clickhouse/security.md#managed-clickhouse-viewer) role or the primitive [`viewer`](../../../../iam/roles-reference.md#viewer) role for the folder the cluster of this managed database resides in. {% endnote %} Connection to the database with the cluster specified in Yandex Cloud. {% list tabs group=instructions %} - Management console {#console} * **Connection type**: Select a database connection option: * **Self-managed**: Allows you to specify connection settings manually. Select **Managed Service for ClickHouse cluster** as the installation type and configure these settings: * **Managed cluster**: Select the cluster to connect to. * **Shard group**: Specify the shard group to transfer the data from. If this value is not set, data will be transferred from all shards. * **Database**: Specify the name of the database in the selected cluster. * **User**: Specify the username Data Transfer will use to connect to the database. * **Password**: Enter the user password for access to the database. * **Connection Manager**: Allows connecting to the cluster via [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md): * Select the folder with the Managed Service for ClickHouse® cluster. * Select **Managed DB cluster** as the installation type and configure these settings: * **Cluster for Managed DB**: Select the cluster to connect to. * **Connection**: Select or create a connection in Connection Manager. * **Database**: Specify the name of the database in the selected cluster. * **Shard group**: Specify the shard group to transfer the data from. If this value is not set, data will be transferred from all shards. {% note warning %} To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher. {% endnote %} * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic. This will allow you to apply the specified security group rules to the VMs and clusters in the selected network without changing their settings. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). Make sure the selected security groups are [configured](../../../../managed-clickhouse/operations/connect/index.md#configuring-security-groups). - CLI {#cli} * Endpoint type: `clickhouse-source`. * `--cluster-id`: ID of the cluster you need to connect to. * `--cluster-name`: Shard group to transfer the data from. If this parameter is not set, data from all shards will be transferred. * `--database`: Database name. * `--user`: Username that Data Transfer will use to connect to the database. * `--security-group`: Network traffic security groups whose rules apply to VMs and clusters without altering their configurations. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). Make sure the specified security groups are [configured](../../../../managed-clickhouse/operations/connect/index.md#configuring-security-groups). * To set a user password to access the DB, use one of the following parameters: * `--raw-password`: Password as text. * `--password-file`: The path to the password file. - Terraform {#tf} * Endpoint type: `clickhouse_source`. * `connection.connection_options.mdb_cluster_id`: ID of cluster to connect to. * `clickhouse_cluster_name`: Shard group to transfer the data from. If this parameter is not set, data from all shards will be transferred. * `subnet_id`: ID of the [subnet](../../../../vpc/concepts/network.md#subnet) the cluster is in. The transfer will use this subnet to access the cluster. If the ID is not specified, the cluster must be accessible from the internet. If the value in this field is specified for both endpoints, both subnets must be hosted in the same availability zone. * `security_groups`: [Security groups](../../../../vpc/concepts/security-groups.md) for network traffic. Security group rules apply to a transfer. They allow opening network access from the transfer VM to the cluster. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). Security groups and the `subnet_id` subnet, if the latter is specified, must belong to the same network as the cluster. {% note info %} In Terraform, it is not required to specify a network for security groups. {% endnote %} Make sure the specified security groups are [configured](../../../../managed-clickhouse/operations/connect/index.md#configuring-security-groups). * `connection.connection_options.database`: Database name. * `connection.connection_options.user`: Username that Data Transfer will use to connect to the database. * `connection.connection_options.password.raw`: Password in text form. Here is an example of the configuration file structure: ```hcl resource "yandex_datatransfer_endpoint" "" { name = "" settings { clickhouse_source { clickhouse_cluster_name="" security_groups = [""] subnet_id = "" connection { connection_options { mdb_cluster_id = "" database = "" user = "" password { raw = "" } } } } } } ``` For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md). - API {#api} * `securityGroups`: Network traffic security groups whose rules apply to VMs and clusters without altering their configurations. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). Make sure the specified security groups are [configured](../../../../managed-clickhouse/operations/connect/index.md#configuring-security-groups). * `mdbClusterId`: ID of the cluster you need to connect to. * `clickhouseClusterName`: Shard group to transfer the data from. If this parameter is not set, data from all shards will be transferred. * `database`: Database name. * `user`: Username that Data Transfer will use to connect to the database. * `password.raw`: Database user password (in text form). {% endlist %} ### Custom installation {#on-premise} Connection to the database with explicitly specified network addresses and ports. {% list tabs group=instructions %} - Management console {#console} * **Connection type**: Select a database connection option: * **Self-managed**: Allows you to specify connection settings manually. Select **Custom installation** as the installation type and configure these settings: * **Shards** * **Shard**: Specify a row that will allow the service to distinguish shards from each other. If sharding is disabled in your custom installation, specify any name. * **Hosts**: Specify FQDNs or IP addresses of the hosts in the shard. * **Cluster**: Specify the name of the cluster to transfer the data from. If this parameter is not set, the default cluster's data will be transferred (the `{cluster}` macro). * **HTTP port**: Set the number of the port that Data Transfer will use for the connection. When connecting via the HTTP port: * For optional fields, default values are used (if any). * Recording complex types is supported (such as `array` and `tuple`). * **Native port**: Set the number of the native port that Data Transfer will use for the connection. * **SSL**: Enable if the cluster supports only encrypted connections. * **CA certificate**: If transmitted data has to be be encrypted, e.g., to meet the PCI DSS, upload the [certificate](../../../../managed-clickhouse/operations/connect/index.md#get-ssl-cert) file or add its contents as text. {% note warning %} If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect). {% endnote %} * **Database**: Specify the name of the database in the selected cluster. * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the cluster. If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone. * **User**: Specify the username Data Transfer will use to connect to the database. * **Password**: Enter the user password for access to the database. * **Connection Manager**: Allows connecting to the database using [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md): * Select the folder where the Connection Manager connection was created. * Select **Custom installation** as the installation type and configure these settings: * **Connection**: Select or create a connection in Connection Manager. * **Database**: Specify the name of the database in the selected cluster. * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the required [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the cluster. If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone. * **Shard group**: Specify the shard group to transfer the data from. If this value is not set, data will be transferred from all shards. {% note warning %} To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher. {% endnote %} * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic. Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). - CLI {#cli} * Endpoint type: `clickhouse-source`. * `--cluster-name`: Name of the cluster to transfer the data from. * `--host`: List of IP addresses or FQDNs of hosts to connect to, in `{shard_name}:{host_IP_address_or_FQDN}` format. If sharding is disabled in your custom installation, specify any shard name. * `http-port`: Port number Data Transfer will use for HTTP connections. * `native-port`: Port number Data Transfer will use for connections to the ClickHouse® native interface. * `--ca-certificate`: CA certificate if the data to transfer must be encrypted to comply with PCI DSS requirements. {% note warning %} If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect). {% endnote %} * `--subnet-id`: ID of the subnet the host is in. The transfer will use that subnet to access the host. * `--database`: Database name. * `--user`: Username that Data Transfer will use to connect to the database. * `--security-group`: Network traffic security groups whose rules apply to VMs and clusters without altering their configurations. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). * To set a user password to access the DB, use one of the following parameters: * `--raw-password`: Password as text. * `--password-file`: The path to the password file. - Terraform {#tf} * Endpoint type: `clickhouse_source`. * Shard settings: * `connection.connection_options.on_premise.shards.name`: Shard name that the service will use to distinguish shards from each other. If sharding is disabled in your custom installation, specify any name. * `connection.connection_options.on_premise.shards.hosts`: specify the FQDNs or IP addresses of the hosts in the shard. * `connection.connection_options.on_premise.http_port`: Port number that Data Transfer will use for HTTP connections. * `connection.connection_options.on_premise.native_port`: Port number that Data Transfer will use for connections to the ClickHouse® native interface. * `connection.connection_options.on_premise.tls_mode.enabled.ca_certificate`: CA certificate if the data to transfer must be encrypted, e.g., to comply with the PCI DSS requirements. {% note warning %} If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect). {% endnote %} * `clickhouse_cluster_name`: Name of the cluster to transfer the data from. * `subnet_id`: ID of the [subnet](../../../../vpc/concepts/network.md#subnet) the cluster is in. The transfer will use this subnet to access the cluster. If the ID is not specified, the cluster must be accessible from the internet. If the value in this field is specified for both endpoints, both subnets must be hosted in the same availability zone. * `security_groups`: [Security groups](../../../../vpc/concepts/security-groups.md) for network traffic. Security group rules apply to a transfer. They allow opening network access from the transfer VM to the database VM. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). Security groups must belong to the same network as the `subnet_id` subnet, if the latter is specified. {% note info %} In Terraform, it is not required to specify a network for security groups. {% endnote %} * `connection.connection_options.database`: Database name. * `connection.connection_options.user`: Username that Data Transfer will use to connect to the database. * `connection.connection_options.password.raw`: Password in text form. Here is an example of the configuration file structure: ```hcl resource "yandex_datatransfer_endpoint" "" { name = "" settings { clickhouse_source { clickhouse_cluster_name="" security_groups = [""] subnet_id = "" connection { connection_options { on_premise { http_port = "" native_port = "" shards { name = "" hosts = [ "list of IP addresses and FQDNs of shard hosts" ] } tls_mode { enabled { ca_certificate = "" } } } database = "" user = "" password { raw = "" } } } } } } ``` For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md). - API {#api} * `onPremise`: Database connection parameters: * `shards`: Shard settings: * `name`: Shard name the service will use to distinguish shards one from another. If sharding is disabled in your custom installation, specify any name. * `hosts`: Specify FQDNs or IP addresses of the hosts in the shard. * `httpPort`: Port number Data Transfer will use for HTTP connections. * `nativePort`: Port number Data Transfer will use for connections to the ClickHouse® native interface. * `tlsMode`: Parameters for encrypting the data to transfer, if required, e.g., for compliance with the PCI DSS requirements. * `disabled`: Disabled. * `enabled`: Enabled. * `caCertificate`: CA certificate. {% note warning %} If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect). {% endnote %} * `subnetId`: ID of the subnet the host is in. The transfer will use that subnet to access the host. * `clickhouseClusterName`: Name of the cluster to transfer the data from. * `securityGroups`: Network traffic security groups whose rules apply to VMs and clusters without altering their configurations. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md). * `database`: Database name. * `user`: Username that Data Transfer will use to connect to the database. * `password.raw`: Database user password (in text form). {% endlist %} ### Table filter {#additional-settings} {% list tabs group=instructions %} - Management console {#console} * **Included tables**: Data is only transferred from listed tables. When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update). * **Excluded tables**: Data from the listed tables is not transferred. Included and excluded table names must meet the ID naming rules in ClickHouse®. For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/syntax#syntax-identifiers). Escaping double quotes is not required. Leave the lists empty to transfer all the tables. - CLI {#cli} * `--include_table`: List of included tables. Only data from the tables listed here will be transferred. When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update). * `--exclude_table`: List of excluded tables. Data from the listed tables will not be transferred. If no lists are specified, data from all tables will be transferred. - Terraform {#tf} * `include_tables`: List of included tables. Only data from the tables listed here will be transferred. When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update). * `exclude_tables`: List of excluded tables. Data from the listed tables will not be transferred. If no lists are specified, data from all tables will be transferred. - API {#api} * `includeTables`: List of included tables. Only data from the tables listed here will be transferred. When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update). * `excludeTables`: List of excluded tables. Data from the listed tables will not be transferred. If no lists are specified, data from all tables will be transferred. {% endlist %} ## Configuring the data target {#supported-targets} Configure the target endpoint: * [YTsaurus](yt.md) * [ClickHouse®](../target/clickhouse.md) For a complete list of supported sources and targets in Yandex Data Transfer, see [Available transfers](../../../transfer-matrix.md). After configuring the data source and target, [create and start the transfer](../../transfer.md#create). ## Troubleshooting data transfer issues {#troubleshooting} * [New tables cannot be added](#no-new-tables). * [Data is not transferred](#no-transfer). * [Unsupported date range](#date-range). For the full list of recommendations, see [Troubleshooting](../../../troubleshooting/index.md). ### New tables cannot be added {#no-new-tables} ​No new tables are added to _**Snapshot and increment**_ transfers. **Solution**: 1. Create tables in the target database manually. For the transfer to work, do the following when creating a table: 1. Add the transfer service fields to it: ```sql __data_transfer_commit_time timestamp, __data_transfer_delete_time timestamp ``` 1. Use `ReplacingMergeTree`: ```sql ENGINE = ReplacingMergeTree ``` 1. [Create](../../transfer.md#create) a separate transfer of the _**Snapshot and increment**_ type and add only new tables to the list of objects to transfer. Deactivating the original _**Snapshot and increment**_ transfer is not required. [Activate](../../transfer.md#activate) the new transfer, and once it switches to the **Replicating** status, [deactivate](../../transfer.md#deactivate) it. To add other tables, put them into the list of objects to transfer in the created separate transfer (replacing other objects in that list), reactivate it, and, once it switches to the **Replicating** status, deactivate it. {% note info %} Since two transfers were simultaneously migrating data, you will have duplicate records in the new tables on the target. Run the `SELECT * from TABLE FINAL` query to hide duplicate records or `OPTIMIZE TABLE ` to delete them. {% endnote %} ### Unsupported date range {#date-range} If the migrated data contains dates outside the supported ranges, ClickHouse® returns the following error: ```text TYPE_ERROR [target]: failed to run (abstract1 source): failed to push items from 0 to 1 in batch: Push failed: failed to push 1 rows to ClickHouse shard 0: ClickHouse Push failed: Unable to exec changeItem: clickhouse: dateTime must be between 1900-01-01 00:00:00 and 2262-04-11 23:47:16 ``` Supported date ranges in ClickHouse®: * For the `DateTime64` type fields: 1900-01-01 to 2299-12-31. For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/data-types/datetime64). * For the `DateTime` type fields: 1970-01-01 to 2106-02-07. For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/data-types/datetime). **Solution**: use one of the following options: * Convert all dates in the source DB to a range supported by ClickHouse®. * In the [source endpoint parameters](../index.md#update), exclude the table with incorrect dates from the transfer. * In the [transfer parameters](../../transfer.md#update), specify the [Convert values to string](../../../concepts/data-transformation.md#convert-to-string) transformer. This will change the field type during the transfer. _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._