Clickhouse Replication without Sharding

All we need is an easy explanation of the problem, so here it is.

How to make replication (1 master, 2 slave for example) in ClickHouse without sharding?
All I can see from the examples are always have sharding:

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Nevermind, found it on other Altinity Blog post

just create a docker-compose.yml file:

version: '3.3'
services:
  # from rongfengliang/clickhouse-docker-compose
  # from https://github.com/abraithwaite/clickhouse-replication-example/blob/master/docker-compose.yaml
  # from http://blog.quidquid.fr/2020/06/clickhouse-multi-master-replication/
  # from https://altinity.com/blog/2017/6/5/clickhouse-data-distribution
  ch1:
    image: yandex/clickhouse-server
    restart: always
    volumes:
      - ./config.xml:/etc/clickhouse-server/config.d/local.xml
      - ./macro1.xml:/etc/clickhouse-server/config.d/macros.xml
      - ./data/1:/var/lib/clickhouse    
    ports: 
      - '18123:8123'
      - '19000:9000'
      - '19009:9009'
    ulimits:
      nproc: 65536
      nofile:
        soft: 252144
        hard: 252144
  ch2:
    image: yandex/clickhouse-server
    restart: always
    volumes:
      - ./config.xml:/etc/clickhouse-server/config.d/local.xml
      - ./macro2.xml:/etc/clickhouse-server/config.d/macros.xml
      - ./data/2:/var/lib/clickhouse
    ports: 
      - '28123:8123'
      - '29000:9000'
      - '29009:9009'
    ulimits:
      nproc: 65536
      nofile:
        soft: 252144
        hard: 252144
  ch3:
    image: yandex/clickhouse-server
    restart: always
    volumes:
      - ./config.xml:/etc/clickhouse-server/config.d/local.xml
      - ./macro3.xml:/etc/clickhouse-server/config.d/macros.xml
      - ./data/3:/var/lib/clickhouse
    ports: 
      - '38123:8123'
      - '39000:9000'
      - '39009:9009'
    ulimits:
      nproc: 65536
      nofile:
        soft: 252144
        hard: 252144
  zookeeper:
    image: zookeeper

and config.xml file:

<yandex>
    <remote_servers>
        <replicated>
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>ch1</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>ch2</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>ch3</host>
                    <port>9000</port>
                </replica>
            </shard>
        </replicated>
    </remote_servers>
    <zookeeper>
        <node>
            <host>zookeeper</host>
            <port>2181</port>
        </node>
    </zookeeper>
</yandex>

and 3 macroX.xml where X=1,2,3 (replace chX with ch1, ch2, or ch3):

<yandex>
    <macros replace="replace">
        <cluster>cluster1</cluster>
        <replica>chX</replica>
    </macros>
</yandex>

then create a data directory and start docker-compose up.

you can create table using this command on one of the cluster clickhouse-client --port 19000:

SELECT * FROM system.clusters;
CREATE DATABASE db1 ON CLUSTER replicated;
SHOW DATABASES;
USE db1;

CREATE TABLE IF NOT EXISTS db1.sbr2 ON CLUSTER replicated
( seller_id UInt64
, recap_date Date
, last_updated_at DateTime 
, products_view UInt64 
, visitor_count UInt32 
, chat_count UInt32 
, trx_count UInt32
, trx_sum UInt64 
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/sbr2', 
'{replica}')
PARTITION BY modulo( seller_id, 1000 )
ORDER BY (seller_id, recap_date); 

INSERT INTO db1.sbr2
(seller_id, recap_date, visitor_count, products_view, chat_count, trx_count, trx_sum, last_updated_at)
VALUES (1,'2021-05-31',1,2,3,4,5,NOW());

then try connect to other replica and select previously inserted rows: clickhouse-client --port 29000

SELECT * FROM db1.sbr2;

┌─seller_id─┬─recap_date─┬─────last_updated_at─┬─products_view─┬─visitor_count─┬─chat_count─┬─trx_count─┬─trx_sum─┐
│         1 │ 2021-05-31 │ 2021-05-31 09:43:30 │             2 │             1 │          3 │         4 │       5 │
└───────────┴────────────┴─────────────────────┴───────────────┴───────────────┴────────────┴───────────┴─────────┘
↘ Progress: 1.00 rows, 42.00 B (132.02 rows/s., 5.54 KB/s.)  99%
1 rows in set. Elapsed: 0.008 sec.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply