Querying with SQL¶

Use these recipes with POST /api/v1/sql or siglake sql. The DataFusion engine supports standard SQL, including joins, common table expressions (CTEs), window functions, and subqueries.

Choose a fast path for a dashboard panel¶

For an auto-refreshing panel, use a metadata shape when it answers the question you need. Use a plain aggregation for any other shape. The plain aggregation remains correct, but it can read data files on every refresh and its cost grows with the selected data.

The query server recognizes these common dashboard shapes:

• count(*) over the whole table
• count(*) over a bounded, pure timestamp range
• GROUP BY one tag or one typed long, double, or bool column, with count(*) as its only aggregate
• date_bin or date_trunc over timestamp, with count(*) as its only aggregate

A group count or date histogram can have no predicate or a pure timestamp range. A dimensional predicate, join, second group column, or second aggregate makes it a plain aggregation. A raw attribute also falls through until you promote it to a typed column. A typed double is valid as a grouping column. A count filtered by a floating-point literal is not eligible. Dimensional count filters accept string, integer, or Boolean equality and IN tests, plus integer ranges.

Windowed counts and histograms use time-bucket metadata for their interior. They may read the files that cross the two window boundaries. A windowed group count reports tier1_windowed_agg when its time-by-group aggregate is usable.

Use the records format while testing the panel so the response includes execution statistics:

curl -s localhost:8089/api/v1/sql -H 'Content-Type: application/json' \
  -d '{"query":"SELECT host, count(*) FROM events GROUP BY host"}' \
  | jq '.stats | {served_by, rows_scanned}'

For a group count, a stats.served_by value that starts with tier1_ confirms that table metadata served it. materialized is an exact per-file fallback. It can sum footers or decode raw pages, so it is not proof of a full scan and can still do substantial I/O. scan means the full DataFusion plan ran. If the result is materialized, follow the aggregate repair procedure.

Newline-delimited JSON (NDJSON) is not a universal fast-path exclusion. A local query can serve counts, group counts, and histograms in NDJSON. The distributed coordinator runs its fast-path battery for records responses, including group counts, before it fans out. If an aggregate reaches shard execution, the shard does not run this battery. Test with records format because it preserves the coordinator-local path and exposes stats.served_by.

Cost a query before running it:

siglake sql --dry-run "SELECT * FROM events WHERE raw LIKE '%error%'"

Browsing¶

Latest events¶

SELECT timestamp, host, sourcetype, raw
FROM events
ORDER BY timestamp DESC
LIMIT 100

This query stops early without sorting. Check that stats.scan.ordering == "advertised".

The ORDER BY is optional here. A bare SELECT over events gets the same newest-first ordering from the server, and the same early stop. Write the ORDER BY out anyway if the query also runs on the batch tier or another engine, neither of which rewrites it. Send default_order: false to get file order back. See Implicit newest-first ordering.

Deep pagination¶

To paginate deeply through ordered results, carry a cursor rather than an OFFSET. With OFFSET the query cost grows with the offset, because the engine produces and discards every row it skips. A cursor on the columns the table is sorted by keeps each page the same cost, however far in you are.

timestamp is microseconds; timestamp_ns is the exact nanosecond and the second sort column (see events). A timestamp < cursor therefore skips every row that shares the previous page's microsecond. Page on the pair the table is physically sorted by:

-- first page
SELECT timestamp, timestamp_ns, host, raw
FROM events
WHERE timestamp >= TIMESTAMP '2026-01-01 00:00:00'
  AND timestamp <  TIMESTAMP '2026-01-02 00:00:00'
ORDER BY timestamp DESC, timestamp_ns DESC
LIMIT 500

Keep timestamp and timestamp_ns from the page's last row, and send both back as the cursor:

-- next page: same window, plus the cursor
SELECT timestamp, timestamp_ns, host, raw
FROM events
WHERE timestamp >= TIMESTAMP '2026-01-01 00:00:00'
  AND timestamp <  TIMESTAMP '2026-01-02 00:00:00'
  AND timestamp <= TIMESTAMP '2026-01-01 00:00:00.000001'  -- cursor timestamp
  AND timestamp_ns < 1767225600000001500                   -- cursor timestamp_ns
ORDER BY timestamp DESC, timestamp_ns DESC
LIMIT 500

The two cursor conjuncts do different jobs, and the asymmetry is the whole point:

• timestamp <= is inclusive and prunes data. It is the day(timestamp) partition column and the first sort column, so it drops whole partitions, and files by their manifest bounds. Making it strict is the bug.
• timestamp_ns < is strict and advances the cursor. It is a total order that agrees with (timestamp, timestamp_ns) on every row, so it cannot skip a same-microsecond sibling.

