クエリを最適化する

このセクションで説明するクエリ最適化手法を使用して、クエリの実行を高速化したり、Athena のリソース制限を超えるクエリの回避策として使用します。

結合を最適化する

分散クエリエンジンで結合を実行するには、さまざまな方法があります。最も一般的なのは、分散ハッシュ結合と複雑な結合条件のクエリの 2 つです。

分散ハッシュ結合では、大きなテーブルを左側に配置し、小さなテーブルを右側に配置します

最も一般的なタイプの結合では、結合条件として等価比較を使用します。Athena はこのタイプの結合を分散ハッシュ結合として実行します。

分散型ハッシュ結合では、エンジンは結合の片側からルックアップテーブル (ハッシュテーブル) を作成します。この側はビルド側と呼ばれます。ビルド側のレコードはノード全体に分散されます。各ノードは、そのサブセットのルックアップテーブルを作成します。結合のもう一方の側 (プローブ側と呼ばれる) がノードを介してストリーミングされます。プローブ側のレコードは、ビルド側と同じ方法でノードに分散されます。これにより、各ノードは独自の検索テーブルで一致するレコードを検索して結合を実行できます。

結合のビルド側から作成されたルックアップテーブルがメモリに収まらない場合、クエリが失敗する可能性があります。ビルド側の合計サイズが利用可能なメモリよりも小さい場合でも、レコードの分布に大きな偏りがあると、クエリが失敗する可能性があります。極端なケースでは、すべてのレコードの結合条件の値が同じで、1 つのノードのメモリに収まる必要がある場合があります。スキューの少ないクエリでも、値のセットが同じノードに送信され、その値の合計が使用可能なメモリを超えると、失敗する可能性があります。ノードにはレコードをディスクにスピルする機能がありますが、スピルするとクエリの実行が遅くなり、クエリの失敗を防ぐには不十分である場合があります。

Athena は、大きい方のリレーションをプローブ側に、小さい方のリレーションをビルド側として使用するように結合の順序を変更しようとします。ただし、Athena はテーブル内のデータを管理しないため、情報が限られており、多くの場合、最初のテーブルが大きく、2 番目のテーブルが小さいと想定する必要があります。

等価ベースの結合条件で結合を記述する場合、JOIN キーワードの左側の表がプローブ側、右側の表がビルド側であると仮定します。正しいテーブル (ビルド側) がテーブルのうち小さい方であることを確認してください。結合のビルド側をメモリに収まるほど小さくできない場合は、ビルドテーブルのサブセットを結合する複数のクエリを実行することを検討してください。

EXPLAIN を使用して複雑な結合を持つクエリを分析する

結合条件が複雑なクエリ (LIKE、>、または他の演算子を使用するクエリなど) では、多くの場合、計算負荷が高くなります。最悪の場合には、結合の片側のすべてのレコードを、結合の反対側のすべてのレコードと比較する必要があります。実行時間はレコード数の 2 乗に比例して長くなるため、このようなクエリには最大実行時間を超えるリスクがあります。

Athena がクエリをどのように実行するかを事前に確認するには、EXPLAIN ステートメントを使用できます。詳細については、Athena での EXPLAIN および EXPLAIN ANALYZE の使用およびAthena EXPLAIN ステートメントの結果を理解するを参照してください。

ウィンドウ関数の範囲を縮小したり、削除したりする

ウィンドウ関数はリソースを大量に消費する操作であるため、クエリの実行が遅くなったり、「このスケールファクターではリソースを使い果たしました」というメッセージが表示され、クエリが失敗したりする可能性があります。ウィンドウ関数は、結果を計算するために操作したすべてのレコードをメモリに保持します。ウィンドウが非常に大きい場合、ウィンドウ関数のメモリが不足する可能性があります。

クエリが使用可能なメモリ制限内で実行されるように、ウィンドウ関数が処理するウィンドウのサイズを小さくしてください。そのようにするためには、PARTITIONED BY 句を追加するか、既存の分割節の範囲を絞り込めます。

ウィンドウ以外の関数を使用する

ウィンドウ関数を含むクエリは、ウィンドウ関数なしで書き直せる場合があります。たとえば、row_number を使用して上位 N レコードを検索する代わりに、ORDER BY および LIMIT を使用できます。row_number または rank を使用してレコードの重複排除を行う代わりに、max_by、min_by、任意 などの集計関数を使用できます。

