Amazon Web Services ブログ

Amazon Athena のパフォーマンスチューニング Tips トップ 10

by AWS Japan Staff | on | in Amazon Athena |

Amazon Athena は、S3 に保存されたデータに対して標準 SQL で簡単に分析を行える、インタラクティブクエリサービスです。Athena はサーバーレスのためインフラ管理の必要がなく、また実行したクエリのぶんだけ料金を支払うかたちになります。Athena は簡単に使えます。Amazon S3 上のデータに対してスキーマを定義し、標準 SQL でクエリを投げるだけです。

このブログポストでは、クエリパフォーマンスを改善するための 10 個の Tips をご紹介します。Tips には、Amazon S3 に置かれたデータに関するものと、クエリチューニングに関するものがあります。Amazon Athena は Presto を実行エンジンとして使用しているため、ここでご紹介する Tips のうちのいくつかは、Amazon EMR 上で Presto を動かす際にも当てはまります。

このポストは、読者の方が Parquet, ORC, Text files, Avro, CSV, TSV, and JSON といった、さまざまなファイルフォーマットについての知識を持っていることを前提としています。

ベストプラクティス: ストレージ

このセクションでは Athena を最大限に活用するために、どのようなデータ構造にするべきかについて議論します。ここで議論する内容は、Amazon EMR 上の Spark, Presto, Hive で Amazon S3 のデータを処理する場合にも、同様に当てはまります。

1. データをパーティションに分ける

パーティショニングとは、テーブルをいくつかに分割し、日付や国、地域といったカラムの値単位でまとめることをさします。パーティションは仮想カラムとして動作します。パーティションをテーブルの作成時に定義することで、クエリでスキャンするデータ量を減らすことができ、その結果パフォーマンスが向上します。パーティションに基づいてフィルタを指定することで、クエリで読み込むデータ量を制限することができます。より詳細については、Partitioning Data を参照してください。

Athena では Hive のパーティショニングをサポートしており、以下のいずれかの記法を使用します。

  1. カラム名のあとに = 記号をつけ、そのあとに値を記述する
    s3://yourBucket/pathToTable/<PARTITION_COLUMN_NAME>=<VALUE>/<PARTITION_COLUMN_NAME>=<VALUE>/

    データセットがこの形でパーティションわけされている場合には、テーブルにパーティションを一括で認識させるために、MSCK REPAIR TABLE を実行します

  2. もしデータの “Path” が上記のフォーマットでない場合には、個々のパーティションに対して、ALTER TABLE ADD PARTITION コマンドを実行することで、パーティションを認識させることができます
    s3://yourBucket/pathToTable/YYYY/MM/DD/
    Alter Table <tablename> add Partition (PARTITION_COLUMN_NAME = <VALUE>, PARTITION_COLUMN2_NAME = <VALUE>) LOCATION ‘s3://yourBucket/pathToTable/YYYY/MM/DD/’;

    注意: このやり方を用いることで、S3 上のどの場所にあるオブジェクトに対してでも、パーティションを認識させることができます

以下の例は、S3 バケットに置かれたフライトテーブルが、year でパーティションわけされているものになります。

$ aws s3 ls s3://athena-examples/flight/parquet/
PRE year=1987/
PRE year=1988/
PRE year=1989/
PRE year=1990/
PRE year=1991/
PRE year=1992/
PRE year=1993/

year カラムに対して ‘WHERE’ 句を用いることで、読み込むデータ量を制限できます。

SELECT dest, origin FROM flights WHERE year = 1991

パーティションキーには、複数のカラムを指定することができます。同様に、そのカラムが特定の値のものだけをスキャンすることができます。

s3://athena-examples/flight/parquet/year=1991/month=1/day=1/
s3://athena-examples/flight/parquet/year=1991/month=1/day=2/

