ClickHouse

Integrating ClickHouse with Coalesce Quality

Coalesce Quality has deep ClickHouse support. In fact, our platform is built on ClickHouse. See the talk by our CEO: Building a Unified Data Platform with ClickHouse

This guide will show you how to securely connect Coalesce Quality to your ClickHouse service. We need this information so we collect metadata about your tables.

To be able to finish this guide, you’ll need the following:
→ Access to create users and grant privileges in ClickHouse (on ClickHouse Cloud, the default user)

⏱️ Estimated time to finish: 10 minutes.

Data we collect

We read metadata from your ClickHouse service using the dedicated user described below. Every read goes through system.* tables. We never scan your table data to build the catalog. Depending on which features you enable, we ingest:

• Catalog: databases, tables, views, materialized views, columns, data types, positions, and table/column comments.
• SQL definitions: the CREATE TABLE / CREATE VIEW / CREATE MATERIALIZED VIEW statement per object. Used for lineage and to detect references between objects.
• Keys and indexes: primary keys, sorting keys, partition keys, and data-skipping indexes (minmax, set, bloom filter, and so on), surfaced as table constraints.
• Table metrics: row counts and last-modified timestamps, derived from system.tables and system.parts, for anomaly detection.
• Query logs (opt-in): system.query_log for performance, cost, usage, and lineage signals.
• Users (opt-in): the server’s users with their roles and last login, to tell people from service users in query history.

These map to a small, fixed set of system tables:

What	System table
Databases	system.databases
Tables, views, engines, comments, keys, DDL	system.tables
Columns, types, comments	system.columns
Data-skipping indexes	system.data_skipping_indices
Row counts and last-modified	system.tables, system.parts
Query logs (opt-in)	system.query_log
Users (opt-in)	system.users, system.role_grants, system.session_log

Metadata vs. data access. The grants below give Coalesce Quality access to your schema and DDL, never to row data. Coalesce Quality reads rows only when you grant SELECT on your own databases, which is needed for Custom SQL monitors, data previews, and column profiling. It also reads rows when you run SQL tests against the audit table.

We connect on a schedule (typically every 30 minutes). The metadata queries read system tables only and are negligible against a normal workload.

Requirements

How metadata is read

ClickHouse system tables are per node. On a service with several replicas, a plain read shows only the replica that answered. For this reason Coalesce Quality reads them through clusterAllReplicas(<cluster>, …). The Metadata reads setting on the integration sets the cluster:

• ClickHouse Cloud: nothing to do. Every service exposes a cluster named default, which is the setting’s default.
• Self-managed ClickHouse: list what your server has with SELECT DISTINCT cluster FROM system.clusters and enter that name in the Cluster field.
• A single node with no cluster defined: choose Read the connected node only. System tables are then read directly on that node, and no REMOTE privilege is needed. The Cluster field does not apply.

Choose single-node reads only when the server really is one node. On a service with replicas it reports the metadata of whichever node answered, which looks like a successful but incomplete scrape.

Network access

For the complete list of IP addresses by region, see Coalesce Quality IP Whitelist.

Coalesce Quality connects over ClickHouse’s native protocol. Allow inbound connections from the Coalesce Quality IPs for your region on port 9440, the native TLS port.

On ClickHouse Cloud this is Settings → Security → IP access list for the service. If the list is set to Anywhere no change is needed, but we recommend restricting it to our published addresses.

Connect over TLS. Coalesce Quality reaches your service over the public internet, so credentials and metadata must be encrypted. Use port 9440 with Use SSL on.

Use a plaintext connection (port 9000, Use SSL off) only when it never leaves a private network, such as a VPN or a peered network between your infrastructure and ours. If that is your setup, talk to support before you turn TLS off. Every other connection, including every ClickHouse Cloud service, must use TLS.

Setup ClickHouse access

Create a dedicated user

Run the statements in this section as an admin user. On ClickHouse Cloud, open your service’s SQL console from the left menu. It runs as default, which has the privileges to create users and grant on system.*. Replace <password> with a secret you define for the integration.

CREATE USER coalesce_quality IDENTIFIED WITH sha256_password BY '<password>';

