Amazon Athena 查詢效能最佳化實務

Athena 查詢效能對於資料分析效率至關重要。除了選擇必要的欄位、使用近似聚合函式和正規表示式等 SQL 最佳化技巧外，善用聯邦查詢功能整合外部資料來源，例如 Amazon Aurora、DynamoDB 和 Redshift 等，能大幅提升資料分析的靈活性。此外，透過 Athena Workgroups 的設定，可以有效管理不同團隊或專案的查詢執行、歷史記錄和儲存的查詢，並透過資料使用控制功能限制掃描資料量，達到成本控管的目的。Workgroups 也允許管理員強制執行查詢結果儲存位置和加密設定，確保資料安全性和符合企業治理規範。

最佳化 Amazon Athena 查詢效能的最佳實踐

Amazon Athena 是一種強大的查詢引擎，能夠對資料湖中的資料執行 SQL 查詢。然而，要充分發揮 Athena 的效能，必須瞭解如何最佳化查詢。本章節將介紹一些最佳實踐，幫助您提高 Athena 查詢的效能。

寫作最佳化 SQL 查詢

撰寫 SQL 查詢的方式會對查詢效能產生重大影響。本文將介紹三個最佳實踐，幫助您最佳化查詢效能。作為資料工程師，建議您與資料分析師和其他使用 Athena 的人員分享這些最佳實踐。

1. 只選擇需要的特定欄位

在探索資料時，通常會執行 SELECT * 查詢來選擇所有欄位。然而，Parquet 是一種根據欄位的檔案格式，這意味著磁碟上的資料是按欄位分組的，而不是按列。當您指定特定的欄位進行查詢時，分析引擎（如 Athena）可以只讀取該欄位的資料。

如果表格有很多欄位，指定重要的欄位可以顯著提高查詢效能。這是因為 Athena 不需要處理所有欄位的資料。由於 Athena 的成本是根據掃描的資料量計算的，因此選擇特定的欄位也可以節省大量成本。

2. 使用近似聚合函式

Presto 資料函式庫引擎（以及 Athena）支援多種函式和運算子，可以在查詢中使用。這些函式和運算子可用於對大型資料集進行計算，例如計算銷售總額、平均訂單數量等。

對於某些計算，可能需要獲得完全準確的結果，例如在正式的財務報告中。然而，在某些情況下，您可能只需要近似值，例如估計昨天存取網站的唯一訪客數量。

對於那些可以容忍結果偏差的場景，Presto 提供了近似聚合函式，這些函式相比完全準確的版本具有顯著的效能改進。

例如，要計算過去 7 天內瀏覽電子商務網站的唯一使用者的大致數量，可以使用 approx_distinct 函式，如下所示：

SELECT 
    approx_distinct(userid)
FROM 
    estore_log
WHERE 
    visit_time > to_iso8601(current_timestamp – interval '7' day)

3. 使用正規表示式代替 like 運算元

常見的方式是使用 like 運算元來選擇符合特定模式的資料列，如下所示：

SELECT 
    category_name, 
    count(category_name)
FROM 
    film_category
WHERE 
    category_name LIKE 'Comedy' OR category_name LIKE 'Drama'
    OR category_name LIKE 'Music' OR category_name LIKE 'New'
GROUP BY 
    category_name

一個替代方案是使用正規表示式進行模式匹配，這既可以簡化陳述式，又可以提高查詢效能。下面的查詢傳回與前一個查詢相同的結果，但使用 regexp_like 運算元，並包含正規表示式語法 (?i) 以使模式匹配不區分大小寫：

SELECT 
    category_name, 
    count(category_name)
FROM 
    film_category
WHERE 
    regexp_like(category_name, '(?i)^comedy|drama|music|new')
GROUP BY 
    category_name

#### 內容解密：

只選擇需要的特定欄位：由於 Parquet 是根據欄位的檔案格式，因此指定特定的欄位可以減少 Athena 需要處理的資料量，從而提高查詢效能和降低成本。
使用近似聚合函式：對於可以容忍結果偏差的場景，使用近似聚合函式（如 approx_distinct）可以顯著提高查詢效能。
使用正規表示式代替 like 運算元：使用 regexp_like 運算元進行模式匹配，可以簡化 SQL 陳述式並提高查詢效能，特別是在需要進行多個比較時。

使用 Amazon Athena Query Federation 聯邦查詢外部資料來源