パーティション対象のカラムを決める際には、以下の点を考慮してください:

  • フィルタとして使われるカラムは、パーティションの有力候補になります
  • パーティショニング自体にコストがかかります。テーブル内のパーティション数が増えるにつれ、パーティションのメタデータを取得して処理するためのオーバーヘッドが大きくなり、かつ 1 パーティションあたりのデータサイズは小さくなります。過剰に細かくパーティショニングすると、パフォーマンス上の利点が失われます
  • データが特定パーティションに偏っており、かつ多くのクエリがその値を使う場合には、同様にパフォーマンス上の利点が失われます

例:

以下のテーブルでは、パーティション分けされたテーブルと、そうでないテーブルのクエリ実行時間を比較しています。両テーブルには、無圧縮のテキストフォーマットデータが 74GB 含まれています。パーティション分けされたテーブルは、l_shipdate カラムによって 2526 個のパーティションに分けられています。

クエリ パーティション分けされていないテーブル コスト パーティション分けされたテーブル コスト 削減度合い
実行時間 スキャンされたデータ量 実行時間 スキャンされたデータ量
SELECT count(*) FROM lineitem WHERE l_shipdate = '1996-09-01' 9.71 秒 74.1 GB $0.36 2.16 秒 29.06 MB $0.0001

99% 価格削減

77% 速度向上

SELECT count(*) FROM lineitem WHERE l_shipdate >= '1996-09-01' AND l_shipdate < '1996-10-01' 10.41 秒 74.1 GB $0.36 2.73 秒 871.39 MB $0.004 98% 価格削減
73% 速度向上

ただし以下に示すように、パーティショニングにはペナルティもあります。過剰にパーティション分けしないように気をつけてください。

クエリ パーティション分けされていないテーブル コスト パーティション分けされたテーブル コスト 削減度合い
実行時間 スキャンされたデータ量 実行時間 スキャンされたデータ量
SELECT count(*) FROM lineitem; 8.4 秒 74.1 GB $0.36 10.65 秒 74.1 GB $0.36 27% 速度低下

2. ファイルを圧縮・分割する

各ファイルが適切なサイズ(詳しくは次のセクションを参照)であるか、ファイルが分割可能であれば、データの圧縮によってクエリの実行速度は著しく向上します。データサイズが小さいほど、S3 から Athena へのネットワークトラフィックが軽減されます。

分割可能なファイルの場合には並列実行性を増すために、Athena の実行エンジンがファイルを分割して、複数のリーダーで処理します。単一の分割不可能なファイルを扱う場合には、単一リーダーのみがファイルを読み込むことができ、そのほかのリーダーは待機状態のままです。すべての圧縮アルゴリズムが分割可能なわけではありません。一般的な圧縮フォーマットとその属性について、以下のテーブルにまとめました。

アルゴリズム 分割可能か否か 圧縮の度合い 圧縮 + 解凍速度
Gzip (DEFLATE) いいえ 高い 普通
bzip2 はい 非常に高い 遅い
LZO いいえ 低い 速い
Snappy いいえ 低い とても速い

一般的に、アルゴリズムの圧縮率が高くなるほど、圧縮および解凍に必要な CPU リソースが増えます。

Athena の場合は、デフォルトでデータ圧縮が行われ、かつ分割可能な Apache Parquet や Apache ORC といったファイルフォーマットの利用をおすすめします(訳注: ここでは、ファイルフォーマットと圧縮フォーマットが混ざっている点に注意してください。Parquet や ORC はファイルフォーマットであり、圧縮フォーマットではありません。ですが、Parquet や ORC はデフォルトのエンコーディング法の中に、辞書エンコーディングという簡単な圧縮処理が含まれています。ここの記述は、そのことを表しています。また Tips の 4 番目で述べているように、Parquet/ORC に対して、さらに Snappy のような圧縮アルゴリズムを指定することが可能です)。もしそれらを利用できない場合には、適切なサイズに分割した BZip2 や Gzip を試してください。

3. ファイルサイズを最適化する

データ読み込みが並列で行われ、データブロックがシーケンシャルに読み込まれる場合に、クエリが効率的に実行されます。分割可能なファイルフォーマットであるようにしておくことで、ファイルの大きさに関わらず並列処理が行われます。

