Introduction to Clickhouse at scale
Tags: dataenginnering, clickhouse, beginners
Published: 2023-05-17 08:48:08

## Introduction
In many cases we prefer to scale our services, we always prefer scale out against scale up because of the lower cost of scaling out.
> Scaling out = adding more components in parallel to spread out a load. Scaling up = making a component bigger or faster so that it can handle more load. [Source](https://packetpushers.net/scale-up-vs-scale-out/)

In Clickhouse terminology, Scale out is equal to sharding.  And we use replicas to ensure availability. In this article, we will learn about  Cluster, Replicas, and Sharding in Clickhouse.
> Replication used for data integrity and automatic failover. Sharding is used for horizontal scaling of the cluster. [From Reference 1](#References)


## Sharding
Suppose we have a table on a Clickhouse node (yellow table in `host1`). After a while data become bigger and the request rate increased. Now we decide to scale the node, here `host2` comes to the scene. We apply sharding to the table and send a second shard on `host2`. 
- **How to make a select query to the table?**
	1. We can directly query a table in each host. In this case, we should know what data exist in each shard.
	2. We can create a `distributed table`. It can create on each node, when we query to `distributed table` it ingests data from the proper shard. It doesn't store data but has metadata about data in each shard.

![Clickhouse Sharding overview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m636jg87a8ujly0a1uy9.png)

- **How to insert data into the table?**
	1. We can directly insert our data into the shards if we have a predefined schema or something same.
	2. Also we cant insert data into the `distributed table` and it inserts data regarding the defined `sharding key`. You can read more about sharding key in [Clickhouse Doc](https://clickhouse.com/docs/en/engines/table-engines/special/distributed#distributed-writing-data)

## Replication
Suppose we run an application depending on Clickhouse or we need online analytics which uses Clickhouse. What happens if one node becomes down or a hardware issue rises? Certainly, we don't want that, so we need replication for each table. 
We can only have replicated tables for \*MergeTree\* tables. 
Clickhouse Keeper is who manages things about replication in Clickhouse. It is compatible with Zookeeper and it's a kind of alternative for Zookeeper which Clickhouse presents.

![Clickhouse cluster](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r7whbeonxuro6s21j20e.png)
## Cluster
A cluster is a way to manage sharding and replication between some nodes. We can have many cluster topologies for the same nodes. Every time we add a table to one cluster, It shards and replicates in a way defined in that cluster.

## How to apply Sharding/Replication/Clustering in Clickhouse?

### Set up Clickhouse Cluster
In the below picture, we have 4 nodes. We define a cluster named `cluster1` (in the Clickhouse config file). Each table associated with this cluster will have 2 replicas and 2 shards (But they should be Replicated* tables)

![Clickhouse cluster config](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i0pyj2z547ifbc9tizc0.png)
### Set up Clickhouse Keeper
See [This part](https://youtu.be/vBjCJtw_Ei0?t=600) of [Reference 1](##Reference)

### Create tables
Every time we want to create a table with a `cluster1` topology, we should use the `ON CLUSTER` statement. For example, if we want to create the table:

![Clickhouse clustered table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6hkhou04hb2pkkx3ozm2.png)
The first parameter of the `ReplicatedMergeTree` engine is a Clickhouse keeper path of the table and the second one is a replica name. **Tables with the same path and different replica names are replicated** (Clickhouse keeper does these things).
We should define `{shard}` and `{replica}` as [`macros`](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#macros) in each node.

![Clickhouse macros](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z6vnipwybqxdcb3nt5c7.png)
Once `mydb.my_table` is created in each of `host1`, `host2`, `host3`, or `host4` it will create in all other nodes.
And finally, we will create a `distributed table` to better query on `my_db.my_table` which has 2 shards.

![Clickhouse distributed table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k71pmyzf7wo6pv3y7rmh.png)
## References
1. Great video by Clickhouse in [youtube](https://www.youtube.com/watch?v=vBjCJtw_Ei0&t=1s)