Quartz 5

Athena 查詢 Production ALB Logs 完整指南

23 min read

Athena 查詢 Production ALB Logs 完整指南

更新日期：2026-07-27

目的

這份手冊用來獨立完成：

  1. 確認 Production ALB logs 是否有 90 天資料。
  2. 使用 Athena SQL 統計各 API 的 7、30、90 天呼叫量。
  3. 區分 GET、POST、OPTIONS、狀態碼、target group 與 listener rule。
  4. 找出仍有正式流量但尚未搬移的 legacy API。
  5. 找出 90 天零流量、可進一步評估不搬的 API。
  6. 控制 Athena 掃描量、費用與敏感資料輸出。

這份手冊不修改 Load Balancer、不切換流量，也不修改 S3 原始 ALB logs。

快速使用地圖

第一次操作時依序閱讀：

  1. 第四節：把權限申請文字交給管理員。
  2. 第五節：權限開通後確認 workgroup、database、table。
  3. 第六節：只有找不到既有 table 時，才處理建表。
  4. 第九節：先跑一天並確認 Data scanned。
  5. 第十一節：確認 90 天資料是否存在。
  6. 第十三節：一次產出 7、30、90 天流量。
  7. 第十八、十九節：匯出 CSV 並和程式 inventory 合併。

遇到錯誤直接看第二十一節；每次重跑前使用第二十三節 checklist。

一、先理解 Athena

Athena 是直接查詢 S3 檔案的 Serverless SQL 服務。

S3 原始 ALB .log.gz
        ↓
Glue Data Catalog table
只保存欄位 schema、S3 location、partition 規則
        ↓
Athena SELECT
        ↓
S3 query result

Athena 不是另一套資料庫，不會先把 ALB logs 搬進 Athena。

SELECT 不會修改來源 log，但每次查詢會：

  • 讀取 S3 source objects。
  • 產生 Athena 掃描費用。
  • 把 query result 寫入指定的 S3 result location。

CREATE TABLE 只會建立 Glue metadata；CTAS、INSERT、UNLOAD 等操作可能另外寫入 S3。本流程預設只允許 CREATE EXTERNAL TABLE 與 SELECT。

二、目前已確認的實際環境

項目ProductionStaging
AWS profiledefaultdefault
Regionap-northeast-1ap-northeast-1
ALB namepro360-apiALB-PRO360-Staging
ALB resource idapp/pro360-api/d3898ad14c394f67app/ALB-PRO360-Staging/22d9e3275ff14d23
S3 bucketpro360-alb-logs待另行確認
S3 prefixweb待另行確認

Production access log：

enabled = true
bucket  = pro360-alb-logs
prefix  = web

實際驗證：

  • 2026-07-27 找到 240 個 pro360-api log objects。
  • 已下載一個約 741 KB 的 gzip log。
  • 已成功解析 request method 與 URL path。
  • 同一個 web prefix 也包含 ALB-PRO360，因此查詢必須限制 Production ALB resource id。
  • 90 天歷史資料是否完整仍待確認。

目前權限狀態：

ProfileIAM userAthena
defaultmattAccessDenied
meitemei-teAccessDenied
meitehk另一個 HK 帳號不使用

三、固定安全規則

每次 Athena 查詢都遵守：

  1. 必須有 day 日期條件。
  2. 必須限制 elb = app/pro360-api/d3898ad14c394f67。
  3. 第一次先查一天，確認 Data scanned 後才擴大到 7、30、90 天。
  4. OPTIONS 分開統計，不混入 business request。
  5. 不輸出 query string。
  6. 原始 URL、IP、ARN、user agent 明細不放進 git。
  7. 不使用 DROP、DELETE、INSERT、UPDATE、CTAS。
  8. LIMIT 不是成本控制；沒有 day filter 時仍可能掃描大量資料。
  9. 使用有 per-query data usage limit 的專用 workgroup。

四、管理員一次性開通

4.1 需要什麼

這次以「可獨立完成 ALB log 分析工作」為目標。權限分成三層，不要全部混成一次申請：

管理員必做：

  • 掛載 AWS managed policy AmazonAthenaFullAccess。
  • Production ALB log S3 prefix 的 List/Get 權限。

