Replication in Managed Service for ClickHouse®

In ClickHouse®, replication is enabled if the cluster meets all these conditions:

• There is at least one shard with two or more hosts.
• Host coordination is configured.

A Managed Service for ClickHouse® cluster with replication is considered to be highly available. In such a cluster, you can create replicated tables and replicated databases.

Replicated tablesReplicated tables

ClickHouse® only supports automatic replication for tables on the ReplicatedMergeTree engine. To enable replication, you can create such tables on each host separately or use a distributed DDL query.

To create a ReplicatedMergeTree table on a specific ClickHouse® host, run the following query:

CREATE TABLE db_01.table_01 (
    log_date date,
    user_name String) ENGINE = ReplicatedMergeTree ('/table_01', '{replica}'
)
PARTITION BY log_date
ORDER BY
    (log_date, user_name);

Where:

• db_01: Database name.
• table_01: Table name.
• /table_01: Path to the table in ZooKeeper or ClickHouse® Keeper, which must start with a forward slash, /.
• {replica}: Host ID macro substitution.

To create replicated tables on all cluster hosts, run this distributed DDL query:

CREATE TABLE db_01.table_01 ON CLUSTER '{cluster}' (
    log_date date,
    user_name String) ENGINE = ReplicatedMergeTree ('/table_01', '{replica}'
)
PARTITION BY log_date
ORDER BY
    (log_date, user_name);

The '{cluster}' argument will be automatically resolved to the ClickHouse® cluster ID.

To learn how to manage the interaction between replicated and distributed tables in a ClickHouse® cluster, see Sharding.

Replicated databasesReplicated databases

When creating a database in ClickHouse®, you can select the Replicated engine that supports table metadata replication across all database replicas. The set of tables and their schemas will be the same for all replicas.

You can only set the engine when creating a database and cannot change it later.

Use casesUse cases

• Asynchronously replicating data from PostgreSQL to ClickHouse®
• Loading data from Yandex Managed Service for YDB to Managed Service for ClickHouse® using Yandex Data Transfer
• Loading data from Yandex Object Storage to Managed Service for ClickHouse® using Yandex Data Transfer
• Migrating a database from MySQL® to ClickHouse® using Yandex Data Transfer

ClickHouse® is a registered trademark of ClickHouse, Inc.