Transferring data from a ClickHouse® source endpoint

1. Make sure the tables you are transferring use the MergeTree family engines. Only these tables and 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.

2. If not planning to use Cloud Interconnect or VPN for connections to an external cluster, make such cluster accessible from the Internet from IP addresses used by Data Transfer.

   For details on linking your network up with external resources, see this concept.

3. Configure access to the source cluster from Yandex Cloud.

4. 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.

• 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: Security groups for network traffic, whose rules will apply to VMs and clusters without changing their settings. For more information, see Networking in Yandex Data Transfer.

  Make sure the specified security groups are configured.

• 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.

• 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 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 for network traffic.

  Security group rules apply to a transfer. They allow opening up network access from the transfer VM to the cluster. For more information, see Networking in Yandex Data Transfer.

  Security groups and the subnet_id subnet, if the latter is specified, must belong to the same network as the cluster.

  Note

  In Terraform, it is not required to specify a network for security groups.

  Make sure the specified security groups are configured.

• 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:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    clickhouse_source {
      clickhouse_cluster_name="<shard_group>"
      security_groups = ["<list_of_security_group_IDs>"]
      subnet_id       = "<subnet_ID>"
      connection {
        connection_options {
          mdb_cluster_id = "<cluster_ID>"
          database       = "<name_of_database_to_migrate>"
          user           = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
        }
      }
      <additional_endpoint_settings>
    }
  }
}

For more information, see the Terraform provider documentation.

• securityGroups: Security groups for network traffic, whose rules will apply to VMs and clusters without changing their settings. For more information, see Networking in Yandex Data Transfer.

  Make sure the specified security groups are configured.

• 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).

• 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.

  Warning

  If no certificate is added, the transfer may fail with an error.

• --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: Security groups for network traffic, whose rules will apply to VMs and clusters without changing their settings. For more information, see Networking in Yandex Data Transfer.

• 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.

• 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.

  Warning

  If no certificate is added, the transfer may fail with an error.

• clickhouse_cluster_name: Name of the cluster to transfer the data from.

• subnet_id: ID of the 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 for network traffic.

  Security group rules apply to a transfer. They allow opening up network access from the transfer VM to the VM with the database. For more information, see Networking in Yandex Data Transfer.

  Security groups must belong to the same network as the subnet_id subnet, if the latter is specified.

  Note

  In Terraform, it is not required to specify a network for 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:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    clickhouse_source {
      clickhouse_cluster_name="<cluster_name>"
      security_groups = ["<list_of_security_group_IDs>"]
      subnet_id       = "<subnet_ID>"
      connection {
        connection_options {
          on_premise {
            http_port   = "<HTTP_connection_port>"
            native_port = "<port_for_native_interface_connection>"
            shards {
              name  = "<shard_name>"
              hosts = [ “list of IP addresses and FQDNs of shard hosts" ]
            }
            tls_mode {
              enabled {
                ca_certificate = "<certificate_in_PEM_format>"
              }
            }
          }
          database = "<name_of_database_to_migrate>"
          user     = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
        }
      }
      <additional_endpoint_settings>
    }
  }
}

For more information, see the Terraform provider documentation.

• 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.

        Warning

        If no certificate is added, the transfer may fail with an error.

  • 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: Security groups for network traffic, whose rules will apply to VMs and clusters without changing their settings. For more information, see Networking in Yandex Data Transfer.

• database: Database name.

• user: Username that Data Transfer will use to connect to the database.

• password.raw: Database user password (in text form).

• --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.

• --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.

• 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.

• 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.

• 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.

• 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.