Destination ClickHouse V2- Ready for Beta Testers!

[katmarkham]

Early Access Available

Calling all Destination ClickHouse users: we are excited to announce our new ClickHouse-V2 Destination!

We would love your help as beta testers.

Some improvements over v1 include:

• All sync modes supported (including dedupe)
• Data will be typed and written to columns matching the defined schema not a raw JSON format
• Performance improvements
• Actively developed and maintained by Airbyte

How to Enroll

We are actively seeking and iterating on feedback to refine these features prior to general availability. Airbyte OSS and Cloud users are welcome!

Note: For OSS you must upgrade your Airbyte Platform version to at least 1.6.0 before using this connector version.

Request for Feedback

All feedback is welcome, but as you explore the new functionality, we ask that you pay special attention to the following:

• Object Types and JSON — We currently are serializing columns of type Object into strings. We are not supporting storing them as JSON as that is a separate paid feature in ClickHouse we can't rely on every user to have available to them. Let us know if the lack of this configuration option is a blocker for you.
• Other Datatype Mapping — We've included the mappings we use internally in the ClickHouse v2 docs. Please let us know how well these work for you, specifically if any of these are a blocker to using the destination.
• Deduplication Behavior — We leverage ClickHouse's native ReplacingMergeTree for compaction-style deduplication as opposed to a more complicated schemes we've used in other destinations.
• Keep an eye out for odd behavior around switching primary keys and sync modes — This is an overlooked but a fraught part of destination code base for bugs.

We look forward to hearing about your experience with Destination ClickHouse v2. Please don't hesitate to share any feedback or questions in the comments below. You can also join us on Slack if you would like to chat directly with the team about this feature.

Update: If you're having issues getting started in OSS, please see the Quick Start Guide posted below. Thank you for your patience.

[havrilyukv]

When it will be available on Airbyte open source?

[benmoriceau]

When it will be available on Airbyte open source?

Hello @havrilyukv,
We are working on adding clickhouse to a new OSS release. For the time being we have documented a way to use clickhouse through a custom connector. It can be find on slack in the beta's private channel. You can ask access to it here.

[tryangul]

Update: v2 will be available in the next release patch release of the platform 1.7.1. We can't give exact estimates when that will land, but it should be soon. We'll update this discussion once we've released and verified it's available.

[mekedron]

Unfortunately it doesn't work on my self-hosted installation. I tried Stripe and MySQL as sources. I tried to use airbyte/destination-clickhouse-v2:0.1.5 for arm64

[benmoriceau]

I think that you'll need to do a manual DB update to make it work. We have an explaination of the steps to do to make the connector to work on OSS here. This is a temporary solution which won't be needed when clickhouse will be bundle in a new release.

[mekedron]

@benmoriceau thanks a lot! Yeah it has fixed the issue

[je-kr]

I'm not sure the ClickHouse JSON data type is a separate paid feature :
https://clickhouse.com/docs/sql-reference/data-types/newjson

It seems that it's supported in open source from version 25.3

In any case, storing data as JSON would be great !

[benmoriceau]

We currently don't support the JSON type, mainly because it is not supported by ClickHouse cloud by default. We are considering adding that as an option in the connector config. Feel free to ask access to the private beta channel here, this is the place where we do most of the announcements.

[benmoriceau]

Hello @je-kr,
The JSON support has been added to the version 0.1.8. You'll need to opt-in to it in the connector configuration.

[tryangul]

X-Posting from beta slack channel:

We are working on getting the v2 version directly available in OSS, but in the meantime, here's a guide to get started.

OSS Quick start guide:

1. Create a custom connector by going to "Settings" -> "Destination" and clicking "+ New Connector". Use airbyte/destination-clickhouse-v2 for the docker repository and latest for the tag to automatically get the new versions as we release them. If you'd like to pin to a specific version, choose one from the repository here here.
2. Connect to your local airbyte database:
   Shell into airbyte-db-0 in your local cluster
   Run psql postgresql://airbyte:airbyte@localhost:5432/db-airbyte
3. Run the following query UPDATE actor_definition_version SET supports_refreshes = true WHERE docker_repository = 'airbyte/destination-clickhouse-v2';
   You should now have a working ClickHouse v2 connector that will automatically get the latest changes.

[tryangul]

Update: We're coordinating with our deployments team to ensure ClickHouse v2 is available in the next patch release of the platform (1.7.1). No exact estimate on our end, but we're hopeful this should land soon.

