# 在 S3 表类数据存储服务中查询访问日志
<a name="sal-cw-querying-s3tables"></a>

启用 Amazon S3 表类数据存储服务集成后，Amazon CloudWatch Logs 会自动以 Apache Iceberg 格式将您的服务器访问日志传输到 `aws-cloudwatch` 托管式表存储桶。由于数据采用 Iceberg 格式，因此，您可以使用任何支持 Apache Iceberg 的工具对数据进行查询，包括：
+ **Amazon Athena**：使用 S3 表类数据存储服务目录直接从 Athena 控制台运行 SQL 查询。
+ **Amazon SageMaker 融通式合作开发工作室**：使用内置的 SQL 编辑器查询您的日志数据以及其它分析工作负载。
+ **Amazon Redshift**：通过 Redshift Spectrum 或直接集成查询 Iceberg 表。
+ **Amazon EMR 上的 Apache Spark**：使用 Spark SQL 或带有 Iceberg 连接器的 DataFrame API。
+ **开源工具**：任何支持 Apache Iceberg REST 目录的工具，例如 Trino、DuckDB 或 PyIceberg。

**注意**  
有关在 CloudWatch Logs Insights 中以交互方式查询日志（不使用 SQL），请参阅[使用 CloudWatch Logs Insights 查询日志](sal-cw-querying-insights.md)。

## 先决条件
<a name="sal-cw-s3tables-prerequisites"></a>

您必须先完成以下步骤，然后才能在 S3 表类数据存储服务中查询访问日志：