開通後可自行處理：

  • 建立名稱符合 aws-athena-query-results-* 的 result bucket。
  • 建立與調整 Athena workgroup、scan limit。
  • 建立與維護 Glue database、table、partition。

條件式處理：

  • 只有 S3 location 受 Lake Formation 管理，才補 Lake Formation grants。
  • 只有 source/result 使用 customer-managed KMS key，才補 KMS 權限。
  • 只有使用自訂名稱的 result bucket，才另補 result bucket policy。

AmazonAthenaFullAccess 已包含：

  • athena 全功能。
  • Glue database/table/partition 的 create、read、update、delete。
  • lakeformation:GetDataAccess。
  • CloudWatch query metrics 與 alarm 相依權限。
  • 名稱符合 aws-athena-query-results-* 的結果 bucket 基本權限。

它不會自動提供 Production ALB source object 的 s3:GetObject；自訂名稱的 result bucket 也要另外授權。

4.2 權限申請文字

可直接交給 AWS 管理員：

目的：
獨立建立與維護 Athena/Glue ALB log 分析環境，
查詢 pro360-api 的 90 天 ALB access logs，
產生並更新 API migration 流量盤點。
 
Region：
ap-northeast-1
 
Source：
s3://pro360-alb-logs/web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1/
 
管理員必做：
1. 掛載 AWS managed policy：
   arn:aws:iam::aws:policy/AmazonAthenaFullAccess
2. Source S3 prefix：
   ListBucket、GetBucketLocation、GetObject。
 
開通後由 matt 自行處理：
1. 建立 aws-athena-query-results-* result bucket。
2. 建立專用 workgroup 並設定 per-query scan limit。
3. 建立 Athena/Glue database、table 與 partition projection。
 
只有確認環境有使用時才處理：
1. 若 S3 location 由 Lake Formation 管理：
   - catalog/database：DESCRIBE
   - database：CREATE_TABLE、ALTER
   - table：SELECT、DESCRIBE、ALTER、DROP
   - source location：DATA_LOCATION_ACCESS
2. 若 source/result 使用 customer-managed KMS key：
   kms:DescribeKey、kms:Decrypt、kms:Encrypt、
   kms:GenerateDataKey、kms:ReEncrypt*
3. 若使用非 aws-athena-query-results-* 的自訂 result bucket，
   才另給該 bucket/prefix 的 List/Get/Put/Delete。
 
不需要：
ELB 修改、IAM 使用者/角色管理、S3 source Put/Delete。

另外建立必要的 Source S3 policy；以下 <ACCOUNT_ID> 由管理員替換：

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListAlbLogSource",
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::pro360-alb-logs",
      "Condition": {
        "StringLike": {
          "s3:prefix": [
            "web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1",
            "web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1/*"
          ]
        }
      }
    },
    {
      "Sid": "ReadAlbLogSource",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject"
      ],
      "Resource": "arn:aws:s3:::pro360-alb-logs/web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1/*"
    }
  ]
}

Source S3 只允許 List/Get。標準 aws-athena-query-results-* bucket 的建立與結果讀寫，已由 AmazonAthenaFullAccess 處理。

若組織有 SCP、permission boundary、S3 bucket policy、KMS key policy 或 Lake Formation explicit deny，IAM Allow 不會覆蓋這些限制。管理員需一次檢查這五層，不能只確認 IAM user policy。

4.3 Workgroup 建議

建議建立專用 workgroup，例如：

pro360-alb-analysis

設定：

  • Enforce workgroup configuration。
  • 固定 query result S3 location。
  • 開啟 result encryption。
  • 第一次設定較小的 per-query scan limit。
  • 看過一天實際 Data scanned 後再調整 90 天上限。
  • 開啟 CloudWatch query metrics。

4.4 管理員 AWS Console 操作

以下操作都先確認右上角 AWS account 是正式台灣帳號，Region 是 Tokyo / ap-northeast-1。

