Tag: Clickhouse

Clickhouse multi master replication

EnglishSoftwareTutorials

File for reproductible environment :

Nothing is stored on the VM instances : see volumes configuration out of the docker-compose file.

Sharding and replica is straightforward and defined in these files :

  • ch_configs/config.d/remote_server.xml
  • ch-configs/macros_chX.xml (replace X with server’s instance number)

Starting clickhouse servers :

$ docker-compose up -d ch1 ch2 ch3 ch4

$ docker ps -a

To use it either use any client you prefer (Tabix, …) or connect with the client (already defined in docker-compose file):

$ docker-compose run –rm client –host ch1 –user quid –password quid

3d8fccb51750 🙂 SELECT * FROM system.clusters

Database and tables creation

CREATE DATABASE todos_cluster on cluster todos;

SHOW DATABASES;

show tables from todos_cluster

This database has been replicated on all the shards/replicas, no tables are yet existing

The name of the cluster can be found in this file : ch_configs/config.d/remote_server.xml with the tag <todos>

Local table

To create tables where content will only be replicated with its replica :

You can check on each server (ch1, ch2, ch3, ch4) if the tatble is created

Now let’s insert data

INSERT INTO todos_cluster.events_local VALUES(1, ‘2020-03-28 00:00:00’, 3, ‘Hit the road Jack’, ‘2020-04-15 00:00:00’);

select * from todos_cluster.events_local;

Distributed tables

To create tables where content will be replicated in all shards :

Use show tables on all instances to see it has been created.

Now we insert data inside this table

INSERT INTO todos_cluster.events_distributed VALUES(2, ‘2020-03-28 00:00:00’, 4, ‘Sweig – Le joueur d\’échec’, ‘2020-05-05 00:00:00’);

INSERT INTO todos_cluster.events_distributed VALUES(3, ‘2020-03-28 00:00:00’, 1, ‘Kitchen’, ‘2020-03-30 00:00:00’);

INSERT INTO todos_cluster.events_distributed VALUES(4, ‘2020-03-28 00:00:00’, 3, ‘Muse Hysteria’, ‘2020-04-25 00:00:00’);

select * from todos_cluster.events_distributed