CloudFrontログをAthenaで分析！日本時間対応とパーティション設定の実践手順ガイド

公開日: 2026年6月23日

皆さんこんにちは。okamoです。

今回は「実録：ITよろず相談」シリーズの第5回目です。

「インフラ運用チームにCloudFrontのログを抽出してもらったのだけど……」

抽出結果として大量のgzファイルを渡され、1ファイルずつ開いて調べようとしたものの、ファイル数が多すぎて困っているというご相談をいただきました。実は、このようなご相談をいただくのは今回が初めてではありません。

ITの現場には、AWS CLIや高度なログ分析ツールを自在に使いこなす人がいる一方で、ログのgzファイルを手作業で1つずつ解凍し、苦労しながら不具合調査やアクセス解析を行っている人も大勢います。この「IT現場の現実」を目の当たりにし、誰でも簡単に、かつ安全にログ分析ができる手順を世の中に共有したいと思いました。

ネット上を探しても、「CloudFrontの標準ログ + Athena + 日付パーティション」に特化した分かりやすい実践記事が見当たらなかったため、okamo独自のガイドを公開します。

インフラ運用チーム（管理者）とアプリ開発チーム（分析者）の2つのロールを想定したステップバイステップのガイドです。ぜひご活用ください。

1. なぜ CloudFront ログを Athena でクエリするのか（Why）

課題: バックエンド側のログだけでは見えない情報がある

観点バックエンド（Apache/Nginx）のログCloudFront のログ
クライアント IPALB/Proxy 経由で X-Forwarded-For の解析が必要エッジで直接記録 → 正確
バックエンド障害時そもそもログが出ないCloudFront まで到達していれば記録される
キャッシュ応答バックエンドに到達しないため記録なしHit/Miss/Error すべて記録
サーバー台数増加台数分のログを結合する必要あり1箇所に全リクエスト集約
バックエンド技術変更Lambda/ECS/EC2 で形式が異なる常に同一フォーマット

解決: CloudFront（最上位）のログを SQL でクエリする

[ユーザー] → CloudFront → ALB → WEB × N台
                ↑
        ここのログを使う
        ・正しいリモート IP が取れる
        ・バックエンドが落ちていても記録される
        ・バックエンドが何台あっても関係ない

3つの主要メリット:

  1. 正しいクライアント IP が取れる — 最上位のエッジで記録されるため、X-Forwarded-For の多段解析が不要。調査の起点として信頼できる
  2. 最上位で全リクエストを捕捉する — バックエンドが 502/503 を返すケース、キャッシュから応答するケースも含め、CloudFront に到達した全トラフィックが記録される
  3. バックエンド構成に依存しない — サーバーが 1台でも 100台でも、Lambda でも ECS でも、ログ解析の手法は変わらない

その他のメリット:

  • エッジロケーション情報 — x_edge_location でどの PoP が処理したか分かる（地理的攻撃パターンの分析に有用）
  • エージェント不要 — 各サーバーに Fluentd 等のログ転送を仕込む必要がない
  • gz のまま直接クエリ可能 — 解凍不要
  • 既存の S3 ログ設定そのまま — Firehose や Lambda の追加構成なし。最小コストで実現
  • AWS WAF ログにも同じ要領で応用可能（別の記事で解説予定）

ユースケース

  • 不正アクセス元 IP の特定
  • 特定 URI へのアクセス傾向分析
  • DDoS/Bot 攻撃時のリアルタイム調査
  • 特定期間のトラフィック集計
  • インシデント対応時のエビデンス取得

設計方針: 既存構成をいじらない

大規模組織やベンダー運用環境では、既存の AWS 構成を変更すること自体がリスクであり承認コストが高い。

本手順は、CloudFront 標準ログの S3 配置を変更せず、調査対象期間のログだけを別バケット内の作業フォルダに Hive 形式でコピー → Athena でクエリ → 作業フォルダを削除 する使い捨てワークフロー。

