【Hugo×AWS】Athenaでアクセスログを分析する

cd hugo-s3-demo-infra/prod
terraform output

cloudfront_distribution_id = "E2XXXXXXXXXX"

hugo-s3-demo-infra/
└── prod/
    ├── versions.tf
    ├── backend.tf
    ├── variables.tf
    ├── main.tf
    ├── iam.tf
    ├── sns.tf
    ├── cloudwatch.tf
    ├── budgets.tf
    ├── outputs.tf
    └── logs.tf      ← 新規作成

# ===========================================
# ログ用S3バケット
# ===========================================
resource "aws_s3_bucket" "logs" {
  bucket = "${var.project_name}-logs-${random_id.suffix.hex}"

  tags = {
    Name = "${var.project_name}-logs"
  }
}

# パブリックアクセスブロック
resource "aws_s3_bucket_public_access_block" "logs" {
  bucket                  = aws_s3_bucket.logs.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

# CloudFrontからのログ書き込みを許可するバケットポリシー
resource "aws_s3_bucket_policy" "logs" {
  bucket = aws_s3_bucket.logs.id

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Sid    = "AllowCloudFrontLogs"
        Effect = "Allow"
        Principal = {
          Service = "cloudfront.amazonaws.com"
        }
        Action   = "s3:PutObject"
        Resource = "${aws_s3_bucket.logs.arn}/cloudfront/*"
        Condition = {
          StringEquals = {
            "AWS:SourceArn" = aws_cloudfront_distribution.main.arn
          }
        }
      }
    ]
  })
}

# ログのライフサイクル設定（90日で削除）
resource "aws_s3_bucket_lifecycle_configuration" "logs" {
  bucket = aws_s3_bucket.logs.id

  rule {
    id     = "delete-old-logs"
    status = "Enabled"

    expiration {
      days = 90
    }

    filter {
      prefix = "cloudfront/"
    }
  }
}

# バケット所有者の強制（ACL無効化）
resource "aws_s3_bucket_ownership_controls" "logs" {
  bucket = aws_s3_bucket.logs.id

  rule {
    object_ownership = "BucketOwnerEnforced"
  }
}

output "logs_bucket_name" {
  value = aws_s3_bucket.logs.bucket
}

resource "aws_s3_bucket_lifecycle_configuration" "logs" {
  rule {
    expiration {
      days = 90
    }
  }
}

resource "aws_s3_bucket_ownership_controls" "logs" {
  rule {
    object_ownership = "BucketOwnerEnforced"
  }
}

resource "aws_cloudfront_distribution" "main" {
  # ...既存の設定...

  # ログ設定を追加
  logging_config {
    bucket          = aws_s3_bucket.logs.bucket_regional_domain_name
    prefix          = "cloudfront/"
    include_cookies = false
  }

  # ...既存の設定...
}

terraform apply

aws s3 ls s3://hugo-s3-demo-logs/cloudfront/ --recursive | head -10

2025-12-04 09:49:29  673 cloudfront/E2XXXXXXXXXX.2025-12-04-00.29cc3bb9.gz
2025-12-04 13:49:30  580 cloudfront/E2XXXXXXXXXX.2025-12-04-04.74d28fa3.gz

# ===========================================
# Athenaクエリ結果用S3バケット
# ===========================================
resource "aws_s3_bucket" "athena_results" {
  bucket = "${var.project_name}-athena-results-${random_id.suffix.hex}"

  tags = {
    Name = "${var.project_name}-athena-results"
  }
}

resource "aws_s3_bucket_public_access_block" "athena_results" {
  bucket                  = aws_s3_bucket.athena_results.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

# クエリ結果は7日で削除
resource "aws_s3_bucket_lifecycle_configuration" "athena_results" {
  bucket = aws_s3_bucket.athena_results.id

  rule {
    id     = "delete-query-results"
    status = "Enabled"

    expiration {
      days = 7
    }

    filter {
      prefix = "query-results/"
    }
  }
}

# ===========================================
# Athena Workgroup
# ===========================================
resource "aws_athena_workgroup" "main" {
  name = "${var.project_name}-workgroup"

  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = true

    result_configuration {
      output_location = "s3://${aws_s3_bucket.athena_results.bucket}/query-results/"
    }
  }

  tags = {
    Name = "${var.project_name}-workgroup"
  }
}

# ===========================================
# Glue Database
# ===========================================
resource "aws_glue_catalog_database" "logs" {
  name = replace("${var.project_name}_logs", "-", "_")
}