Grant metadata access

-- Required when metadata is read across a cluster, which is the default.
-- Skip it if the integration is set to read the connected node only.
GRANT READ ON REMOTE TO coalesce_quality;

-- Catalog, DDL, keys, indexes, metrics
GRANT SELECT ON system.databases              TO coalesce_quality;
GRANT SELECT ON system.tables                 TO coalesce_quality;
GRANT SELECT ON system.columns                TO coalesce_quality;
GRANT SELECT ON system.data_skipping_indices  TO coalesce_quality;
GRANT SELECT ON system.parts                  TO coalesce_quality;

Grant the whole set, even if a smaller one appears to work today.

Older ClickHouse versions let a user read some system.* tables without an explicit grant, and filter the rows by what the user can see. Later versions require an explicit grant on the table. After an upgrade, which ClickHouse Cloud does automatically, reads that worked before fail with Not enough privileges, one table at a time. Grant the full list from the start to avoid this. See Troubleshooting.

READ ON REMOTE is the grant in ClickHouse 25.7 and later. If your server is older and rejects it, grant REMOTE ON *.* instead. The Not enough privileges error names the one your server expects.

Grant query log access (optional)

Required only if you enable Fetch query logs:

GRANT SELECT ON system.query_log TO coalesce_quality;

Grant access to users (optional)

Required only if you enable Fetch users and service accounts, which shows once query logs are on:

GRANT SELECT ON system.users       TO coalesce_quality;
GRANT SELECT ON system.role_grants TO coalesce_quality;
GRANT SELECT ON system.session_log TO coalesce_quality;

ClickHouse refuses system.users and system.role_grants without these grants, even for the connecting user’s own row. system.session_log gives each user’s last login, and exists only when session logging is configured on the server.

Grant data access (optional)

Required if you want Coalesce Quality to read rows, for Custom SQL monitors, catalog data previews, column profiling, and SQL test execution:

GRANT SELECT ON <database>.* TO coalesce_quality;

If you configure an audit table for SQL tests, the user also needs to create and write it:

GRANT CREATE TABLE, INSERT ON <database>.<audit_table> TO coalesce_quality;

Verify

SHOW GRANTS FOR coalesce_quality;

Input data in the Coalesce Quality UI

Go to Settings → Integrations → Add integration → ClickHouse.

Where to find these values on ClickHouse Cloud

1. Open your service in the ClickHouse Cloud console and click Connect in the left menu.
2. In the dialog, switch the protocol drop-down from HTTPS to Native. The dialog opens on HTTPS and shows port 8443, which this integration cannot use. On Native it shows port 9440.
3. Copy the hostname from the clickhouse client --host … example. It has the form <service-id>.<region>.<csp>.clickhouse.cloud, for example abc123xyz.europe-west4.gcp.clickhouse.cloud. Copy only the hostname, without the --host flag and without an https:// prefix.

The dialog does not give you these two values:

• The user. It shows credentials for the built-in default user. Use the dedicated coalesce_quality user you created above instead, so this integration’s access is scoped and revocable on its own.
• The password. ClickHouse Cloud shows a service password once, at creation. This integration uses the password you set in Create a dedicated user, which you control.

If your service has private endpoints configured, the dialog’s path selector changes the hostname it shows. Pick the path that Coalesce Quality uses to reach you. For an internet-facing service, that is the public one.

Integration name

For example ClickHouse.

Instance name

A unique name for this instance, for example prod or staging. It becomes part of the asset paths for everything this integration ingests, so you cannot change it after you save the integration. To use a different name, create a new integration. The same applies to Host and Port.

Host

The hostname of your service, without a scheme and without a trailing slash, for example abc123xyz.europe-west4.gcp.clickhouse.cloud. On ClickHouse Cloud, take it from the Connect dialog as described above.

Port

Pre-filled with 9440, the native TLS port. Use it for every ClickHouse Cloud service and for any service reachable over the internet. Change it only on a private network, where a plaintext native connection uses 9000 (see Network access).

Username and password

coalesce_quality and the password you set above.

Use SSL