A. 掛載 AmazonAthenaFullAccess

  1. 開啟 IAM Console。
  2. 左側選 Users。
  3. 選使用者 matt。
  4. 開啟 Permissions。
  5. 選 Add permissions。
  6. 選 Attach policies directly。
  7. 搜尋 AmazonAthenaFullAccess。
  8. 勾選後選 Next，再選 Add permissions。
  9. 回到 matt 的 Permissions，確認 policy 已出現。
  10. 同頁檢查 Permissions boundary；若存在，確認它允許 Athena、Glue、S3、Lake Formation 與 KMS。

若公司統一用 IAM group 或 role，將同樣 policy 掛到 group/role，再讓 matt 使用該身分。

B. 準備 Athena result bucket

掛上 AmazonAthenaFullAccess 後，本段可由 matt 自行完成；不必要求管理員先建立。

  1. 開啟 S3 Console。
  2. 先找是否已有 Tokyo Region 的 Athena result bucket。
  3. 若沒有，選 Create bucket。
  4. 建議名稱：aws-athena-query-results-<ACCOUNT_ID>-ap-northeast-1。
  5. Region 選 ap-northeast-1。
  6. 保持 Block all public access。
  7. 開啟 default encryption；SSE-S3 最簡單，SSE-KMS 則繼續做下面 KMS 步驟。
  8. 建議使用 prefix：pro360-alb-analysis/。
  9. 建立 lifecycle rule，只清理 pro360-alb-analysis/，例如 90 天後刪除 result。

然後建立前述自訂 S3 policy：

  1. IAM Console 左側選 Policies。
  2. 選 Create policy。
  3. 選 JSON。
  4. 貼上 4.2 的 S3 policy。
  5. 將 ACCOUNT_ID 換成實際值。
  6. 選 Next。
  7. Policy name 建議 Pro360AlbAthenaS3Access。
  8. 建立後回到 Users > matt > Permissions > Add permissions。
  9. 掛上 Pro360AlbAthenaS3Access。

C. 建立專用 Athena workgroup

掛上 AmazonAthenaFullAccess 後，本段也可由 matt 自行完成。

  1. 開啟 Athena Console。
  2. 左側選 Workgroups。
  3. 選 Create workgroup。
  4. Name 填 pro360-alb-analysis。
  5. Analytics engine 選 Athena SQL。
  6. Query result location 填：
    s3://<ATHENA_RESULT_BUCKET>/pro360-alb-analysis/
  7. 開啟 Override client-side settings / Enforce workgroup configuration。
  8. 開啟 query result encryption。
  9. 開啟 Publish query metrics to CloudWatch。
  10. 設定 per-query data usage control；初始可設 50 GB，實測一天與 90 天掃描量後再調整。
  11. 建立 workgroup。
  12. 進入 Query editor 時切換到 pro360-alb-analysis，不使用 primary。

使用 workgroup 強制 result location，可以避免 Console、CLI 使用不同輸出位置。

D. 檢查 Lake Formation

  1. 開啟 Lake Formation Console。
  2. 左側找 Data lake locations。
  3. 搜尋 pro360-alb-logs 或對應 S3 ARN。
  4. 若找不到，表示此 location 未受 Lake Formation location registration 管理，可跳過本段。
  5. 若有註冊，進入 Permissions > Data permissions > Grant。
  6. Principal 選 IAM user matt。
  7. 若 pro360_log_analysis 尚未建立，在 Catalog 層授予 CREATE_DATABASE 與 DESCRIBE。
  8. 若 database 已存在，對 pro360_log_analysis 授予 Super；不需要 Grantable permissions，除非 matt 要再授權別人。
  9. 到 Permissions > Data locations > Grant。
  10. Principal 選 matt。
  11. Location 選包含 pro360-alb-logs 的已註冊位置。
  12. 授予 DATA_LOCATION_ACCESS。
  13. 若分析既有 table，再對該 table 或 database 下所有 tables 授予 SELECT、DESCRIBE。

IAM policy 和 Lake Formation grant 是兩層檢查；只完成其中一層仍可能 AccessDenied。

E. 檢查 KMS

  1. 在 Source bucket 與 Result bucket 的 Properties 查看 Default encryption。
  2. 若任一 bucket 使用 AWS KMS key，記下 Key ARN。
  3. 開啟 KMS Console。
  4. 選該 customer-managed key。
  5. 在 Key users 或 Key policy 加入 matt 使用的 IAM user/role。
  6. 確認允許 DescribeKey、Decrypt、Encrypt、GenerateDataKey、ReEncrypt。
  7. 若是 AWS managed key，依該服務限制確認是否可直接使用。

