ClickHouse Connection

The ClickHouse connection integrates your ClickHouse database with OpsTower, giving agents the ability to run read-only SQL queries against your columnar analytics data. All queries are executed in read-only mode, return results in JSON format, and are capped at 100 rows per query.

Prerequisites

You need a ClickHouse instance that is accessible over HTTP or HTTPS. Both ClickHouse Cloud and self-hosted instances are supported.

Credentials Required

To set up a ClickHouse connection, you need the following:

• URL — the HTTP(S) endpoint of your ClickHouse instance
• Username — a ClickHouse user with read access (defaults to default)
• Password — the password for the ClickHouse user
• Database (optional) — the default database for queries (defaults to default)

How to Find Your Credentials (ClickHouse Cloud)

If you are using ClickHouse Cloud:

1. Log in to clickhouse.cloud and open your service.
2. Click Connect in the top-right corner of the service overview.
3. The connection dialog displays your hostname (the URL), port (8443), and username (default).
4. If you do not have your password, click Reset password to generate a new one.

For self-hosted instances, use the HTTP(S) endpoint and credentials configured by your administrator.

Connection URL Formats

ClickHouse Cloud

https://<service-id>.<region>.clickhouse.cloud:8443

You can find this URL in the Connect dialog of your ClickHouse Cloud service.

Self-Hosted (HTTPS)

https://your-host:8443

Self-Hosted (HTTP)

http://your-host:8123

The URL must be accessible from the internet. If your ClickHouse instance is behind a private network, you will need to configure network access before connecting.

Security Considerations

OpsTower enforces read-only mode at the ClickHouse client level (readonly=1 session setting). All queries are validated before execution to reject write operations. However, it is strongly recommended to create a dedicated read-only user for OpsTower.

Creating a Read-Only User

CREATE USER opstower IDENTIFIED BY 'your-secure-password'
  SETTINGS readonly = 1;

GRANT SELECT ON your_database.* TO opstower;

This ensures that even if OpsTower’s query validation were bypassed, the database user itself cannot perform mutations.

Setting Up the Connection in OpsTower

1. In OpsTower, navigate to Connections in the sidebar.
2. Click Add Connection and select ClickHouse.
3. Enter your URL, Username, and Password.
4. Optionally specify a Database name (leave blank to use the default database).
5. Click Test Connection to verify your credentials.
6. Click Save to create the connection.

What Agents Can Do with ClickHouse

Once connected and enabled on an agent, the agent can:

• Execute read-only SQL queries against your ClickHouse database
• List all tables and their column schemas using system table introspection
• Use ClickHouse-specific SQL features including toStartOfHour(), uniq(), LIMIT N BY, and other columnar query patterns

ClickHouse is a columnar database optimized for analytics workloads. Agents are guided to write efficient queries that take advantage of ClickHouse’s strengths — for example, selecting only needed columns rather than using SELECT *, and leveraging time-bucketing functions for time-series analysis.

Troubleshooting

• Connection refused: Verify that the URL and port are correct. ClickHouse Cloud uses port 8443 (HTTPS). Self-hosted instances typically use 8443 (HTTPS) or 8123 (HTTP).
• Authentication failed: Double-check the username and password. Ensure the user exists and has the correct permissions on the target database.
• Database not found: If you specified a database name, verify that it exists on the ClickHouse instance. You can leave the database field blank to use the default database.
• Timeout errors: Ensure your ClickHouse instance is reachable from the internet. For ClickHouse Cloud, check that your service is running and not paused.
• Permission denied on tables: Verify that the ClickHouse user has SELECT privileges on the tables you want to query. Use SHOW GRANTS FOR your_user to check.