ORDER BY has to name the same pair. ORDER BY timestamp DESC alone leaves the order within a microsecond unspecified, and then no cursor can be correct.

Expect stats.scan.ordering to read filtered rather than advertised on cursor pages: only timestamp predicates keep the ordered early-stop, so a page carrying a timestamp_ns conjunct is a bounded top-n over the surviving files. Keep the window's lower bound to limit that work.

Still prefer this over OFFSET, which produces and discards every row it skips.

Successive pages, measured¶

Measured 2026-09-07 on a local fixture written by Siglake ba31866: 2,000 rows one nanosecond apart across exactly two microseconds (2026-01-01T00:00:00.000000Z and .000001Z), so 1,000 rows share each microsecond and every page boundary but the last falls inside a tie.

siglake --data-dir ./fixture iceberg-demo --n 2000 --reset
siglake --data-dir ./fixture sql-direct --query "SELECT timestamp, timestamp_ns FROM events …"

With LIMIT 500, the pair cursor walks the table exactly once:

The four pages return all 2,000 rows exactly once. Page 3 shows why the two cursor columns differ. Its timestamp cursor is still .000001 while every row it returns is at .000000. The inclusive bound prunes; the nanosecond does the walking.

The timestamp-only cursor on the same fixture loses half the table:

The timestamp-only cursor misses 1,000 of the 2,000 rows. Page 1 is not even the newest 500. Without a tiebreak, the engine returns an arbitrary 500 rows sharing that microsecond.

Identical nanosecond timestamps¶

timestamp_ns is the OTLP time_unix_nano value verbatim. Nothing makes it unique, and the table has no row identity to fall back on, so rows really can tie on the full pair. The following commands ingest three such rows from one file:

for r in a b c; do
  printf '{"timestamp":"2026-01-02T00:00:00.000000500Z","host":"h","source":"s",'
  printf '"sourcetype":"t","index":"main","raw":"%s"}\n' "$r"
done > ties.ndjson

siglake --data-dir ./ties ingest --input ties.ndjson
siglake --data-dir ./ties query --sql "SELECT timestamp_ns, raw FROM events ORDER BY timestamp DESC, timestamp_ns DESC LIMIT 2"

All three rows read back at timestamp_ns = 1767312000000000500.

Page 1 returns two of the three (b and a, in an arbitrary order). Then, with the cursor at 1767312000000000500:

• timestamp_ns < (strict) returns nothing, so row c is lost.
• timestamp_ns <= (inclusive) returns b and a again, so the walk makes no progress and loops forever.

So this recipe is not exactly-once, and no cursor over these columns can be. It is exact only while no single nanosecond holds more rows than one page. LIMIT 500 is usually enough for OpenTelemetry traffic, but this is a precondition rather than a guarantee. When you need every row exactly once, do not page at all: stream a closed window or submit it as a batch job (see Large results).

Pages cross snapshots¶

Successive pages are separate requests, and separate requests are not pinned to one snapshot. Replicas serve table metadata from their own caches and can disagree for up to 60 seconds across a commit (Query replicas can briefly disagree). Mid-walk, a newer snapshot can add rows inside your window, and retention or a delete sweep can remove rows you have not reached yet. Two mitigations, both cheap:

• Page a closed window that ends in the past, as above. Newly ingested events land above it, not inside it.
• Send the whole walk to one pod, so the sequence sees one metadata cache.

Neither makes the walk transactional. Each page is consistent with itself because one fan-out is pinned to the coordinator's whole generation. See One generation per fan-out.

A time window¶

SELECT timestamp, host, raw
FROM events
WHERE timestamp BETWEEN now() - INTERVAL '4 hours' AND now()
ORDER BY timestamp DESC
LIMIT 200

Searching¶

Substring¶

SELECT timestamp, raw FROM events
WHERE raw LIKE '%connection refused%'
ORDER BY timestamp DESC LIMIT 50

File-level trigram blooms and row-group token blooms prune files that cannot contain the substring. Check scan.files_pruned_bloom.

Term search¶

-- all terms present
WHERE match_terms(raw, 'timeout upstream')

-- any term
WHERE match_any(raw, 'timeout refused reset')

-- adjacent, in order
WHERE match_phrase(raw, 'connection refused')

-- prefix
WHERE match_prefix(raw, 'conn')

match(...) is an alias for match_terms(...).