1. **启用 S3 表类数据存储服务集成。**请参阅[启用 S3 表类数据存储服务集成（可选）](sal-cw-enabling.md#sal-cw-tables-integration)。

1. **启用 S3 表类数据存储服务与分析服务集成。**在 Amazon S3 控制台中，导航到**表存储桶**，然后选择**启用集成**。这将在数据目录中创建 `s3tablescatalog` 联合目录，并使您的表对分析服务可见。默认情况下，访问权限通过 IAM 权限进行控制。您可以选择使用 AWS Lake Formation 进行精细的访问控制。有关更多信息，请参阅[将 S3 表类数据存储服务与 AWS 分析服务集成](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-aws.html)。

1. **等待数据进行填充。**数据通常会在首次传输到 CloudWatch Logs 后的一小时内出现。

## 连接 Amazon Athena
<a name="sal-cw-s3tables-athena-setup"></a>

1. 从 [https://console.aws.amazon.com/athena/](https://console.aws.amazon.com/athena/) 打开 Athena 控制台。

1. 在查询编辑器中，从数据来源下拉列表中选择 **Amazon S3 表类数据存储服务**目录。

1. 您的日志表出现在 `logs` 命名空间下的数据库列表中。

服务器访问日志的表名称为 `amazon_s3__server_access`。完全限定的表参考是：

```
"s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
```

## 流量分析
<a name="sal-cw-s3tables-traffic"></a>

**Example 一段时间内的请求量**  

```
SELECT date_trunc('minute', request_time) AS interval,
  COUNT(*) AS requests
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY date_trunc('minute', request_time)
ORDER BY interval ASC;
```

**Example 按操作类型划分的请求组合**  

```
SELECT operation, COUNT(*) AS cnt
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY operation
ORDER BY cnt DESC;
```

**Example 按存储桶划分的流量**  

```
SELECT bucket_arn, COUNT(*) AS requests, SUM(bytes_sent_size) AS bytes_out
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY bucket_arn
ORDER BY requests DESC;
```

## 错误排查
<a name="sal-cw-s3tables-errors"></a>

**Example 错误率细分**  

```
SELECT http_status, error_code, operation, COUNT(*) AS error_count
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE http_status >= 400
GROUP BY http_status, error_code, operation
ORDER BY error_count DESC;
```

**Example 403 拒绝访问请求**  

```
SELECT key_name, remote_ip, requester, COUNT(*) AS denied_count
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE http_status = 403
GROUP BY key_name, remote_ip, requester
ORDER BY denied_count DESC;
```

**Example 404 找不到请求**  

```
SELECT key_name, error_code, remote_ip, COUNT(*) AS miss_count
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE http_status = 404
GROUP BY key_name, error_code, remote_ip
ORDER BY miss_count DESC;
```

**Example 503 减速（节流）事件**  

```
SELECT key_name, remote_ip, COUNT(*) AS throttle_count
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE http_status = 503
GROUP BY key_name, remote_ip
ORDER BY throttle_count DESC;
```

## 访问模式
<a name="sal-cw-s3tables-access"></a>

**Example 按源 IP 划分的流量**  

```
SELECT remote_ip,
  COUNT(*) AS total_requests,
  SUM(CASE WHEN http_status >= 400 THEN 1 ELSE 0 END) AS errors,
  SUM(bytes_sent_size) AS bytes_transferred
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY remote_ip
ORDER BY total_requests DESC;
```

**Example 请求者的操作**  

```
SELECT requester, operation, COUNT(*) AS requests
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY requester, operation
ORDER BY requests DESC;
```

**Example 最常访问的键**  

```
SELECT key_name, COUNT(*) AS access_count, SUM(bytes_sent_size) AS bytes_out
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation IN ('REST.GET.OBJECT', 'REST.PUT.OBJECT')
GROUP BY key_name
ORDER BY access_count DESC
LIMIT 10;
```

## 延迟分析
<a name="sal-cw-s3tables-latency"></a>

**Example 按操作类型划分的延迟**  

```
SELECT operation,
  AVG(total_duration) AS avg_ms,
  APPROX_PERCENTILE(total_duration, 0.5) AS p50_ms,
  APPROX_PERCENTILE(total_duration, 0.95) AS p95_ms,
  MAX(total_duration) AS max_ms,
  COUNT(*) AS requests
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation IN ('REST.GET.OBJECT', 'REST.PUT.OBJECT')
GROUP BY operation;
```

**Example 按照 p95 延迟划分的最慢键**  

```
SELECT key_name,
  AVG(total_duration) AS avg_ms,
  APPROX_PERCENTILE(total_duration, 0.95) AS p95_ms,
  COUNT(*) AS requests
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation = 'REST.GET.OBJECT'
GROUP BY key_name
ORDER BY p95_ms DESC
LIMIT 10;
```

**Example 一段时间内的延迟**  

```
SELECT date_trunc('minute', request_time) AS interval,
  AVG(total_duration) AS avg_ms,
  APPROX_PERCENTILE(total_duration, 0.95) AS p95_ms
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation IN ('REST.GET.OBJECT', 'REST.PUT.OBJECT')
GROUP BY date_trunc('minute', request_time)
ORDER BY interval ASC;
```

## 成本归属
<a name="sal-cw-s3tables-cost"></a>

**Example 按操作划分的数据传输**  

```
SELECT operation,
  SUM(bytes_sent_size) AS total_bytes_out,
  SUM(object_size) AS total_object_bytes,
  COUNT(*) AS request_count
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation IN ('REST.GET.OBJECT', 'REST.PUT.OBJECT', 'REST.DELETE.OBJECT')
GROUP BY operation
ORDER BY total_bytes_out DESC;
```

**Example 正在提供的最大对象**  

```
SELECT key_name, MAX(object_size) AS max_size, AVG(object_size) AS avg_size, COUNT(*) AS reads
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE operation = 'REST.GET.OBJECT' AND bytes_sent_size > 0
GROUP BY key_name
ORDER BY max_size DESC
LIMIT 10;
```

## 安全分析
<a name="sal-cw-s3tables-security"></a>

**Example 具有高错误率的 IP**  

```
SELECT remote_ip,
  COUNT(*) AS total,
  SUM(CASE WHEN http_status >= 400 THEN 1 ELSE 0 END) AS errors
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY remote_ip
HAVING SUM(CASE WHEN http_status >= 400 THEN 1 ELSE 0 END) > 10
ORDER BY errors DESC;
```

**Example 按键划分的尝试访问失败**  

```
SELECT remote_ip, key_name, operation, http_status, COUNT(*) AS attempts
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
WHERE http_status >= 400
GROUP BY remote_ip, key_name, operation, http_status
ORDER BY attempts DESC
LIMIT 20;
```

**Example TLS 版本分配**  

```
SELECT tls_version, COUNT(*) AS cnt
FROM "s3tablescatalog/aws-cloudwatch"."logs"."amazon_s3__server_access"
GROUP BY tls_version
ORDER BY cnt DESC;
```

## 使用人工智能代理查询 Amazon S3 服务器访问日志
<a name="sal-cw-s3tables-ai-agents"></a>

与 MCP 兼容的人工智能代理可以发现包含 Amazon S3 服务器访问日志的 S3 表，生成 SQL 查询并分析结果。由于您的 Amazon S3 服务器访问日志在 `aws-cloudwatch` 表存储桶中以 Iceberg 表的形式导出，因此，代理可以通过您在 Amazon Athena、Amazon EMR、Amazon Redshift 或任何与 Iceberg 兼容的工具中使用的相同 S3 表类数据存储服务目录对这些日志进行查询。

[Agent Toolkit for AWS](https://github.com/aws/agent-toolkit-for-aws) 提供的技能可为代理提供经过验证的过程来处理 AWS 数据。[查询 CloudWatch Logs 系统表](https://github.com/aws/agent-toolkit-for-aws/tree/main/skills/specialized-skills/system-table-skills/querying-aws-cloudwatch)技能对导出到 S3 表类数据存储服务的 CloudWatch Logs 数据运行 SQL 查询，包括 `aws-cloudwatch` 表存储桶中的 Amazon S3 服务器访问日志。要使用它，请从 GitHub 上的存储库下载该技能，并将其添加到代理的技能目录中。

例如，您可以要求代理显示过去 24 小时内，S3 服务器访问日志中请求计数排名前 10 的源 IP 地址。代理使用该技能来定位访问日志表并在 Athena 中运行查询。