Athena 不僅允許您使用標準 SQL 語義查詢載入到資料湖中的資料，還具備執行聯邦查詢的能力。聯邦查詢是一項在 2021 年隨著 Athena 查詢引擎 v2 推出的重要功能，能夠對多個資料來源執行查詢。

#### 內容解密：

聯邦查詢：Athena 的聯邦查詢功能允許您跨多個資料來源執行 SQL 查詢，無需將資料載入到資料湖中。這一功能擴充套件了 Athena 的能力，使其成為一個更強大的查詢引擎。

Amazon Athena 的聯邦查詢功能：整合多資料源的查詢解決方案

Amazon Athena 的聯邦查詢（Federated Query）功能允許使用者透過單一 SQL 查詢陳述式來查詢多個外部資料源，無論這些資料源位於不同的資料函式庫引擎還是其他系統中。AWS 於 2019 年 11 月宣佈了 Athena 聯邦查詢的預覽版，這一功能使得 Athena 的查詢能力不僅限於資料湖中的資料，還能擴充套件到外部資料源。

資料湖與外部資料源的整合需求

資料湖的設計目的是收集組織內多個系統的資料並將其集中儲存，從而能夠以新的方式結合資料，為企業創造價值。然而，將組織內的所有資料集都納入資料湖的集中儲存並不總是實際的。對於某些資料集，組織可能不需要保留所有歷史資料，或者資料已經儲存在能夠儲存歷史資料的系統中。在這些場景中，直接查詢原始資料集並動態地將原始資料與資料湖中的資料結合起來可能更為合理。

聯邦查詢的應用場景

如果一個資料集需要被多個團隊頻繁查詢，並且查詢需要傳回大量的資料，那麼將該資料集直接載入到資料湖中可能是最佳選擇。此外，如果需要頻繁查詢一個已經負載較重的系統，可以透過在非峰值時段將資料從該系統載入到資料湖中，以減少對該系統的負載，而不是在峰值時段執行聯邦查詢。

然而，如果是進行即席查詢（ad hoc queries），或者只有少數團隊需要以相對較低的頻率查詢資料，那麼使用 Athena 的聯邦查詢功能來存取這些資料是合理的。許多人已經對 Athena 聯邦查詢進行了效能測試，證明瞭它能夠每秒查詢數萬條記錄。

範例：結合多資料源的查詢

例如，Athena 聯邦查詢可以使資料分析師執行單一 SQL 查詢，結合以下資料集：

儲存在 Amazon S3 中的主客戶資料
儲存在 Amazon Aurora 中的當前訂單資訊
儲存在 Amazon DynamoDB 中的運輸跟蹤資料
儲存在 Amazon Redshift 中的產品目錄資料

SELECT 
    c.customer_id,
    o.order_id,
    d.shipment_status,
    p.product_name
FROM 
    "s3_dataset"."customer" c
JOIN 
    "aurora_dataset"."orders" o ON c.customer_id = o.customer_id
JOIN 
    "dynamodb_dataset"."shipment" d ON o.order_id = d.order_id
JOIN 
    "redshift_dataset"."product" p ON o.product_id = p.product_id;

內容解密：

跨資料源的 JOIN 操作：此 SQL 查詢展示瞭如何結合來自不同資料源的資料，包括 S3、Aurora、DynamoDB 和 Redshift。這種跨資料源的 JOIN 操作使得資料分析師可以在不移動資料的情況下，進行綜合分析。
資料虛擬化的優勢：透過 Athena 的聯邦查詢功能，資料分析師無需將所有資料移動到一個集中式的資料倉儲中，就能夠對分散在不同系統中的資料進行聯合分析，大大提高了資料處理的靈活性和效率。
效能考量：雖然聯邦查詢提供了極大的便利，但在處理大規模資料時，仍需考慮查詢效能和底層資料來源的負載能力。適當的索引設計和查詢最佳化對於確保高效的查詢效能至關重要。

使用預建聯結器和自定義聯結器

Athena 聯邦查詢使用執行在 AWS Lambda 中的程式碼來連線和查詢外部系統中的資料和後設資料。當執行使用連線資料來源的查詢時，Athena 會呼叫相關的 Lambda 函式來讀取後設資料，識別需要讀取的表部分，並啟動多個 Lambda 函式以平行讀取資料。

AWS 開源了多個聯結器，能夠對許多流行的資料來源進行聯邦查詢，包括但不限於：