たとえば、センサーからの更新を含むデータセットがあるとします。センサーは定期的にバッテリーの状態を報告し、位置情報などのメタデータを含みます。各センサーの最新のバッテリー状態とその位置を知りたい場合は、次のクエリを使用できます。

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

位置などのメタデータはすべてのレコードで同じなので、arbitrary 関数を使用してグループから任意の値を選択できます。

max_by 関数を使用すると、最新のバッテリー状態を取得できます。max_by 関数は、別の列の最大値が見つかったレコードから列の値を選択します。この場合、グループ内の最終更新時刻を含むレコードのバッテリーステータスを返します。このクエリは、ウィンドウ関数を使用する同等のクエリよりも実行速度が速く、メモリ使用量も少なくて済みます。

集計を最適化する

Athena が集約を実行すると、GROUP BY 句内の列を使用してレコードがワーカーノード全体に分散されます。レコードをグループと照合するタスクを可能な限り効率的に行うために、ノードはレコードをメモリに保持し、必要に応じてディスクに書き出そうとします。

GROUP BY 句に重複する列を含めないようにするのも良い考えです。列の数が少ないと必要なメモリも少なくなるため、使用する列の数が少ないグループを記述するクエリの方が効率的です。また、数値列は文字列よりもメモリ使用量が少なくなります。たとえば、数値のカテゴリ ID とカテゴリ名の両方を持つデータセットを集約する場合、GROUP BY 句にはカテゴリ ID 列のみを使用してください。

場合によっては、列が GROUP BY 句または集計式の一部である必要があるという事実を回避するために、クエリの GROUP BY 句に列が含まれることがあります。このルールに従わない場合、次のようなエラーメッセージが表示されることがあります。

EXPRESSION_NOT_AGGREGATE: 1:8 行目:「カテゴリ」は集合式であるか、GROUP BY 句に含まれている必要があります

GROUP BY 句に余分な列を追加しなくても済むように、次の例のように任意の関数を使用できます。

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

ARBITRARY 関数はグループから任意の値を返します。この関数は、グループ内のすべてのレコードが 1 つの列に対して同じ値を持つことは把握しているがその値がグループを識別できない場合に便利です。

上位 N 個のクエリを最適化する

ORDER BY 句は、クエリの結果をソートされた順序で返します。Athena は分散ソートを使用して、ソート操作を複数のノードで並行して実行します。

結果を厳密にソートする必要がない場合は、ORDER BY 句を追加しないでください。また、必ずしも必要ではない場合は、内部クエリへの ORDER BY の追加は避けてください。多くの場合、クエリプランナーは冗長なソートを削除できますが、これは保証されていません。このルールの例外は、内部クエリが上位 N の操作 (最新の N の値、または最も一般的な Nの値を検索するなど) を実行している場合です。

Athena が ORDER BY を LIMIT を使用して見た場合、実行されている N クエリが上位のクエリであることを認識し、それに応じて専用の操作を行います。

注記

Athena では、上位の N を使用する row_number のようなウィンドウ関数も多くの場合検出できますが、ORDER BY と LIMIT を使用するより単純なバージョンをお勧めします。詳細については、「ウィンドウ関数の範囲を縮小したり、削除したりする」を参照してください。

必要な列のみを含める

列が必ずしも必要ではない場合は、クエリに含めないでください。クエリが処理しなければならないデータが少ないほど、実行速度は速くなります。これにより、必要なメモリ量と、ノード間で送信する必要のあるデータ量の両方が削減されます。列形式のファイル形式を使用している場合、列の数を減らすと、Amazon S3 から読み取られるデータの量も減ります。

Athena には結果の列数に特別な制限はありませんが、クエリの実行方法によって、可能な列の合計サイズが制限されます。列を合わせたサイズには、名前とタイプが含まれます。

たとえば、次のエラーは、リレーションがリレーション記述子のサイズ制限を超えるために発生します。

GENERIC_INTERNAL_ERROR: io.airlift.bytecode.CompilationException

この問題を回避するには、クエリ内の列の数を減らす、またはサブクエリを作成して、より少ない量のデータを取得する JOIN を使用します。一番外側のクエリで SELECT * を実行するクエリがある場合は、* を必要な列のみのリストに変更する必要があります。

