Query Logs Like a Database: SQL-Style Log Search in Practice

Why query logs with SQL

When an incident hits, most people reach for grep and a chain of pipes: grep error, grep -v away the noise, awk out a field, then sort | uniq -c to count. That workflow is fine for tens of megabytes of logs, but it falls apart at terabytes per hour across dozens of nodes: grep scans one machine at a time and gets slow, the pipeline grows longer with every investigation, and you rebuild it from scratch next time.

炬鲸 turns log search into a SQL-like query language. You get three things in return: a stable query syntax (no more wrestling with regex escaping), aggregation that runs over the full dataset rather than a sample, and queries you can save and reuse across the team.

The basics: filter and project

The most common statement is SELECT plus WHERE. Fields come from the structured fields extracted during log parsing, such as level, service, host, message, and any custom fields you define.

SELECT timestamp, host, message
FROM logs
WHERE level = 'ERROR'
  AND service = 'order-service'
  AND timestamp >= now() - 1h
ORDER BY timestamp DESC
LIMIT 100

Field comparisons support =, !=, >, <, IN and LIKE. For substring matching use LIKE '%timeout%' or message CONTAINS 'connection refused'. IN is handy for scoping several services at once: service IN ('order-service', 'pay-service').

One trap worth knowing: field names must match the parsed names. If the raw log says loglevel but the parser maps it to level, querying loglevel returns an empty result rather than an error. Check a sample log first to confirm your field names.

Aggregation and grouping: turning noise into signal

grep tells you how many lines matched; aggregation tells you where the problem is concentrated. This query shows the error count per service over the last hour:

SELECT service, count(*) AS err_count
FROM logs
WHERE level = 'ERROR'
  AND timestamp >= now() - 1h
GROUP BY service
ORDER BY err_count DESC

Drill one level deeper to see which hosts and versions a given error is clustered on:

SELECT host, version, count(*) AS cnt
FROM logs
WHERE service = 'order-service'
  AND level = 'ERROR'
GROUP BY host, version
ORDER BY cnt DESC
LIMIT 20

Besides count, the engine supports sum, avg, min, max, p50, p99 and uniq (approximate distinct). p99 and uniq are especially useful for performance work — for example, SELECT p99(duration) FROM logs WHERE service = 'api-gateway' shows tail latency directly.

Time buckets and trend charts

Bucketing by time is what turns "is there a problem right now" into "when did the problem start":

SELECT time_bucket(timestamp, '5m') AS t, count(*) AS cnt
FROM logs
WHERE service = 'order-service' AND level = 'ERROR'
GROUP BY t
ORDER BY t

time_bucket accepts s, m, h and d units. Plot the result as a line and you can see at a glance when errors started to spike, then line that up against your deployment history to find the likely trigger.

Patterns worth saving

A few queries pay for themselves the moment you save them: the error-spike query above (share it with on-call), slow requests via SELECT p99(duration), count(*) FROM logs WHERE service = 'api-gateway' GROUP BY route ORDER BY p99 DESC, and auth failures via WHERE message CONTAINS 'invalid credentials' GROUP BY host, which usually surfaces a credential rotation that went wrong on a single node. Save them with descriptive names and a new on-call engineer can start triaging without learning the syntax at all.

Performance tips

• Always bound the time range in WHERE to avoid a full scan.
• Put the most selective condition (like service =) first in high-frequency queries.
• Use LIMIT rather than SELECT when the result set is large — SELECT pulls back whole raw log lines and is slow and memory-hungry.
• Save queries you reuse and share them with the team instead of reinventing the pipeline each time.

The point of SQL-style search isn't learning yet another syntax. It's turning a debugging ritual of throwaway pipelines into an asset you can persist, reuse and collaborate on.