# ClickHouse

Monitor ClickHouse 25.8 or later that you run yourself, on virtual machines or in containers.  

For a cluster run by the Altinity Kubernetes Operator, use
[ClickHouse on Kubernetes](/docs/connect/altinity-clickhouse/).  For NetApp Instaclustr managed
ClickHouse, use [Instaclustr ClickHouse](/docs/connect/instaclustr-clickhouse/).

## Before you start

- Check the version on any server.  The collector supports 25.8 and later.

  ```sql
  SELECT version();
  ```

- Ensure that you have the query log enabled.  ClickHouse typically has it enabled by default.

- Ensure that you have DBGorilla collector 0.17.0 or later

- Ensure that the collector can reach the clickhouse server on port 8123, or 8443 with TLS. 

- Ensure the collector can reach `otlp.dbgorilla.com` and `auth.dbgorilla.com` on port 443.  See
  [What the collector is](/docs/connect/#what-the-collector-is) in Choose your setup.

- Install Docker on the machine that will run the collector.

- Add a collector in the DBGorilla web admin interface and record the values provided to include in your collector config file.

## Prepare the cluster

Create the user as an account that may manage users.

```sql
CREATE USER dbg_collector IDENTIFIED WITH sha256_password BY '<strong password>';
GRANT SHOW ON *.* TO dbg_collector;
GRANT SELECT ON system.* TO dbg_collector;
```

ClickHouse stores users on each server.  On a cluster, run all three statements on every server.
If the cluster has ClickHouse Keeper or ZooKeeper, you can run them once with `ON CLUSTER`:

```sql
CREATE USER dbg_collector ON CLUSTER '<cluster>' IDENTIFIED WITH sha256_password BY '<strong password>';
GRANT ON CLUSTER '<cluster>' SHOW ON *.* TO dbg_collector;
GRANT ON CLUSTER '<cluster>' SELECT ON system.* TO dbg_collector;
```


| Grant | What it adds | Without it |
|---|---|---|
| `SHOW ON *.*` | The topology: the names and definitions of databases, tables, columns and dictionaries. | Only the cluster, its servers and their settings arrive. |
| `SELECT ON system.*` | Server metrics, the workload from the query log, and the queries that are running now. | The cluster does not appear.  The collector log says `Not enough privileges` and names the grant it needs on `system.clusters`. |

## Install

Write `collector.toml`.  This is everything a cluster that serves plain HTTP on port 8123 needs:

```toml
[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = "${DBG_SERVER_SECRET}"

[[component]]
name   = "events-cluster"
engine = "clickhouse"

[component.provider]
type = "self_hosted"

[component.auth]
method   = "password"
user     = "dbg_collector"
password = { env = "CLICKHOUSE_COLLECTOR_PASSWORD" }

[component.connect]
host = "ch-1.internal.example.com"
```

`host` is any one server of the cluster.  Give the address of a single server, such as one pod's
DNS name, and not a load balancer in front of several.

Run the collector from the directory that contains both files:

```sh
docker run -d --name dbg-collector --restart unless-stopped \
  -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
  --env-file secrets.env \
  dbgorillapublic.azurecr.io/dbg-collector:0.17.0 \
  --config-file /etc/dbg-collector/collector.toml
```

## Additional configuration

### TLS

If the HTTP interface uses TLS, set the HTTPS port and an `ssl_mode`.  The default port is 8123 and
the default `ssl_mode` is `disable`.

```toml
[component.connect]
host     = "ch-1.internal.example.com"
port     = 8443
ssl_mode = "verify_full"
ca_cert  = "/etc/dbgorilla/clickhouse-ca.pem"
```


| `ssl_mode` | What it checks |
|---|---|
| `disable` | Nothing.  The collector uses plain HTTP, which a TLS port refuses. |
| `require` | Encrypts, but does not check the certificate. |
| `verify_full` | The certificate chain, and that the certificate names the host the collector connects to. |


The collector connects to each server at the host name in the cluster definition.  With
`verify_full`, every server's certificate needs that name.

`ca_cert` is the CA that signed the server certificates, as a path inside the container.  Leave it
out when a public CA signed them.  For a private CA, mount the file at that path:

```sh
docker run -d --name dbg-collector --restart unless-stopped \
  -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
  -v "$PWD/clickhouse-ca.pem:/etc/dbgorilla/clickhouse-ca.pem:ro" \
  --env-file secrets.env \
  dbgorillapublic.azurecr.io/dbg-collector:0.17.0 \
  --config-file /etc/dbg-collector/collector.toml
```

### Cluster Selection

By default the collector monitors every cluster that the server in `host` belongs to.  You may specify a single cluster directly.

```toml
[component.connect]
host    = "ch-1.internal.example.com"
cluster = "main"
```

### Choose databases

By default the collector monitors every non-system database.  `databases` monitors only
the ones listed.  `exclude_databases` leaves databases out of the topology, metrics and workload,
and wins over `databases`.

```toml
[component.connect]
host              = "ch-1.internal.example.com"
databases         = ["shop", "billing"]
exclude_databases = ["scratch"]
```

### Commands

RPC Commands allow DBGorilla agents to run commands on your database.  By default commands are disabled.

```toml
[commands]
enabled = true
allowed = ["explain", "execute_query"]
```

| Command | Agents can run? | What it does |
|---|----|---|
| `explain` | Yes | Returns the plan estimate of one `SELECT` without query execution. |
| `execute_query` | No | Runs one read statement and returns at most 1,000 rows.  It accepts `SELECT`, `WITH`, `SHOW`, `DESCRIBE`, `EXPLAIN` and `EXISTS`. |

ClickHouse checks the user's grants for both commands, so grant `SELECT` on each database they may
reach:

```sql
GRANT SELECT ON shop.* TO dbg_collector;
```

### Command answer size

A command answer larger than 1 MiB fails with a message that says so.  To change the limit, set it
in bytes:

```toml
[engine.clickhouse.commands]
max_response_bytes = 4194304
```


### Host metrics

For CPU, memory and disk metrics, run Prometheus `node_exporter` on each server on its default port
9100.  The collector finds it there with no change to `collector.toml`.  See
[node_exporter](/docs/connect/self-managed/#node_exporter) in Self-managed server.

## Check it is working

Read the collector log:

```sh
docker logs dbg-collector 2>&1 | grep -E 'opamp handshake complete|discovered component|snapshot serialized|WARN|ERROR'
```

A healthy start has these three messages:

```text
opamp handshake complete
discovered component
topology snapshot serialized for OTLP post
```

The first `discovered component` line carries `"engine":"clickhouse"` and a `"nodes"` count.  The
count is the number of servers in the cluster.  One `topology scrape failed; retrying next tick`
warning can appear as the collector starts.  It sends the topology again on the next pass.

Then open Home in DBGorilla.  The cluster appears under the name you gave the component, marked
**Monitored**, as ClickHouse with its version.  Its databases, tables, views, materialized views
and dictionaries arrive first, and server metrics within a minute.  The workload arrives within a
few minutes of the cluster taking queries.  Each workload entry is one statement, with its call
count and time.

If nothing arrives, the log names the cause:

| Log line | Fix |
|---|---|
| `is not supported: 25.8 or later is required` | Upgrade ClickHouse to 25.8 or later. |
| `unsupported combination (engine=clickhouse, provider=self_hosted, auth=password)` | The collector is older than 0.17.0.  Use 0.17.0 or later. |
| `Authentication failed` | Check the user name and `CLICKHOUSE_COLLECTOR_PASSWORD`, and that the user exists on every server. |
| `Not enough privileges` | Run the grants in [Prepare the cluster](#prepare-the-cluster) on every server. |
| `unknown ssl_mode` | Use `disable`, `require` or `verify_full`. |
| `open ca_cert` | The path in `ca_cert` does not exist in the container.  Mount the certificate. |
| `invalid peer certificate: UnknownIssuer` | Set `ca_cert` to the CA that signed the server certificates. |
| `Connection reset by peer` | The port uses TLS.  Set `ssl_mode`. |


## Upgrade

Change the tag in the `docker run` command to the new release, remove the container with
`docker rm -f dbg-collector`, and run the command again.  The collector reconnects under the same
agent ID.

## Remove

Remove the container with `docker rm -f dbg-collector`.  To delete the cluster from Home, follow
[Removing the database from DBGorilla](/docs/administer/manage-collectors/#removing-the-database-from-dbgorilla)
in Manage collectors.  Then drop the user:

```sql
DROP USER dbg_collector;
```

## Next

- [Topology](/docs/using-dbgorilla/topology/) shows what the collector found.
- [Organization Management](/docs/administer/members-and-roles/) lets you bring in your team.