Skip to content

Query Analysis

Click-Dog adds query context to exported spans. It groups similar statements, enriches slow-query traces with query-log data, and includes Datadog dashboards.

What you get

Capability What it does
Normalized query families Group similar statements by ClickHouse's normalized_query_hash.
Slow-query traces Query root spans carrying clickhouse.query_id are enriched with system.query_log context and log_comment metadata. Internal child spans without a query ID pass through unchanged.
Datadog dashboards Includes Application Query Analysis and Exported User Activity dashboards.
Per-sink export health Know exactly which backend is healthy and which is dropping spans.

Local analysis report (analyze queries)

click-dog analyze queries builds a bounded, read-only analysis report directly from system.query_log and system.opentelemetry_span_log: query-family resource outliers, log_comment attribution gaps, user / client / host skew, and coverage prerequisites. It opens a single read-only ClickHouse connection and nothing is written to ClickHouse. The command still loads config through the normal loader, so exporter fields are expanded and *_file secret paths are read at config-load time, but no exporter clients are constructed and no exporter connections are opened.

# Human-readable table for the last hour (default window)
./click-dog analyze queries --config click-dog.yaml

# Widen the window and emit JSON for tooling
./click-dog analyze queries --config click-dog.yaml --lookback 24h --format json --output report.json

# Redact user/client/host dimension values in the report
./click-dog analyze queries --config click-dog.yaml --redact-dimensions

# Capture an explicit known-good window (report is still emitted)
./click-dog analyze queries --config click-dog.yaml --lookback 24h \
  --save-baseline /var/lib/click-dog/query-baseline.json

# Compare a later, non-overlapping window without changing the baseline
./click-dog analyze queries --config click-dog.yaml --lookback 1h \
  --baseline /var/lib/click-dog/query-baseline.json

# Preserve JSON output for CI and fail its policy step on warning or critical findings
./click-dog analyze queries --config click-dog.yaml --format json \
  --output report.json --fail-on warning

# After writing the report, notify every enabled analysis-findings destination
./click-dog analyze queries --config click-dog.yaml --baseline baseline.json \
  --format json --output report.json --notify --fail-on critical
Flag Default Purpose
--lookback 1h Analysis window ending at command start (e.g. 30m, 24h)
--timeout 2m Command-level timeout for the run
--format table table or json
--output stdout Write the report to a path instead of stdout
--redact-dimensions off Redact user/client/host dimension values
--min-executions 3 Minimum executions for exact query-family groups
--family-limit 200 Exact normalized groups fetched before rollup
--span-sample-limit 0 Maximum spans sampled for attribution and coverage (0 = auto)
--query-preview-length 500 Maximum normalized-query preview length in the report
--save-baseline empty Atomically save this completed window as a private analysis.baseline.v1 artifact
--baseline empty Compare against an existing baseline without modifying it
--fail-on none Finding policy: none, critical, or warning; a reached threshold exits 3 after report output and successful notifications
--notify off Synchronously notify every enabled analysis_findings webhook and Datadog Events destination after report output
--config auto Configuration path. When omitted, tries /etc/click-dog/click-dog.yaml, then ./click-dog.yaml

Reports are operational artifacts

JSON reports contain normalized SQL and dimension values that can reveal schema and ownership details. Treat them like logs and traces. --output writes mode 0600 on every run — a path that already exists is brought back to 0600 rather than keeping a looser mode, and a symlink at that path is replaced instead of written through.

Known-good baselines and regressions

Baseline capture is explicit: Click-Dog never promotes or rotates a baseline automatically, and --baseline cannot be combined with --save-baseline. A bad deployment therefore cannot silently become the new normal. The baseline is a local operational artifact written atomically with mode 0600; Click-Dog does not write it to ClickHouse. --output must name a different file from either baseline flag so report output cannot replace the known-good artifact.

analysis.baseline.v1 stores metrics by exact decimal normalized_query_hash, so a hash can still compare when its higher-level rollup family changes between windows. It includes execution counts, p95/p99 duration, successful and failed counts, failure rate, and at most five top exception code/count pairs. It intentionally omits raw and normalized query text, user/client/host dimensions, config paths, endpoints, and credentials. Configured user filters are represented by a one-way fingerprint only.

