Destination Clickhouse - Add Typing and Deduping

[jbfbell]

We removed (PR )"normalization" from Destination Clickhouse. Normalization was a process which would extract data from the raw json format and cast the data into tables with the appropriate types. For other destinations, we implemented Typing and Deduping to replace normalization and we will bring typing and deduplication to Clickhouse if there is sufficient interest

The purpose of this discussion is to gauge interest in bringing this functionality back to Clickhouse.

Without typing and deduping, you would need to integrate another tool, such as dbt to accomplish the same result. While integrating another tool gives you greater customization over the data format, it comes with additional operational overhead.

Should Airbyte bring Typing and Deduping to Clickhouse?

[asimumba]

How can I revert to pre 1.0.0 upgrade of the Clickhouse destination?

[kvosha]

Definitely we need normalization for clickhouse! Is there any way to stay on old version of clickhouse connection until normalization will be implemented?

[alberk8]

Yes, bring Typing and Deduping ClickHouse

[Riya-gupta-8]

Yes . You can degrade to v0.2.5 version for clickhouse

[nailele]

airbyte should bring typing and deduping for clickhouse 🥹

[nailele]

[MaxMusing]

Strong +1 on this, we would benefit greatly from typing and deduping on ClickHouse as a destination.

[asimumba]

In support to have this feature implemented.

[machariamuguku]

FYI: The airbyte team published the connector docker images on dockerhub. You can 'revert' to an older connector version (with normalization for example) by uploading it's older image as a custom connector. See clickhouse connector images here cc @asimumba @kvosha

[asimumba]

Hi, I actually resolved this already. Thanks.

[Aryanshaw]

how did u solve this problem can u tell me

[AlexisSerneels]

I've spent hours debugging why the final tables were not created before finding this discussion.
We definitely need this feature ;)

[vietpm]

I can't understand why this feature is removed. Please bring this back in destination v2.

For anyone wants this feature, you can go back to version 0.2.5 of clickhouse destination. Note that you'll be switched back to destination v1.
1, Settings -> Destinations -> Clickhouse. Change version to 0.2.5 and click "Change".
2, Enter your connection, choose Transformation, change to "Normalized tabular data", click "Save".
3, (optional) If you're using Incremental sync mode, "Incremental | Append + Deduped" is available now so you may be want to change.
4, Reset data, resync from scratch.

[shakalandy]

@vietpm does this still work for you?
The "Transformation" tab is not shown anymore, not sure if it's because of a new ClickHouse Version?
Downgrading to ClickHouse Connector 0.2.4 and then back to 0.2.5 does not bring transformation back anymore...

[shakalandy]

ok, Downgraded Airbyte to 0.63.3, works again - with 0.63.9 the "transformation" tab was lost despite the downgrade of the clickhouse Destination.

[Aryanshaw]

Hey @shakalandy does it still work for you?

[shakalandy]

Migrated to MySQL as Destination in this case.

[edwardysun]

+1 need this

[cedstrom]

Holy hell, please bring normalization back +1000.

[luishdosreis]

Pretty please bring normalisation back :) 🍰

[zowen-ch]

We need the normalization for our workflows. Please keep supporting this in Clickhouse!

[georgeemr]

Please bring it back!
Any reason why it was removed?

[jesperbagge]

A big heartfelt YES! Please bring Typing and Deduping to ClickHouse!

[Lawchak-Fadhil]

Please bring it back, I've spent countless hours trying to figure out what is wrong with my setup! We really need this for ClickHouse.

[matheuzinoficial]

+1 - We are waiting for this for too long, it would be very good for our company!

[o1lab]

Please bring normalization back for Clickhouse +1

[ThomasSertorio]

+1 - Please, please, we want it 😎
Really like to see normalization back for CH.

[prakashbalaji]

+1 We would need this feature back.

[CNimmo16]

+1 like many others I've spent hours trying to figure out what's going on, please bring this back!

[vitas-pm]

I just don't get it. Without trying to sound rude but Airbyte as a whole is completely useless for our purposes without normalization. Why take the T out of the ETL only to ask if you should put it back in? This downgrade has completely messed up our pipelines and cost us multiple days to fix. Please, please put it back in asap.

[evantahler]

An update from the Airbyte engineering team:

• Airbyte made some rather big changes to our destinations in the past year. As Clickhouse was not a certified connector, not was it as popular as Snowflake, Bigquery, etc, it was not included in the original batch of connectors that was migrated to Destinations V2, and now Refreshes. As we have changed how the Airbyte Platform works (e.g. removing the old dbt-based normalization), we had to remove the old normalization system from the Clickhouse destination so it would still function.
  • We removed dbt-based normalization to dramatically increase reliability (e.g.) and performance.
• At the moment, we are focused on revamping our tools so that we can build and certify connectors faster going forward. This includes building a new Bulk Load (Destinations) CDK which will include adding Typing and Deduping, Refrehses, etc to a destination easily. Today, it's a tricky thing to do.
• The plan is to complete our new Bulk Load (Destinations) CDK in late 2024, and re-introduce the community to this new tool in early 2025. At that point, we will welcome community contributions to modernize the connector, which will include adding Typing and Deduping and Refreshes support. Or, at that time, if we've gotten significant interest from our commercial users (Airbyte Cloud or Self-hosted Airbyte Enterprise), we will likely in-house the connector and certify it.

[SergeAx]

Hello, and thank you for your work on Airbyte, especially for making it open-source and available for enthusiasts and low-budget builders.

What is the current status of the Clickhouse destination connector? From the documentation page it seems that all the features are currently supported: four "yes" features and zero "No". However, I cannot make it support normalization and transformation of data.

Please note that Clickhouse is also an open-source product and thus is cherished by enthusiasts and makers, the crowd you are addressing by making Airbyte OSS.

[ankitswifter]

Yes Please, bring typing and deduping for clickhouse.

[hiteshchinu]

Yes we need it back!!!!!!!!!!!!

[sky93]

same problem here airbyte isn't useful for clickhouse anymore.

[gustavfjorder]

Can we fix the docs? They currently say that dedupe mode is supported https://docs.airbyte.com/integrations/destinations/clickhouse

[Aryanshaw]

guys do not rely on airbyte I did it I figured out a way to normalize it but it is using dbt so instead of ETL you guys will have to rely on ELT
I wrote an article on this tutorial u can try it
https://medium.com/@aryanrot234/airbyte-raw-data-transformation-using-dbt-dynamically-221de752733b

[geekbleek]

+1 this is drastically needed. ETL without the T just becomes the. Clickhouse (self-hosted and cloud) is one of the fastest performing columnar stores for analytics. If we're evaluating entire new ETL/schedulers/implementing ELT we might as well eval moving off Airbyte (self hosted or enterprise).

And ELT isn't the solve, because without dedupe, streams with high velocity of change will result in large raw datasets with many duplicates. Yes you can ELT to new tables and sort/dedupe on primary keys, but that reinforces the need for standing up a job scheduler stack with dbt where it wasn't required at all previously.

[Aryanshaw]

checkout my article

[mert-ozturk-lgs]

Yes, please integrate Typing and Deduping to Clickhouse. It is essential. Without the functionality, Airbyte becomes less attractive.

[Aryanshaw]

checkout my article

[mert-ozturk-lgs]

Yes, I saw your article, it's good! But still it would be better to avoid the additional effort in future.