F. 管理員最後檢查

管理員完成前一次確認：

  • IAM user/group/role policy。
  • Permissions boundary。
  • AWS Organizations SCP。
  • Source/result S3 bucket policy。
  • KMS key policy。
  • Lake Formation grants。
  • Athena workgroup result location 與 scan limit。

任一層有 explicit Deny，都會蓋過 IAM Allow。

Athena 超過 per-query limit 時會取消查詢，但取消前已掃描的資料仍可能計費。

五、權限開通後的第一輪檢查

5.1 確認身分

aws sts get-caller-identity \
  --profile default \
  --no-cli-pager

確認是台灣正式 AWS account。

5.2 列出 workgroups

aws athena list-work-groups \
  --profile default \
  --region ap-northeast-1 \
  --query 'WorkGroups[].{Name:Name,State:State}' \
  --output table \
  --no-cli-pager

確認管理員指定的 workgroup 是 ENABLED。

5.3 查看 workgroup

export MIG_ATHENA_WORKGROUP='pro360-alb-analysis'
 
aws athena get-work-group \
  --profile default \
  --region ap-northeast-1 \
  --work-group "$MIG_ATHENA_WORKGROUP" \
  --output json \
  --no-cli-pager

確認：

  • ResultConfiguration.OutputLocation。
  • EnforceWorkGroupConfiguration。
  • EncryptionConfiguration。
  • BytesScannedCutoffPerQuery 或管理員設定的 data controls。

5.4 列出 databases

aws athena list-databases \
  --profile default \
  --region ap-northeast-1 \
  --catalog-name AwsDataCatalog \
  --query 'DatabaseList[].Name' \
  --output table \
  --no-cli-pager

5.5 列出 tables

假設 database 名稱是 pro360_log_analysis：

export MIG_ATHENA_DATABASE='pro360_log_analysis'
 
aws athena list-table-metadata \
  --profile default \
  --region ap-northeast-1 \
  --catalog-name AwsDataCatalog \
  --database-name "$MIG_ATHENA_DATABASE" \
  --query 'TableMetadataList[].Name' \
  --output table \
  --no-cli-pager

若已有 ALB table，先使用既有 table，不重複建立。

六、尚無 table 時如何建立

6.1 建議做法

使用 AWS 官方的 ALB access log partition projection DDL：

AWS：Create ALB access log table with partition projection

不要自行縮短官方欄位或刪除 RegexSerDe 最後的 optional pattern。AWS 可能在 ALB log 尾端增加新欄位，官方 pattern 已保留向後相容空間。

6.2 固定替換值

複製官方 DDL 後替換：

官方 placeholder此環境
table namepro360_alb_logs
bucketpro360-alb-logs
prefixweb
account執行 sts get-caller-identity 取得
regionap-northeast-1
partition columnday
day formatyyyy/MM/dd
day range start依 S3 最舊保留日設定

LOCATION：

s3://pro360-alb-logs/web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1/

storage.location.template：

s3://pro360-alb-logs/web/AWSLogs/<ACCOUNT_ID>/elasticloadbalancing/ap-northeast-1/${day}

確認官方 DDL 至少包含下列 partition projection properties；起始日要換成 S3 實際最舊保留日：

projection.enabled = true
projection.day.type = date
projection.day.range = <oldest-yyyy/MM/dd>,NOW
projection.day.format = yyyy/MM/dd
projection.day.interval = 1
projection.day.interval.unit = DAYS
storage.location.template = .../${day}

建議 table：

Database：pro360_log_analysis
Table：pro360_alb_logs
Partition projection：enabled

Partition projection 啟用後，不需要每天 ALTER TABLE ADD PARTITION，也不需要 MSCK REPAIR TABLE。

6.3 建表的影響

CREATE EXTERNAL TABLE：

  • 寫入 Glue schema metadata。
  • 不搬移 S3 log。
  • 不修改 S3 source log。
  • 不修改 ALB。

