ClickHouse / ClickHouse/dbt-clickhouse

[Bug] <clickhouse replicated error & freeze>

Open
#388 4 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Python
Stars
362
Forks
177
Avg merge
2d 10h
Merged PRs (30d)
8

Description

### Describe the bug
DBT Core is unable to update tables with the Replicated engine, as DBT creates an empty copy of the destination table and, after completing the update (in my case, with `materialized='incremental' and incremental_strategy='delete+insert'`, inserts data from the copy into the destination table and then deletes the copy. However, this process ignores the fact that Clickhouse is unable to create a copy of a replicated table, resulting in an exception being thrown (`Code: 253. DB::Exception: Replica /clickhouse/tables/db/table/replicas/replica_name already exists`).

Although this was expected, I attempted to use the MergeTree engine for updating, since it does not require replicating the table with new data. However, unexpectedly, DBT begins to freeze randomly while performing the updates. Sometimes, it works fine, but at other times, it appears to be deadlocked for some reason, regardless of the number of threads I use.

### Steps to reproduce
1. create Replicated table
2. run update with incremental strategy

1. create replicated table
2. switch model engine to not replicated
3. run update with incremental strategy

### Expected behaviour
DBT Core works properly with replicated tables in ClickHouse, and DBT Core does not randomly freeze while updating replicated tables.
### Code examples, such as models or profile settings
```
{{ config(
order_by='(id)',
engine="ReplicatedMergeTree('/clickhouse/tables/dbt/my_table', 'my_replica')",
materialized='incremental',
unique_key=['id'],
incremental_strategy='delete+insert',
on_schema_change='fail'
) }}
select * from source_table
{% if is_incremental() %}
where id not in (select id from {{ this }})
{% endif %}
```

### dbt and/or ClickHouse server logs
14:39:47
14:39:47 Finished running 2 incremental models in 0 hours 0 minutes and 13.53 seconds (13.53s).
14:39:47
14:39:47 Completed with 2 errors and 0 warnings:
14:39:47
14:39:47 Database Error in model some_model (models/model_name/model.sql)
HTTPDriver for http://46.101.104.158:8123 received ClickHouse error code 253
Code: 253. DB::Exception: Replica /clickhouse/tables/dbt/table/replicas/replica1 already exists. (REPLICA_ALREADY_EXISTS) (version 24.8.2.3 (official build))
14:39:47
14:39:47 Database Error in model some_model (models/model_name/model2.sql)
HTTPDriver for http://46.101.104.158:8123 received ClickHouse error code 253
### Configuration
{{ config(
order_by='(id)',
engine="ReplicatedMergeTree('/clickhouse/tables/dbt/my_table', 'my_replica')",
materialized='incremental',
unique_key=['id'],
incremental_strategy='delete+insert',
on_schema_change='fail'
) }}
#### Environment
* dbt version: 1.8.8
* dbt-clickhouse version: 1.8.4
* clickhouse-connect version (if using http): 0.8.5
* Python version: 3.11.10
* Operating system: Ubuntu 22.04.5 LTS

#### ClickHouse server
* ClickHouse Server version: 24.8.2.3

Contributor guide

Open the contributing guide

Research direction

Start by reproducing both incremental_strategy='delete+insert' cases from the model configuration: one with ReplicatedMergeTree and one with a non-replicated engine. Inspect the generated table-copy, insert, and delete operations alongside the ClickHouse error code 253 and the reported freeze. Done means replicated-table updates no longer attempt an invalid replica copy and non-replicated updates do not freeze.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.