ただしファイルサイズが非常に小さい場合、特に 128MB 未満の場合には、実行エンジンは S3ファイルのオープン、ディレクトリのリスト表示、オブジェクトメタデータの取得、データ転送のセットアップ、ファイルヘッダーの読み込み、圧縮ディレクトリの読み込み、といった処理に余分な時間がかかります。その一方で、ファイルが分割不可能でサイズが非常に大きいときには、単一のリーダーがファイル全体の読み込みを完了するまで、クエリ自体の処理は行われません。この場合、並列性が下がってしまいます。

細切れファイル問題に対する解決法のひとつとして、EMR の S3DistCP ユーティリティがあります。これを使うことで、小さなファイル群を大きなオブジェクトへとまとめることができます。S3DistCP は、大量のデータを最適なやり方で HDFS から S3、S3 からS3 、S3 から HDFS へと移動させる際にも利用できます。

大きなファイルにまとめるのは、複数の利点があります:

  • 高速な一覧表示
  • S3 へのリクエスト数の削減
  • 管理するメタデータの削減

:

以下では、単一の大きなファイルのテーブルと、5000 個の小さなファイルのテーブルとで、クエリ実行時間を比較しています。データはテキストフォーマットで、サイズは 7GB です。

クエリ ファイル数 実行時間
SELECT count(*) FROM lineitem 5000 files 8.4 秒
SELECT count(*) FROM lineitem 1 file 2.31 秒
実行速度 72% 向上

4. 列指向データの作成を最適化する

Apache Parquet と Apache ORC はポピュラーな列指向データフォーマットです。両者には列方向圧縮、さまざまなエンコーディング、データ型に合わせた圧縮、プレディケイトプッシュダウン(訳注: プレディケイトプッシュダウンとは、WHERE 句や GROUP BY 句などの処理を効率的に行うための手法です。例えば GROUP BY に対するプレディケイトプッシュダウンでは、各リーダーで読み込んだデータについて、全データで GROUP BY を行う前に、各ワーカー内であらかじめ GROUP BY をしておき、その結果を集約ワーカーに転送する、といったプロセスをとります。各ワーカーで先に集約を行うことでデータの転送コストが下がり、結果的にパフォーマンスが向上します。このように、クエリの実行パイプラインの最後で行う処理を、効率化のためにあらかじめ各プロセスでおこなっておく(= プッシュダウン)のが、プレディケイトプッシュダウンの役割となります)といった、データを効率的に持つための機能があります。両者はともに分割可能です。一般的に、高い圧縮率やデータブロックのスキップによって、S3 から読み込むデータが減り、その結果よりよいクエリパフォーマンスが得られます。

チューニング可能なパラメーターのひとつとして、ブロックサイズまたはストライプのサイズがあります。Parquet におけるブロックサイズ、 ORC におけるストライプサイズというのは、バイトサイズ単位で表されるもので、単一ブロックで保持できる最大行数を意味します。ブロックまたはストライプのサイズが大きいほど、単一ブロックで格納できる行数も多くなります。デフォルトでは Parquet のブロックサイズは 128MB で、ORC のストライプサイズは 64MB になります。テーブル内に大量のカラムがある場合は、各カラムのブロック単位で効率的にシーケンシャル I/O を行えるように、より大きなブロックサイズにすることをおすすめします。

そのほかのパラメーターとして、データブロックの圧縮アルゴリズムが挙げられます。Parquet のデフォルト圧縮フォーマットは Snappy ですが、それ以外に圧縮なし、 GZIP、そして LZO も使用可能です。ORC のデフォルトは ZLIB ですが、それ以外に圧縮なしと Snappy が利用可能です。デフォルトの圧縮アルゴリズムからはじめて、10GB 以上のデータサイズになった場合には、それ以外の圧縮アルゴリズムを試してみることをおすすめします。