用於連線到 MySQL、Postgres 和 Redshift 等來源的 JDBC 聯結器。
用於從 Amazon 管理的 NoSQL 資料函式庫讀取資料的 DynamoDB 聯結器。
用於從 Redis 例項讀取資料的 Redis 聯結器。
用於查詢應用程式日誌檔案和指標的 CloudWatch 日誌和 CloudWatch 指標聯結器。
一個與多個 AWS 服務整合的 AWS CMDB 聯結器，能夠對 AWS 資源執行 SQL 查詢。

完整的聯結器列表可以在 AWS Labs Athena Query Federation GitHub 頁面上找到和下載。

除了 AWS 提供的聯結器外，任何人都可以建立自定義聯結器來連線到外部系統。只要能夠從 AWS Lambda 建立到目標系統的網路連線，無論是在本地還是在雲中，都可以建立 Athena 聯邦查詢聯結器。

使用 Amazon Athena Workgroups 管理治理與成本

Amazon Athena 的成本取決於查詢掃描的資料量。本章的第一部分探討瞭如何最佳化資料以減少查詢掃描的資料量，從而降低成本。然而，一些最佳化方法依賴於編寫高效的 SQL 查詢。組織通常擔心使用者可能會意外執行未經最佳化的 SQL 查詢，從而掃描大量資料。因此，組織需要一種方法來控制不同使用者或團隊掃描的資料量。

Athena Workgroups 概述

為瞭解決這些治理問題，AWS 引入了 Athena Workgroups 的概念。Workgroups 是一種資源型別，可實作不同使用者、團隊或系統之間的查詢執行和查詢歷史記錄的分離。在 Athena 主控台中，使用者可以儲存經常執行的查詢，並且還可以檢視他們執行過的歷史查詢清單。然而，這些清單只顯示在特定 Workgroup 中執行的查詢，因此將團隊或專案劃分為不同的 Workgroups 可以確保查詢歷史和儲存的查詢只對相關團隊可見。

對使用者群組實施設定

Athena Workgroups 的主要用途之一是區分不同的 Athena 使用者群組，並為每個群組實施設定。這些設定可以包括每個團隊、應用程式或使用者型別的不同 Workgroups。Workgroups 使管理員能夠為不同的使用者群組、專案或使用案例實施各種設定。預設情況下，每個使用者都可以控制多個設定，但 Workgroups 使管理員能夠覆寫使用者的設定，強制他們使用 Workgroup 設定。

可實施的設定專案

管理員可以為 Workgroup 成員實施以下設定：

查詢結果位置：這是 Athena 查詢結果將寫入的 S3 路徑。如果在 Workgroup 中設定了此路徑並啟用了「覆寫使用者端設定」，則所有在該 Workgroup 中執行的查詢都將使用此位置。這使組織能夠控制 S3 中查詢結果檔案的儲存位置，並對該位置實施嚴格的存取控制選項，以防止未經授權的使用者存取查詢結果。
加密查詢結果：此選項可用於強制加密查詢結果，以協助組織遵守企業安全要求。
指標：您可以選擇將指標傳送到 CloudWatch 日誌，以反映在該 Workgroup 中執行的所有查詢的成功查詢數量、查詢執行時間和掃描的資料量等資訊。
覆寫使用者端設定：如果未啟用此專案，則使用者可以組態自己的查詢結果位置和是否加密查詢結果等設定。因此，必須啟用此設定，以確保查詢結果受到保護並符合企業治理標準。
請求者付費 S3 Bucket：建立 S3 Bucket 時，其中一個選項是組態 Bucket，以便查詢 Bucket 的使用者支付 API 存取成本。預設情況下，Athena 不允許對已組態請求者付費的 Bucket 執行查詢，但您可以啟用此專案以允許此操作。
標籤：您可以提供多個鍵值標籤，以協助進行成本分配或控制對 Workgroup 的存取。例如，您可以為兩個具有不同查詢輸出位置設定的 Workgroup 提供一個包含團隊名稱的標籤，然後透過 IAM 策略為團隊成員提供對標有其團隊名稱的所有 Workgroup 的存取許可權。

實施資料使用控制

由於 Athena 的定價根據掃描的資料量（撰寫本文時的成本為每 TB 掃描資料 5 美元），因此限制掃描的資料量有助於管理成本。為了實作這一點，Athena Workgroups 包含資料使用控制功能，可以實施兩種型別的控制。

每個查詢的資料使用控制