建表仍是 AWS metadata write，需 reviewer/admin 明確同意。

七、建議使用 Athena Console

第一次操作建議使用 AWS Console：

  1. 登入 AWS Console。
  2. Region 切到 Tokyo / ap-northeast-1。
  3. 開啟 Athena。
  4. 選管理員指定的 workgroup。
  5. Data source 選 AwsDataCatalog。
  6. Database 選 pro360_log_analysis。
  7. Table 選 pro360_alb_logs。
  8. 先跑一天的 sanity query。
  9. 在 Query statistics 查看 Data scanned。
  10. 確認正確後再執行 7、30、90 天 query。

不要一開始就跑 90 天。

八、SQL 固定值

以下 SQL 假設：

Database：pro360_log_analysis
Table：pro360_alb_logs
Production elb：app/pro360-api/d3898ad14c394f67

日期 partition 使用 UTC：

day = yyyy/MM/dd

台灣日期跨 UTC 邊界時，需同時查相鄰 UTC day，再用 timestamp 轉 Asia/Taipei 過濾。

九、第一個低成本查詢

只查一天，確認資料與 parser：

SELECT
    count(*) AS total_requests,
    min(from_iso8601_timestamp(time)) AS first_request_utc,
    max(from_iso8601_timestamp(time)) AS last_request_utc
FROM pro360_log_analysis.pro360_alb_logs
WHERE day = '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67';

執行後記錄：

Query execution ID
Data scanned
Engine version
Workgroup
Result location

若 total_requests = 0：

  • 檢查 day 格式是不是 yyyy/MM/dd。
  • 檢查 elb 使用 slash 格式，不是 object filename 的 dot 格式。
  • 暫時只保留 day filter，查看 DISTINCT elb。
  • 檢查 table LOCATION 是否包含 web/AWSLogs。

十、安全查看欄位

只輸出必要欄位並移除 query string：

SELECT
    from_iso8601_timestamp(time) AS request_time_utc,
    request_verb,
    regexp_extract(
        request_url,
        '^https?://[^/]+([^?]*)',
        1
    ) AS request_path,
    elb_status_code,
    target_status_code,
    regexp_extract(
        target_group_arn,
        'targetgroup/([^/]+)',
        1
    ) AS target_group,
    matched_rule_priority
FROM pro360_log_analysis.pro360_alb_logs
WHERE day = '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67'
LIMIT 20;

這個 LIMIT 只限制輸出，不取代 day filter。

十一、確認 90 天資料是否存在

先查起點、終點與中間日期：

SELECT
    day,
    count(*) AS requests
FROM pro360_log_analysis.pro360_alb_logs
WHERE day IN (
    '2026/04/29',
    '2026/06/01',
    '2026/07/27'
)
  AND elb = 'app/pro360-api/d3898ad14c394f67'
GROUP BY day
ORDER BY day;

判讀：

  • 三天都有：可以繼續做 90 天查詢。
  • 最舊日期為 0：查更晚日期，找出實際 retention 起點。
  • 中間日期為 0：檢查 log delivery 或 table partition 設定。

零筆不能直接推論該日完全無流量。

十二、API path normalization

ALB log 會出現實際 ID：

/quote_bids/change_status/read/2604664734.json
/quote_service_categories/add/quote_service_id:9930/quote_category_id:700.json

盤點時要轉成：

/quote_bids/change_status/read/{id}.json
/quote_service_categories/add/quote_service_id:{id}/quote_category_id:{id}.json

Normalization query：

WITH base AS (
    SELECT
        day,
        time,
        request_verb,
        regexp_extract(
            request_url,
            '^https?://[^/]+([^?]*)',
            1
        ) AS raw_path,
        elb_status_code,
        target_status_code,
        target_group_arn,
        matched_rule_priority
    FROM pro360_log_analysis.pro360_alb_logs
    WHERE day BETWEEN '2026/07/21' AND '2026/07/27'
      AND elb = 'app/pro360-api/d3898ad14c394f67'
      AND request_verb <> 'OPTIONS'
),
normalized AS (
    SELECT
        *,
        regexp_replace(
            regexp_replace(
                regexp_replace(
                    raw_path,
                    '/[0-9]+(/|\.json|$)',
                    '/{id}$1'
                ),
                '([A-Za-z_]+:)[0-9]+',
                '$1{id}'
            ),
            '/[0-9a-fA-F]{8}-[0-9a-fA-F-]{27}([/]|$)',
            '/{uuid}$1'
        ) AS normalized_path
    FROM base
)
SELECT
    request_verb,
    normalized_path,
    count(*) AS calls,
    max(from_iso8601_timestamp(time)) AS last_seen_utc
