# Clickhouse

> Query and execute ClickHouse SQL through a JSON CLI. Use when the user explicitly asks to inspect ClickHouse or run a confirmed statement such as CREATE, ALTER, INSERT, DELETE, DROP, RENAME, or OPTIMIZE. Access is limited only by the configured ClickHouse user grants.

- Skill: `bgevorkian/clickhouse` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add bgevorkian/clickhouse`
- Raw SKILL.md: https://api.skillmd.com/api/skills/bgevorkian/clickhouse/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: bgevorkian (https://skillmd.com/u/bgevorkian)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/bgevorkian/clickhouse

---


# ClickHouse

Generic ClickHouse JSON CLI. Read commands use `readonly=1`; `exec` runs one statement allowed by the configured ClickHouse user.

## Local `.env`

The CLI automatically looks for a private `.env` beside this `SKILL.md`. Copy `.env.example` to `.env`; never commit the real file.

```env
CLICKHOUSE_HOST=clickhouse.example.net
CLICKHOUSE_PORT=443
CLICKHOUSE_SECURE=true
CLICKHOUSE_VERIFY=true
CLICKHOUSE_USER=pass://<vault>/<item>/<field>
CLICKHOUSE_PASSWORD=pass://<vault>/<item>/<field>
```

When the file exists, the CLI re-runs itself once through:

```bash
pass-cli run --env-file .env -- <skill-command>
```

The `.env` may contain ordinary non-secret connection settings and Proton Pass references. It is private, ignored by Git, and must never contain resolved secrets in a repository. The CLI does not print or copy its values.

Without a local `.env`, use these environment variables or non-secret flags:

| Variable | Flag | Default |
|---|---|---|
| `CLICKHOUSE_HOST` | `--host` | none |
| `CLICKHOUSE_PORT` | `--port` | client default |
| `CLICKHOUSE_USER` | `--user` | `default` |
| `CLICKHOUSE_PASSWORD` | none | empty |
| `CLICKHOUSE_DATABASE` | `--database` | server default |
| `CLICKHOUSE_SECURE` | `--secure` / `--no-secure` | `true` |
| `CLICKHOUSE_VERIFY` | `--verify` / `--no-verify` | `true` |

Never pass a password as a CLI argument.

## Read

```bash
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py query --sql "SELECT count() AS n FROM analytics.events"
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py query --sql @query.sql --params '{"day":"2026-01-01"}'
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py list-databases
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py list-tables --database analytics
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py describe-table --database analytics --table events
```

Read commands allow only obvious read-only SQL and send ClickHouse `readonly=1`, timeout, and result-row limits.

## Execute

Use `exec` after the user approves the exact operation and target:

```bash
uv run --python 3.13 --with clickhouse-connect python scripts/ch.py \
  exec --sql "CREATE TABLE analytics.example (id UInt64) ENGINE=MergeTree ORDER BY id"
```

`exec` accepts one DDL, DML, or administrative statement. It rejects empty and multi-statement input. There is no local write authorization gate: ClickHouse user grants are the only authorization boundary.

`--sql` accepts literal SQL, `@file.sql`, or `-` for stdin. Named ClickHouse parameters use `{name:Type}` with a JSON object from `--params`.

## Safety contract

- Obtain fresh user approval before each mutation scope. CREATE, backfill, swap, and DROP are separate operations.
- Read commands keep local SQL guards and server `readonly=1`.
- `exec` does not add `readonly=1`; the configured ClickHouse user decides what is allowed.
- `exec` rejects empty and multi-statement SQL.
- Do not print secrets, connection strings, `.env` contents, or environment variables.
- Ask before touching sensitive data, even in read mode.

## Output

Read success:

```json
{
  "rows": [{"n": 42}],
  "row_count": 1
}
```

Execution success:

```json
{
  "ok": true
}
```

## Tests

```bash
uv run --python 3.13 --with clickhouse-connect python tests/test_ch.py
```