# ===========================================
# Glue Table（CloudFrontログ用）
# ===========================================
resource "aws_glue_catalog_table" "cloudfront_logs" {
  name          = "cloudfront_logs"
  database_name = aws_glue_catalog_database.logs.name

  table_type = "EXTERNAL_TABLE"

  parameters = {
    "skip.header.line.count" = "2"
    "EXTERNAL"               = "TRUE"
  }

  storage_descriptor {
    location      = "s3://${aws_s3_bucket.logs.bucket}/cloudfront/"
    input_format  = "org.apache.hadoop.mapred.TextInputFormat"
    output_format = "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"

    ser_de_info {
      serialization_library = "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"
      parameters = {
        "field.delim"            = "\t"
        "serialization.format"   = "\t"
      }
    }

    # CloudFrontログの33カラム定義
    columns {
      name = "date"
      type = "date"
    }
    columns {
      name = "time"
      type = "string"
    }
    columns {
      name = "x_edge_location"
      type = "string"
    }
    columns {
      name = "sc_bytes"
      type = "bigint"
    }
    columns {
      name = "c_ip"
      type = "string"
    }
    columns {
      name = "cs_method"
      type = "string"
    }
    columns {
      name = "cs_host"
      type = "string"
    }
    columns {
      name = "cs_uri_stem"
      type = "string"
    }
    columns {
      name = "sc_status"
      type = "int"
    }
    columns {
      name = "cs_referer"
      type = "string"
    }
    columns {
      name = "cs_user_agent"
      type = "string"
    }
    columns {
      name = "cs_uri_query"
      type = "string"
    }
    columns {
      name = "cs_cookie"
      type = "string"
    }
    columns {
      name = "x_edge_result_type"
      type = "string"
    }
    columns {
      name = "x_edge_request_id"
      type = "string"
    }
    columns {
      name = "x_host_header"
      type = "string"
    }
    columns {
      name = "cs_protocol"
      type = "string"
    }
    columns {
      name = "cs_bytes"
      type = "bigint"
    }
    columns {
      name = "time_taken"
      type = "float"
    }
    columns {
      name = "x_forwarded_for"
      type = "string"
    }
    columns {
      name = "ssl_protocol"
      type = "string"
    }
    columns {
      name = "ssl_cipher"
      type = "string"
    }
    columns {
      name = "x_edge_response_result_type"
      type = "string"
    }
    columns {
      name = "cs_protocol_version"
      type = "string"
    }
    columns {
      name = "fle_status"
      type = "string"
    }
    columns {
      name = "fle_encrypted_fields"
      type = "int"
    }
    columns {
      name = "c_port"
      type = "int"
    }
    columns {
      name = "time_to_first_byte"
      type = "float"
    }
    columns {
      name = "x_edge_detailed_result_type"
      type = "string"
    }
    columns {
      name = "sc_content_type"
      type = "string"
    }
    columns {
      name = "sc_content_len"
      type = "bigint"
    }
    columns {
      name = "sc_range_start"
      type = "bigint"
    }
    columns {
      name = "sc_range_end"
      type = "bigint"
    }
  }
}

# ===========================================
# Outputs
# ===========================================
output "athena_workgroup" {
  value = aws_athena_workgroup.main.name
}

output "athena_database" {
  value = aws_glue_catalog_database.logs.name
}

output "athena_table" {
  value = aws_glue_catalog_table.cloudfront_logs.name
}

output "athena_results_bucket" {
  value = aws_s3_bucket.athena_results.bucket
}

resource "aws_athena_workgroup" "main" {
  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = true

    result_configuration {
      output_location = "s3://${aws_s3_bucket.athena_results.bucket}/query-results/"
    }
  }
}

resource "aws_glue_catalog_database" "logs" {
  name = replace("${var.project_name}_logs", "-", "_")
}

terraform apply

SELECT * FROM hugo_s3_demo_logs.cloudfront_logs LIMIT 10;

SELECT 
  cs_uri_stem AS path,
  COUNT(*) AS views
FROM hugo_s3_demo_logs.cloudfront_logs
WHERE sc_status = 200
  AND cs_uri_stem NOT LIKE '%.js'
  AND cs_uri_stem NOT LIKE '%.css'
  AND cs_uri_stem NOT LIKE '%.png'
  AND cs_uri_stem NOT LIKE '%.jpg'
  AND cs_uri_stem NOT LIKE '%.ico'
GROUP BY cs_uri_stem
ORDER BY views DESC
LIMIT 10;

SELECT 
  x_edge_result_type AS result_type,
  COUNT(*) AS count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM hugo_s3_demo_logs.cloudfront_logs
GROUP BY x_edge_result_type
ORDER BY count DESC;

SELECT 
  x_edge_location AS edge,
  COUNT(*) AS requests
FROM hugo_s3_demo_logs.cloudfront_logs
GROUP BY x_edge_location
ORDER BY requests DESC
LIMIT 10;

SELECT 
  date,
  COUNT(*) AS requests
FROM hugo_s3_demo_logs.cloudfront_logs
GROUP BY date
ORDER BY date DESC
LIMIT 30;

SELECT 
  sc_status AS status,
  COUNT(*) AS count
FROM hugo_s3_demo_logs.cloudfront_logs
GROUP BY sc_status
ORDER BY count DESC;