FROM normalized
GROUP BY request_verb, normalized_path
ORDER BY calls DESC;

這只是通用 normalization。匯出後仍需抽查：

  • CakePHP named parameters。
  • 非數字 business keys。
  • locale/version path。
  • 空 segment 與多餘斜線。
  • 同一路徑但不同 method。

不要把會改變行為的 named parameter 整段刪掉。

十三、一次產出 7、30、90 天統計

以下範例以 2026-07-27 為統計終點。執行時更新日期：

WITH base AS (
    SELECT
        day,
        time,
        request_verb,
        regexp_extract(
            request_url,
            '^https?://[^/]+([^?]*)',
            1
        ) AS raw_path,
        elb_status_code,
        target_status_code,
        target_group_arn,
        matched_rule_priority
    FROM pro360_log_analysis.pro360_alb_logs
    WHERE day BETWEEN '2026/04/29' AND '2026/07/27'
      AND elb = 'app/pro360-api/d3898ad14c394f67'
),
normalized AS (
    SELECT
        *,
        regexp_replace(
            regexp_replace(
                regexp_replace(
                    raw_path,
                    '/[0-9]+(/|\.json|$)',
                    '/{id}$1'
                ),
                '([A-Za-z_]+:)[0-9]+',
                '$1{id}'
            ),
            '/[0-9a-fA-F]{8}-[0-9a-fA-F-]{27}([/]|$)',
            '/{uuid}$1'
        ) AS normalized_path
    FROM base
)
SELECT
    request_verb,
    normalized_path,
    sum(
        CASE
            WHEN day BETWEEN '2026/07/21' AND '2026/07/27'
            THEN 1 ELSE 0
        END
    ) AS calls_7d,
    sum(
        CASE
            WHEN day BETWEEN '2026/06/28' AND '2026/07/27'
            THEN 1 ELSE 0
        END
    ) AS calls_30d,
    count(*) AS calls_90d,
    count_if(elb_status_code BETWEEN 200 AND 299) AS status_2xx,
    count_if(elb_status_code BETWEEN 400 AND 499) AS status_4xx,
    count_if(elb_status_code BETWEEN 500 AND 599) AS status_5xx,
    max(from_iso8601_timestamp(time)) AS last_seen_utc,
    max_by(
        regexp_extract(
            target_group_arn,
            'targetgroup/([^/]+)',
            1
        ),
        from_iso8601_timestamp(time)
    ) AS latest_target_group,
    max_by(
        matched_rule_priority,
        from_iso8601_timestamp(time)
    ) AS latest_rule_priority
FROM normalized
GROUP BY request_verb, normalized_path
ORDER BY calls_90d DESC;

注意：

  • 上面會依 method 分組，因此 OPTIONS 會是獨立的列。
  • 只要 business request 時，在 base CTE 加上 AND request_verb <> 'OPTIONS'。
  • 90 天日期要以 UTC day 計算，若以台灣日界線為準需加 timestamp filter。
  • Query statistics 的 Data scanned 要記錄。

十四、只看 legacy API

常見 legacy route 不以 /new/v1 開頭。初步篩選：

WITH paths AS (
    SELECT
        request_verb,
        regexp_extract(
            request_url,
            '^https?://[^/]+([^?]*)',
            1
        ) AS request_path
    FROM pro360_log_analysis.pro360_alb_logs
    WHERE day BETWEEN '2026/04/29' AND '2026/07/27'
      AND elb = 'app/pro360-api/d3898ad14c394f67'
      AND request_verb <> 'OPTIONS'
)
SELECT
    request_verb,
    request_path,
    count(*) AS calls
