<@U04S8PLJWMT>, what did I miss in clickhouse setu...
# ask-ai
s
@kapa.ai, what did I miss in clickhouse setup?
Copy code
warn WARN main i.a.c.l.c.CheckOperation(execute):58 Caught throwable during CHECK java.lang.IllegalArgumentException: Failed to insert expected rows into check table. Actual written: 0
	at io.airbyte.integrations.destination.clickhouse.check.ClickhouseChecker.check(ClickhouseChecker.kt:48) ~[io.airbyte.airbyte-integrations.connectors-destination-clickhouse.jar:?]
	at io.airbyte.integrations.destination.clickhouse.check.ClickhouseChecker.check(ClickhouseChecker.kt:20) ~[io.airbyte.airbyte-integrations.connectors-destination-clickhouse.jar:?]
	at io.airbyte.cdk.load.check.CheckOperation.execute(CheckOperation.kt:48) [bulk-cdk-core-load-0.1.78.jar:?]
	at io.airbyte.cdk.AirbyteConnectorRunnable.run(AirbyteConnectorRunnable.kt:36) [bulk-cdk-core-base-0.1.78.jar:?]
	at picocli.CommandLine.executeUserObject(CommandLine.java:2030) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine.access$1500(CommandLine.java:148) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2465) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2457) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2419) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2277) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine$RunLast.execute(CommandLine.java:2421) [picocli-4.7.6.jar:4.7.6]
	at picocli.CommandLine.execute(CommandLine.java:2174) [picocli-4.7.6.jar:4.7.6]
	at io.airbyte.cdk.AirbyteDestinationRunner$Companion.run(AirbyteConnectorRunner.kt:289) [bulk-cdk-core-base-0.1.78.jar:?]
	at io.airbyte.cdk.AirbyteDestinationRunner$Companion.run$default(AirbyteConnectorRunner.kt:75) [bulk-cdk-core-base-0.1.78.jar:?]
	at io.airbyte.integrations.destination.clickhouse.ClickhouseDestinationKt.main(ClickhouseDestination.kt:10) [io.airbyte.airbyte-integrations.connectors-destination-clickhouse.jar:?]

Stack Trace: java.lang.IllegalArgumentException: Failed to insert expected rows into check table. Actual written: 0
	at io.airbyte.integrations.destination.clickhouse.check.ClickhouseChecker.check(ClickhouseChecker.kt:48)
	at io.airbyte.integrations.destination.clickhouse.check.ClickhouseChecker.check(ClickhouseChecker.kt:20)
	at io.airbyte.cdk.load.check.CheckOperation.execute(CheckOperation.kt:48)
	at io.airbyte.cdk.AirbyteConnectorRunnable.run(AirbyteConnectorRunnable.kt:36)
	at picocli.CommandLine.executeUserObject(CommandLine.java:2030)
	at picocli.CommandLine.access$1500(CommandLine.java:148)
	at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2465)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2457)
	at picocli.CommandLine$RunLast.handle(CommandLine.java:2419)
	at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2277)
	at picocli.CommandLine$RunLast.execute(CommandLine.java:2421)
	at picocli.CommandLine.execute(CommandLine.java:2174)
	at io.airbyte.cdk.AirbyteDestinationRunner$Companion.run(AirbyteConnectorRunner.kt:289)
	at io.airbyte.cdk.AirbyteDestinationRunner$Companion.run$default(AirbyteConnectorRunner.kt:75)
	at io.airbyte.integrations.destination.clickhouse.ClickhouseDestinationKt.main(ClickhouseDestination.kt:10)