These engage inverted-index pruning on columns that have index blobs; on other columns they fall back to row evaluation. Footer inverted indexes are on by default. See Storage.

Combining search with filters¶

SELECT timestamp, host, raw
FROM events
WHERE sourcetype = 'nginx:error'
  AND timestamp >= now() - INTERVAL '1 hour'
  AND match_terms(raw, 'upstream timeout')
ORDER BY timestamp DESC
LIMIT 100

The planner can reorder these predicates. The tag and time predicates make the bloom check cheaper.

Aggregating¶

Whole-table group-by (metadata fast-path candidate)¶

SELECT host, count(*) AS n
FROM events
GROUP BY host
ORDER BY n DESC
LIMIT 20

Date histogram (metadata fast-path candidate)¶

SELECT date_trunc('minute', timestamp) AS bucket, count(*) AS n
FROM events
WHERE timestamp >= now() - INTERVAL '2 hours'
GROUP BY bucket
ORDER BY bucket

Arbitrary windows use core-plus-boundary decomposition: whole buckets from rollups, only the partial edges computed.

The qualifying shape is date_bin or date_trunc over timestamp with count(*), grouped by the bucket, either unfiltered or with a WHERE that is a pure time range. A predicate on any other column, such as host = 'web-01', sends the query back to the planner, as does a stride with a month component. A LIMIT is honored only alongside ORDER BY bucket. Confirm the execution path in stats.served_by, as in Choose a fast path for a dashboard panel.

Distinct counts¶

SELECT count(DISTINCT host) FROM events

Exact, and eligible for a metadata fast path. Confirm the execution path in stats.served_by.

Counts with predicates¶

SELECT count(*) FROM events WHERE status = 404;
SELECT count(*) FROM events WHERE status BETWEEN 500 AND 599;
SELECT count(*) FROM events WHERE status != 200;

These are eligible for a metadata fast path when status is a typed column (promoted or declared). Integer literals render to footer entries, and comparisons normalize to inclusive ranges summed over them. Confirm that a tier1_* path served the result rather than assuming rows_scanned: 0 proves that no I/O occurred.

Aggregate by attribute¶

SELECT attr_get(attributes, 'k8s.namespace') AS ns, count(*) AS n
FROM events
GROUP BY ns
ORDER BY n DESC

Eligible for a metadata fast path once the attribute is promoted to a typed column. The query text does not change when you promote it. Confirm the path in stats.served_by.

Working with attributes¶

attributes is a JSON object string. attr_get returns text, so everything CASTs cleanly:

-- filter on a numeric attribute
SELECT timestamp, raw FROM events
WHERE CAST(attr_get(attributes, 'http.status_code') AS INT) >= 500
ORDER BY timestamp DESC LIMIT 100;

-- multiple attributes
SELECT attr_get(attributes, 'k8s.namespace') AS ns,
       attr_get(attributes, 'k8s.pod')       AS pod,
       count(*) AS n
FROM events
WHERE timestamp >= now() - INTERVAL '1 hour'
GROUP BY ns, pod
ORDER BY n DESC LIMIT 50;

-- presence
WHERE attr_get(attributes, 'exception.type') IS NOT NULL

-- substring match across the whole blob (cheap pre-filter)
WHERE attributes LIKE '%"deployment.environment":"prod"%'

Extract fields from unstructured text¶

Pull name=value pairs out of raw:

SELECT kv_extract(raw, 'status') AS status, count(*) AS n
FROM events
WHERE sourcetype = 'nginx:access'
GROUP BY status
ORDER BY n DESC

Handles both bare and quoted values: status=500 → "500", path="/api/v1/foo" → /api/v1/foo. No match returns NULL.

This function evaluates each row and does not prune files. Filter first.

Query audit¶

Each retained query_audit row records who ran the query, its text, the endpoint, duration, status, and the planner's complexity and scan estimates (see query_audit). It does not record the serving path, so confirm a fast path from stats.served_by on the response rather than from this table. After the fact, duration_ms and estimated_bytes_scanned are the closest proxies.

-- expensive shapes
SELECT query, count(*) AS runs, avg(duration_ms) AS avg_ms
FROM query_audit
WHERE timestamp >= now() - INTERVAL '24 hours'
  AND complexity IN ('large','huge')
GROUP BY query ORDER BY avg_ms DESC LIMIT 20;

-- who is running what
SELECT subject, count(*) AS queries, avg(duration_ms) AS avg_ms
FROM query_audit
WHERE timestamp >= now() - INTERVAL '24 hours'
GROUP BY subject ORDER BY queries DESC;