Parquet/ORC ファイルフォーマットは、ともにプレディケイトプッシュダウン(プレディケイトフィルタリングとも呼ばれます)をサポートしています。Parquet/ORC はともに、ブロック内にカラムの値を保持するだけでなく、最大値/最小値のようなブロックごとの統計データも持っています。クエリが実行される際に、統計情報によって当該ブロックを読み込む必要があるか、スキップしてよいかを判断します。スキップするブロック数を最適化するための一つの方法として、Parquet や ORC で書き出す前に、よくフィルタされるカラムでデータをソートしておくやり方があります。これによって、各ブロックにおける最小値と最大値の差分が、全体としてもっとも小さくなることが保証されます。これによって、フィルタ効率を向上させることができます。

Amazon EMR 上で Spark や Hive を実行することで、既存のデータを Parquet や ORC に変換できます。 詳細については、S3のデータをAmazon Athenaを使って分析する のブログポストを参照してください。また以下のリソースもあります:

ベストプラクティス: クエリ

Athena ではクエリの実行エンジンとして Presto を使用しています。クエリ実行時に Presto がどのように動いているか理解することで、クエリの最適化方法について把握することができます。

5. ORDER BY を最適化する

ORDER BY 句はクエリの実行結果をソートして返します。ソートを実行するために、Presto はデータのすべてのレコードを単一のワーカーに送り、それからソートを実行します。この処理は Presto のメモリを大量に消費するため、クエリの実行時間が非常に長くなります。最悪の場合には、クエリが失敗します。

トップ N の値をみるために ORDER BY 句を使用する場合には、単一ワーカーでソートを実行するのではなく、LIMIT 句によって各ワーカーにソートとリミットの処理をプッシュし、ソートにかかるコストを大きく削減してください。

:

データセット: 7.25GB、無圧縮、テキストフォーマット、6000万行

クエリ 実行時間
SELECT * FROM lineitem ORDER BY l_shipdate 528 秒
SELECT * FROM lineitem ORDER BY l_shipdate LIMIT 10000 11.15 秒
速度 98% 向上

6. JOIN を最適化する

2 つのテーブルを結合する際には、両者のうち大きなほうを左側に、小さなほうを右側に指定してください。Presto は JOIN 句の右側で指定されたテーブルを各ワーカーノードに転送して、左側のテーブルを順になめていくことで結合を行います。右側のテーブルを小さくすることで、メモリ消費量を少なく、クエリを高速に実行することができます。

:

データセット: 74GB、無圧縮、テキストフォーマット、6億200万行

クエリ 実行時間
SELECT count(*) FROM lineitem, part WHERE lineitem.l_partkey = part.p_partkey 22.81 秒
SELECT count(*) FROM part, lineitem WHERE lineitem.l_partkey = part.p_partkey 10.71 秒
価格削減 / 速度向上 53% 速度向上

例外として、複数のテーブルをまとめて結合する場合と、クロス結合が行われる場合があります。Presto は結合の実行順序の最適化をサポートしていないため、が左側から右側に対して結合処理が行われます。そのためテーブルを左側から大きい順に並べることにより、結合条件に合わせたテーブルの並びにならない場合、クロス結合が実行されてしまいます(訳注: 以下の例の上側のクエリの場合、まず lineitem と customer とで結合処理が行われます。しかし結合条件にあるのは lineitem と orders、ccustomer と orders のため、lineitem と customer の間ではクロス結合が行われてしまいます。その結果得られた巨大なテーブルと orders の間で,次の結合処理が行われます。クロス結合は非常にコストの高い処理のため、この場合は 30 分以内にクエリが完了せず、タイムアウトしてしまっています)。

:

データセット: 9.1GB、無圧縮、テキストフォーマット、7600万行

クエリ 実行時間
SELECT count(*) FROM lineitem, customer, orders WHERE lineitem.l_orderkey = orders.o_orderkey AND customer.c_custkey = orders.o_custkey Timed Out
SELECT count(*) FROM lineitem, orders, customer WHERE lineitem.l_orderkey = orders.o_orderkey AND customer.c_custkey = orders.o_custkey 3.71 seconds

7. GROUP BY を最適化する

GROUP BY 演算子は、指定されたカラムの値に応じてレコードを各ワーカーノードに分散し、各ノードはメモリ内に対象の値を保持します。レコードが追加されると、メモリ内の GROUP BY 対象のカラムを比較します。一致した場合には、対象となる値を集約します。

