From ELK to SQL-Style Log Search: One Query Across Clusters

The Problem: Three Backends, Three Dialects

A typical log team runs three systems at once: Elasticsearch for real-time search, ClickHouse for cold-data analytics, and object storage for long-term retention. Each has its own query language — Lucene query_string, ClickHouse's SQL dialect, and grep or Presto over objects. Troubleshooting one incident means switching between three consoles and writing the same condition three times.

OBSERVE unifies all three behind one SQL-style syntax. You write status >= 500 AND host = 'api-gw-01' in the search box, and the engine translates it into the right backend query depending on where the data lives. The results look the same regardless of layer.

Basic Syntax

# Field filter
level = 'ERROR' AND service = 'order'

# Range and IN
elapsed > 2000 AND host IN ('api-01','api-02')

# Wildcard and regex
path LIKE '/api/order/%'
message REGEXP 'OutOfMemory|OOM'

# Sort and paginate
ORDER BY timestamp DESC LIMIT 100

Field names come from the JSON keys of structured logs. If your app still writes plain text, add a parse rule on the collection side to extract timestamp, level, host, service, and path first — search quality improves immediately.

Cross-Cluster Aggregation

Raw queries show you rows; aggregations show you bottlenecks. This counts 5xx by host:

SELECT host, COUNT(*) AS cnt
FROM logs
WHERE status >= 500 AND time >= now() - 1h
GROUP BY host
ORDER BY cnt DESC

Nested aggregation works too. To find the slowest endpoints:

SELECT path,
       COUNT(*) AS calls,
       AVG(elapsed) AS avg_ms,
       P95(elapsed) AS p95_ms
FROM logs
WHERE service = 'gateway' AND time >= now() - 30m
GROUP BY path
HAVING p95_ms > 1000
ORDER BY p95_ms DESC

The engine pushes P95 down to the backend — quantile(0.95) on ClickHouse, percentiles aggregation on Elasticsearch — instead of pulling all rows to the client and computing locally.

Migration Costs Across Backends

The real cost of switching backends is syntax compatibility, not data movement. Use this checklist:

1. Field types. Elasticsearch dynamic mapping turns numbers into long; ClickHouse needs an explicit schema. Align numeric fields like elapsed and status before migrating, or aggregations will throw type errors.
2. Tokenization. ES's default analyzer and ClickHouse's tokenbf index behave differently. Re-verify recall for LIKE '%xxx%' patterns.
3. Time sharding. Daily indexes/partitions are the common convention, but keep the timezone UTC everywhere or cross-day queries will silently drop data.
4. Retention. Configure hot-warm-cold TTL (hot ES → warm ClickHouse → cold S3) at the routing layer; don't delete data twice in the storage layer.

Practical Tips

• Build query templates for your highest-frequency investigations — login 5xx, error logs per service, top-10 slow endpoints. On-call engineers click one saved query instead of writing from scratch.
• Always bound your search with a time range. An unbounded full scan is the number one cause of a hammered cluster.
• Use HAVING for post-aggregation filtering instead of client-side filtering, keeping the compute server-side.

Once the syntax is unified, on-call engineers stop juggling three dialects, and typical triage time drops by more than half.