FROM paths
WHERE request_path NOT LIKE '/new/v1/%'
GROUP BY request_verb, request_path
ORDER BY calls DESC;

這只是初步篩選，不代表所有非 /new/v1 都是 getlancer legacy。最終仍需和 legacy/new code inventory 比對。

十五、確認實際導向哪個 Target Group

SELECT
    request_verb,
    regexp_extract(
        request_url,
        '^https?://[^/]+([^?]*)',
        1
    ) AS request_path,
    regexp_extract(
        target_group_arn,
        'targetgroup/([^/]+)',
        1
    ) AS target_group,
    matched_rule_priority,
    count(*) AS calls
FROM pro360_log_analysis.pro360_alb_logs
WHERE day BETWEEN '2026/07/21' AND '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67'
GROUP BY
    request_verb,
    regexp_extract(request_url, '^https?://[^/]+([^?]*)', 1),
    regexp_extract(target_group_arn, 'targetgroup/([^/]+)', 1),
    matched_rule_priority
ORDER BY calls DESC;

這份結果用來交叉驗證 listener-rules.json，不只依賴規則設定推測流量。

十六、Latency 與尖峰

p50/p95/p99 target latency

SELECT
    approx_percentile(target_processing_time, 0.50) AS p50_seconds,
    approx_percentile(target_processing_time, 0.95) AS p95_seconds,
    approx_percentile(target_processing_time, 0.99) AS p99_seconds
FROM pro360_log_analysis.pro360_alb_logs
WHERE day BETWEEN '2026/07/21' AND '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67'
  AND target_processing_time >= 0;

Peak requests/minute

SELECT
    date_trunc(
        'minute',
        from_iso8601_timestamp(time)
    ) AS minute_utc,
    count(*) AS requests
FROM pro360_log_analysis.pro360_alb_logs
WHERE day BETWEEN '2026/07/21' AND '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67'
GROUP BY 1
ORDER BY requests DESC
LIMIT 100;

容量評估應用 peak requests/minute 或 RPS，不用 API 數量判斷是否加機器。

十七、Caller 分類

User-Agent 分類只能作近似判斷：

SELECT
    CASE
        WHEN lower(user_agent) LIKE '%mozilla/%' THEN 'web'
        WHEN regexp_like(
            lower(user_agent),
            'alamofire|cfnetwork|ios'
        ) THEN 'ios'
        WHEN regexp_like(
            lower(user_agent),
            'okhttp|android'
        ) THEN 'android'
        ELSE 'internal_or_unknown'
    END AS caller,
    count(*) AS calls
FROM pro360_log_analysis.pro360_alb_logs
WHERE day BETWEEN '2026/07/21' AND '2026/07/27'
  AND elb = 'app/pro360-api/d3898ad14c394f67'
GROUP BY 1
ORDER BY calls DESC;

分類規則要用實際 User-Agent 樣本校正，不可直接當絕對結論。

十八、匯出 CSV

Athena Console：

  1. 等 Query status 變成 Succeeded。
  2. 先記錄 Query execution ID 與 Data scanned。
  3. 在 Results 選 Download CSV。
  4. 存到非 git evidence 資料夾。

建議檔名：

production_api_traffic_2026-04-29_to_2026-07-27.csv

同時建立摘要：

Environment
ALB resource id
UTC date range
Athena database/table
Workgroup
Query execution ID
Data scanned
Query result location
Normalization version
Known limitations

十九、如何產生 Migration Gap

Athena 只回答 Production 流量，不知道程式是否已搬。

最後要合併：

Legacy code inventory
+ New code/route inventory
+ Production listener rules
+ Athena production traffic
= api_gap_inventory.csv

狀態：

狀態判斷
MIGRATED_LIVENew code + production rule/target + production evidence
MIGRATED_NOT_LIVENew code 存在，但 production 仍走 legacy
LEGACY_ACTIVE_GAPLegacy 有 90 天流量，但 new code/route 缺少
LEGACY_DORMANT_CANDIDATE90 天觀測為 0，仍待其他 caller 確認
NO_MIGRATION_APPROVEDReviewer/product 明確核准不搬

零流量不能直接等於不搬。

二十、成本控制