Large results¶

A large result set is a streaming or batch problem. Stream it as NDJSON to export the rows now, or submit it as a batch job when the query can outlive your shell. To walk a table in order instead, run the (timestamp, timestamp_ns) cursor rather than deep OFFSET pagination, which produces and discards rows.

The search v1 bounds do not apply here: they limit index pruning, not result size. What you can hit without meaning to is the row cap, limits.max_rows_returned clamped by the server ceiling, which returns 413 on a records response.

Stream it as NDJSON¶

set -o pipefail
curl -fsSN localhost:8089/api/v1/sql \
  -H 'Content-Type: application/json' \
  -d '{"query":"SELECT * FROM events WHERE …","format":"ndjson"}' \
| awk '/^\{"_meta"/ { trailer = $0; next } { print }
       END { if (trailer != "") { print "stream cut: " trailer > "/dev/stderr"; exit 1 } }'

A request the server refuses answers with an error body, not with rows. -f makes curl print nothing and exit 22 on any 4xx or 5xx, so a 401, a 422, a 429 or a 503 never reaches awk as exported rows.

A 200 is weaker than it looks: it arrives before the rows do, so a short stream can look successful. The awk guard passes rows through a line at a time and diverts a _meta last line to stderr with a non-zero exit; pipefail does the same for a curl that dies mid-stream, which produces a short stream with no trailer at all. A complete stream has no trailer. See NDJSON trailers.

Submit it as a batch job¶

Batch jobs run on the dedicated runtime. Four statuses decide the whole script: submission answers 202 with the job id, or 429 with Retry-After and no job when the shared admission budget stays full; the result route answers 404 while the job is pending or running and 409 when it ended in failed, cancelled, or timeout. Any job route answers 503 with Retry-After when the job store is temporarily unavailable. That response says nothing about the job, so wait and ask again. Read the submission status before touching .job_id, and poll /api/v1/jobs/{id} until the status is succeeded before fetching rows. See Batch jobs.

SQL="SELECT host, sourcetype, count(*) AS n
     FROM events
     WHERE timestamp >= now() - INTERVAL '30 days'
     GROUP BY host, sourcetype
     ORDER BY n DESC"

BODY=$(mktemp) HDR=$(mktemp) RESULT=$(mktemp)
trap 'rm -f "$BODY" "$HDR" "$RESULT"' EXIT
CODE=$(curl -s -o "$BODY" -D "$HDR" -w '%{http_code}' \
  localhost:8089/api/v1/sql -H 'Content-Type: application/json' \
  -d "$(jq -n --arg q "$SQL" '{query: $q, priority: "batch"}')")

case "$CODE" in
  202) ID=$(jq -er '.job_id | select(. != "")' "$BODY") \
         || { echo "202 without a job id"; exit 1; } ;;
  429) RETRY=$(tr -d '\r' < "$HDR" | sed -n 's/^[Rr]etry-[Aa]fter: *//p')
       echo "admission budget full, no job created; retry after ${RETRY:-5}s"
       exit 75 ;;
    *) echo "submit failed: HTTP $CODE"; cat "$BODY"; exit 1 ;;
esac

# Poll to a terminal state. A batch job can outlive your shell.
DEADLINE=$(( $(date +%s) + 900 ))
STATE=not-polled
cancel_job() {
  echo "job $ID still $STATE after 15m; cancelling"
  CANCEL_CODE=$(curl -sS --connect-timeout 5 --max-time 10 -o "$BODY" \
    -w '%{http_code}' -X DELETE localhost:8089/api/v1/jobs/"$ID")
  CANCEL_STATUS=$?
  if [ "$CANCEL_STATUS" -ne 0 ]; then
    echo "job $ID: cancellation could not be confirmed (curl exit $CANCEL_STATUS)" >&2
  elif [ "$CANCEL_CODE" != 202 ]; then
    echo "job $ID: cancellation could not be confirmed (HTTP $CANCEL_CODE)" >&2
  fi
  exit 1
}

