Replicating data

ClickHouse Cloud simplifies management

[ClickHouse Cloud](/docs/cloud/overview)
removes the operational burden associated with managing shards and replicas. The
platform automatically handles high availability, replication, and scaling decisions.
Compute and storage are separate and scale based on demand without requiring manual
configuration or ongoing maintenance.

[Read more](/docs/manage/scaling)

Test the setup

## Test the setup[​](#test-the-setup "Direct link to Test the setup")

Make sure that docker is running on your machine.
Start the cluster using the `docker-compose up` command from the root of the `cluster_1S_2R` directory:

```
docker-compose up -d
```

You should see docker begin to pull the ClickHouse and Keeper images,
and then start the containers:

```
[+] Running 6/6
 ✔ Network cluster_1s_2r_default   Created
 ✔ Container clickhouse-keeper-03  Started
 ✔ Container clickhouse-keeper-02  Started
 ✔ Container clickhouse-keeper-01  Started
 ✔ Container clickhouse-01         Started
 ✔ Container clickhouse-02         Started
```

To verify that the cluster is running, connect to either `clickhouse-01` or `clickhouse-02` and run the
following query. The command to connect to the first node is shown:

```
# Connect to any node
docker exec -it clickhouse-01 clickhouse-client
```

If successful, you will see the ClickHouse client prompt:

```
cluster_1S_2R node 1 :)
```

Run the following query to check what cluster topologies are defined for which
hosts:

```
SELECT 
    cluster,
    shard_num,
    replica_num,
    host_name,
    port
FROM system.clusters;
```

```
   ┌─cluster───────┬─shard_num─┬─replica_num─┬─host_name─────┬─port─┐
1. │ cluster_1S_2R │         1 │           1 │ clickhouse-01 │ 9000 │
2. │ cluster_1S_2R │         1 │           2 │ clickhouse-02 │ 9000 │
3. │ default       │         1 │           1 │ localhost     │ 9000 │
   └───────────────┴───────────┴─────────────┴───────────────┴──────┘
```

Run the following query to check the status of the ClickHouse Keeper cluster:

```
SELECT *
FROM system.zookeeper
WHERE path IN ('/', '/clickhouse')
```

```
   ┌─name───────┬─value─┬─path────────┐
1. │ sessions   │       │ /clickhouse │
2. │ task_queue │       │ /clickhouse │
3. │ keeper     │       │ /           │
4. │ clickhouse │       │ /           │
   └────────────┴───────┴─────────────┘
```

The `mntr` command is also commonly used to verify that ClickHouse Keeper is
running and to get state information about the relationship of the three Keeper nodes.
In the configuration used in this example, there are three nodes working together.
The nodes will elect a leader, and the remaining nodes will be followers.

The `mntr` command gives information related to performance, and whether a particular
node is a follower or a leader.

Tip

You may need to install `netcat` in order to send the `mntr` command to Keeper.
Please see the [nmap.org](https://nmap.org/ncat/) page for download information.

Run the command below from a shell on `clickhouse-keeper-01`, `clickhouse-keeper-02`, and
`clickhouse-keeper-03` to check the status of each Keeper node. The command
for `clickhouse-keeper-01` is shown below:

```
docker exec -it clickhouse-keeper-01  /bin/sh -c 'echo mntr | nc 127.0.0.1 9181'
```

The response below shows an example response from a follower node:

```
zk_version      v23.3.1.2823-testing-46e85357ce2da2a99f56ee83a079e892d7ec3726
zk_avg_latency  0
zk_max_latency  0
zk_min_latency  0
zk_packets_received     0
zk_packets_sent 0
zk_num_alive_connections        0
zk_outstanding_requests 0
# highlight-next-line
zk_server_state follower
zk_znode_count  6
zk_watch_count  0
zk_ephemerals_count     0
zk_approximate_data_size        1271
zk_key_arena_size       4096
zk_latest_snapshot_size 0
zk_open_file_descriptor_count   46
zk_max_file_descriptor_count    18446744073709551615
```

The response below shows an example response from a leader node:

```
zk_version      v23.3.1.2823-testing-46e85357ce2da2a99f56ee83a079e892d7ec3726
zk_avg_latency  0
zk_max_latency  0
zk_min_latency  0
zk_packets_received     0
zk_packets_sent 0
zk_num_alive_connections        0
zk_outstanding_requests 0
# highlight-next-line
zk_server_state leader
zk_znode_count  6
zk_watch_count  0
zk_ephemerals_count     0
zk_approximate_data_size        1271
zk_key_arena_size       4096
zk_latest_snapshot_size 0
zk_open_file_descriptor_count   48
zk_max_file_descriptor_count    18446744073709551615
# highlight-start
zk_followers    2
zk_synced_followers     2
# highlight-end
```

With this, you have successfully set up a ClickHouse cluster with a single shard and two replicas.
In the next step, you will create a table in the cluster.

Create a database

## Create a database[​](#creating-a-database "Direct link to Create a database")

Now that you have verified the cluster is correctly setup and is running, you
will be recreating the same table as the one used in the [UK property prices](/docs/getting-started/example-datasets/uk-price-paid)
example dataset tutorial. It consists of around 30 million rows of prices paid
for real-estate property in England and Wales since 1995.

Connect to the client of each host by running each of the following commands from separate terminal
tabs or windows:

```
docker exec -it clickhouse-01 clickhouse-client
docker exec -it clickhouse-02 clickhouse-client
```

You can run the query below from clickhouse-client of each host to confirm that
there are no databases created yet, apart from the default ones:

```
SHOW DATABASES;
```

```
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ system             │
   └────────────────────┘
```

From the `clickhouse-01` client run the following **distributed** DDL query using the
`ON CLUSTER` clause to create a new database called `uk`:

```
CREATE DATABASE IF NOT EXISTS uk 
-- highlight-next-line
ON CLUSTER cluster_1S_2R;
```

You can again run the same query as before from the client of each host
to confirm that the database has been created across the cluster despite running
the query only `clickhouse-01`:

```
SHOW DATABASES;
```

```
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ system             │
#highlight-next-line
5. │ uk                 │
   └────────────────────┘
```

Create a table on the cluster

## Create a table on the cluster[​](#creating-a-table "Direct link to Create a table on the cluster")

Now that the database has been created, create a table on the cluster.
Run the following query from any of the host clients:

```
CREATE TABLE IF NOT EXISTS uk.uk_price_paid_local
--highlight-next-line
ON CLUSTER cluster_1S_2R
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
--highlight-next-line
ENGINE = ReplicatedMergeTree
ORDER BY (postcode1, postcode2, addr1, addr2);
```

Notice that it is identical to the query used in the original `CREATE` statement of the
[UK property prices](/docs/getting-started/example-datasets/uk-price-paid) example dataset tutorial,
except for the `ON CLUSTER` clause and use of the `ReplicatedMergeTree` engine.

The `ON CLUSTER` clause is designed for distributed execution of DDL (Data Definition Language)
queries such as `CREATE`, `DROP`, `ALTER`, and `RENAME`, ensuring that these
schema changes are applied across all nodes in a cluster.

The [`ReplicatedMergeTree`](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication#converting-from-mergetree-to-replicatedmergetree)
engine works just as the ordinary `MergeTree` table engine, but it will also replicate the data.