2. 構成概要と必要な IAM ポリシー

アーキテクチャ

アーキテクチャ図

必要なリソースと権限の関係

IAMポリシー構成図

変数の紐づけ表

本ドキュメントおよび CLI コマンドで使用する環境変数を以下に定義する。実環境に合わせて値を設定すること。

環境変数名説明例
$BUCKETCloudFront ログが保存されている S3 バケット名prod-myapp-logs-aws
$PREFIXバケット内のログ保存プレフィックスAWSLogs/123456789012/CloudFront
$DIST_IDCloudFront Distribution ID（ファイル名に含まれる）EXXXXXXXXXXXXX
$ANALYSIS_BUCKET解析用 S3 バケット名（管理者が事前作成）prod-myapp-log-analysis
$ACCOUNT_IDAWS アカウント ID123456789012
$REGIONAWS リージョンap-northeast-1

ワークフロー全体像

1. S3 コピー:     対象日付のログを Hive 形式の作業フォルダにコピー
2. Athena テーブル: 作業フォルダを指す Partition Projection テーブルを作成
3. クエリ:         SQL で分析
4. クリーンアップ:  テーブル削除 → 作業フォルダ削除

S3 バケット構成

s3://$BUCKET/
  └── $PREFIX/                      ← 既存（フラット・変更なし・読み取り専用）
      ├── $DIST_ID.2024-03-14-00.xxx.gz
      ├── $DIST_ID.2024-03-14-01.xxx.gz
      └── ...

s3://$ANALYSIS_BUCKET/               ← 解析用バケット（分析後に削除）
  └── year=2024/month=03/day=14/
      ├── $DIST_ID.2024-03-14-00.xxx.gz
      ├── $DIST_ID.2024-03-14-01.xxx.gz
      └── ...

3. S3 バケット作成と Athena ワークグループの初期設定（管理者で実施）

以下は管理者アカウントで実施する。 バケット名が確定しないと IAM ポリシーの ARN が書けないため、先にバケットを作成する。

なぜ管理者が先にやるべきか

  • ログ分析ユーザーに S3 バケット作成権限を与えなくて済む
  • ワークグループで出力先を固定すれば、ユーザー側の初回設定が不要になる
  • バケット名が確定しないと IAM ポリシーの ARN が書けない

手順

# === 環境変数の定義（実環境に合わせて値を変更） ===
BUCKET="prod-myapp-logs-aws"              # 元ログバケット（既存）
PREFIX="AWSLogs/123456789012/CloudFront"  # ログプレフィックス（既存）
DIST_ID="EXXXXXXXXXXXXX"                  # Distribution ID
ANALYSIS_BUCKET="prod-myapp-log-analysis" # 解析用バケット（新規作成）
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
REGION="ap-northeast-1"

# === Step 1: 解析用バケットの作成 ===
aws s3 mb "s3://${ANALYSIS_BUCKET}" --region "${REGION}"

# === Step 2: Athena クエリ結果用バケットの作成 ===
ATHENA_BUCKET="aws-athena-query-results-${ACCOUNT_ID}-${REGION}"
aws s3 mb "s3://${ATHENA_BUCKET}" --region "${REGION}"

# === Step 3: Athena ワークグループに出力先を設定（ユーザー側の上書きを禁止） ===
aws athena update-work-group \
  --work-group primary \
  --configuration-updates "{
    \"ResultConfigurationUpdates\": {
      \"OutputLocation\": \"s3://${ATHENA_BUCKET}/\"
    },
    \"EnforceWorkGroupConfiguration\": true
  }" \
  --region "${REGION}"

# === Step 4: 設定を確認 ===
aws athena get-work-group \
  --work-group primary \
  --query 'WorkGroup.Configuration.{OutputLocation:ResultConfiguration.OutputLocation,EnforceConfig:EnforceWorkGroupConfiguration}' \
  --region "${REGION}"

期待される出力:

