Exactly-once write semantics

[alexeypavlenko]

Hi Team,

I'd like to understand the proper way to ensure exactly-once ingestion into a sharded deployment of ClickHouse (multiple shards, with the same ReplicatedMergeTree tables configured in each shard).

My understanding of the process looks as follows (based on the documentation/talks/etc, haven't studied the code though):

1. On ingestion into ClickHouse node the latter calculates the checksum of the block and saves it to ZK.
2. The checksum is used to verify the integrity of the block (obviously) and to ensure that subsequent blocks with the same checksum are ignored.

This raises two issues (according to my understanding):

1. Inserting two identical blocks of data (that may be distinct ones) into a shard may result in just one block in a table.
2. Insertion retries shall be performed against the same shard, to let deduplication mechanism work.

Therefore to ensure exactly-once write semantics the following measures needs to be taken:

1. Inject into each block a unique column that helps to differentiates distinct blocks with identical payload. For instance, a UUID column (storage management is a different issue).
2. Ensure all insert retries go to the same shard.

Please let me know what's wrong with my understanding.
Thanks.

[alexey-milovidov]

You can retry the whole INSERT to Distributed table.
The batch will be split between shards and data will be send to shards as usual.
If you have deterministic sharding key, then data will be split between shards deterministically.
Then, on every shard that already have this block of data, it will be skipped, and inserted on shards that don't have data yet.

Moreover, if you use asynchronous inserts to Distributed table (which is by default) and don't enable distributed_directory_monitor_batch_inserts, the Distributed table will perform retries automatically for you, until the data will be inserted exactly once.