On by default, paired with port 9440. If you turn it off, credentials and metadata travel unencrypted. Do this only on a connection that stays inside a private network. See Network access.

Default database

The database used when a table reference has no database, for example default. It does not restrict which databases are ingested.

Audit table FQN (optional)

Fully qualified name (database.table) of the table where Coalesce Quality should write SQL test audit logs, for example default.quality_sql_test__audit. Coalesce Quality creates the table if it does not exist. Leave empty if you only run metadata monitors and don’t execute custom SQL tests against the warehouse.

Metadata reads

Whether system tables are read across a cluster or on the connected node only. Choosing the cluster read reveals the Cluster field below it. See How metadata is read.

Cluster

The cluster to read through, as named under remote_servers in your ClickHouse configuration. Defaults to default, which every ClickHouse Cloud service provides; SELECT DISTINCT cluster FROM system.clusters lists what a self-managed server has. Only shown when Metadata reads is set to read across a cluster.

Additional parameters (optional)

ClickHouse settings applied to every connection this integration opens, as name/value pairs. For example, max_execution_time 300 gives a large warehouse longer than the 60 seconds Coalesce Quality allows by default.

Values are typed the way ClickHouse types them in a connection string: true and false become 1 and 0, whole numbers become integers, anything else is passed through as text. A name given here replaces the value Coalesce Quality would otherwise use, so this is also how you raise a default that is too low for your warehouse.

Fetch query logs (recommended)

Pulls from system.query_log. Required for proactive anomaly-monitor training, unused-table detection, usage insights, and cost insights. Needs the system.query_log grant above.

Fetch users and service accounts (optional)

Shown once query logs are on. Once a day, lists the server’s users, so query history shows whether each login is a person or a service. Needs the grants in Grant access to users.

Fetch table metrics (required for automatic monitors)

On by default. Every 30 minutes, reads the row count, size and last change time of each table from system.tables and system.parts. Automatic freshness and volume monitors stop working when it is off.

Catalog scope and Metrics scope (optional)

Restrict which databases and tables are ingested into the catalog, and which of those are scanned for metrics. Leave both empty to ingest everything the user can see. system, information_schema, and INFORMATION_SCHEMA are always excluded.

Personal credentials

In addition to the workspace credentials above, you can let each Coalesce Quality member authenticate as themselves against ClickHouse. Queries then run under the member’s own ClickHouse user and respect the grants configured on it. See Data Warehouse Access for the full model and when to enable it.

Troubleshooting

Not enough privileges … it's necessary to have the grant SHOW COLUMNS ON system.columns

The user is missing a grant on one of the system tables. The message names the table it stopped on, but usually more than one grant is missing. Fix them all at once by re-running the full Grant metadata access block, then re-running the same for query logs if that option is enabled.

This error usually appears after a ClickHouse server upgrade on a connection that worked before.

Requested cluster '…' not found. (CLUSTER_DOESNT_EXIST)

Your ClickHouse has no cluster by that name. List what it does have with SELECT DISTINCT cluster FROM system.clusters and set Metadata reads accordingly; if the result is empty, choose Read the connected node only. See How metadata is read.

Not enough privileges … it's necessary to have the grant READ ON REMOTE

Reading across a cluster needs GRANT READ ON REMOTE (GRANT REMOTE ON *.* when the message names REMOTE ON *.*, on an older server). Either grant it, per Grant metadata access, or set Metadata reads to Read the connected node only if the server is a single node.

All connection tries failed … Connection refused

Reported when replicas behind the service are unreachable, for example a service that has scaled down to zero, or is mid-restart. It usually clears on the next catalog refresh. If it persists, check the service is running and that the IP access list allows our addresses.

Connect failed

Check the host has no https:// prefix and no trailing slash, that the port is 9440 with Use SSL enabled, and that the credentials are correct.

If you changed the port, check it is a native-protocol one. 8123 and 8443 are ClickHouse’s HTTP ports, and this integration uses the native protocol, so they cannot connect. Use 9440 with TLS, or 9000 without.

In some cases, permission grants can take a while to propagate. If you keep getting a Connect failed error after double-checking your details, try again after a minute.