{
    "OutputLocation": "s3://aws-athena-query-results-123456789012-ap-northeast-1/",
    "EnforceConfig": true
}

4. IAM ポリシー作成とユーザーの作成（管理者で実施）

IAM ポリシー（JSON）

以下のポリシーをログ分析用 IAM ユーザーにアタッチする。

権限の分離: 元ログバケット ($BUCKET) は読み取り専用。解析用バケット ($ANALYSIS_BUCKET) は読み書き削除自由。元ログに対する書き込み・削除権限は一切ない。

❗ 以下の JSON 内の ${BUCKET}, ${PREFIX}, ${ANALYSIS_BUCKET}, ${ACCOUNT_ID}, ${REGION} は環境変数名を示している。policy.json として保存する前に実際の値に置き換えること。 Section 3 で定義した値を使う。

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AthenaAccess",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:StopQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults",
        "athena:GetWorkGroup",
        "athena:ListWorkGroups"
      ],
      "Resource": "*"
    },
    {
      "Sid": "S3ReadLogBucket",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::${BUCKET}",
        "arn:aws:s3:::${BUCKET}/${PREFIX}/*"
      ]
    },
    {
      "Sid": "S3AnalysisBucket",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::${ANALYSIS_BUCKET}",
        "arn:aws:s3:::${ANALYSIS_BUCKET}/*"
      ]
    },
    {
      "Sid": "AthenaResultsAccess",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::aws-athena-query-results-*"
      ]
    },
    {
      "Sid": "GlueDataCatalog",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:CreateDatabase",
        "glue:DeleteDatabase",
        "glue:GetTable",
        "glue:GetTables",
        "glue:CreateTable",
        "glue:UpdateTable",
        "glue:DeleteTable",
        "glue:GetPartition",
        "glue:GetPartitions",
        "glue:BatchGetPartition"
      ],
      "Resource": [
        "arn:aws:glue:${REGION}:${ACCOUNT_ID}:catalog",
        "arn:aws:glue:${REGION}:${ACCOUNT_ID}:database/cloudfront_logs",
        "arn:aws:glue:${REGION}:${ACCOUNT_ID}:table/cloudfront_logs/*"
      ]
    }
  ]
}

💡 Tips: Section 3 の環境変数がセットされているターミナルで envsubst を使えば自動置換も可能:

envsubst < policy-template.json > policy.json

なぜ専用ユーザーを作るべきか

  • 最小権限の原則 — 管理者アカウントでクエリすると不必要な権限が付随する
  • 監査証跡の明確化 — CloudTrail で「誰がいつクエリしたか」が明確になる
  • ベンダー共有時の安全性 — 読み取り専用の分析ユーザーとして外部に渡せる
  • 誤操作防止 — 元ログバケットに対する書き込み・削除権限は一切ない

作成手順（CLI）

# ユーザー作成（コンソールアクセスなし・CLI 専用）
aws iam create-user --user-name log-analyst

# ポリシーをアタッチ（Section 2 の JSON を policy.json として保存済みの前提）
aws iam put-user-policy \
  --user-name log-analyst \
  --policy-name AthenaLogAnalystPolicy \
  --policy-document file://policy.json

コンソールログインは付与しない。 S3 コピーや Athena クエリはすべて CLI で実行するため、プログラムアクセス（アクセスキー）のみで十分。

アクセスキーの発行（調査開始時）

調査のたびに管理者がアクセスキーを発行し、分析担当者に渡す。

# アクセスキー発行
aws iam create-access-key --user-name log-analyst

出力例:

{
    "AccessKey": {
        "UserName": "log-analyst",
        "AccessKeyId": "AKIA****************",
        "SecretAccessKey": "************************************",
        "Status": "Active"
    }
}

AccessKeyId と SecretAccessKey を分析担当者に安全な経路で伝達する。

アクセスキーの無効化・削除（調査完了後）

調査完了後、または期限到来時に管理者がキーを無効化/削除する。