while :; do
  REMAINING=$(( DEADLINE - $(date +%s) ))
  [ "$REMAINING" -gt 0 ] || cancel_job
  CONNECT_TIMEOUT=5
  [ "$CONNECT_TIMEOUT" -le "$REMAINING" ] || CONNECT_TIMEOUT=$REMAINING
  CODE=$(curl -sS --connect-timeout "$CONNECT_TIMEOUT" --max-time "$REMAINING" \
    -o "$BODY" -D "$HDR" -w '%{http_code}' \
    localhost:8089/api/v1/jobs/"$ID")
  CURL_STATUS=$?
  WAIT=5
  if [ "$CURL_STATUS" -ne 0 ]; then
    echo "job $ID: status request failed (curl exit $CURL_STATUS)" >&2
    STATE=poll-unavailable
  elif [ "$CODE" = 503 ]; then
    # The job store could not answer. The job itself is unaffected.
    STATE=store-unavailable
    WAIT=$(tr -d '\r' < "$HDR" | sed -n 's/^[Rr]etry-[Aa]fter: *//p')
  else
    STATE=$(jq -r '.status // "unknown"' "$BODY")
  fi
  case "$STATE" in
    succeeded) break ;;
    failed|cancelled|timeout)
      echo "job $ID ended $STATE: $(jq -r '.error // "no detail"' "$BODY")"
      exit 1 ;;
    pending|running|store-unavailable|poll-unavailable) ;;
    *) echo "job $ID: unreadable status ($STATE, HTTP $CODE)"; exit 1 ;;
  esac
  REMAINING=$(( DEADLINE - $(date +%s) ))
  [ "$REMAINING" -gt 0 ] || cancel_job
  case "$WAIT" in ''|*[!0-9]*) WAIT=5 ;; esac
  [ "$WAIT" -le "$REMAINING" ] || WAIT=$REMAINING
  sleep "$WAIT"
done

# Fetch into its own file: a failed fetch must not leave the poll's last
# status body behind for jq to read as a result.
CODE=$(curl -sS --retry 3 -o "$RESULT" -w '%{http_code}' \
  localhost:8089/api/v1/jobs/"$ID"/result) \
  || { echo "job $ID: result fetch failed (curl exit $?)" >&2; exit 1; }

case "$CODE" in
  200) ;;
  503) echo "job $ID: job store still unavailable after the retries; the job" \
            "kept its result, so fetch it again" >&2
       exit 75 ;;
    *) echo "job $ID: result fetch answered HTTP $CODE" >&2
       cat "$RESULT" >&2
       exit 1 ;;
esac

jq -e '.rows | type == "array"' "$RESULT" > /dev/null \
  || { echo "job $ID: 200 without a rows array" >&2; exit 1; }
if jq -e '.truncated == true' "$RESULT" > /dev/null; then
  echo "job $ID hit the $(jq -r '.max_rows' "$RESULT")-row cap; narrow or page the query" >&2
  exit 1
fi
jq '.rows' "$RESULT"

Dropping the exit 75 branch is the common mistake: jq -r .job_id on a 429 body yields the string null, and the script then polls /jobs/null until the 404s look like a broken server rather than a refused submission. Likewise, do not extract .rows before checking truncated: a batch result that hit the per-request row cap still returns 200, with max_rows naming the cap, and the rows are only a prefix. And a 503 from a job route is not a verdict on the job. The store could not answer, so the poll waits for its Retry-After and asks again. curl --retry does the same for the result fetch.

Those retries can run out, so the result fetch is checked the way the submission is. curl --retry gives up after the third 503 and hands back the last response, and a transport failure returns no response at all; neither is an empty answer from a job that succeeded. So the fetch reads its status before jq runs, writes to its own file rather than the one the poll left behind, and requires a rows array. .rows on a 503 body, an error page, or a stale status body would otherwise print null and read like a job that returned nothing. An empty rows array is a real answer and prints as []; the exhausted-retry branch exits 75 like the refused submission, because the result is still there to fetch.

running does not prove that the query is still running, which is why each poll and sleep uses the time left before DEADLINE. A failed transfer is not a status response, even if curl received a body before its timeout. When a job-store outage swallows a finished run's terminal write, its executor keeps retrying that write every five seconds and the row reads running until one lands, however long the outage lasts. What eventually appears is an ordinary failed or timeout, so the loop above needs no new branch. Read the .error before blaming the query. A lost success reads this job finished but its result could not be stored (the job store was unavailable); the result was discarded, so resubmit the query: the rows were computed and thrown away, and resubmitting is the whole remedy. See Batch jobs for the other reconciled texts.

The deadline DELETE has its own 5-second connection timeout and 10-second request timeout. It is terminal when it answers 202. Any other response or transfer failure means the script cannot confirm cancellation. Check the job again before assuming that it stopped. After a 202, the job reads cancelled everywhere, and a completion written after it is refused. The replica actually running the query frees its admission share and stops its scans on its own poll, within --jobs-cancel-poll-secs (default 2 seconds). See cancelling a batch job for the cross-replica rules.

Things to avoid¶