[mekedron]

Trying to synchronize invoices from Stripe to Clickhouse using the latest connector version, and it seems like all the datetime fields are decimals or integers, but not datetime type.

Also noticed that after initial sync, every time I click on Sync now button, it adds the last record one more time. Even though no new transactions happened in Stripe.

[benmoriceau]

Hello @mekedron,
I you are running on cloud could you provide a link to the connection. I am wondering if there might be an issue with the stripe connector where the datetime type is not properly defined.

[mekedron]

Hey @benmoriceau! I'm hosting Airbyte in my k8s cluster.

Ah yes, as I might see all the time-related fields are Integers in Stripe connector itself. Sorry for bothering you about that!

However, there is still that strange behavior that loads an extra record each synchronization.

Is there something I can provide what can be helpful?

[benmoriceau]

Could you share the values of the ids and the cursor between 2 runs? Or let me know if the cursor has been updated.

Also in your queries to the DB, make sure to add FINAL at the end of it. We are not running dedup through airbyte and we are leveraging the internal dedup engine of clickhouse in order to lower the compute cost in clickhouse. Adding the final keyword will help you to make sure that the datas are deduplicated: https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree#query-time-de-duplication--final

[japesh-SuperAgi]

I had connected google sheets via clickhouse v2 connector.!! on airbyte cloud!

If I try to update a row in my sheet and resync it(append+deduped). it gives this error

Warning from destination: Code: 48. DB::Exception: Moving tables between databases is not supported for Replicated engine. (NOT_IMPLEMENTED) (version 25.4.1.37289 (official build))

com.clickhouse.client.api.ServerException: Code: 48. DB::Exception: Moving tables between databases is not supported for Replicated engine. (NOT_IMPLEMENTED) (version 25.4.1.37289 (official build))

[benmoriceau]

Thanks, I am looking at a potential issue with the change of primary key. In the meantime could you try to refresh truncate your connection?

[japesh-SuperAgi]

okay sure!! Thanks!!

[japesh-SuperAgi]

hey @benmoriceau Even with the TRUNCATE TABLE and refresh connection ...same error is coming

[benmoriceau]

Hello,
@japesh-SuperAgi I'm currently working on this issue. I managed to reproduce it locally and have a proper fix. I'll submit a PR soon and get it through our internal review process. I'll let you know when it is included in a new release.

Thanks for your patience.

[benmoriceau]

It has been released in the version 0.1.7

[havrilyukv]

Postgres to Clickhouse v2 latest

com.clickhouse.client.api.ServerException: Code: 241. DB::Exception: (total) memory limit exceeded: would use 20.95 GiB (attempt to allocate chunk of 4.03 MiB bytes), current RSS: 1.55 GiB, maximum: 20.62 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker: While executing BinaryRowInputFormat. (MEMORY_LIMIT_EXCEEDED) (version 25.4.7.66 (official build))

[benmoriceau]

Hello @havrilyukv,
This might come from an issue in clickhouse: ClickHouse/ClickHouse#82627.

Are you running with a self-hosted clickhouse?

[havrilyukv]

Are you running with a self-hosted clickhouse?

yeah, clickhouse selfhost. will test it, thanks!

[havrilyukv]

Clickhouse v2 Release v25.6.2.5-stable Latest - helped

[benmoriceau]

Great!

[davinchia]

Hi everyone, we strongly encourage you to join the Clickhouse Beta Slack channel. The team monitors that closely so responses will be faster. It's also an easier medium to discuss in-depth about any specific Clickhouse questions. Thanks!

[MusaadAlassaf]

[tryangul]

Hey Musaad. Thanks for the feedback.

Can you elaborate on how it is not supporting Incremental - Append + Deduped? I am able to create a connection on cloud in this mode. Note: you must use a source that supports incremental itself.

Also, I'm about to GA this version, which will unfortunately deprecate the temporary separate v2 version. I'll update this thread once that happens.

[tryangul]

It is now released

[tryangul]

Hey everyone 👋

We are excited to announce the end of the beta and the move to GA 🎉

It is now available in cloud and OSS!

It will replace the existing 1.0.0 connector as a breaking change. For users of the existing 1.0.0 connector, you can find more about what that means in the migration guide here.

I'd like to express my sincere thanks to you all for joining the beta and providing invaluable feedback 🙇 We look forward to your continued input post GA 🤝