クエリ内で GROUP BY を使う際には、カーディナリティ(訳注: カラム内のユニークな値の個数)が高い順にカラムを並べてください(これはユニークな値の数が多いほど、データが各ワーカーに均等に分散するためです)。

SELECT state, gender, count(*) 
           FROM census 
GROUP BY state, gender;

もうひとつの最適化方法は、可能ならば GROUP BY の対象を文字列でなく数字にすることです。数字は文字列に比べて必要とするメモリが少ないため、高速に処理することができます。

またその他の方法として、SELECT の中で扱うカラム数を制限することによって、レコードをメモリに確保したり、GROUP BY で集約する際に必要とするメモリ総量を減らすやり方があります。

8. LIKE 演算子を最適化する

文字列のカラムに対して、複数の値でフィルタリングする際には、一般的に LIKE よりも RegEx を使う方が望ましいです。LIKE を使う回数が多いほど、また文字列カラムのサイズが大きいほど、RegEx の効果も大きくなります。

:

データセット: 74GB、無圧縮、テキストフォーマット、6億行

クエリ 実行時間
SELECT count(*) FROM lineitem WHERE l_comment LIKE '%wake%' OR l_comment LIKE '%regular%' OR l_comment LIKE '%express%' OR l_comment LIKE '%sleep%' OR l_comment LIKE '%hello% 20.56 秒
SELECT count(*) FROM lineitem WHERE regexp_like(l_comment, 'wake|regular|express|sleep|hello') 15.87 秒
速度向上 17% 向上

9. 近似関数を使う

大規模なデータセットを処理する際の典型的なユースケースとして、COUNT(DISTINCT column) を使って、特定のカラムのユニークな値ごとのレコード数を求めるというものがあります。例として挙げられるのは、ウェブサイトを訪れたユニークユーザーを求めるというものです。

もし正確な値が必要ない場合、例えばサイト内のどのウェブページを突っ込んで調べるべきか判断するといったケースでは、approx_distinct() の使用を検討してみてください。この関数は、全文字列を捜査するかわりに、ユニークなハッシュの数をカウントすることで、メモリ使用量を最小限におさえます。この手法の欠点は、得られた値に 2.3% の標準誤差を持つことです。

:

データセット: 74GB、無圧縮、テキストフォーマット、6億行

クエリ 実行時間
SELECT count(distinct l_comment) FROM lineitem; 13.21 秒
SELECT approx_distinct(l_comment) FROM lineitem; 10.95 秒
速度向上 17% 向上

詳しくは Presto ドキュメントの Aggregate Functions を参照してください。

10. 必要なカラムだけを読み込む

クエリを実行する際に、すべてのカラムを使用するかわりに、最後の SELECT 句で必要なカラムだけに絞ってください。処理対象のカラム数を減らすことで、クエリの実行パイプライン全体で処理しなければいけないデータ総量を削減できます。これは特に、大量のカラムがあるテーブルや、文字列ベースのカラムが主体のテーブルにクエリを投げるときに有効です。

:

データセット: 7.25GB、無圧縮、テキストフォーマット、6000万行

クエリ 実行時間
SELECT * FROM lineitem, orders, customer WHERE lineitem.l_orderkey = orders.o_orderkey AND customer.c_custkey = orders.o_custkey; 983 秒
SELECT customer.c_name, lineitem.l_quantity, orders.o_totalprice FROM lineitem, orders, customer WHERE lineitem.l_orderkey = orders.o_orderkey AND customer.c_custkey = orders.o_custkey; 6.78 秒
価格削減 / 速度向上 145 倍速度向上

結論

このポストでは、Amazon Athena および Presto エンジンにおけるインタラクティブ分析を最適化するための、ベストプラクティスについて述べました。これらは Amazon EMR 上で Presto を動かす際にも、同様に適用可能です。

原文: Top 10 Performance Tuning Tips for Amazon Athena (翻訳: SA志村)