使用 GROUP BY 優化 AWS Athena 查詢
分享一個最近使用 AWS Athena 進行大型資料集查詢時所用到的 SQL 查詢優化小技巧。
AWS Athena 是一個無伺服器的互動式查詢服務，讓使用者可以透過標準 SQL 語法直接分析儲存在 Amazon S3 中的資料，無需預先配置或管理任何基礎設施。Athena 基於開源的 Trino 分散式 SQL 查詢引擎，原生支援多種資料格式，包括 CSV、JSON 和 Parquet 等。
將資料儲存在 S3，然後使用 AWS Glue 產出資料表的 Schema 後，就可以使用 Athena 對資料進行查詢了。
分析雲端網路流量的來源與目的地
我們最近需要分析雲端網路流量的來源與目的地分布，並識別最大的流量流向。資料來源為 Flow Logs，資料量級為 TB，包含數百億筆記錄。因為 Flow Logs 的流量資料只有來源 IP 與 目的地 IP 等比較簡單的資訊，所以無法很直觀的看出流量是從哪裡到哪裡。
我們希望可以幫來源 IP 與目的地 IP 標註以下的資訊：
- 屬於哪個雲端廠商？
- 在哪個地區？
- 擁有者是誰？屬於哪個 AWS Account 或是 Azure Subscription？
- 在哪個 VPC 或是 VNet 底下？
我們已經有公司內部每個 VPC 與 VNet 的詳細資訊，其中包含 CIDR（Classless Inter-Domain Routing）。所以只要判斷 IP 在哪個 CIDR 底下，就能知道這個 IP 的詳細資訊。搞清楚需求之後，查詢寫起來就很容易了。
先 Show 出原始效能不佳的查詢：
-- 先整合 VPC 和 VNet 的雲端網路資訊
with cloud_network as (
select *
from aws_vpc
union all
select *
from azure_vnet
)
-- 查詢流量來源和目的地的雲端網路資訊
select
-- 來源 IP 的網路資訊，包含網路類型、擁有者、區域、網路 ID 和 CIDR
source_network.vendor as source_vendor,
source_network.owner as source_owner,
source_network.region as source_region,
source_network.id as source_network_id,
source_network.cidr as source_cidr,
flow_logs.source_ip as source_ip,
-- 目的地 IP 的網路資訊，包含網路類型、擁有者、區域、網路 ID 和 CIDR
destination_network.vendor as destination_vendor,
destination_network.owner as destination_owner,
destination_network.region as destination_region,
destination_network.id as destination_network_id,
destination_network.cidr as destination_cidr,
flow_logs.destination_ip as destination_ip
-- 有上百億筆流量資料的 flow_logs 表單
from flow_logs
-- 透過 IP 位址對應到雲端網路資訊
-- Athena 提供的 contains 函式，可以用來判斷一個 IP 位址是否屬於某個 CIDR 範圍
-- 根據 CIDR 範圍，找到對應的雲端網路資訊
left join cloud_network as source_network on contains(
source_network.cidr,
cast(flow_logs.source_ip as IPADDRESS)
) = true
left join cloud_network as destination_network on contains(
destination_network.cidr,
cast(flow_logs.destination_ip as IPADDRESS)
) = true簡單說明我的查詢邏輯：
- 從
flow_logs表單取得每一筆流量的資料，這些資料缺乏 IP 的詳細資訊。 - 使用
source_ip和destination_ip與cloud_network.cidr進行關聯查詢。 - 透過 CIDR 範圍查找
source_ip與destination_ip所對應的雲端網路資訊。
查詢寫起來是很容易，但我還是忽略了幾個問題點，導致原始查詢效能極差，無法在合理時間內完成執行。我分析了幾個效能瓶頸：
- 資料量過大：數百億筆 Flow Logs 記錄需要逐一處理。
- 函式執行成本：
contains()函式需要對每筆資料執行 IP 範圍檢查，計算成本相當高昂。 - 重複計算：相同的流量重複執行相同的網路資訊查詢。
使用 GROUP BY 優化大量資料查詢
優化策略是使用 GROUP BY 預先聚合相同 source_ip 和 destination_ip 的流量，減少後續 JOIN 操作的資料量。
優化後的查詢語法：
with cloud_network as (
select *
from aws_vpc
union all
select *
from azure_vnet
), filtered_flow as (
-- 根據 source_ip 與 destination_ip 去除重複的流量
-- 並計算相同的流量總共出現幾次
select
source_ip,
destination_ip,
count(*) as connection_count
from flow_logs
group by source_ip, destination_ip
)
select
source_network.vendor as source_vendor,
source_network.owner as source_owner,
source_network.region as source_region,
source_network.id as source_network_id,
source_network.cidr as source_cidr,
flow_logs.source_ip as source_ip,
destination_network.vendor as destination_vendor,
destination_network.owner as destination_owner,
destination_network.region as destination_region,
destination_network.id as destination_network_id,
destination_network.cidr as destination_cidr,
flow_logs.destination_ip as destination_ip,
-- 該流量的總數
flow_logs.connection_count as connection_count
-- 使用過濾過的流量資料
from filtered_flow as flow_logs
left join cloud_network as source_network on contains(
source_network.cidr,
cast(flow_logs.source_ip as IPADDRESS)
) = true
left join cloud_network as destination_network on contains(
destination_network.cidr,
cast(flow_logs.destination_ip as IPADDRESS)
) = true說明我的優化方式：
filtered_flow中間表透過GROUP BY將相同的來源 IP 與目的地 IP 的流量進行聚合。- 統計每種流量的出現次數，也就是
connection_count。 - 將數百億筆原始資料縮減至數萬筆組合。
- 大幅降低後續
JOIN操作的計算複雜度，查詢效能顯著提升。
此優化技巧特別適用於需要對大量重複資料進行關聯查詢的場景。透過預先聚合去重，可以將查詢時間從數小時縮短至數分鐘。我們原本就有將 Athena 的查詢時間上限延展到 3 個小時（預設為 30 分鐘），但優化前的查詢還是會超時，優化後只需要 5 分鐘左右就能執行完畢，效果顯著。
這其實也是一般資料庫查詢常用到的小技巧。
Athena 的官方文件還有提到多種優化技巧，想了解更多的朋友不妨點擊連結看看還有什麼可以減少查詢時間的方式。
