SQL reference

OBSESC exposes your full-fidelity raw logs as a single SQL table, raw_events, backed by the Parquet files in your own S3 bucket. You can query it from Explore in the console, over the query API, or with your own tools.

You can run read-only SELECT queries with WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, subqueries and the usual aggregate and scalar functions.

The raw_events table

Column	Type	Notes
timestamp_ns	BIGINT	Event time, nanoseconds since the Unix epoch (UTC).
source	TINYINT UNSIGNED	The ingest protocol that delivered the event (numeric code).
service	VARCHAR	Logical service name, for example OTLP service.name.
body	BINARY	The log body exactly as the shipper sent it.
idempotency_key	FIXED_SIZE_BINARY(16)	Stable per-event key used for de-duplication.
attributes	MAP(VARCHAR, BINARY)	Every structured field on the event.
host	VARCHAR, nullable	Canonical host attribute.
env	VARCHAR, nullable	Canonical env attribute.
namespace	VARCHAR, nullable	Canonical namespace attribute.
tenant	VARCHAR, nullable	Canonical tenant attribute.

The four dimension columns (host, env, namespace, tenant) can be NULL on older rows.

In results, body and attribute values are shown as readable text rather than hex.

Reading attributes

Index the attributes map by key:

SELECT timestamp_ns, body
FROM raw_events
WHERE service = 'checkout'
  AND attributes['level'] = 'error'
LIMIT 100;

Attribute values are stored as bytes so nothing is lost. Comparing a value to a string literal works directly. To treat a value as a number, cast it to text first and then to the numeric type:

SELECT timestamp_ns
FROM raw_events
WHERE service = 'checkout'
  AND CAST(CAST(attributes['latency'] AS VARCHAR) AS DOUBLE) > 100;

Writing fast queries

Queries run fastest when they:

• bound the time range on timestamp_ns,
• name a service (or a short list with IN),
• use exact matches, such as attributes['k'] = 'v' or the host, env, namespace and tenant columns.

Always set a time range. A query with no time condition covers all the data you hold.

-- Errors for one service in a one-hour period
SELECT timestamp_ns, host, body
FROM raw_events
WHERE service = 'payments'
  AND env = 'prod'
  AND timestamp_ns >= 1767225600000000000
  AND timestamp_ns <  1767229200000000000
  AND attributes['level'] = 'error'
ORDER BY timestamp_ns DESC
LIMIT 50;

Large-query confirmation

Before a SQL statement runs, OBSESC works out a cautious upper limit on how much raw data it could read. The actual amount is often much less, especially with a LIMIT.

If that upper limit is over your limit, the statement is refused and nothing is read. You are shown how much the statement could read, and can choose to run it anyway.

• By default, the limit is 100 GiB.
• You can set a different limit for your own queries: in Explore, use Query Settings; over the API, use max_scan_bytes (and optionally max_scan_seconds) in the request.

Contact us if you need to change the default limit for your deployment.

Running SQL over the API

The query API listens on port 18080, on the host shown in the QueryApiEndpoint output of your CloudFormation stack. When role-based access is turned on, send an API token with every request (see IAM and permissions).

export OBSESC_API=http://obsesc.your-domain.internal:18080
export OBSESC_TOKEN=...   # your API token

# Run, with a 20 GiB limit for this request
curl -s "$OBSESC_API/v1/sql" \
  -H "Authorization: Bearer $OBSESC_TOKEN" \
  -H 'content-type: application/json' \
  -d '{"sql": "SELECT service, count(*) AS n FROM raw_events WHERE timestamp_ns >= 1767225600000000000 GROUP BY service ORDER BY n DESC LIMIT 20",
       "max_scan_bytes": 21474836480}'

A successful run returns:

• rows: an array of result rows.
• consumption: what the run actually read, including bytes_read, rows_returned, elapsed_ms and complete.

To download the results instead, add ?format=ndjson or ?format=csv to the URL. The response body then contains only the rows. NDJSON represents every result shape, so prefer it for results that include attributes.

Status	Meaning
200	Rows returned.
400	The SQL did not parse or plan, a function argument was invalid, or format is unknown.
412	Refused because the query could read more than your limit. Nothing was read. Send it again with "confirm": true to run it anyway.
503	SQL is not available on this node. Contact us.

Reading the data from other engines

The raw data is standard Parquet in your own bucket, so you can also point your own query tools at it. See Storage and formats.