A comparison reports matched, current-only, and baseline-only hashes and families. New or disappeared hashes show coverage changes, not regressions. The baseline must use the same family algorithm, similarity threshold, filter fingerprint, --min-executions, --family-limit, and --query-preview-length, and its window must end before the current window starts. Valid-but-incompatible baselines are reported as incompatible; baselines more than 30 days behind the current window are stale. Both states skip regression findings rather than making a misleading claim. Missing, malformed, oversized, or tampered requested files are operational errors (exit 1).

Compatible comparisons add two deterministic analyzers:

Analyzer Conservative trigger
latency_regression At least 20 matched executions in each window, at least 80% current execution coverage, and matched p95 or p99 latency is both at least 2x baseline and at least 250ms higher.
failure_spike The same volume/coverage gate, at least three current failures with exception evidence, and failure rate rises by at least 5 percentage points and 3x. A zero-failure baseline instead requires a current rate of at least 10%.

Failure spikes become critical at a current failure rate of 15% or a 10-percentage-point increase; otherwise they are warning. Findings continue to exit 0 under the default --fail-on none; CI can select an explicit threshold. Use the exact hash in each recommendation with click-dog analyze trace --normalized-query-hash <hash>.

Finding policy and notifications

Policy and delivery are opt-in post-processing of the same completed report. Click-Dog always renders and writes the report before it attempts a notification or returns a finding-policy failure. --fail-on considers only findings that the compiled-in analyzer registry accepted into report.findings; rejected or failed analyzer output cannot fail the policy gate.

Exit Meaning Precedence
0 Report completed; configured notifications, if requested, succeeded; finding policy passed or was none Success
1 Operational failure, including config/data/report I/O, missing notification destinations, destination construction, timeout, or any requested delivery failure Overrides a finding-policy result
2 Invalid CLI usage or flag value Evaluated before analysis
3 The completed report reached the selected finding threshold Returned only after report output and successful requested notifications

--fail-on critical fails only when at least one included finding is critical. --fail-on warning fails on warning or critical. --fail-on none preserves the original exit behavior. Policy status and notification outcomes go to stderr, so JSON on stdout remains valid.

--notify is an execution switch, not a background service. It evaluates the shared bounded analysis.notification.v1 summary and attempts each configured destination sequentially before exit:

  • Critical findings are always eligible.
  • Warning findings are eligible only when a usable explicit baseline comparison proves them new. A current-only exact hash proves an ordinary finding new; an emitted baseline-regression finding is new for that comparison.
  • Informational findings are never eligible. Without a usable baseline, new counts are zero and only critical findings are eligible.
  • An enabled destination with no eligible findings is reported as skipped and receives no HTTP request. Requesting --notify with no enabled destination is an operational error. A failure at one destination does not prevent attempts to the others, but any failure returns exit 1.

The webhook destination is enabled when webhook.enabled is true and its event filter includes analysis_findings (an empty filter includes every event). The separate datadog_events.enabled destination posts one structured alert event to Datadog Events API v2. See Configuration and Datadog setup.

Stable, private notification identity

Every included condition has a deterministic analysis.condition.v1 key based on analyzer, stable finding scope, family identity, and the complete sorted set of exact normalized-query hashes. Analysis window, severity, mutable prose, and evidence do not affect that key. A changed family or exact-hash subject changes the key, making it suitable for grouping repeated notifications without making the window-specific finding ID into a false long-term identity.

Both destinations consume the same privacy-reviewed summary. It includes only the window, report/notification schema versions, total and new severity counts, highest eligible severity, condition keys, analyzer names, family IDs, and exact decimal normalized-query hashes. It excludes SQL/query previews, user/client/host dimensions, finding prose and evidence, local paths, endpoints, configuration, and credentials. Payloads include at most 20 conditions and 10 hashes per condition: the named MaxNotificationFindingIdentities cap is 20 and MaxNotificationHashesPerCondition is 10, with explicit truncation counts. Keep the local report for full evidence and use a listed hash with analyze trace for drilldown.

