使用 Amazon Athena 查询 Amazon S3 日志文件中的互联网测量数据 - Amazon CloudWatch
使用 Amazon Athena 创建互联网测量数据表用于互联网测量数据的 Athena 查询示例

使用 Amazon Athena 查询 Amazon S3 日志文件中的互联网测量数据

您可以使用 Amazon Athena，来查询和查看 Amazon CloudWatch 网络监测仪发布到 Amazon S3 存储桶的互联网测量数据。网络监测仪中有一个选项，选择此选项后可将应用程序的互联网测量数据发布到 S3 存储桶，以了解所监测城市-网络 [客户端位置和 ASN，后者通常是互联网服务提供商（ISP）] 的互联网流量。无论您是否选择将测量数据发布到 S3，Internet Monitor 每五分钟都会自动将每个监测仪的前 500 个（按流量计）城市网络的互联网测量数据发布到 CloudWatch Logs。

本章包括如何在 Athena 中为位于 S3 日志文件中的互联网测量数据创建表的步骤，然后提供示例查询以查看测量数据的不同视图。例如，您可以按延迟影响查询受影响的前 10 个城市网络。

使用 Amazon Athena 在 Internet Monitor 中创建互联网测量数据表

要开始将 Athena 与您的网络监测仪 S3 日志文件结合使用，您首先需要为互联网测量数据创建一个表。

按照此过程中的步骤在 Athena 中根据 S3 日志文件创建表。然后，您可以在表上运行 Athena 查询，例如这些示例互联网测量数据查询，以获取有关测量数据的信息。

