CloudTrail Lake SQL constraints

CloudTrail Lake queries are SQL strings. This section describes the allowed SQL language that you use to create queries.

Only SELECT statements are allowed. No query strings can change or mutate data. The API restricts the scope of a SELECT statement to the argument tree shown in the following template. Simple aggregations, conditions, and operators are allowed. A keyword, operator, or function that is not described in this section is disallowed. The event data store ID—the ID portion of the event data store's ARN—is the valid value for tables.

SELECT [ DISTINCT ] columns [ Aggregate ]
[ FROM Tables event_data_store_ID]
[ WHERE columns [ Conditions ] ]
[ GROUP BY columns [ DISTINCT | Aggregate ] ]
[ HAVING columns [ Aggregate | Conditions ] ]
[ ORDER BY columns [ Aggregate | ASC | DESC | NULLS | FIRST | LAST ]
[ LIMIT [ INT ] ]

Supported schema for event record fields

The following is the valid SQL schema for event record fields.

[
    {
        "Name": "eventversion",
        "Type": "string"
    },
    {
        "Name": "useridentity",
        "Type": "struct<type:string,principalid:string,arn:string,accountid:string,accesskeyid:string,
                 username:string,sessioncontext:struct<attributes:struct<creationdate:timestamp,
                 mfaauthenticated:string>,sessionissuer:struct<type:string,principalid:string,arn:string,
                 accountid:string,username:string>,webidfederationdata:struct<federatedprovider:string,
                 attributes:map<string,string>>,sourceidentity:string,ec2roledelivery:string,
                 ec2issuedinvpc:string>,invokedby:string,identityprovider:string>"
    },
    {
        "Name": "eventtime",
        "Type": "timestamp"
    },
    {
        "Name": "eventsource",
        "Type": "string"
    },
    {
        "Name": "eventname",
        "Type": "string"
    },
    {
        "Name": "awsregion",
        "Type": "string"
    },
    {
        "Name": "sourceipaddress",
        "Type": "string"
    },
    {
        "Name": "useragent",
        "Type": "string"
    },
    {
        "Name": "errorcode",
        "Type": "string"
    },
    {
        "Name": "errormessage",
        "Type": "string"
    },
    {
        "Name": "requestparameters",
        "Type": "map<string,string>"
    },
    {
        "Name": "responseelements",
        "Type": "map<string,string>"
    },
    {
        "Name": "additionaleventdata",
        "Type": "map<string,string>"
    },
    {
        "Name": "requestid",
        "Type": "string"
    },
    {
        "Name": "eventid",
        "Type": "string"
    },
    {
        "Name": "readonly",
        "Type": "boolean"
    },
    {
        "Name": "resources",
        "Type": "array<struct<accountid:string,type:string,arn:string,arnprefix:string>>"
    },
    {
        "Name": "eventtype",
        "Type": "string"
    },
    {
        "Name": "apiversion",
        "Type": "string"
    },
    {
        "Name": "managementevent",
        "Type": "boolean"
    },
    {
        "Name": "recipientaccountid",
        "Type": "string"
    },
    {
        "Name": "sharedeventid",
        "Type": "string"
    },
    {
        "Name": "annotation",
        "Type": "string"
    },
    {
        "Name": "vpcendpointid",
        "Type": "string"
    },
    {
        "Name": "serviceeventdetails",
        "Type": "map<string,string>"
    },
    {
        "Name": "addendum",
        "Type": "map<string,string>"
    },
    {
        "Name": "edgedevicedetails",
        "Type": "map<string,string>"
    },
    {
        "Name": "insightdetails",
        "Type": "map<string,string>"
    },
    {
        "Name": "eventcategory",
        "Type": "string"
    },
    {
        "Name": "tlsdetails",
        "Type": "struct<tlsversion:string,ciphersuite:string,clientprovidedhostheader:string>"
    },
    {
        "Name": "sessioncredentialfromconsole",
        "Type": "string"
    },
    {
        "Name": "eventjson",
        "Type": "string"
    }
    {
        "Name": "eventjsonchecksum",
        "Type": "string"
    }
]

Aggregate functions and condition operators

The following are allowed Aggregate functions.

SUM
MIN
MAX
AVG
COUNT

The following are allowed Condition operators.

AND
OR
IN
NOT
IS (NOT) NULL
LIKE
<
>
<=
>=
<>
!=
( conditions ) #parenthesised conditions

Supported functions

The following are supported functions for CloudTrail Lake queries. For descriptions and examples, see JSON Functions and Operators on the Presto 0.266 documentation website.

Function
element_at(Map | Array, Object | number) ➝ Object
cardinality(Map | Array) ➝ BigInt
date convert functions (see following table)
map_values(Map) ➝ Array(Object)
map_keys(Map) ➝ Array(Object)
contains(array, object) ➝ boolean
array_distinct(array) ➝ array
array_max(array) ➝ Object, array_min(array)➝ Object
slice(array, start, length) ➝ array
json_parse(string) ➝ json
is_json_scalar(json) ➝ boolean
json_extract(json, string) ➝ Json
json_extract_scalar(json, string) ➝ string
json_format(json) ➝ string
json_array_contains(json_array, object) ➝ boolean
json_array_get(json_array, index) ➝ json
json_array_length(json_array)➝ bigInt

Supported date convert functions

For more information about the supported date and time functions, see Date and Time Functions and Operators on the Presto 0.266 documentation website.

Date convert functions
date
date_trunc
day
day_of_month
day_of_week
day_of_year
dow
doy
hour
millisecond
minute
month
quarter
second
week
week_of_year
year
year_of_week
yow