CloudTrail Lake SQL constraints - AWS CloudTrail

CloudTrail Lake SQL constraints

CloudTrail Lake queries are SQL strings. This section provides information about the supported functions, operators, and schemas.

Only SELECT statements are allowed. No query strings can change or mutate data.

CloudTrail Lake supports all valid Presto SQL SELECT statements, functions, and operators. For more information about the supported SQL functions and operators, see Functions and Operators on the Presto documentation website.

The CloudTrail console provides a number of sample queries that can help you get started writing your own queries. For more information, see View sample queries in the CloudTrail console.

Supported functions, condition and join operators

Supported functions

CloudTrail Lake supports all Presto functions. For more information about the supported functions, see Functions and Operators on the Presto documentation website.

CloudTrail Lake does not support the INTERVAL keyword.

Supported condition operators

The following are supported condition operators.

AND OR IN NOT IS (NOT) NULL LIKE BETWEEN GREATEST LEAST IS DISTINCT FROM IS NOT DISTINCT FROM < > <= >= <> != ( conditions ) #parenthesised conditions

Supported join operators

The following are the supported JOIN operators. For more information about running multi-table queries, see Advanced, multi-table query support.

UNION UNION ALL EXCEPT INTERSECT LEFT JOIN RIGHT JOIN INNER JOIN

Advanced, multi-table query support

CloudTrail Lake supports advanced query language across multiple event data stores.

To run your query, use the start-query command in the AWS CLI. The following is an example, using one of the sample queries in this section.

aws cloudtrail start-query --query-statement "Select eventId, eventName from EXAMPLEf852-4e8f-8bd1-bcf6cEXAMPLE UNION Select eventId, eventName from EXAMPLEg741-6y1x-9p3v-bnh6iEXAMPLE UNION ALL Select eventId, eventName from EXAMPLEb529-4e8f9l3d-6m2z-lkp5sEXAMPLE ORDER BY eventId LIMIT 10;"

The response is a QueryId string. To get the status of a query, run describe-query, using the QueryId value returned by start-query. If the query is successful, you can run get-query-results to get results.

UNION|UNION ALL|EXCEPT|INTERSECT

The following is an example query that uses UNION and UNION ALL to find events by their event ID and event name in three event data stores, EDS1, EDS2, and EDS3. The results are selected from each event data store first, then results are concatenated, ordered by event ID, and limited to ten events.

Select eventId, eventName from EDS1 UNION Select eventId, eventName from EDS2 UNION ALL Select eventId, eventName from EDS3 ORDER BY eventId LIMIT 10;

LEFT|RIGHT|INNER JOIN

The following is an example query that uses LEFT JOIN to find all events from an event data store named eds2, mapped to edsB, that match those in a primary (left) event data store, edsA. The returned events occur on or before January 1, 2020, and only the event names are returned.

SELECT edsA.eventName, edsB.eventName, element_at(edsA.map, 'test') FROM eds1 as edsA LEFT JOIN eds2 as edsB ON edsA.eventId = edsB.eventId WHERE edsA.eventtime <= '2020-01-01' ORDER BY edsB.eventName;