ClickHouse Keeper (clickhouse-keeper)

```
       <remote_servers>
           <cluster_2S_1R>
               <shard>
                   <replica>
                       <host>chnode1.domain.com</host>
                       <port>9000</port>
                       <user>default</user>
                       <password>ClickHouse123!</password>
                   </replica>
               </shard>
               <shard>
                   <replica>
                       <host>chnode2.domain.com</host>
                       <port>9000</port>
                       <user>default</user>
                       <password>ClickHouse123!</password>
                   </replica>
               </shard>
           </cluster_2S_1R>
       </remote_servers>
   ```

   |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
   | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
   | Parameter Description Example|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | shard list of replicas on the cluster definition list of replicas for each shard|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | replica list of settings for each replica settings entries for each replica|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | host hostname, IP or FQDN of server that will host a replica shard `chnode1.domain.com`| port port used to communicate using the native tcp protocol 9000|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | user username that will be used to authenticate to the cluster instances default|  |  |  | | --- | --- | --- | | password password for the user define to allow connections to cluster instances `ClickHouse123!` | | | | | | | | | | | | | | | | | | | | |
2. Restart ClickHouse and verify the cluster was created:

   ```
   SHOW clusters;
   ```

   You should see your cluster:

   ```
   ┌─cluster───────┐
   │ cluster_2S_1R │
   └───────────────┘
   ```

### 3. Create and test distributed table[​](#3-create-and-test-distributed-table "Direct link to 3. Create and test distributed table")

1. Create a new database on the new cluster using ClickHouse client on `chnode1`. The `ON CLUSTER` clause automatically creates the database on both nodes.

   ```
   CREATE DATABASE db1 ON CLUSTER 'cluster_2S_1R';
   ```
2. Create a new table on the `db1` database. Once again, `ON CLUSTER` creates the table on both nodes.

   ```
   CREATE TABLE db1.table1 on cluster 'cluster_2S_1R'
   (
       `id` UInt64,
       `column1` String
   )
   ENGINE = MergeTree
   ORDER BY column1
   ```
3. On the `chnode1` node, add a couple of rows:

   ```
   INSERT INTO db1.table1
       (id, column1)
   VALUES
       (1, 'abc'),
       (2, 'def')
   ```
4. Add a couple of rows on the `chnode2` node:

   ```
   INSERT INTO db1.table1
       (id, column1)
   VALUES
       (3, 'ghi'),
       (4, 'jkl')
   ```
5. Notice that running a `SELECT` statement on each node only shows the data on that node. For example, on `chnode1`:

   ```
   SELECT *
   FROM db1.table1
   ```

   ```
   Query id: 7ef1edbc-df25-462b-a9d4-3fe6f9cb0b6d

   ┌─id─┬─column1─┐
   │  1 │ abc     │
   │  2 │ def     │
   └────┴─────────┘

   2 rows in set. Elapsed: 0.006 sec.
   ```

   On `chnode2`:
6. ```
   SELECT *
   FROM db1.table1
   ```

   ```
   Query id: c43763cc-c69c-4bcc-afbe-50e764adfcbf

   ┌─id─┬─column1─┐
   │  3 │ ghi     │
   │  4 │ jkl     │
   └────┴─────────┘
   ```
7. You can create a `Distributed` table to represent the data on the two shards. Tables with the `Distributed` table engine don't store any data of their own, but allow distributed query processing on multiple servers. Reads hit all the shards, and writes can be distributed across the shards. Run the following query on `chnode1`:

   ```
   CREATE TABLE db1.dist_table (
       id UInt64,
       column1 String
   )
   ENGINE = Distributed(cluster_2S_1R,db1,table1)
   ```
8. Notice querying `dist_table` returns all four rows of data from the two shards:

   ```
   SELECT *
   FROM db1.dist_table
   ```

   ```
   Query id: 495bffa0-f849-4a0c-aeea-d7115a54747a

   ┌─id─┬─column1─┐
   │  1 │ abc     │
   │  2 │ def     │
   └────┴─────────┘
   ┌─id─┬─column1─┐
   │  3 │ ghi     │
   │  4 │ jkl     │
   └────┴─────────┘

   4 rows in set. Elapsed: 0.018 sec.
   ```

### Summary[​](#summary "Direct link to Summary")

This guide demonstrated how to set up a cluster using ClickHouse Keeper. With ClickHouse Keeper, you can configure clusters and define distributed tables that can be replicated across shards.

## Configuring ClickHouse Keeper with unique paths[​](#configuring-clickhouse-keeper-with-unique-paths "Direct link to Configuring ClickHouse Keeper with unique paths")

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The procedure documented here is automated in ClickHouse Cloud services.

### Description[​](#description "Direct link to Description")

This article describes how to use the built-in `{uuid}` macro setting
to create unique entries in ClickHouse Keeper or ZooKeeper. Unique
paths help when creating and dropping tables frequently because
this avoids having to wait several minutes for Keeper garbage collection
to remove path entries as each time a path is created a new `uuid` is used
in that path; paths are never reused.

### Example environment[​](#example-environment "Direct link to Example environment")

A three node cluster that will be configured to have ClickHouse Keeper
on all three nodes, and ClickHouse on two of the nodes. This provides
ClickHouse Keeper with three nodes (including a tiebreaker node), and
a single ClickHouse shard made up of two replicas.

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| node description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `chnode1.marsnet.local` data node - cluster `cluster_1S_2R`| `chnode2.marsnet.local` data node - cluster `cluster_1S_2R`| `chnode3.marsnet.local` ClickHouse Keeper tie breaker node | | | | | | | |

Example config for cluster:

```
    <remote_servers>
        <cluster_1S_2R>
            <shard>
                <replica>
                    <host>chnode1.marsnet.local</host>
                    <port>9440</port>
                    <user>default</user>
                    <password>ClickHouse123!</password>
                    <secure>1</secure>
                </replica>
                <replica>
                    <host>chnode2.marsnet.local</host>
                    <port>9440</port>
                    <user>default</user>
                    <password>ClickHouse123!</password>
                    <secure>1</secure>
                </replica>
            </shard>
        </cluster_1S_2R>
    </remote_servers>
```

### Procedures to set up tables to use `{uuid}`[​](#procedures-to-set-up-tables-to-use-uuid "Direct link to procedures-to-set-up-tables-to-use-uuid")

1. Configure Macros on each server
   example for server 1:

```
    <macros>
        <shard>1</shard>
        <replica>replica_1</replica>
    </macros>
```

Note

Notice that we define macros for `shard` and `replica`, but that `{uuid}` isn't defined here — it's built-in and there is no need to define it.

2. Create a Database

```
CREATE DATABASE db_uuid
      ON CLUSTER 'cluster_1S_2R'
      ENGINE Atomic;
```

```
CREATE DATABASE db_uuid ON CLUSTER cluster_1S_2R
ENGINE = Atomic

Query id: 07fb7e65-beb4-4c30-b3ef-bd303e5c42b5

┌─host──────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode2.marsnet.local │ 9440 │      0 │       │                   1 │                0 │
│ chnode1.marsnet.local │ 9440 │      0 │       │                   0 │                0 │
└───────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
```