sqlmesh
sqlmesh copied to clipboard
TobikoData
Clickhouse and SQLMesh real world usage issues
Let me preface by saying, that I understand the clickhouse engine is still very new.
After some considerable work on our side to get try to get things to work properly with SQLMesh and clickhouse we’ve realized a few things that make using sqlmesh with clickhouse unusable with the built-in materialization strategies. We are fine writing our own custom strategy at this time but we wanted to take the opportunity to offer some insight into the things that we’ve seen and how we suggest to improve it.
Clickhouse and deletes
As described to us by a clickhouse engineer they consider the written to be “immutable” so deletions in clickhouse are not generally part of the suggested workflow. Normally if a DELETE command is used on clickhouse, the deletion is handled asynchronously. Forcing it to be a synchronous operation is costly for the clickhouse engine. In our experiments, when trying to backfill a very large model that sources some time series data, we found that the execution would take days to complete. Part of the issue is that we used a batch_size that was not the full size of the database but this was to reduce the memory requirements on our deployment. As sqlmesh handles incremental materializations by doing a delete then an insert, even if the table is initially empty, the clickhouse cluster cannot optimize this and synchronous delete operations still take a long time to return with a success. If you instead attempt to allow for the deletes to be synchronous and continuously attempt to write to the table, this can lead to some concurrency issues that are not entirely predictable.
Some suggestions for solutions
- Append only incremental
- This would obviously be some kind of opt in flag but doing this would allow the use of table engines like “ReplacingMergeTree” or simply speed up the process and leave de-duplication to the user of clickhouse
- The obvious issues I see with this are:
- A full restatement may still need to allow deletes or write to a completely different table then swap the references in the final schema.
- A partial restatement would probably need to be slow and one would hope that maybe we can pass run time parameters to improve efficiency of the materialization
Some requests
- Allow setting different materialization strategies depending on the dialect in use.
- We currently have some things setup to use duckdb with some local test data to test our models. It’s one of the reasons we’ve been trying to integrate sqlmesh more into our data pipeline as opposed to dbt. For this specific instance, if we wrote a custom materialization strategy for clickhouse it would nice if we could just use the normal
INCREMENTAL_BY_TIME_RANGEstrategy.
- We currently have some things setup to use duckdb with some local test data to test our models. It’s one of the reasons we’ve been trying to integrate sqlmesh more into our data pipeline as opposed to dbt. For this specific instance, if we wrote a custom materialization strategy for clickhouse it would nice if we could just use the normal
- Make it easier to subclass the existing materialization strategies
- Unless sqlmesh can include an “append-only” mode into the incremental by time range strategy
Other things to note
- Each model, when using the delete + insert style of materialization, must not run concurrent batches if there are batches set. This seemed to cause some unpredictable concurrency issues due to the deletions mutating the storage.
Hi @ravenac95, thanks for sharing this. We'll have some internal discussions about how to move to a more efficient implementation.
Hi @ravenac95, thanks for sharing this. We'll have some internal discussions about how to move to a more efficient implementation.
If I can be of assistance, I can be available for any discussions on the topic.
Hi @ravenac95 - the updated implementation is about ready to go. https://github.com/TobikoData/sqlmesh/pull/3226
We would welcome any thoughts or feedback from your team!
@treysp Thanks for the heads up! Sorry for the late response. I've been out on vacation. That being said, we've actually, generally, been looking at clickhouse alternatives but I'll see if I can still run some tests on this!
@ravenac95 We are evaluating clickhouse cloud as a DWH with DBT and now SQLMesh. Do you mind sharing more light on why you have been looking for clickhouse alternatives ?
