CloudTrail Lake SQL 限制
CloudTrail Lake 查询是 SQL 字符串。本节介绍了用于创建查询而允许使用的 SQL 语言。
仅允许使用 SELECT
语句。没有查询字符串可以更改或变更数据。API 将 SELECT
语句的范围限制为以下模板中显示的参数树。允许简单的聚合、条件和运算符。不允许使用本节中未描述的关键字、运算符或函数。事件数据存储 ID(事件数据存储的 ARN 的 ID 部分)是表的有效值。
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 ] ]
事件记录字段支持的架构
以下是事件记录字段的有效 SQL 架构。
[ { "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
函数。
SUM MIN MAX AVG COUNT
允许以下 Condition
运算符。
AND OR IN NOT IS (NOT) NULL LIKE < > <= >= <> != ( conditions ) #parenthesised conditions
支持的函数
以下是 CloudTrail Lake 查询支持的函数。有关描述和示例,请参阅 Presto 0.266 文档网站上的 JSON 函数和运算符
函数 |
---|
element_at(映射 | 数组、对象 | 数字)➝ 对象 |
cardinality(映射 | 数组)➝ BigInt |
日期转换函数(见下表) |
map_value(映射)➝ 数组(对象) |
map_keys(映射)➝ 数组(对象) |
contains(数组、对象)➝ 布尔值 |
array_distinct(数组)➝ 数组 |
array_max(数组)➝ 对象,array_min(数组)➝ 对象 |
slice(数组、开始、长度)➝ 数组 |
json_parse(字符串)➝ json |
is_json_scalar (json) ➝ 布尔值 |
json_extract(json,字符串)➝ Json |
json_extract_scalar(json,字符串)➝ 字符串 |
json_format (json) ➝ 字符串 |
json_array_contains(json_array,对象)➝ 布尔值 |
json_array_get(json_array,索引)➝ json |
json_array_length (json_array) ➝ bigInt |
支持的日期转换函数
如需详细了解支持的日期和时间函数,请参阅 Presto 0.266 文档网站中的日期与时间函数和运算符
日期转换函数 |
---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|