Once we shut down the Slack channel, please feel free to open any new issues in the main airbyte/ repository.

[mtomasek87]

Hey everyone!

We're having an issue with the Incremental Append + Deduped sync mode and column name sanitation with version 2.0.3(Airbyte version 1.6.1 and ClickHouse version 25.6.3.116). On our MSSQL source we have column names with spaces, for example "Line No". When Airbyte does an initial load of the data into Clickhouse, the column gets renamed to "Line_No". On the second load we get a destination error "Warning from destination: Code: 497. DB::Exception: username: Not enough privileges. To execute this query, it's necessary to have the grant ALTER ADD COLUMN(Line No)". It seems like there is a discrepancy because the connector cannot figure out that the "Line_No" and "Line No" columns are the same. Is this a schema‑diff bug in the V2 connector, or can we disable the sanitiser?

Thanks!

[benmoriceau]

Hello,
We have publish a related fix in the version 2.0.6. Let us know if this fixed your issue.

[mtomasek87]

Yes, it did. Thank you!

[vonschultz666]

Can confirm this works now.

[vonschultz666]

@benmoriceau perhaps any updates on this - #63353 - it seems that Terraform Airbyte provider is still not up to date with the the new ClickHouse v2 connector?

[benmoriceau]

Hello @vonschultz666,
@JonsSpaghetti Is working on fixing the provider. He posted an update here.

[theartofdevel]

how to get the most latest tag in OSS?

here is https://hub.docker.com/r/airbyte/destination-clickhouse-v2/tags only latest which updated 14 days ago

[benmoriceau]

Hello @theartofdevel,
The connector has been merge into the existing clickhouse connector. The current version is 2.0.8, you can find the tags here.

[vonschultz666]

We’re working with a table that has a composite primary key and a last_modify_date column used for incremental ingestion and deduplication—the cursor is the last_modify_date column. This column is actually a date-stamp, not a true timestamp (its format is 2024-12-02 00:00:00). Consequently, if we trigger the sync several times in a single day, Airbyte keeps ingesting rows starting from midnight because there’s no timestamp-level precision. This isn’t ideal, but it still works (deduplication should still occur).

The table is about 5 GB, with ~190 million rows and 7–8 columns (source MSSQL), and deduplication was stalled for some reason (for 4+ days).

We noticed this because we ran:

SELECT * FROM xxx FINAL;

and received a deduplicated view of the table.

After executing:

OPTIMIZE TABLE xxx FINAL;

everything looked fine (no duplicates; only the latest version remained).

Do you have any advice on how to config ClickHouse to carry out deduplication straight away after the Airbyte ingest is done? At least how to config ClickHouse in relation to Airbyte...? I guess this mostly relates to ReplacingMergeTree.

[benmoriceau]

@vonschultz666 Thanks for the feedback.
I think that the OPTIMIZE statement can be improved by using BY COLUMNS. That being said, Clickhouse doesn't recommend using OPTIMIZE because of the potential cost of it.

I believe that we could improve the deduplication by providing an advanced option min_age_to_force_merge_seconds which will force the deduplication to start faster. The default value is 0 which disable the setting (source). For now, you can try those settings by altering the table. Later on we may add the option.

The goal of using the ReplacingMergeTree is to avoid running the deduplication through a query (which we are doing in other connectors). This is generation extra complexity and more importantly extra computing costs. We believe that using clickhouse implementation is lowering this cost.

Thanks a lot for raising that, it is likely a concern shared across many users.

[vonschultz666]

@benmoriceau yes, adding that parameter in Airbyte would be beneficial. Do you have any deadline in mind?

Yet again, with that parameter defined, 100% control is still not possible while the database itself still decides when to trigger the merge.

Defining parameter with ALTER TABLE for each table is a solid alternative, but if the table is erased and again manually altered, ALTER TABLE statement can be forgotten... i.e it can be tricky for everyday maintenance.

On the other hand, i think we will put OPTIMIZE statement as a final task in the Airflow job which starts with the Airbyte task, but only for those tables which use increment+dedupe option. Since our jobs for dimension and fact creation follows shortly after, this is the only way to be 100% sure that the deduplication was done.

Does anyone have any better idea?

[benmoriceau]

Hello,
I can't give you an ETA for the new parameter. We are currently working on improving the CDK which will improve the speed of the connectors using it. I agree that doing a manual ALTER is not a viable long term solution. I'll try to find some time to implement the changes, but I can't commit on a deadline unfortunately.