近似値を使用してクエリを最適化する

Athena では、近似集計関数をサポートしており、個別値、最も頻度の高い値、パーセンタイル (近似中央値を含む) のカウント、およびヒストグラムの作成を行えます。これらの関数は、正確な値が不要な場合に使用してください。

COUNT(DISTINCT col) 操作とは異なり、approx_distinct はメモリ使用量がはるかに少なく、実行速度も速くなります。同様に、ヒストグラムの代わりに numeric_histogram を使用すると、近似法を使用するため、メモリ使用量が少なくなります。

LIKE を最適化する

LIKE を使用して一致する文字列を検索できますが、文字列が長い場合は計算量が多くなります。regexp_like 関数は、ほとんどの場合、より高速な代替手段であり、柔軟性にも優れています。

多くの場合、探している部分的な文字列を固定することで検索を最適化できます。たとえば、プレフィックスを探している場合は、'% substr%' の代わりに 'substr %' を使用する方がはるかに優れています。または regexp_like、「^ substr」を使用している場合。

UNION の代わりに UNION ALL を使用する

UNION ALL と UNION は、2 つのクエリの結果を 1 つの結果にまとめる 2 つの方法です。 UNION ALL は最初のクエリのレコードを 2 番目のクエリと連結し、UNION は同じ処理を実行しますが、重複も削除します。UNION ではすべてのレコードを処理して重複を見つける必要があり、メモリと計算を大量に消費しますが、UNION ALL は比較的高速な操作です。レコードの重複排除が必要でない限り、UNION ALL はベストパフォーマンスを実現するために使用してください。

大きな結果セットには UNLOAD を使用する

クエリの結果が大きくなることが予想される場合 (たとえば、数万行以上など)、UNLOAD を使用して結果をエクスポートします。ほとんどの場合、これは通常のクエリを実行するよりも高速です。また、UNLOAD を使用すると、出力をより細かく制御できます。

クエリの実行が終了すると、Athena は結果を 1 つの非圧縮 CSV ファイルとして Amazon S3 に保存します。結果が圧縮されないだけでなく、操作を並列化できないため、UNLOAD よりも時間がかかります。これとは対照的に、UNLOAD は結果をワーカーノードから直接書き込み、計算クラスターの並列処理を最大限に活用します。さらに、結果を圧縮形式や JSON や Parquet などの他のファイル形式で書き込むように UNLOAD を構成できます。

詳細については、「UNLOAD」を参照してください。

CTAS または Glue ETL を使用して、頻繁に使用する集計をマテリアライズする

クエリの「マテリアライズ」とは、事前に計算された複雑なクエリ結果 (集計や結合など) を保存して後続のクエリで再利用することで、クエリのパフォーマンスを向上させる方法です。

クエリの多くに同じ結合や集計が含まれている場合は、共通のサブクエリを新しいテーブルとして作成し、そのテーブルに対してクエリを実行できます。クエリ結果からテーブルを作成する (CTAS)、または Glue ETL などの専用 ETL ツールを使用して新しいテーブルを作成できます。

たとえば、オーダーデータセットのさまざまな側面を表示するウィジェットを含むダッシュボードがあるとします。各ウィジェットには独自のクエリがありますが、クエリはすべて同じ結合とフィルターを共有します。注文テーブルは品目テーブルと結合され、過去 3 か月のみ表示するフィルターがあります。これらのクエリに共通する機能がわかれば、ウィジェットが使用できる新しいテーブルを作成できます。これにより、重複が減り、パフォーマンスが向上します。欠点は、新しいテーブルを最新の状態に保つ必要があることです。

クエリ結果の再利用

同じクエリが短期間に複数回実行されることはよくあります。たとえば、複数のユーザーが同じデータダッシュボードを開いたときに発生する可能性があります。クエリを実行するときに、以前に計算した結果を再利用するよう Athena に指示できます。再利用できる結果の最大保管期間を指定します。同じクエリがその時間枠内で以前に実行された場合、Athena はクエリを再実行する代わりにそれらの結果を返します。詳細については、「Amazon Athena ユーザーガイド」の Athena でクエリ結果を再利用する、「AWS ビッグデータブログ」の「Amazon Athena クエリ結果の再利用によるコスト削減とクエリパフォーマンスの向上」を参照してください。