Missing synced tables in Clickhouse database

Question

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.

Answer 1

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.

Answer 2

• Airbyte configuration is incorrect and that the tables are not actually being created in the public database. To check this, you can go to the Airbyte UI and click on the "Settings" tab. Under "Destinations," click on the "Clickhouse" destination and then click on the "Configuration" tab. Make sure that the "Database" field is set to public.

• Permissions run SHOW GRANTS command in the Clickhouse client. This will show you all of the grants that have been given to your user account. Make sure that you have the SELECT privilege on the public database.

• connectivity issues Rule out issue with the connection between your Airbyte instance and your Clickhouse database. To check this, you can use the SHOW STATUS LIKE 'Connections' command in the Clickhouse client. This will show you the number of active connections to your database. If the number of active connections is zero, then there is an issue with the connection.

Few things you can try to t-shoot :

--Try restarting the Airbyte server & / or Clickhouse server.

--Try connecting to the Clickhouse database using a different client, such as the Clickhouse client or the Clickhouse ODBC driver.

-- Try creating the tables manually in the public database.