# キーの一覧確認
aws iam list-access-keys --user-name log-analyst

# キーを無効化（一時停止。再有効化も可能）
aws iam update-access-key \
  --user-name log-analyst \
  --access-key-id AKIA**************** \
  --status Inactive

# キーを完全削除（不要になったら）
aws iam delete-access-key \
  --user-name log-analyst \
  --access-key-id AKIA****************

運用ポイント: 長期間キーを放置しない。調査完了の報告を受けたら速やかに無効化する。

5. ログ分析の実施（ログ分析ユーザーで実施）

以降の作業はすべてログ分析用 IAM ユーザーの CLI で実施する。

Windows ユーザー向け: WSL + AWS CLI セットアップ

Windows 環境の場合、WSL（Windows Subsystem for Linux）上で作業する。

1. WSL のインストール（PowerShell を管理者で実行）

wsl --install

再起動後、スタートメニューから Ubuntu を起動してユーザー名・パスワードを設定する。

2. AWS CLI のインストール（WSL Ubuntu 内）

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
sudo apt update && sudo apt install -y unzip
unzip awscliv2.zip
sudo ./aws/install
rm -rf aws awscliv2.zip

# 確認
aws --version

3. クレデンシャルの設定（環境変数）

管理者から受け取ったアクセスキーを環境変数にセットする。

# 既存の profile 設定がある場合、競合しないよう無効化
export -n AWS_PROFILE
export -n AWS_DEFAULT_PROFILE

export AWS_ACCESS_KEY_ID="管理者から受け取った AccessKeyId"
export AWS_SECRET_ACCESS_KEY="管理者から受け取った SecretAccessKey"
export AWS_DEFAULT_REGION="ap-northeast-1"

セキュリティ注意:

  • 環境変数はターミナルを閉じると消える（= 安全）。再度作業するときは改めて export する。
  • .bashrc や .profile にキーを書き込まないこと。

動作確認:

aws sts get-caller-identity

log-analyst のユーザー情報が表示されれば OK。

Step 0: 変数の設定

各作業の冒頭で以下の変数を設定する。分析対象の期間に合わせて START_DATE / END_DATE を変更すること。

# === 環境設定（実環境に合わせて変更） ===
BUCKET="prod-myapp-logs-aws"              # 元ログバケット（既存）
PREFIX="AWSLogs/123456789012/CloudFront"  # ログプレフィックス（既存）
DIST_ID="EXXXXXXXXXXXXX"                  # Distribution ID
ANALYSIS_BUCKET="prod-myapp-log-analysis" # 解析用バケット（新規作成）
REGION="ap-northeast-1"
PARALLEL=8                                # 並列コピー数

# === 分析対象期間（JST）（ここを変更する） ===
START_DATE_JST="2026-06-25"   # 開始日（JST, YYYY-MM-DD）
END_DATE_JST="2026-06-28"     # 終了日（JST, YYYY-MM-DD）

Step 1: 対象期間のログを解析用バケットに Hive 形式でコピー

# JST → UTC 変換: 開始日を1日前にずらして全範囲カバー
START_DATE=$(date -d "${START_DATE_JST} - 1 day" +%Y-%m-%d)
END_DATE="${END_DATE_JST}"

echo "=== S3 コピー開始: ${START_DATE_JST} 〜 ${END_DATE_JST} (JST) ==="
echo "    UTC コピー範囲: ${START_DATE} 〜 ${END_DATE}"

current="$START_DATE"
while [[ "$current" < "$END_DATE" ]] || [[ "$current" == "$END_DATE" ]]; do
  year=${current:0:4}
  month=${current:5:2}
  day=${current:8:2}

  echo "  処理中: ${current}"
  aws s3 ls "s3://${BUCKET}/${PREFIX}/${DIST_ID}.${current}-" --region "${REGION}" \
    | awk '{print $4}' \
    | xargs -r -P "${PARALLEL}" -I{} \
        aws s3 cp \
          "s3://${BUCKET}/${PREFIX}/{}" \
          "s3://${ANALYSIS_BUCKET}/year=${year}/month=${month}/day=${day}/{}" \
          --region "${REGION}" --quiet

  current=$(date -d "${current} + 1 day" +%Y-%m-%d)