k
The error message you’re seeing:
Copy code
java.lang.IllegalArgumentException: Failed to insert expected rows into check table. Actual written: 0
indicates that Airbyte’s ClickHouse destination connector was unable to write test data to the database during the connection check. This usually points to a misconfiguration or missing permissions in your ClickHouse setup. Here are the most common setup issues to check: 1. User Permissions The Airbyte user must have sufficient permissions to create tables, insert data, and select data in the target database. Make sure you have run the required GRANT statements for the user you configured. For example:
Copy code
CREATE USER airbyte_user IDENTIFIED BY 'your_password';     GRANT CREATE ON * TO airbyte_user;    GRANT CREATE ON {database}.* TO airbyte_user;    GRANT ALTER ON {database}.* TO airbyte_user;    GRANT TRUNCATE ON {database}.* TO airbyte_user;    GRANT INSERT ON {database}.* TO airbyte_user;    GRANT SELECT ON {database}.* TO airbyte_user;    GRANT CREATE DATABASE ON {database}.* TO airbyte_user;    GRANT CREATE TABLE ON {database}.* TO airbyte_user;    GRANT DROP TABLE ON {database}.* TO airbyte_user;
Replace
{database}
with your actual database name. If you use custom namespaces, grant permissions for each namespace as well. [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide) 2. Network Access Ensure that your Airbyte instance can reach the ClickHouse server on the correct port (8123 for HTTP or 8443 for HTTPS). If you’re using Airbyte Cloud, you may need to whitelist Airbyte’s IP addresses in your ClickHouse instance. 3. Hostname Format The hostname in your Airbyte ClickHouse destination configuration must not include a protocol prefix (e.g., use
my-clickhouse-server.com
, not
https://my-clickhouse-server.com
). [ClickHouse Migration Guide](https://docs.airbyte.com/integrations/destinations/clickhouse-migrations) 4. Database Version Make sure your ClickHouse server version meets the minimum requirement (21.8.10.19 or later for the v2 connector). If you’ve checked all the above and the issue persists, double-check that the user credentials are correct and that there are no network/firewall issues blocking the connection. If you need to grant permissions for multiple databases or namespaces, repeat the GRANT statements for each one as needed. If you still encounter issues after verifying these steps, please provide more details about your ClickHouse user permissions and network setup.
s
@kapa.ai, how to check that I don't use custom namespace?
k
A message from kapa.ai
To check if you are using a custom namespace in your Airbyte connection, review the Destination Namespace setting in your connection configuration. In the Airbyte UI, this is typically found under the connection settings when you set up or edit a connection. - If the namespace is set to a specific value you entered (e.g., "my_namespace"), you are using a custom namespace. - If it is set to "Destination-defined," all streams will be replicated to the default namespace defined in the destination settings. - If it is set to "Source-defined," Airbyte will use the namespace provided by the source, if available; otherwise, it defaults to the destination's default namespace. You can find this setting by: 1. Going to your connection in the Airbyte UI. 2. Looking for the Destination Namespace option in the connection settings. For more details on how namespaces work and how to identify the current setting, see the [Namespaces documentation](https://docs.airbyte.com/platform/using-airbyte/core-concepts/namespaces#what-is-a-namespace). If you do not see a custom value and the setting is "Destination-defined," you are not using a custom namespace. If you see a specific name or a dynamic parameter like
${SOURCE_NAMESPACE}
, then you are using a custom namespace.
s
@kapa.ai I have the following grants to the user
Copy code
GRANT SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP TABLE, TRUNCATE ON default.* TO airbyte_user
What do I miss?
k
A message from kapa.ai
Based on the official Airbyte documentation for the ClickHouse destination, the required permissions for the Airbyte user are: -
CREATE
on * and on the specific database -
ALTER
on the database -
TRUNCATE
on the database -
INSERT
on the database -
SELECT
on the database -
CREATE DATABASE
on the database -
CREATE TABLE
on the database -
DROP TABLE
on the database Your current grant statement is:
Copy code
sql GRANT SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP TABLE, TRUNCATE ON default.* TO airbyte_user
What you might be missing: 1. **GRANT CREATE ON \*** The documentation recommends granting
CREATE
on all databases (
*
), not just on
default.*
. This is done with:
Copy code
sql    GRANT CREATE ON * TO airbyte_user;
This allows the user to create databases and tables at the global level, which may be required for some operations. 2. GRANT DROP TABLE vs. GRANT DROP The documentation uses
GRANT DROP TABLE ON {database}.*
, but in some ClickHouse setups, you may also need
GRANT DROP ON {database}.*
for full drop privileges. 3. GRANTS FOR CUSTOM NAMESPACES If you use custom namespaces (other than
default
), you need to repeat the grants for each namespace. If you are not using custom namespaces, this is not required. 4. **GRANT CREATE DATABASE ON {database}.\*** You have
CREATE DATABASE
on
default.*
, but the docs specify
GRANT CREATE DATABASE ON {database}.* TO airbyte_user;
. Double-check if this is required for your ClickHouse version. Summary: Add at least the following grant:
Copy code
sql GRANT CREATE ON * TO airbyte_user;
And ensure you have all the other grants as shown in the [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide). If you are not using custom namespaces, you do not need to repeat the grants for other schemas. If you are unsure, check your Airbyte connection settings for the namespace configuration as described earlier. If you still encounter issues after updating the grants, please provide the exact error message for further troubleshooting. [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide)
s
how to check if GRANT CREATE ON * TO airbyte_user; is granted?
@kapa.ai how to check if GRANT CREATE ON * TO airbyte_user; is granted?
k
A message from kapa.ai
I'm sorry, but the knowledge sources provided do not contain explicit instructions or SQL queries for checking if the specific privilege
GRANT CREATE ON * TO airbyte_user;
has been granted in ClickHouse. The documentation covers how to grant the necessary permissions but does not detail how to verify existing grants for a user in ClickHouse [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide). If you need further assistance, you may need to consult the official ClickHouse documentation or your database administrator for the exact method to check user privileges.
s
@kapa.ai Here is my setup in airbyte cloud: { "name": "ClickHouse", "workspaceId": "78cace4d-4b28-4222-8e93-73cd6532e196", "definitionId": "ce0d828e-1dc4-496c-b122-2da42e637e48", "configuration": { "port": "8443", "database": "INITIAL", "protocol": "https", "username": "airbyte_user", "enable_json": true, "tunnel_method": { "tunnel_method": "NO_TUNNEL" }, "host": "ofut46zul4.germanywestcentral.azure.clickhouse.cloud", "password": "******" } } I've also checked that inserts actually worked from my local client to clickhouse. So there is not issue in grants. What should I do? Anyone experienced that before?
k
A message from kapa.ai
Given your setup in Airbyte Cloud and the fact that you can insert data into ClickHouse from your local client, but Airbyte's connection check fails with
Failed to insert expected rows into check table. Actual written: 0
, here are the most relevant troubleshooting steps based on the knowledge sources: 1. Hostname Format Ensure the
host
field does not include a protocol prefix (e.g.,
https://
). In your configuration, you have:
Copy code
"host": "ofut46zul4.germanywestcentral.azure.clickhouse.cloud"
This is correct, as the documentation specifies the hostname should be without
http://
or
https://
 [ClickHouse Migration Guide](https://docs.airbyte.com/integrations/destinations/clickhouse-migrations). 2. Port and Protocol You are using port
8443
with protocol
https
, which is the standard for ClickHouse Cloud and matches the documentation [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide). 3. User Permissions Even if inserts work from your local client, Airbyte may require additional permissions for operations like creating and dropping tables during the connection check. The documentation recommends the following grants:
Copy code
sql    GRANT CREATE ON * TO airbyte_user;    GRANT CREATE ON INITIAL.* TO airbyte_user;    GRANT ALTER ON INITIAL.* TO airbyte_user;    GRANT TRUNCATE ON INITIAL.* TO airbyte_user;    GRANT INSERT ON INITIAL.* TO airbyte_user;    GRANT SELECT ON INITIAL.* TO airbyte_user;    GRANT CREATE DATABASE ON INITIAL.* TO airbyte_user;    GRANT CREATE TABLE ON INITIAL.* TO airbyte_user;    GRANT DROP TABLE ON INITIAL.* TO airbyte_user;
Double-check that all these permissions are granted, especially
CREATE ON *
, which is sometimes missed [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide). 4. Network Access Since you are using Airbyte Cloud, ensure that ClickHouse Cloud allows inbound connections from Airbyte Cloud's IP addresses. This is a common issue and is required for the connection to succeed [ClickHouse Setup Guide](https://docs.airbyte.com/integrations/destinations/clickhouse#setup-guide). 5. Custom Namespaces If you are not using custom namespaces, you do not need to grant permissions on other databases. You can check your Airbyte connection settings for the namespace configuration as described earlier. 6. ClickHouse Version Ensure your ClickHouse server version is 21.8.10.19 or later, as required by the v2 connector [ClickHouse Destination Docs](https://docs.airbyte.com/integrations/destinations/clickhouse). If all of the above are correct and you still encounter the issue, it may be a connector-specific bug or a subtle configuration issue. In that case, consider opening an issue on the Airbyte GitHub or reaching out on their Slack for more targeted support, as suggested by the maintainers [ClickHouse V2 Beta Discussion](https://github.com/airbytehq/airbyte/discussions/62035). Summary: - Double-check all required permissions, especially
GRANT CREATE ON * TO airbyte_user;
- Ensure network access from Airbyte Cloud to ClickHouse Cloud - Confirm you are not using custom namespaces unless intended - If the problem persists, contact Airbyte support or open a GitHub issue If you need to check for missing permissions, unfortunately, the knowledge sources do not provide a direct query for listing user grants in ClickHouse. You may need to consult ClickHouse documentation or your database admin for that step.