I am using Airbyte to sync some tables from a Postgres database >> Clickhouse db. According to Airbyte, the synchronization was successful and my tables were created in the target database in Clickhouse. However, when I try to run queries over my tables Clickhouse says that they don't exist:
DB::Exception: Table public.contracts_shop doesn't exist. (UNKNOWN_TABLE) (version 23.4.2.11 (official build))
But I am able to visualize and query the raw tables created by Airbyte such as _airbyte_raw_contracts_shop. I'm not sure if it's a misconfiguration from Airbyte, my Clickhouse database, or the way I'm connecting to clickhouse.
Note: I know that the default destination to clickhouse is default database but I made sure to send my data to public database.
The error message mentioning that my tables did not exist was correct. My ClickHouse server consists of 3 replicas, therefore, a replicated engine must be used to create any table (I am currently using ReplicatedMergeTree for all my tables). However, Airbyte uses the default engine which is MergeTree. The problem with this engine is that it does not create/replicate the tables on all other nodes, while ReplicatedMergeTree does.
As of today, Airbyte does not allow users to choose which engine will be used to create the tables. I was also using dbt, so from the .sql scripts I was able to bring the required data by implementing the postgresql table function from ClickHouse. This function is used to connect to a remote postgres server and run SELECT and INSERT queries. See.