quick question: I'm using the clickhouse destination to move some data and i've found airbyte is moving all data into a

public

database inside clickhouse instead of the database I indicated in the destination config page. Is this because the connector is still in alpha?

Hey @Gerard Clos, thanks for the question. Could you provide some more context on you configured the database you’re using for the clickhouse destination connector?

Aloja 👋 So I'm moving data from a postgres db to clickhouse. The schema of the tables in postgres is "public" and I think that is the reason why airbyte is importing those in a "public" database in clickhouse. I say so because when I import from another source to clickhouse (e.g google sheets connector), data is correctly imported into the warehouse I indicated in the clickhouse destination settings.

Hey @Gerard Clos, my name is Ryadh and I work on the integrations at ClickHouse. If you have any feedback about the ClickHouse connector, please don’t hesitate to share! we are constantly monitoring for improvements

Found a bug, maybe you know about it, when using incremental - append strategy the replication fails with this error.

DB::Exception: System call renameat2() is not supported.

And I imagine it's because of this.

Interesting! thanks for sharing. We will investigate! Are you using ZFS as well? cc. @Geoff Genz, @Mikhail Shustov

I'm running clickhouse inside docker with the official image, clickhouse data mounted in a docker volume, no extra configuration. So it might be that it's using ZFS under the hood, although not sure.

Thanks @Gerard Clos for digging into this and finding the solution to your problem. For future reference, where did you find the “mirror source structure” value and how were you able to change it?

it's one of the options of destination namespace in the connection settings

Thanks Gerard, hopefully this helps future users if they run into the same problem

getting the same renameat2() bug, on the clickhouse/clickhouse-server docker image on an m1 pro chip

If you're using the latest dbt-clickhouse version (1.3.0), it's just an information message. See my comments here: https://github.com/ClickHouse/dbt-clickhouse/issues/111

Yeah, but it effeectively makes normalization fails and makes clickhouse unusable

If normalization is failing it's not because of the rename table issue.

Here's for example what we get:

8 of 41 ERROR creating sql incremental model pipedrive.pipedrive_activities_scd ........................................ [ERROR in 31.13s]
11 of 41 ERROR creating sql incremental model pipedrive.pipedrive_deals_scd ............................................ [ERROR in 14.65s]
14 of 41 ERROR creating sql incremental model pipedrive.pipedrive_organizations_scd .................................... [ERROR in 11.73s]
16 of 41 ERROR creating sql incremental model pipedrive.pipedrive_persons_scd .......................................... [ERROR in 9.55s]
17 of 41 ERROR creating sql incremental model pipedrive.pipedrive_pipelines_scd ........................................ [ERROR in 1.76s]
18 of 41 ERROR creating sql incremental model pipedrive.pipedrive_stages_scd ........................................... [ERROR in 1.78s]
19 of 41 ERROR creating sql incremental model pipedrive.pipedrive_users_scd ............................................ [ERROR in 2.26s]
Database Error in model pipedrive_activities_scd (models/generated/airbyte_incremental/scd/pipedrive/pipedrive_activities_scd.sql)
  :HTTPDriver url <http://localhost:8123> returned response code 500)
   Code: 1. DB::Exception: System call renameat2() is not supported. (UNSUPPORTED_METHOD) (version 22.10.2.11 (official build))
  compiled Code at ../build/run/airbyte_utils/models/generated/airbyte_incremental/scd/pipedrive/pipedrive_activities_scd.sql
Database Error in model pipedrive_deals_scd (models/generated/airbyte_incremental/scd/pipedrive/pipedrive_deals_scd.sql)
  :HTTPDriver url <http://localhost:8123> returned response code 500)
   Code: 1. DB::Exception: System call renameat2() is not supported. (UNSUPPORTED_METHOD) (version 22.10.2.11 (official build))

It is the pipedrive integration

what is the dbt-clickhouse version?

Not sure what the airbyte normalizer uses

No, those ports should be sufficient and that error wouldn't be affected anyway. I don't know anything about pipedrive but I would expect a different error if was actually using dbt-clickhouse. Let me dig into a bit.

Yeah was fearing this was the case. I'll create an issue

did you create the issue @Berzan Yildiz? having the same problem on my end can't do incremental syncs to clickhouse

@Gerard Clos Are you seeing the same error? Can share version numbers and any error messages?

yep. Trying to replicate incrementally to a clickhouse instance within a docker container (using the official clickhouse docker image). It fails with this error:

:HTTPDriver url <http://host.docker.internal:8123> returned response code 500)
airbyte-worker      | 2023-01-19 12:23:31 normalization >    Code: 1. DB::Exception: System call renameat2() is not supported. (UNSUPPORTED_METHOD) (version 22.10.2.11 (official build))

