ClickHouse

Prerequisites

Before you connect Semaphor to ClickHouse, ensure the following requirements are met.

Database Permissions

The user must have read access to the necessary tables.
The user must be able to query information_schema table to retrieve metadata.

Required Permissions

To fetch metadata (such as column names and types), your database user must have the following privilege:

GRANT SELECT(column_name, data_type, table_catalog, table_name) ON information_schema.columns

Without this, you may see an ACCESS_DENIED error when trying to run queries or introspect schemas.

Connecting to ClickHouse

To connect Semaphor to your ClickHouse instance, use a connection string in the following format.

Connection String Format

Connection String

clickhousedb://username:password@host:port/database

username – Your database username.
password – The corresponding password.
host – The hostname or IP address of your ClickHouse instance.
port - Your database port. If not specified, the default port (8443) will be used.
database - Your target database. This is necessary since Clickhouse connector doesn't support multi-line SQL statements. Ex. use mydatabase; select * from table.

Example Connection String

If your ClickHouse user is default, your password is mypassword, and your host is instance.us-east-1.aws.clickhouse.cloud, your connection string will be:

Connection String

clickhousedb://default:mypassword@instance.us-east-1.aws.clickhouse.cloud

Network Access

If your database is private, whitelist the Semaphor IP: 44.199.53.142 to allow connections.
Make sure your firewall rules and security groups allow inbound connections from Semaphor.

See what Semaphor runs in your query log

Every card, filter, control and export query Semaphor sends to ClickHouse is labeled with the project, dashboard and card it ran for. You can see which projects, dashboards and cards drive your warehouse load, straight from ClickHouse's own system.query_log, with no lookup on Semaphor's side. Metadata reads (listing schemas, tables and columns) and connection tests are not labeled.

The label is the query's query_id: a small JSON object with these keys.

Key	What it holds
`run`	One id per query run. A card's main query, row count and totals share it.
`stmt`	Which statement of the run: `main`, `row_count` or `totals`.
`proj`, `proj_name`	The project's id and name.
`dash`, `dash_name`	The dashboard's id and name.
`sheet`, `sheet_name`	The sheet's id and name.
`card`, `card_name`	The card's id and title.
`op`	The id of the action that ran the query.
`trig`	What started it, such as `initial_load` or `input_change`.

A key appears only when Semaphor knows its value, and names are cut to 100 characters. The label never holds filter values or SQL, and Semaphor never changes the SQL it runs. The SQL that actually ran is already in query_log, in the query column.

Semaphor sets query_id as a request setting, so labeling works with read-only ClickHouse users. You don't need to grant any new privilege to the user Semaphor connects with.

Example: the cards that read the most data this week

Run this as a user who can read system.query_log, such as an admin:

Warehouse load by project, dashboard and card

SELECT
  JSONExtractString(query_id, 'proj_name') AS project,
  JSONExtractString(query_id, 'dash') AS dashboard_id,
  JSONExtractString(query_id, 'card') AS card_id,
  JSONExtractString(query_id, 'card_name') AS card,
  count() AS queries,
  sum(read_rows) AS rows_read,
  formatReadableSize(sum(read_bytes)) AS data_read,
  round(avg(query_duration_ms)) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 7
  AND isValidJSON(query_id)
  AND JSONHas(query_id, 'run')
GROUP BY project, dashboard_id, card_id, card
ORDER BY sum(read_bytes) DESC
LIMIT 20;

To see failed queries instead, filter on type = 'ExceptionWhileProcessing'. On a cluster, or on ClickHouse Cloud, every replica keeps its own log, so read it with clusterAllReplicas('default', system.query_log).

Troubleshooting

If you encounter issues connecting to Clickhouse, review the following:

Invalid Credentials: Verify your username and password.
Network Restrictions: Confirm that your server accepts connections from Semaphor’s IP address.
Incorrect Connection String: Double-check that your connection string matches the required format.
Database Permissions: Make sure your database user has permission to query the necessary tables and metadata.

If you continue to experience issues, contact Semaphor Support for assistance.

Prerequisites

Connecting to ClickHouse

Connection String Format

See what Semaphor runs in your query log

Troubleshooting

On this page