Athena on-demand SQL 主要依 Data scanned 計價。AWS 公開範例使用每 TB 5 USD。

成本估算：

Estimated query cost
= Data scanned TB × Athena region price per TB

降低成本：

  • 一定加 day filter。
  • 第一次只查一天。
  • 使用 partition projection。
  • 使用 gzip source。
  • 不重複執行相同大查詢。
  • 使用 query result reuse。
  • Workgroup 設 per-query limit。
  • 將大範圍統計合併成一次 90 天 scan，不分別重掃 7、30、90 天。

目前 web prefix 是多 ALB 共用。elb filter 對正確性是必要的，但資料仍存放在共用日期目錄，實際 Data scanned 以 Athena Query statistics 為準。

除了 Athena，還有：

  • S3 LIST/GET request。
  • Query result S3 storage/request。
  • Glue Data Catalog。
  • KMS，若 result/source 使用 KMS encryption。

二十一、常見錯誤

AccessDenied：athena:ListDatabases

Athena metadata 權限缺少。目前 default、meite 都是此狀態。

AccessDenied：Glue

已有 Athena 權限，但缺 Glue GetDatabase/GetTable/GetPartitions。

Unable to verify/create output bucket

Query result location 未設定，或缺 result bucket 權限。

Query succeeded but returns 0

檢查：

  • day 是 yyyy/MM/dd。
  • elb 使用 app/pro360-api/d3898ad14c394f67。
  • Table LOCATION 包含 web/AWSLogs。
  • S3 該 UTC 日期是否有物件。

HIVE_BAD_DATA 或欄位全部 null

RegexSerDe 和目前 ALB log 格式不一致。回到 AWS 官方最新 DDL，不自行刪除尾端 optional pattern。

Data scanned 異常大

通常是：

  • 缺 day filter。
  • Partition projection 設定錯。
  • Table LOCATION 指到過大的 bucket root。
  • 誤以為 LIMIT 可以限制掃描。

日期少一天或多一天

S3 day 與 time 是 UTC。台灣日期查詢需處理 Asia/Taipei +08:00。

同一時間有多個 log files

ALB 每個 node 約每五分鐘各自產生檔案，也可能同一區間產生多個檔。不要用檔案數當 request 數。

二十二、準確性限制

ALB access logs 是 best-effort，適合流量趨勢與 migration evidence，不是財務或計費級完整帳本。

另外要排除：

  • 繞過 pro360-api ALB 的 direct/internal call。
  • 其他 ALB。
  • Webhook、cron、queue。
  • 舊版 App 與季節性流量。
  • URL normalization 誤合併。
  • OPTIONS 和 business request 混算。

90 天為 0 只能標 LEGACY_DORMANT_CANDIDATE。

二十三、每次獨立操作 Checklist

開始前

  • AWS Account/Region 正確。
  • Workgroup 正確且有 scan limit。
  • Database/table 正確。
  • Source/result S3 權限正常。
  • 統計日期已換成需要的 UTC range。

先跑一天

  • total_requests > 0。
  • elb filter 正確。
  • request_path 正常。
  • target_group/rule priority 有值。
  • 記錄 Data scanned。

再跑 90 天

  • 起點、中間、終點都有資料。
  • 正規化抽查完成。
  • OPTIONS 分開。
  • 2xx/4xx/5xx 分開。
  • last_seen 有值。
  • target group/rule 已輸出。

匯出後

  • CSV 存在非 git evidence 資料夾。
  • 保存 Query execution ID。
  • 保存 Data scanned 與 workgroup。
  • 不保存 query string、IP 或 raw URL。
  • 與 legacy/new code inventory 合併。
  • 零流量項目仍標待確認。

二十四、主線執行順序

1. 管理員開 Athena/Glue/S3-result 權限
2. 確認 workgroup 與 scan limit
3. 找既有 database/table
4. 沒有 table 才由管理員建立 partition projection table
5. 跑一天 sanity query
6. 查看 Data scanned
7. 確認 90 天 retention
8. 跑單次 7/30/90 統計
9. 匯出 CSV
10. 合併 code inventory 與 LB rules
11. 產生 remaining API count
12. 安排 migration batches

官方文件

Graph View

Backlinks