Transferring data to a ClickHouse® target endpoint

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

2. Create a target database. Its name must be the same as the source database name. If you need to transfer multiple databases, create a separate transfer for each one of them.

3. Create a user with access to the target database.

   Once started, the transfer will connect to the target on behalf of this user.

4. Grant the new user the following permissions:

   GRANT CLUSTER ON *.* TO <username>
   GRANT SELECT, INSERT, ALTER DELETE, CREATE TABLE, CREATE VIEW, DROP TABLE, TRUNCATE, dictGet ON <DB_name>.* TO <username>
   GRANT SELECT(macro, substitution) ON system.macros TO <username>
   

• Endpoint type: clickhouse-target.

• --cluster-id: ID of the cluster you need to connect to.

• --cluster-name: Shard group to transfer the data to. If this parameter is not set, data will go to all shards.

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

• connection.connection_options.mdb_cluster_id: ID of cluster to connect to.

• clickhouse_cluster_name: Shard group to transfer the data to. If this parameter is not set, data will go to all shards.

• 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 the configuration file example:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    clickhouse_target {
      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 to. If this parameter is not set, the data will go to all shards.

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

• --cluster-name: Name of the cluster to transfer the data to.

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

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

• 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 the configuration file example:

resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
  name = "<endpoint_name>"
  settings {
    clickhouse_target {
      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 to.

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

• --alt-name: Rules for renaming the source database tables when transferring them to the target database. The values are specified in <source_table_name>:<target_table_name> format.

• Data sharding settings:

  • --shard-by-column-hash: Name of the table column to shard the data by. Uniform distribution between shards will depend on the hash of this column value.

  • --custom-sharding-column-name: Name of the table column to shard the data by. Data sharding is based on the column values specified by the --custom-sharding-mapping-string setting.

  • --custom-sharding-mapping-string: Mapping of the values from the column specified in the --custom-sharding-column-name setting and shards. The setting values are specified in <colimn_value>:<shard_name> format.

  • --shard-by-transfer-id: Data will be distributed between shards based on the transfer ID value. The parameter contains no value.

  You can only specify one of the sharding options:

  • --shard-by-column-hash
  • --custom-sharding-column-name and --custom-sharding-mapping-string
  • --shard-by-transfer-id

• cleanup_policy: Way to clean up data in the target database before the transfer:

  • CLICKHOUSE_CLEANUP_POLICY_DISABLED: Do not clean up (default).

    Select this option only for replication without data copying.

  • CLICKHOUSE_CLEANUP_POLICY_DROP: Completely delete the tables included in the transfer.

    Use this option to always transfer the latest version of the table schema to the target database from the source whenever the transfer is activated.

  • CLICKHOUSE_CLEANUP_POLICY_TRUNCATE: Delete only the data from the tables included in the transfer but keep the schema.

    Use this option if the schema in the target database differs from the one that would have been transferred from the source during the transfer.

• alt_names: Rules for renaming the source database tables when transferring them to the target database.

  • from_name: Source table name.
  • to_name: Target table name.

• Data sharding settings:

  • sharding.column_value_hash.column_name: Name of the table column to shard the data by. Uniform distribution between shards will depend on the hash of this column value.

  • sharding.transfer_id: Data is distributed between shards based on the transfer ID value. The transfer_id section contains no parameters.

  • sharding.custom_mapping: Sharding by column value:

    • column_name: Name of the table column to shard the data by.

    • mapping: Mapping of column values and shards:

      • column_value.string_value: Column value.
      • shard_name: Shard name.

  • sharding.round_robin: Data will be randomly distributed between shards. Each shard will contain approximately the same amount of data. The round_robin section contains no parameters.

  You can only specify one of the sharding options: sharding.column_value_hash.column_name, sharding.transfer_id, sharding.custom_mapping, or sharding.round_robin. If no sharding option is specified, all data will be transferred to a single shard.

• altNames: Rules for renaming the source database tables when transferring them to the target database.

  • fromName: Source table name.
  • toName: Target table name.

• cleanupPolicy: Way to clean up data in the target database before the transfer:

  • CLICKHOUSE_CLEANUP_POLICY_DISABLED: Do not clean up (default).

    Select this option only for replication without data copying.

  • CLICKHOUSE_CLEANUP_POLICY_DROP: Completely delete the tables included in the transfer.

    Use this option to always transfer the latest version of the table schema to the target database from the source whenever the transfer is activated.

  • CLICKHOUSE_CLEANUP_POLICY_TRUNCATE: Delete only the data from the tables included in the transfer but keep the schema.

    Use this option if the schema in the target database differs from the one that would have been transferred from the source during the transfer.

• sharding: Settings for data sharding. You can only specify one of the sharding options:

  • columnValueHash.columnName: Name of the table column to shard the data by. Uniform distribution between shards will depend on the hash of this column value.

  • customMapping: Sharding by column value:

    • columnName: Name of the table column to shard the data by.

    • mapping: Mapping of column values and shards:

      • columnValue.stringValue: Column value.
      • shardName: Shard name.

  • transferId: Data will be distributed between shards based on the transfer ID value. The parameter contains no value.

  • roundRobin: Data will be randomly distributed between shards. Each shard will contain approximately the same amount of data. The parameter contains no value.

  If no sharding option is specified, all data will be transferred to a single shard.

• If the target ClickHouse® cluster has sharding enabled and the data is migrated into a sharded table, write the data into an underlying table based on the ReplicatedMergeTree engine, not a distributed table (Distributed engine). In the target, select the migrated data from the distributed table. To redefine the write table, specify it in the --alt-name setting for the target.
• If you need to migrate multiple topics, in the --alt-name attribute of the target endpoint, specify the same target ClickHouse® table name for all topics of the source.

• If the target ClickHouse® cluster has sharding enabled and the data is migrated into a sharded table, write the data into an underlying table based on the ReplicatedMergeTree engine, not a distributed table (Distributed engine). In the target, select the migrated data from the distributed table. To redefine the write table, specify it in the alt_names.to_name setting for the target.
• If in the source you selected JSON in parser.json_parser:
  • You should specify UTF-8 instead of STRING for string types in the parser.json_parser.data_schema data schema.
  • The parser.json_parser.add_rest_column=true attribute may slow down your transfer.
• If you need to migrate multiple topics, in the alt_names attribute of the target endpoint, specify the same ClickHouse® table name in alt_names.to_name for all topics in alt_names.from_name.

• If the target ClickHouse® cluster has sharding enabled and the data is migrated into a sharded table, write the data into an underlying table based on the ReplicatedMergeTree engine, not a distributed table (Distributed engine). In the target, select the migrated data from the distributed table. To redefine the write table, specify it in the altNames.toName setting for the target.
• If in the source you selected JSON in parser.jsonParser:
  • You should specify UTF-8 instead of STRING for string types in the parser.jsonParser.dataSchema data schema.
  • The parser.jsonParser.addRestColumn=true parameter may slow down your transfer.
• If you need to migrate multiple topics, in the altNames parameter of the target endpoint, specify the same ClickHouse® table name in altNames.toName for all topics in altNames.fromName.