Configuring ClickHouse Keeper with unique paths
Description
This article describes how to use the built-in {uuid}
macro setting
to create unique entries in ClickHouse Keeper or ZooKeeper. Unique
paths helps 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
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}
- 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}
is not defined here, it is built-in and there is no need to define.
- 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 │
└───────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
- Create a table on the cluster using the macros and
{uuid}
CREATE TABLE db_uuid.uuid_table1 ON CLUSTER 'cluster_1S_2R'
(
id UInt64,
column1 String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db_uuid/{uuid}', '{replica}' )
ORDER BY (id);
CREATE TABLE db_uuid.uuid_table1 ON CLUSTER cluster_1S_2R
(
`id` UInt64,
`column1` String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db_uuid/{uuid}', '{replica}')
ORDER BY id
Query id: 8f542664-4548-4a02-bd2a-6f2c973d0dc4
┌─host──────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode1.marsnet.local │ 9440 │ 0 │ │ 1 │ 0 │
│ chnode2.marsnet.local │ 9440 │ 0 │ │ 0 │ 0 │
└───────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
- Create a distributed table
create table db_uuid.dist_uuid_table1 on cluster 'cluster_1S_2R'
(
id UInt64,
column1 String
)
ENGINE = Distributed('cluster_1S_2R', 'db_uuid', 'uuid_table1' );
CREATE TABLE db_uuid.dist_uuid_table1 ON CLUSTER cluster_1S_2R
(
`id` UInt64,
`column1` String
)
ENGINE = Distributed('cluster_1S_2R', 'db_uuid', 'uuid_table1')
Query id: 3bc7f339-ab74-4c7d-a752-1ffe54219c0e
┌─host──────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode2.marsnet.local │ 9440 │ 0 │ │ 1 │ 0 │
│ chnode1.marsnet.local │ 9440 │ 0 │ │ 0 │ 0 │
└───────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
Testing
- Insert data into first node (e.g
chnode1
)
INSERT INTO db_uuid.uuid_table1
( id, column1)
VALUES
( 1, 'abc');
INSERT INTO db_uuid.uuid_table1 (id, column1) FORMAT Values
Query id: 0f178db7-50a6-48e2-9a1b-52ed14e6e0f9
Ok.
1 row in set. Elapsed: 0.033 sec.
- Insert data into second node (e.g
chnode2
)
INSERT INTO db_uuid.uuid_table1
( id, column1)
VALUES
( 2, 'def');
INSERT INTO db_uuid.uuid_table1 (id, column1) FORMAT Values
Query id: edc6f999-3e7d-40a0-8a29-3137e97e3607
Ok.
1 row in set. Elapsed: 0.529 sec.
- View records using distributed table
SELECT * FROM db_uuid.dist_uuid_table1;
SELECT *
FROM db_uuid.dist_uuid_table1
Query id: 6cbab449-9e7f-40fe-b8c2-62d46ba9f5c8
┌─id─┬─column1─┐
│ 1 │ abc │
└────┴─────────┘
┌─id─┬─column1─┐
│ 2 │ def │
└────┴─────────┘
2 rows in set. Elapsed: 0.007 sec.
Alternatives
The default replication path can be defined before hand by macros and using also {uuid}
- Set default for tables on each node
<default_replica_path>/clickhouse/tables/{shard}/db_uuid/{uuid}</default_replica_path>
<default_replica_name>{replica}</default_replica_name>
tip
You can also define a macro {database}
on each node if nodes are used for certain databases.
- Create table without explicit parameters:
CREATE TABLE db_uuid.uuid_table1 ON CLUSTER 'cluster_1S_2R'
(
id UInt64,
column1 String
)
ENGINE = ReplicatedMergeTree
ORDER BY (id);
CREATE TABLE db_uuid.uuid_table1 ON CLUSTER cluster_1S_2R
(
`id` UInt64,
`column1` String
)
ENGINE = ReplicatedMergeTree
ORDER BY id
Query id: ab68cda9-ae41-4d6d-8d3b-20d8255774ee
┌─host──────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode2.marsnet.local │ 9440 │ 0 │ │ 1 │ 0 │
│ chnode1.marsnet.local │ 9440 │ 0 │ │ 0 │ 0 │
└───────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
2 rows in set. Elapsed: 1.175 sec.
- Verify it used the settings used in default config
SHOW CREATE TABLE db_uuid.uuid_table1;
SHOW CREATE TABLE db_uuid.uuid_table1
Query id: 5925ecce-a54f-47d8-9c3a-ad3257840c9e
┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE db_uuid.uuid_table1
(
`id` UInt64,
`column1` String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db_uuid/{uuid}', '{replica}')
ORDER BY id
SETTINGS index_granularity = 8192 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.003 sec.
Troubleshooting
Example command to get table information and UUID:
SELECT * FROM system.tables
WHERE database = 'db_uuid' AND name = 'uuid_table1';
Example command to get information about the table in zookeeper with UUID for the table above
SELECT * FROM system.zookeeper
WHERE path = '/clickhouse/tables/1/db_uuid/9e8a3cc2-0dec-4438-81a7-c3e63ce2a1cf/replicas';
note
Database must be Atomic
, if upgrading from a previous version, the
default
database is likely of Ordinary
type.
To check: For example,
SELECT name, engine FROM system.databases WHERE name = 'db_uuid';
SELECT
name,
engine
FROM system.databases
WHERE name = 'db_uuid'
Query id: b047d459-a1d2-4016-bcf9-3e97e30e49c2
┌─name────┬─engine─┐
│ db_uuid │ Atomic │
└─────────┴────────┘
1 row in set. Elapsed: 0.004 sec.