Replicating data

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.

You can run the query below from either `clickhouse-01` or `clickhouse-02` client
to confirm that the table has been created across the cluster:

```
SHOW TABLES IN uk;
```

```
   ┌─name────────────────┐
1. │ uk_price_paid.      │
   └─────────────────────┘
```

Insert data

## Insert data[​](#inserting-data "Direct link to Insert data")

As the data set is large and takes a few minutes to completely ingest, we will
insert only a small subset to begin with.

Insert a smaller subset of the data using the query below from `clickhouse-01`:

```
INSERT INTO uk.uk_price_paid_local
SELECT
    toUInt32(price_string) AS price,
    parseDateTimeBestEffortUS(time) AS date,
    splitByChar(' ', postcode)[1] AS postcode1,
    splitByChar(' ', postcode)[2] AS postcode2,
    transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
    b = 'Y' AS is_new,
    transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM url(
    'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
    'CSV',
    'uuid_string String,
    price_string String,
    time String,
    postcode String,
    a String,
    b String,
    c String,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String,
    d String,
    e String'
) LIMIT 10000
SETTINGS max_http_get_redirects=10;
```

Notice that the data is completely replicated on each host:

```
-- clickhouse-01
SELECT count(*)
FROM uk.uk_price_paid_local

--   ┌─count()─┐
-- 1.│   10000 │
--   └─────────┘

-- clickhouse-02
SELECT count(*)
FROM uk.uk_price_paid_local

--   ┌─count()─┐
-- 1.│   10000 │
--   └─────────┘
```

To demonstrate what happens when one of the hosts fails, create a simple test database
and test table from either of the hosts:

```
CREATE DATABASE IF NOT EXISTS test ON CLUSTER cluster_1S_2R;
CREATE TABLE test.test_table ON CLUSTER cluster_1S_2R
(
    `id` UInt64,
    `name` String
)
ENGINE = ReplicatedMergeTree
ORDER BY id;
```

As with the `uk_price_paid` table, we can insert data from either host:

```
INSERT INTO test.test_table (id, name) VALUES (1, 'Clicky McClickface');
```

But what will happen if one of the hosts is down? To simulate this, stop
`clickhouse-01` by running:

```
docker stop clickhouse-01
```

Check that the host is down by running:

```
docker-compose ps
```

```
NAME                   IMAGE                                        COMMAND            SERVICE                CREATED          STATUS          PORTS
clickhouse-02          clickhouse/clickhouse-server:latest          "/entrypoint.sh"   clickhouse-02          X minutes ago    Up X minutes    127.0.0.1:8124->8123/tcp, 127.0.0.1:9001->9000/tcp
clickhouse-keeper-01   clickhouse/clickhouse-keeper:latest-alpine   "/entrypoint.sh"   clickhouse-keeper-01   X minutes ago    Up X minutes    127.0.0.1:9181->9181/tcp
clickhouse-keeper-02   clickhouse/clickhouse-keeper:latest-alpine   "/entrypoint.sh"   clickhouse-keeper-02   X minutes ago    Up X minutes    127.0.0.1:9182->9181/tcp
clickhouse-keeper-03   clickhouse/clickhouse-keeper:latest-alpine   "/entrypoint.sh"   clickhouse-keeper-03   X minutes ago    Up X minutes    127.0.0.1:9183->9181/tcp
```

With `clickhouse-01` now down, insert another row of data into the test table
and query the table:

```
INSERT INTO test.test_table (id, name) VALUES (2, 'Alexey Milovidov');
SELECT * FROM test.test_table;
```

```
   ┌─id─┬─name───────────────┐
1. │  1 │ Clicky McClickface │
2. │  2 │ Alexey Milovidov   │
   └────┴────────────────────┘
```

Now restart `clickhouse-01` with the following command (you can run `docker-compose ps` again after to confirm):

```
docker start clickhouse-01
```

Query the test table again from `clickhouse-01` after running `docker exec -it clickhouse-01 clickhouse-client`:

```
SELECT * FROM test.test_table
```

```
   ┌─id─┬─name───────────────┐
1. │  1 │ Clicky McClickface │
2. │  2 │ Alexey Milovidov   │
   └────┴────────────────────┘
```

If at this stage you would like to ingest the full UK property price dataset
to play around with, you can run the following queries to do so:

```
TRUNCATE TABLE uk.uk_price_paid_local ON CLUSTER cluster_1S_2R;
INSERT INTO uk.uk_price_paid_local
SELECT
    toUInt32(price_string) AS price,
    parseDateTimeBestEffortUS(time) AS date,
    splitByChar(' ', postcode)[1] AS postcode1,
    splitByChar(' ', postcode)[2] AS postcode2,
    transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
    b = 'Y' AS is_new,
    transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM url(
    'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
    'CSV',
    'uuid_string String,
    price_string String,
    time String,
    postcode String,
    a String,
    b String,
    c String,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String,
    d String,
    e String'
    ) SETTINGS max_http_get_redirects=10;
```

Query the table from `clickhouse-02` or `clickhouse-01`:

```
SELECT count(*) FROM uk.uk_price_paid_local;
```

```
   ┌──count()─┐
1. │ 30212555 │ -- 30.21 million
   └──────────┘
```

## Conclusion[​](#conclusion "Direct link to Conclusion")

The advantage of this cluster topology is that with two replicas,
your data exists on two separate hosts. If one host fails, the other replica
continues serving data without any loss. This eliminates single points of
failure at the storage level.

When one host goes down, the remaining replica is still able to:

- Handle read queries without interruption
- Accept new writes (depending on your consistency settings)
- Maintain service availability for applications

When the failed host comes back online, it is able to:

- Automatically sync missing data from the healthy replica
- Resume normal operations without manual intervention
- Restore full redundancy quickly

In the next example, we'll look at how to set up a cluster with two shards but
only one replica.

- [Prerequisites](#pre-requisites)- [Set up directory structure and test environment](#set-up)- [Configure ClickHouse nodes](#configure-clickhouse-servers)
      - [Server setup](#server-setup)- [User configuration](#user-config)- [Configure ClickHouse Keeper](#configure-clickhouse-keeper-nodes)
        - [Keeper setup](#configuration-explanation)- [Test the setup](#test-the-setup)- [Create a database](#creating-a-database)- [Create a table on the cluster](#creating-a-table)- [Insert data](#inserting-data)- [Conclusion](#conclusion)

Was this page helpful?