Query Logs Like a Database: SQL-Powered Log Search in JJHub

Every operations team eventually hits the same wall: logs are cheap to store but painful to query. You know the error is somewhere in the last hour, but finding it means clicking through pages or exporting gigabytes into a spreadsheet. JJHub's SQL log search exists to kill that workflow.

Why SQL for logs

The hardest part of a log platform isn't storage—it's querying. Keyword search only finds "that one line". The moment you need to answer something like "which five endpoints had the highest error rate in the last 24 hours" or "which pods suffer the worst GC pauses", you end up paging through results or exporting data to process it yourself. JJHub treats logs as a queryable table and supports standard SQL: SELECT for projection, WHERE for filtering, GROUP BY for aggregation, and JOIN for correlation. One statement replaces half an hour of manual paging.

There are two reasons SQL beats a custom query DSL here. First, ops and developers already know it, so the learning curve is close to zero and the team can agree on a shared investigation language instead of each person writing their own ad-hoc scripts. Second, SQL is composable: a query that answers one question can be wrapped in a subquery or a window function to answer the next one, which is much harder with a search box or a homegrown filter syntax. If your team already writes SQL for the analytics warehouse, the log console feels like the same tool, which cuts the mental switch when jumping from a dashboard down to raw logs.

Basic queries: filter and project

Fields parsed at ingest time (service name, level, duration, trace_id, pod, and so on) are just column names in SQL. To find ERROR logs for a service over the last hour:

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

You can alias columns in Chinese to hand statements to colleagues who don't write SQL:

SELECT level AS 级别, count(*) AS 条数
FROM logs
WHERE timestamp > now() - INTERVAL 1 HOUR
GROUP BY level;

Nested fields (for example request.user_id inside a JSON payload) are reached with dot notation, exactly like a regular column—no need to parse first and query later. If you're coming from Elasticsearch-style query strings, this is the single biggest ergonomic win: one consistent syntax for flat and nested data alike.

Aggregation and correlation

Aggregation is where SQL log search really pays off. To find which endpoint is dragging the whole service down:

SELECT api_path,
       avg(duration_ms) AS avg_ms,
       p99(duration_ms) AS p99_ms,
       count(*)        AS cnt
FROM logs
WHERE level = 'INFO'
  AND duration_ms IS NOT NULL
  AND timestamp > now() - INTERVAL 1 HOUR
GROUP BY api_path
ORDER BY p99_ms DESC
LIMIT 20;

Quantile aggregates like p99 and p95 are built in—no need to implement them yourself. Correlation is just as easy: use trace_id to string together every log line from one slow request and walk the call chain hop by hop, from gateway to database, to see exactly where it stalled. You can even JOIN error logs onto an alert table by trace_id, so every alert arrives with its call chain attached and the on-call engineer sees full context the moment the page fires. Bucketing errors by hour (GROUP BY date_trunc('hour', timestamp)) gives you a fast "did this start at deploy time?" answer before you dig into any single request.

Performance and indexing tips

To keep SQL log queries fast, build two habits: always bound the time range, since a full scan is the biggest source of slow queries; and index your high-frequency filter fields. JJHub partitions by time automatically on ingest and pre-builds indexes for service, level, and trace_id on the query side, so the statements above still return in seconds across billions of log lines. For heavy aggregation, prefer async jobs whose results land in scheduled reports—don't let them compete with real-time search during an incident. As a rule of thumb, any query you run more than twice a week should become a saved, shared query rather than a re-typed one. Finally, save your recurring statements as shared queries so the whole team reuses them instead of retyping (and mis-typing) them every time.