The current version of airbyte should just log that error as a warning and keep working

nope, it fails to normalize

is there anything else in the log? In particular something referencing the dbt version?

let me check

Yeah Airbyte does most things in Java, but delegates normalization to dbt

hmm yep you were right I think there's something fishy with our config, I can see this error

Yeah I have no idea what that means, but it doesn't seem ClickHouse related. Is there any other logging around the

renameat2()

error? That is expected if ClickHouse doesn't support

EXCHANGE TABLES

(trying to exchange tables is currently the only way to test), but as I said, dbt/normalization should just keep going after that.

no nothing more related to renameat2()

👋 Hi everyone, I'm also interested in this issue

@Andres Gutierrez I agree that error message looks like it might be a big part of the problem. I can't tell much more without seeing what SQL airbyte is generating, but I do know that dbt-clickhouse doesn't support anything that looks like dedup.

Hi @Geoff Genz thanks for the reply! So you think even if we manage to configure our Clickhouse with

allow_nondeterministic_mutations

flag enable it

incremental + dedup

won't work?

Well I can't see what airbyte is trying to do, but if it's trying to alter a key column you will probably just get another error

Hi @Ryadh I just saw your message. Any ideas on what could be happening here? https://airbytehq.slack.com/archives/C021JANJ6TY/p1666959569052819?thread_ts=1666787887.489099&cid=C021JANJ6TY

In dbt the only ALTER that dbt-clickhouse allows is in an

update_rows

function. ClickHouse doesn't allow updates of key columns.

Hi @Geoff Genz I have new insights related with

Airbyte -> Clickhouse

integration So to resume we want to do

incremental + dedup

on all streams in the source. I'm trying connecting my local Airbyte with a local Clickhouse and with a instance hosted on

Clickhouse Cloud

. In my local Clickhouse updating streams configured as

incremental + dedup

does not fail but the update takes longer that initial full scan of the source. 1. Full scan: 4m 2. Incremental Update: 7m In the case of Clickhouse CLOUD. Incremental update fails with this error:

:HTTPDriver for <https://MY_ID_HERE>.[thing].aws.clickhouse.cloud:8443 returned response code 500
Code: 36. DB::Exception: ALTER UPDATE/ALTER DELETE statement with subquery may be nondeterministic, see allow_nondeterministic_mutations setting. (BAD_ARGUMENTS)

The weird thing is that my LOCAL clickhouse doesn't have either

allow_nondeterministic_mutations

flag enabled looking on clickhouse console

SELECT value FROM system.settings where name='allow_nondeterministic_mutations'

SELECT value
FROM system.settings
WHERE name = 'allow_nondeterministic_mutations'

Query id: e361bd2f-a7a1-46f4-98f6-042e90e7a82a

┌─value─┐
│ 0     │
└───────┘

1 row in set. Elapsed: 0.014 sec.

Do you have any idea why 1. And incremental update takes longer that initial full scan? 2. How is possible that's working in my local clickhouse even if I don't have

allow_nondeterministic_mutations

?

Incremental updates using default dbt-clickhouse strategy are quite slow. The diagram here should give an idea why: https://clickhouse.com/docs/en/integrations/dbt/dbt-incremental-model#internals

Thanks!

airbyte/dbt-clickhouse really shouldn't be doing alter statements . . .

So dbt-clickhouse should not be used for incremental + dedup updates?

I wouldn't recommend the version that's embedded in Airbyte for large datasets

Well, my dataset is not very large. It's a zoho connector with my Sandbox zoho account which is almost empty. I have a contact xD

and I haven't had a chance to figure out where that ALTER is coming from. dbt has an update method that uses it but I don't know in what circumstances (and it probably shouldn't)

Why ALTER is bad? I would like to understand the issue

ClickHouse is not designed to update data. It's designed for immutable data. ALTER is an async mutation that is slow and has to be coordinated among all replicas.

Oh shit sounds really bad. So I think

incremental + dedup

is not a good idea for Clickhouse

Hey @Geoff Genz quick question. We are considering moving away from clickhouse as our destination considering it's not ideal for incremental replication. What destination would you recommend in terms of airbyte support? BigQuery? I'm looking for the ones that have the most stable connectors and we know are gonna perform properly with several types of syncing strategies.

You're asking the wrong person 🙂. I work for ClickHouse, and I don't think you'll find a better combination of performance and cost with much else.

oh apologies I thought you worked for airbyte. The thing is that not supporting incremental updates is a show stopper for us. We can't afford to do full refresh syncs regularly.

You generally need to think about your use case and your data structures to work around certain limitations. Airbyte can make that difficult at times because the actual work is abstracted under several layers.