您可以組態每個查詢允許掃描的最大資料量。如果使用者執行查詢並且 Athena 試圖掃描超過允許的資料量，則查詢將被取消。但是，請注意，AWS 帳戶仍將被收取在查詢取消之前掃描的資料量的費用。

範例

假設您有一組相對缺乏 SQL 經驗的使用者，希望為他們提供一個沙盒環境，讓他們可以安全地執行即席查詢。在這種情況下，您可以建立一個名為「sandbox」的 Athena Workgroup，並組態這些使用者存取該 Workgroup。您可以將該 Workgroup 組態為具有每個查詢 100 GB 的限制，例如，這將確保任何單個查詢都不會花費超過 0.50 美元。

內容解密：

在上述範例中，我們使用了 Athena Workgroups 的每查詢資料使用控制功能。這種控制功能對於需要嚴格控制每個查詢掃描資料量的場景非常有用。然而，這種控制是限制性的，因為它會自動取消任何超過指定資料量的查詢。

Amazon Athena 工作群組與查詢管理詳解

Amazon Athena 提供了一個強大的查詢服務，能夠對儲存在 Amazon S3 中的資料執行 SQL 查詢。為了更好地管理和控制查詢，Athena 引入了「工作群組」（Workgroup）的概念。本篇文章將探討 Athena 工作群組的功能、設定方法以及如何使用 Athena 執行 SQL 查詢。

工作群組資料使用控制

Athena 的工作群組允許管理員設定資料使用控制，以限制特定工作群組在指定時間內掃描的資料量。當設定的限制被超過時，Athena 可以透過 Amazon Simple Notification Service（SNS）觸發相關動作，例如傳送通知給管理員或執行特定的程式碼（例如 Lambda 函式）來自動化處理。

設定工作群組資料使用控制的範例

設定一個工作群組的每日資料掃描上限為 3 TB。
組態 SNS 主題，當資料掃描量超過設定的限制時，傳送電子郵件通知管理員。
建立一個 Lambda 函式，當達到設定的限制時自動停用該工作群組，以防止進一步的查詢執行。

實作：建立與設定 Amazon Athena 工作群組

本文將指導如何建立和設定新的 Athena 工作群組，並執行 SQL 查詢。

建立工作群組的步驟

登入 AWS 管理控制檯並存取 Athena 服務。
在左側選單中，點選「Workgroups」以存取工作群組管理頁面。
點選「Create workgroup」並輸入以下資訊：
- 工作群組名稱：例如 datalake-user-sandbox。
- 描述：例如「Sandbox Workgroup for new datalake-users」。
- 查詢結果位置：選擇或建立一個 S3 儲存桶來儲存查詢結果，例如 s3://aws-athena-query-results-dataengbook-xxxxx/datalake-user-sandbox/。
- 加密查詢結果：選擇 SSE-S3 作為加密型別。
- 覆寫客戶端設定：勾選此選項以防止使用者更改查詢結果的位置或加密設定。
設定每個查詢的資料使用限制，例如將資料限制大小設為 10 GB。

切換工作群組與執行查詢

建立並設定好工作群組後，您可以切換到新的工作群組並開始執行 SQL 查詢。

切換工作群組與執行查詢的步驟

在左側選單中，點選「Query editor」。
使用工作群組下拉式選單切換到新建立的工作群組。
在查詢編輯器中，選擇 curatedzonedb 資料函式庫，並執行以下查詢：
```
SELECT category_name,
count(category_name) streams
FROM streaming_films
GROUP BY category_name
ORDER BY streams DESC
```
此查詢用於找出最受歡迎的電影類別。
若需要定期執行某個查詢，可以點選「Save as」按鈕儲存查詢。

程式碼解析

SELECT category_name,
count(category_name) streams
FROM streaming_films
GROUP BY category_name
ORDER BY streams DESC

內容解密：

SELECT category_name, count(category_name) streams：從 streaming_films 表中選擇 category_name 欄位，並計算每個類別的出現次數，將計數結果命名為 streams。
FROM streaming_films：指定要查詢的表為 streaming_films。由於已經在左側選單中選擇了 curatedzonedb 資料函式庫，Athena 自動假設該表位於該資料函式庫中。
GROUP BY category_name：按 category_name 分組結果，確保每個類別只傳回一筆記錄。
ORDER BY streams DESC：按 streams 欄位降序排序結果，將最受歡迎的類別排在最前面。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。