done

echo "=== S3 コピー完了 ==="
echo "解析用バケット: s3://${ANALYSIS_BUCKET}/"

元のログファイルは一切変更しない。コピー先は解析用バケット (AAA)。

Step 2: データベース・テーブル作成

# データベース作成
QID=$(aws athena start-query-execution \
  --query-string "CREATE DATABASE IF NOT EXISTS cloudfront_logs;" \
  --work-group primary --region "${REGION}" \
  --query 'QueryExecutionId' --output text)
sleep 3
aws athena get-query-execution --query-execution-id "$QID" --region "${REGION}" \
  --query 'QueryExecution.Status.State' --output text

# テーブル作成（既存テーブルがあれば削除してから作成）
QID=$(aws athena start-query-execution \
  --query-string "DROP TABLE IF EXISTS cloudfront_logs.access_logs;" \
  --work-group primary --region "${REGION}" \
  --query-execution-context Database=cloudfront_logs \
  --query 'QueryExecutionId' --output text)
sleep 3

QID=$(aws athena start-query-execution \
  --query-string "
    CREATE EXTERNAL TABLE cloudfront_logs.access_logs (
      \`date\`                        DATE,
      \`time\`                        STRING,
      x_edge_location               STRING,
      sc_bytes                      BIGINT,
      c_ip                          STRING,
      cs_method                     STRING,
      cs_host                       STRING,
      cs_uri_stem                   STRING,
      sc_status                     INT,
      cs_referer                    STRING,
      cs_user_agent                 STRING,
      cs_uri_query                  STRING,
      cs_cookie                     STRING,
      x_edge_result_type            STRING,
      x_edge_request_id             STRING,
      x_host_header                 STRING,
      cs_protocol                   STRING,
      cs_bytes                      BIGINT,
      time_taken                    FLOAT,
      x_forwarded_for               STRING,
      ssl_protocol                  STRING,
      ssl_cipher                    STRING,
      x_edge_response_result_type   STRING,
      cs_protocol_version           STRING,
      fle_status                    STRING,
      fle_encrypted_fields          INT,
      c_port                        INT,
      time_to_first_byte            FLOAT,
      x_edge_detailed_result_type   STRING,
      sc_content_type               STRING,
      sc_content_len                BIGINT,
      sc_range_start                BIGINT,
      sc_range_end                  BIGINT
    )
    PARTITIONED BY (year STRING, month STRING, day STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION 's3://${ANALYSIS_BUCKET}/'
    TBLPROPERTIES (
      'skip.header.line.count' = '2',
      'projection.enabled'     = 'true',
      'projection.year.type'   = 'integer',
      'projection.year.range'  = '2024,2099',
      'projection.month.type'  = 'integer',
      'projection.month.range' = '1,12',
      'projection.month.digits' = '2',
      'projection.day.type'    = 'integer',
      'projection.day.range'   = '1,31',
      'projection.day.digits'  = '2',
      'storage.location.template' = 's3://${ANALYSIS_BUCKET}/year=\${year}/month=\${month}/day=\${day}/'
    );
  " \
  --work-group primary --region "${REGION}" \
  --query-execution-context Database=cloudfront_logs \
  --query 'QueryExecutionId' --output text)

sleep 5
aws athena get-query-execution --query-execution-id "$QID" --region "${REGION}" \
  --query 'QueryExecution.Status.{State:State,Reason:StateChangeReason}' --output json

Step 2.5: jq のインストール

クエリ結果の整形に jq を使用する。未インストールの場合:

sudo apt update && sudo apt install -y jq

Step 3: 動作確認

# サンプル 5件: UTC と JST が正しく変換されているか目視確認
QID=$(aws athena start-query-execution \
  --query-string "
    SELECT
      date AS date_utc,
      \"time\" AS time_utc,
      DATE_FORMAT(
        CAST(CAST(date AS VARCHAR) || ' ' || \"time\" AS TIMESTAMP) + INTERVAL '9' HOUR,
        '%Y-%m-%d %H:%i:%s'
      ) AS datetime_jst,
      c_ip, cs_uri_stem, sc_status
    FROM cloudfront_logs.access_logs
    WHERE year = '$(date -d "$START_DATE" +%Y)'
      AND month = '$(date -d "$START_DATE" +%m)'
      AND day = '$(date -d "$START_DATE" +%d)'
    LIMIT 5;
  " \
  --work-group primary --region "${REGION}" \
  --query-execution-context Database=cloudfront_logs \
  --query 'QueryExecutionId' --output text)

sleep 5
aws athena get-query-execution --query-execution-id "$QID" --region "${REGION}" \
  --query 'QueryExecution.Status.State' --output text
aws athena get-query-results --query-execution-id "$QID" --region "${REGION}" \
  --output json | jq -r '.ResultSet.Rows[] | .Data | map(.VarCharValue) | @tsv' | column -t -s $'\t'

UTC の time_utc に +9h した値が datetime_jst に正しく反映されていれば OK。

6. 実践 SQL 例

以下のクエリは Step 3 と同じ要領で aws athena start-query-execution + get-query-results で実行する。

⚠️ 最初に以下のヘルパー関数をターミナルにコピペして Enter してください。 これにより athena-query コマンドが使えるようになります（ターミナルを閉じるまで有効）。

athena-query() {
  local qid=$(aws athena start-query-execution \
    --query-string "$1" \
    --work-group primary \
    --query-execution-context Database=cloudfront_logs \
    --region ap-northeast-1 \
    --query 'QueryExecutionId' --output text)
  echo "Waiting... (${qid})"
  while true; do
    local state=$(aws athena get-query-execution \
      --query-execution-id "$qid" --region ap-northeast-1 \
      --query 'QueryExecution.Status.State' --output text)
    [[ "$state" == "RUNNING" || "$state" == "QUEUED" ]] || break
    sleep 2
  done
  echo "State: ${state}"
  [[ "$state" == "SUCCEEDED" ]] && aws athena get-query-results \
    --query-execution-id "$qid" --region ap-northeast-1 \
    --output json | jq -r '.ResultSet.Rows[] | .Data | map(.VarCharValue) | @tsv' | column -t -s $'\t'
}

コピペ後、何も表示されなければ OK（関数が登録された状態）。type athena-query で確認可能。

6.0 データ範囲の確認（最初に実行）

テーブルにデータが入っているか、UTC/JST 変換が正しいかを確認する。

⚠️ 必ずパーティションフィルタを指定すること。 フィルタなしだと全パーティション走査で遅くなる。

# サンプル 5件: UTC と JST が正しく変換されているか目視確認
# ※ パターン解説 1〜5 をすべて網羅した書き方
athena-query "$(cat <<'SQL'
SELECT
  date AS date_utc,
  "time" AS time_utc,
  DATE_FORMAT(
    CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR,
    '%Y-%m-%d %H:%i:%s'
  ) AS datetime_jst,
  c_ip, cs_uri_stem, sc_status
FROM cloudfront_logs.access_logs
WHERE (
    (year = '2026' AND month = '05' AND day = '31')
    OR (year = '2026' AND month = '06' AND day BETWEEN '01' AND '05')
  )
  AND CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR
      BETWEEN TIMESTAMP '2026-06-01 00:00:00' AND TIMESTAMP '2026-06-05 23:59:59'
ORDER BY date, "time"
LIMIT 5;
SQL
)"

💡 パターン解説:

  1. パーティションは JST の前日〜最終日で 広めに 指定する（スキャンコスト僅少）
  2. 月跨ぎの場合は OR で前月の日を含める（上記例: 5/31 + 6/1〜5）
  3. CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR で JST 変換
  4. BETWEEN TIMESTAMP '...' AND TIMESTAMP '...' で JST ベースの精密フィルタ
  5. 結果に date_utc / time_utc / datetime_jst を並べて出力 → 確認・エビデンスが容易 UTC の time に +9h した結果が datetime_jst に正しく反映されていれば OK。

6.1 特定 URI にアクセスした IP を特定する

調査対象（JST）: 2026-06-01 00:00 〜 2026-06-05 23:59 パーティション範囲（UTC）: 5月31日 + 6月1〜5日（JST 6/1 00:00 = UTC 5/31 15:00 のため前月を含める）

athena-query "$(cat <<'SQL'
SELECT
  c_ip,
  COUNT(*) AS request_count,
  DATE_FORMAT(
    MIN(CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR),
    '%Y-%m-%d %H:%i:%s'
  ) AS first_seen_jst,
  DATE_FORMAT(
    MAX(CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR),
    '%Y-%m-%d %H:%i:%s'
  ) AS last_seen_jst
FROM cloudfront_logs.access_logs
WHERE (
    (year = '2026' AND month = '05' AND day = '31')
    OR (year = '2026' AND month = '06' AND day BETWEEN '01' AND '05')
  )
  AND CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR
      BETWEEN TIMESTAMP '2026-06-01 00:00:00' AND TIMESTAMP '2026-06-05 23:59:59'
  AND cs_uri_stem = '/admin.php'
GROUP BY c_ip
ORDER BY request_count DESC
LIMIT 50;
SQL
)"

6.2 特定 IP のアクセスログを時系列で抜く

調査対象（JST）: 2026-06-01 00:00 〜 2026-06-05 23:59（5日間） パーティション範囲（UTC）: 5月31日 + 6月1〜5日（JST 6/1 00:00 = UTC 5/31 15:00 のため前月を含める）

athena-query "$(cat <<'SQL'
SELECT
  date AS date_utc,
  "time" AS time_utc,
  DATE_FORMAT(
    CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR,
    '%Y-%m-%d %H:%i:%s'
  ) AS datetime_jst,
  cs_method, cs_uri_stem, cs_uri_query,
  sc_status, sc_bytes, cs_user_agent
FROM cloudfront_logs.access_logs
WHERE (
    (year = '2026' AND month = '05' AND day = '31')
    OR (year = '2026' AND month = '06' AND day BETWEEN '01' AND '05')
  )
  AND CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR
      BETWEEN TIMESTAMP '2026-06-01 00:00:00' AND TIMESTAMP '2026-06-05 23:59:59'
  AND c_ip = '203.0.113.50'
ORDER BY date, "time";
SQL
)"

6.3 500 エラーの発生状況を調査

調査対象（JST）: 2026-06-02 00:00 〜 2026-06-05 23:59 パーティション範囲（UTC）: day = '01' 〜 '05'

athena-query "$(cat <<'SQL'
SELECT
  DATE_FORMAT(
    CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR,
    '%Y-%m-%d %H:%i:%s'
  ) AS datetime_jst,
  cs_uri_stem, cs_method, c_ip, sc_status,
  cs_user_agent
FROM cloudfront_logs.access_logs
WHERE year = '2026' AND month = '06'
  AND day BETWEEN '01' AND '05'
  AND CAST(CAST(date AS VARCHAR) || ' ' || "time" AS TIMESTAMP) + INTERVAL '9' HOUR
      BETWEEN TIMESTAMP '2026-06-02 00:00:00' AND TIMESTAMP '2026-06-05 23:59:59'
  AND sc_status BETWEEN 500 AND 599
ORDER BY date, "time";
SQL
)"

7. クリーンアップ（作業完了後に必ず実施）

分析が完了したら、テーブルと作業フォルダを削除する。

Step 1: Athena テーブルを削除

QID=$(aws athena start-query-execution \
  --query-string "DROP TABLE IF EXISTS cloudfront_logs.access_logs;" \
  --work-group primary --region "${REGION}" \
  --query-execution-context Database=cloudfront_logs \
  --query 'QueryExecutionId' --output text)

sleep 3
aws athena get-query-execution --query-execution-id "$QID" --region "${REGION}" \
  --query 'QueryExecution.Status.State' --output text

Step 2: 解析用バケットのデータを削除

echo "=== 解析データ削除: s3://${ANALYSIS_BUCKET}/ ==="

# 削除対象のファイル数を確認
FILE_COUNT=$(aws s3 ls "s3://${ANALYSIS_BUCKET}/" \
  --recursive --region "${REGION}" | wc -l)
echo "削除対象: ${FILE_COUNT} ファイル"

# 削除実行
aws s3 rm "s3://${ANALYSIS_BUCKET}/" --recursive --region "${REGION}"

echo "=== クリーンアップ完了 ==="

元のログファイル（s3://XXX/YYY/）は一切削除されない。 削除されるのは解析用バケット（s3://AAA/）内のコピーデータのみ。

補足: Athena クエリ結果バケット（aws-athena-query-results-*）にクエリ結果の CSV が残る。こちらは aws s3 rm s3://${ATHENA_BUCKET}/ --recursive で削除可能。

8. コスト目安

項目料金
Athena クエリ$5 / スキャン 1TB
S3 コピー（PUT リクエスト）$0.005 / 1,000 リクエスト
S3 解析用バケットのストレージ分析中のみ（削除すれば課金なし）
Glue Data Catalogテーブル数が少なければ無料枠内

Partition Projection + Hive 形式パーティションにより、スキャン対象を指定日時のファイルのみに限定できるため、Athena クエリコストは極めて低い。

9. トラブルシューティング

症状原因対処
HIVE_CANNOT_OPEN_SPLITS3 バケットへの権限不足IAM ポリシーの S3ReadLogBucket / S3AnalysisBucket を確認
0件が返る解析用バケットにファイルがないStep 1 の S3 コピーが完了しているか aws s3 ls s3://AAA/ --recursive | head で確認
ACCESS_DENIED on AthenaGlue Data Catalog 権限不足IAM ポリシーの GlueDataCatalog を確認
ACCESS_DENIED on S3 copy解析用バケットへの書き込み権限不足IAM ポリシーの S3AnalysisBucket に s3:PutObject があるか確認
文字化け・パースエラーCloudFront ログ形式がカスタムフィールド付きカラム定義を実際のログヘッダーに合わせる

以上です。

長文にお付き合いいただき、感謝いたします。

この手の話は詳しい方（専門家）がたくさんいらっしゃると思いますので、 今後、良い記事が見つかったら、「この記事の中身は削除し、良記事へのリンクだけ残したい」と考えてます。

おまけ：okamoちゃんねるのレビュー

この記事について、3人のAI仮想読者がレビューしてくれました。

  • クロード（辛口エンジニア）
    • 「AthenaアクセスのResource: "*"はちょっと甘いぞ。athena:StartQueryExecutionとかathena:GetWorkGroupをワイルドカードで許可してる。リソースベースのARNでarn:aws:athena:REGION:ACCOUNT_ID:workgroup/primaryに絞れるはずだ。」
  • GPT（税理士）
    • 「手順は丁寧なんですが、非エンジニアが本当に再現するにはまだ少し重いです。」
  • Gemini（お母さん）
    • 「困っている仲間を置き去りにしないっていう、あったか〜い愛を感じて本当に感動しちゃったのよ！」

👉 AI 仮想読者3人による辛口レビュー全編はこちら

タグ: AWS, CloudFront, Athena, ログ分析, 実録ITよろず相談, WSL
トップページへ