About running OPTIMIZE for the table to dedup, we though about it and decided to not do it because of the performances degradation related to it. We could also add it an option but we need to make sure that properly surface the risk associated with it and make sure that the option is classified as an advance option.

[vonschultz666]

@benmoriceau thanks for the clarification and overall effort. Please let us know about the possible updates.

Can you just give a short comment regarding the "overall performance degradation"? We tested 200 million row table with 10+ columns, on our node it took around 2mins when we ran OPTIMIZE manually. Nevertheless, that was one of the very few jobs on the node at the time.

Did you tried to run it when ingest on 10+ tables is running or something like that?

[benmoriceau]

Hello @vonschultz666,
We didn't try to run it while ingesting on our side. Our test focussed on making sure that the data were correct and with an performance improvement compare to the V1 connector.
The overall performance degradation comes from the recommendation from the clickhouse documentation.

[mshustov]

Hi Mikhail,

Thanks for the v2 update! I have a couple of enhancement requests for the v2 connector:

1. Allow users to specify the engine type. Currently, it's calculated based on the import type

   airbyte/airbyte-integrations/connectors/destination-clickhouse/src/main/kotlin/io/airbyte/integrations/destination/clickhouse/client/ClickhouseSqlGenerator.kt

   Lines 71 to 75 in 307c0b2

   	val engine =
   	when (stream.importType) {
   	is Dedupe -> "ReplacingMergeTree()"
   	else -> "MergeTree()"
   	}

   . This is fine, but there are some advanced use cases where users want more control over data flow. For example, a common pattern in ClickHouse is ingesting data into the Null table engine, which triggers MV for further data transformation. See https://www.youtube.com/watch?v=r-QQ4VEJN68 for more information. I'd suggest allowing users to choose between MergeTree, ReplacingMergeTree, and Null table engine types in advanced mode. However, I'm not sure Null TE is possible, considering that Airbyte stores additional metadata there.

2. Allow specifying a primary key not only for Dedup mode, but also for Append mode. In ClickHouse, primary keys aren't unique and define a sparse index that significantly improves query performance. See https://clickhouse.com/docs/guides/creating-tables#a-brief-intro-to-primary-keys and https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes for more information. Currently, the Append mode implementation causes a full table scan on SELECT, which isn't suitable for the typical ClickHouse volume of data.

3. Allow partitions configuration https://clickhouse.com/docs/best-practices/choosing-a-partitioning-key

4. Support the ability to set settings via DSN https://clickhouse.com/docs/integrations/language-clients/java/jdbc#supplying-credentials-and-settings. These might be used to specify ClickHouse and JDBC driver settings. similar to what used to be supported in the old version Connector Issue: Doesn't respect configured JDBC params #25605

[vonschultz666]

Hi @mshustov , i would definitely give kudos for the options you've mentioned, i think few of them directly correlate to deduplication discussion i had with @benmoriceau - for example maybe partitioning would be beneficial in raw ingested tables for deduplication as well?

The first occurrence when my team is doing partitions is in the facts models (created with dbt) where we can take the advantage of different incremental strategies like insert+update because they work faster when certain table is partitioned.

[benmoriceau]

@vonschultz666 The kudos are well deserved!
@mshustov is working at ClickHouse and worked on the V1 of the connector. We are going to schedule a call with him about his suggestions. We will make sure that we speak about our previous discussion as well.
There is currently no public issue, but we created some internal ones to make sure that we are properly tracking all the feedback we received. It is now a matter of prioritization and then execution. I'll keep updating you when we have more to share.

[vonschultz666]

@benmoriceau @mshustov perhaps any news from your side on the topics covered?

[benmoriceau]

Hello,
@vonschultz666 We have put those items in our roadmap. Unfortunately, I can't give you a target date. We are focussing on migrating the clickhouse connector on a new CDK we implemented recently. Since those are new functionalities and not bugs, we don't plan to address them immediately.

[hansh0801]

Will there be support for multiple nodes (ON cluster clause)?

[mohammedalisj]

Hi @hansh0801 ... May i know how are u handling it now ? For me table is magically gone for sometime and appears suddenly depending on which node connecton is hitting

[mohammedalisj]

I'm also waiting in line with @hansh0801 :) for multi node support...

[davinchia]

Hi everyone! I'm going to close this discussion as our beta has officially closed. Please instead create an issue with the clickhouse destination label for follow up questions and discussions. Thank you!