Amazon Athena を使用して、Amazon S3 ログファイルのインターネット測定値をクエリする

Amazon Athena を使用すると、Internet Monitor が Amazon S3 バケットに発行するインターネット測定値をクエリして表示できます。Internet Monitor では、モニタリング対象の都市ネットワーク (クライアントのロケーションと ASN、通常はインターネットサービスプロバイダー (ISP)) のインターネット向けトラフィック用 S3 バケットにアプリケーションのインターネット測定値を発行するオプションが用意されています。S3 に測定値をパブリッシュすることを選択するかどうかにかかわらず、Internet Monitor は、各モニタの (トラフィック量で) 上位 500 の都市ネットワークのインターネット測定値を、5 分ごとに CloudWatch Logs に自動的にパブリッシュします。

この章では、S3 ログファイルにあるインターネット測定値のテーブルを Athena に作成する手順を説明します。また、測定値をさまざまなビューで表示するクエリの例を紹介します。例えば、レイテンシーによる影響度の上位 10 の都市ネットワークをクエリできます。

Amazon Athena を使用して、Internet Monitor でインターネット測定値用のテーブルを作成する

Internet Monitor S3 ログファイルで Athena の使用を開始するには、まずインターネット測定値用のテーブルを作成します。

以下の手順に従い、S3 ログファイルに基づいて Athena にテーブルを作成します。次に、これらのインターネット測定クエリの例など、テーブルで Athena クエリを実行して、測定値に関する情報を取得できます。

Athena テーブルを作成する

インターネット測定値用の 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 ユーザーガイド」を参照してください。