Replication in Managed Service for ClickHouse
In Managed Service for ClickHouse, replication and query distribution are managed by ZooKeeper.
This allows you to use replicated tables in a cluster with multiple hosts in a shard. Meanwhile, the replication is managed automatically.
ZooKeeper
Before adding new hosts to a single-host shard, you need to enable fault tolerance for the cluster, if it is not already enabled. In this case, three ZooKeeper hosts, which is the minimum number of hosts required to manage replication and fault tolerance, will be added to the cluster.
You can enable fault tolerance and configure ZooKeeper hosts after creating a cluster with a single host.
You can also configure ZooKeeper hosts immediately when creating a cluster with multiple hosts.
The minimum number of cores per ZooKeeper host depends on the total number of cores on ClickHouse hosts:
Total number of ClickHouse host cores | Minimum number of cores per ZooKeeper host |
---|---|
Less than 48 | 2 |
48 or higher | 4 |
The ZooKeeper host class can be changed when configuring fault tolerance or cluster settings. You cannot change ZooKeeper settings or connect to such hosts.
Warning
Note:
- ZooKeeper hosts, if any, are taken into account when calculating resource usage and the cost of the cluster.
- In shards with multiple hosts, if you created a cluster and then enabled fault tolerance, you will not be able to reduce the number of hosts to one.
Replicated tables
ClickHouse only supports automatic replication for tables running on the ReplicatedMergeTree
Warning
We recommend creating replicated tables on all cluster hosts; otherwise, when restoring a cluster from a backup, you may lose data. For more information, see Backups.
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, which must start with a forward slash (/
).{replica}
: Host ID macro.
To create replicated tables on all cluster hosts, run the following 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.