1

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:

Kokizzu
  • 1,403
  • 6
  • 18
  • 35

1 Answers1

2

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.
Kokizzu
  • 1,403
  • 6
  • 18
  • 35