创建 Athena 表

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

  2. 在 Athena 查询编辑器中，输入查询语句以生成一个包含 Internet Monitor 互联网测量数据的表。将 LOCATION 参数的值替换为存储 Internet Monitor 互联网测量数据的 S3 存储桶的位置。

    CREATE EXTERNAL TABLE internet_measurements (
    version INT,
    timestamp INT,
    clientlocation STRING,
    servicelocation STRING,
    percentageoftotaltraffic DOUBLE,
    bytesin INT,
    bytesout INT,
    clientconnectioncount INT,
    internethealth STRING,
    trafficinsights STRING
)
PARTITIONED BY (year STRING, month STRING, day STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://amzn-s3-demo-bucket/bucket_prefix/AWSLogs/account_id/internetmonitor/AWS_Region/'
TBLPROPERTIES ('skip.header.line.count' = '1');

  3. 输入一条语句来创建一个分区以读取数据。例如，以下示例查询创建了指定日期和位置的单个分区：

    ALTER TABLE internet_measurements
ADD PARTITION (year = 'YYYY', month = 'MM', day = 'dd')
LOCATION
's3://amzn-s3-demo-bucket/bucket_prefix/AWSLogs/account_id/internetmonitor/AWS_Region/YYYY/MM/DD';

  4. 选择运行。

互联网测量数据的 Athena 语句示例

下面是用于生成表的语句示例：

CREATE EXTERNAL TABLE internet_measurements (
    version INT,
    timestamp INT,
    clientlocation STRING,
    servicelocation STRING,
    percentageoftotaltraffic DOUBLE,
    bytesin INT,
    bytesout INT,
    clientconnectioncount INT,
    internethealth STRING,
    trafficinsights STRING
)
PARTITIONED BY (year STRING, month STRING, day STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://internet-measurements/TestMonitor/AWSLogs/1111222233332/internetmonitor/us-east-2/'
TBLPROPERTIES ('skip.header.line.count' = '1');

以下是用于创建分区以读取数据的语句示例：

ALTER TABLE internet_measurements
ADD PARTITION (year = '2023', month = '04', day = '07')
LOCATION 's3://internet-measurements/TestMonitor/AWSLogs/1111222233332/internetmonitor/us-east-2/2023/04/07/'

用于 Internet Monitor 中互联网测量数据的 Amazon Athena 查询示例

本节包含查询示例，您可以在 Amazon Athena 中使用这些查询来获取有关发布到 Amazon S3 的应用程序互联网测量数据的信息。

查询前 10 个（按流量的总百分比计）受影响的客户端位置和 ASN

运行此 Athena 查询，返回受影响的前 10 个（按流量的总百分比计）城市网络（即客户端位置和 ASN，通常是互联网服务提供商）。

SELECT json_extract_scalar(clientLocation, '$.city') as city,
    json_extract_scalar(clientLocation, '$.networkname') as networkName,
    sum(percentageoftotaltraffic) as percentageoftotaltraffic
FROM internet_measurements
GROUP BY json_extract_scalar(clientLocation, '$.city'),
    json_extract_scalar(clientLocation, '$.networkname')
ORDER BY percentageoftotaltraffic desc
limit 10

查询受影响的前 10 个（按可用性计）客户端位置和 ASN

运行此 Athena 查询，返回受影响的前 10 个（按流量的总百分比计）城市网络（即客户端位置和 ASN，通常是互联网服务提供商）。

SELECT json_extract_scalar(clientLocation, '$.city') as city,
    json_extract_scalar(clientLocation, '$.networkname') as networkName,
    sum(
        cast(
            json_extract_scalar(
                internetHealth,
                '$.availability.percentageoftotaltrafficimpacted'
            )
        as double ) 
    ) as percentageOfTotalTrafficImpacted
FROM internet_measurements
GROUP BY json_extract_scalar(clientLocation, '$.city'),
    json_extract_scalar(clientLocation, '$.networkname')
ORDER BY percentageOfTotalTrafficImpacted desc
limit 10

查询受影响的前 10 个（按延迟计）客户端位置和 ASN

运行此 Athena 查询，返回受影响的前 10 个（按延迟影响计）城市网络（即客户端位置和 ASN，通常是互联网服务提供商）。

SELECT json_extract_scalar(clientLocation, '$.city') as city,
    json_extract_scalar(clientLocation, '$.networkname') as networkName,
    sum(
        cast(
            json_extract_scalar(
                internetHealth,
                '$.performance.percentageoftotaltrafficimpacted'
            )
        as double ) 
    ) as percentageOfTotalTrafficImpacted
FROM internet_measurements
GROUP BY json_extract_scalar(clientLocation, '$.city'),
    json_extract_scalar(clientLocation, '$.networkname')
ORDER BY percentageOfTotalTrafficImpacted desc
limit 10

查询您的客户端位置和 ASN 的流量亮点

运行此 Athena 查询以返回流量亮点，包括可用性分数、性能分数以及城市网络（即客户端位置和 ASN，通常是互联网服务提供商）的首字节时间。


SELECT json_extract_scalar(clientLocation, '$.city') as city,
    json_extract_scalar(clientLocation, '$.subdivision') as subdivision,
    json_extract_scalar(clientLocation, '$.country') as country,
    avg(cast(json_extract_scalar(internetHealth, '$.availability.experiencescore') as double)) as availabilityScore,
    avg(cast(json_extract_scalar(internetHealth, '$.performance.experiencescore') as double)) performanceScore,
    avg(cast(json_extract_scalar(trafficinsights, '$.timetofirstbyte.currentexperience.value') as double)) as averageTTFB,
    sum(bytesIn) as bytesIn,
    sum(bytesOut) as bytesOut,
    sum(bytesIn + bytesOut) as totalBytes
FROM internet_measurements
where json_extract_scalar(clientLocation, '$.city') != 'N/A'
GROUP BY 
json_extract_scalar(clientLocation, '$.city'),
    json_extract_scalar(clientLocation, '$.subdivision'),
    json_extract_scalar(clientLocation, '$.country')
ORDER BY totalBytes desc
limit 100

有关使用 Athena 的更多信息，请参阅 Amazon Athena 用户指南。