Trace drilldown (analyze trace)

click-dog analyze trace is the incident-response inverse of analyze queries: start from a running query, recent query-log row, query ID, trace ID, or normalized query hash, then fetch the native ClickHouse trace spans that carry the query and fan out to query-log stats, the matching query-family rollup, and any findings for that family.

# Guided flow
./click-dog analyze trace --config click-dog.yaml --wizard

# Search recent finished/failed queries
./click-dog analyze trace --config click-dog.yaml --source recent --match events

# Drill explicit identities
./click-dog analyze trace --config click-dog.yaml --query-id abc123
./click-dog analyze trace --config click-dog.yaml --trace-id <trace-id>
./click-dog analyze trace --config click-dog.yaml --normalized-query-hash 123456789

Like analyze queries, the trace drilldown is local and read-only: no exporters, metrics or health servers, leader election, circuit breaker, or adaptive polling are started. Missing trace spans, unsupported rollups, or no matching findings produce warnings or empty sections rather than a command failure.

--source current searches system.processes, which the monitoring user's two standard grants do not cover: add GRANT SELECT ON system.processes TO click_dog_monitor (on every node in cluster mode) or the candidate search fails with Not enough privileges. The drilldown honors clickhouse.use_cluster_queries: with it on, the query log, span log, and system.processes are all read through cluster(), so a reader connected to one node drills traces and running queries from every shard.

AI-ready JSON

The JSON report is the supported boundary for AI-assisted workflows. It is deterministic, schema-versioned, and built from the same bounded read-only analysis path as the table output. Prefer giving assistants this report over granting direct ClickHouse access.

./click-dog analyze queries \
  --config click-dog.yaml \
  --lookback 24h \
  --format json \
  --redact-dimensions \
  --output report.json

Use --redact-dimensions before sharing reports with external services. The report can still reveal normalized SQL and schema shape, so review it with the same care you would apply to logs or traces.

Machine-readable references:

Click-Dog does not run LLM calls in the export path, and the launch surface does not include MCP. AI narration or recommendation providers should consume these JSON artifacts downstream.

Datadog dashboards

Click-Dog ships three Datadog dashboards:

Dashboard What it shows Selector
Application Query Analysis Exact query families, latency, resource use, failures, and application attribution from exported live query spans. --dashboard query
Exported User Activity Searchable user, database, table, operation, and access-type relationships from enriched exported live query spans. --dashboard activity
Click-Dog: Health Click-Dog's own export, enrichment, backoff, and circuit-breaker metrics. --dashboard health

Provision either query-focused dashboard on its own:

click-dog create-dashboards --dashboard query
click-dog create-dashboards --dashboard activity

Omitting --dashboard uses the default all selection and provisions all three dashboards; click-dog create-dashboards --dashboard all is the explicit form.

The two query views are bounded to qualified, exported root-query spans from scheduled mode. Their counts reflect monitor.min_trace_duration_ms and any configured query, operation, IP, or user filters, not total ClickHouse traffic and not a compliance or audit log. Use a dedicated, retention-controlled system.query_log pipeline when you need a complete activity record.

If operation/access-type tiles or activity relationship tables are empty, check the Datadog self-metric click_dog.query_operation_supported. A value of 1 means every required system.query_log.query_kind capability probe passed and those dimensions are enabled. A value of 0 means the capability was disabled at startup (including a failed probe, no discovered replicas, or mixed-version cluster). Other query-log attributes remain on eligible enriched spans, but dashboard widgets that filter or group by operation/access type can be empty, including the user-to-database and user-to-table relationship tables.

The separate Health dashboard reads Click-Dog's own metrics. See Datadog Self-Monitoring for the OTLP self-metrics setup and legacy OpenMetrics fallback.

How families are derived

ClickHouse assigns each statement a normalized_query_hash; Click-Dog rolls those hashes up into query families and attributes sampled spans back to them. The normalized query family and its attributes are documented in the Span Attributes reference. Filtering controls which spans get exported in the first place. See Filtering.