Summary
When replicating a PostgreSQL database to ClickHouse using Airbyte, the table ‘clients’ from PostgreSQL is not properly replicated to ClickHouse. Instead, a new database ‘airbyte_internal’ is created with a table ‘public_raw___stream__clients’ containing different columns. Need help troubleshooting this issue.
Question
Hey everyone, I am having an issue replicating a PostgreSQL database to ClickHouse using Airbyte. I have faced this issue when I am trying to replicate the table exactly as it exists on the source to the destination (CH). The connection settings are screenshotted and attached. My table on the postgresql db is called “clients” and it shows up properly on airbyte, but once the sync is completed instead of having a new database with the table “clients” on the ClickHouse side I have a new db called “airbyte_internal” with a table called “public_raw___stream__clients” with 4 columns:
• _airbyte_raw_id
• _airbyte_data
• _airbyte_extracted_at
• _airbyte_loaded_at
Anyone knows why this is happening?
Any help would be appreciated!
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want to access the original thread.
Join the conversation on Slack
["postgresql", "clickhouse", "airbyte", "replication", "table-replication", "airbyte-internal"]
I came here with the exact same issue, but replicating from both Oracle and S3 buckets. So I’m guessing the issue is with the destination connector settings?
Found a response to <[destination-clickhouse] is not syncing data to the main tables but only creating the internal tables · Issue #36199 · airbytehq/airbyte · GitHub issue on GitHub>. Apparently it’s due to a migration to a new destination version. We’ll just have to parse the raw data until this connector is migrated,
Thank you very much <@U052KE77PB7> for your input! I didn’t find anything about this issue and this helped quite a lot. Did you finally solve your issue? I found <Destination Clickhouse - Add Typing and Deduping · airbytehq/airbyte · Discussion #35339 · GitHub solution here> that worked perfectly by downgrading the destination version.
My pleasure. Since my team is doing all data warehouse transformations with dbt Core, I plan to just use ClickHouse’s excellent family of JSONExtract() functions to parse the JSON-encoded data in the column _airbyte_data to transform our models. Until team Airbyte has migrated this connector to V2.