CloudWatch Omni SQL query syntax
This page describes how to compose a CloudWatch Omni SQL query and the clauses and commands you can use in it. For information about tables, system fields, quoting, column types, and time filtering, see SQL query reference. For information about query limits, see Explore and query your telemetry.
Example data
The examples on this page assume that your telemetry contains records like the following.
Log example (logs.default)
{ "request_id": "abc-123", "path": "/api/auth/login", "status_code": 500, "latency_ms": 182.4, "user_id": "u-1042", "traceId": "4bf92f3577b34da6a3ce929d0e0e4736" }
Trace example (traces.default)
{ "traceId": "5b8efff798038103d269b633813fc60c", "spanId": "eee19b7ec3c1b174", "name": "invoke_agent weather_agent", "kind": "INTERNAL", "startTimeUnixNano": 1792519316000000000, "endTimeUnixNano": 1792519318450000000, "resource": { "attributes": { "service.name": "weather-agent" } }, "attributes": { "gen_ai.operation.name": "invoke_agent", "gen_ai.agent.name": "weather_agent" } }
Composing a query
A CloudWatch Omni query is a single SQL statement with an optional trailing semicolon. CloudWatch Omni rejects a request that contains more than one statement.
When you run a query with the StartTelemetryQuery API, bound @timestamp with both a start and an end. In Explore, the time range you select fills in a missing bound.
To add a comment, use -- for the rest of a line or /* ... */ for a block. The hash character (#) does not start a comment.
The following example uses both comment styles.
/* Most recent login requests */ SELECT `@timestamp`, request_id, status_code FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() -- time bound AND path = '/api/auth/login' ORDER BY `@timestamp` DESC LIMIT 50
Clauses and commands
The following table describes each clause and command. Every clause and command on this page works on all three CloudWatch Omni tables: logs.default, traces.default, and default.
| Clause or command | Description |
|---|---|
WITH |
Defines a common table expression (CTE), a named subquery that the main query can read from. |
TABLESAMPLE |
Follows a table name and scans a portion of the table's data, selected by percentage of the time range (PERCENT) or by data volume (MAX_VOLUME). |
JOIN |
Combines rows from two tables, or from one table with itself. Supports [INNER] JOIN, LEFT JOIN, RIGHT JOIN, and FULL [OUTER] JOIN on a matching condition, and CROSS JOIN, which pairs every row on one side with every row on the other. Bound @timestamp on each side. |
WHERE |
Keeps only the rows that match a condition. Supports comparison operators, AND, OR, NOT, IN, BETWEEN, LIKE, ILIKE (case-insensitive LIKE), IS NULL, and the ~ regular expression match. |
IN (subquery) |
Keeps rows whose value appears in the result of a subquery. |
EXISTS (subquery) |
Keeps rows for which a subquery returns at least one row. Use NOT EXISTS to keep rows for which it returns none. |
| Scalar subquery | Uses the single value that a subquery returns in an expression, such as latency_ms > (SELECT avg(latency_ms) FROM ...). |
GROUP BY |
Groups rows that share values so you can aggregate them. Accepts expressions or column positions such as GROUP BY 1. |
Aggregate functions with FILTER |
Computes one value over a group of rows. Add FILTER (WHERE ...) after any aggregate function to include only some rows. |
HAVING |
Keeps only the groups that match a condition on an aggregate. |
| Window functions | Computes a value for each row from a set of related rows, called a window. Define the window with OVER (PARTITION BY ... ORDER BY ...) and, optionally, a frame, which limits the window to a range of rows around the current row. |
QUALIFY |
Keeps only the rows that match a condition on a window function. |
ORDER BY |
Sorts results with ASC or DESC. Use NULLS FIRST or NULLS LAST to choose where null values go. |
LIMIT and OFFSET |
Caps the number of rows returned and, with OFFSET, skips rows first. |
UNION, UNION ALL, INTERSECT, EXCEPT |
Combines the results of two queries. UNION ALL keeps duplicate rows. The others remove them. |
CASE |
Returns the value for the first condition that is true. |
CAST, TRY_CAST, :: |
Converts a value to another type. TRY_CAST returns null instead of failing the query when a value cannot be converted. |
EXPLAIN (ANALYZE_FIELDS) |
Returns a description of each field in the rows the query selects, with its types and sample values, instead of the rows themselves. Not billed. |
EXPLAIN (ESTIMATE [QUICK|ACCURATE]) |
Returns an estimate of the bytes the query would scan, in an estimated_bytes column, instead of running the query. QUICK, the default, returns an estimate sooner. ACCURATE checks more detailed statistics and returns an estimate closer to the actual bytes scanned, but takes longer. Not billed. |
| Other operators and functions | CloudWatch Omni also supports string, regular expression, math, conditional, date and time, aggregate, window, array and struct, JSON, IP address, and hash and encoding functions. See Operators and functions and Hash and encoding functions. |
Example – Count failing traces with WITH
WITH errors AS ( SELECT traceId FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() AND status_code >= 500 ) SELECT COUNT(DISTINCT traceId) AS failing_traces FROM errors
Example – Sample the most recent 5 GB of the last 30 days with TABLESAMPLE
SELECT `@timestamp`, request_id, status_code FROM "logs.default" TABLESAMPLE (5G MAX_VOLUME, BIAS LATEST) WHERE `@timestamp` BETWEEN NOW() - INTERVAL '30' DAY AND NOW() AND status_code >= 500 LIMIT 1000
Example – Count errors and distinct users per path with FILTER and HAVING
SELECT path, COUNT(*) AS requests, COUNT(*) FILTER (WHERE status_code >= 500) AS errors, COUNT(DISTINCT user_id) AS users FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() GROUP BY path HAVING COUNT(*) > 100 ORDER BY errors DESC
Example – Compare log and trace record counts with UNION ALL
SELECT 'logs' AS signal, COUNT(*) AS records FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() UNION ALL SELECT 'traces' AS signal, COUNT(*) AS records FROM "traces.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW()
Example – Find paths seen in the last hour but not in the 23 hours before it with EXCEPT
SELECT path FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() EXCEPT SELECT path FROM "logs.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' DAY AND NOW() - INTERVAL '1' HOUR
Example – Discover the fields on agent spans
EXPLAIN (ANALYZE_FIELDS) SELECT * FROM "traces.default" WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1' HOUR AND NOW() AND name LIKE 'invoke_agent%'
The result has one row for @timestamp and one for @record. The @record row's description lists the fields inside @record.