CloudTrail Lake SQL 限制 - Amazon CloudTrail
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

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 文档网站中的日期与时间函数和运算符

日期转换函数

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