[アップデート] Amazon AthenaでEXPLAINによるクエリ計画を確認できるようになりました！

本日、Amazon AthenaがついにEXPLAINに対応しました！

Athenaは今までクエリ計画が見えずSQLのチューニングがしづらかったので、このアップデートはかなり待望しておりました…!

この記事ではEXPLAINの詳細と実行例を掲載していきます。

EXPLAINでできること

EXPLAINでは、SQLの内部ロジックや分散環境、データ移動の発生などを確認することができ、クエリの非効率性を見出してチューニングに活かすこと可能です。RDMSではよくある機能ですね。

Athena engineのversion1とversion2両方に対応していますが、対応フォーマットなどに差があります。

• Version1
  • フォーマット
    • TEXT
    • GRAPHVIZ
  • インフォメーションタイプ
    • LOGICAL
    • DISTRIBUTED
    • VALIDATE
• Version2
  • フォーマット
    • TEXT
    • GRAPHVIZ
    • JSON
  • インフォメーションタイプ
    • LOGICAL
    • DISTRIBUTED
    • VALIDATE
    • IO

出力結果の見方は、公式ドキュメントに記載してあります。深堀にかなり時間がかかりそうなので、本記事では割愛します。

注意点

EXPLAIN実行時、データのスキャンは発生しませんが、内部でGlueのメタデータを呼びに行っているので、Glueデータカタログへのリクエスト無料利用枠を超える範囲に対して課金が発生します。東京リージョンでのGlueの課金体系は下記の通りです。ワークロードでGlueを使用してるかたは、EXPLAINの使いすぎにご注意ください。

• 最初の100万回のリクエストは毎月無料
• 月に100万回を超えると、100万回のリクエストあたり 1.00USD

料金 - AWS Glue | AWS

また、JDBC・ODBCドライバーは現在未サポートの模様です。

実際に実行してみた！

手持ちのAthenaにサンプルテーブルを作成して、Athena engine version2で各インフォメーションタイプを、TEXTと実行計画が見やすいGRAPHVIZで見ていきます。当方はMacなので、Homebrewを使ってインストールしました。

$ brew install graphviz

$ dot -V
dot - graphviz version 2.47.0 (20210316.0004)

Athenaのサンプルデータは公式チュートリアルから拝借します。

Getting Started - Amazon Athena

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  os STRING,
  Browser STRING,
  BrowserVersion STRING
  ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
  "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
  ) LOCATION 's3://athena-examples-ap-northeast-1/cloudfront/plaintext/';

サンプルクエリも公式から拝借しました。このクエリに対して、クエリ計画を見て行きます。

SELECT os, COUNT(*) count
FROM cloudfront_logs
WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05'
GROUP BY os;

LOGICAL

TYPEを何も指定しない場合のデフォルトはLOGICALです。（EXPALINに続くSQL文は上記の通りなので省略）

EXPLAIN (FORMAT GRAPHVIZ)
...

出力されたテキストをlogical.dotというファイル名で保存し、以下のコマンドを実行して可視化します。

$ dot -Tpng logical.dot -o logical.png

良い感じですね！FORMAT GRAPHVIZを指定しない、通常の出力だと以下のテキストのようになります。若干情報量に差があるので、GRAPHVIZで可視化した後に、気になるところをTEXTで確認する流れが良さそう。

Query Plan
- Output[os, count] => [[os, count]]
    - RemoteExchange[GATHER] => [[os, count]]
        - Project[] => [[os, count]]
            - Aggregate(FINAL)[os][$hashvalue] => [[os, $hashvalue, count]]
                    count := "count"("count_8")
                - LocalExchange[HASH][$hashvalue] ("os") => [[os, count_8, $hashvalue]]
                    - RemoteExchange[REPARTITION][$hashvalue_9] => [[os, count_8, $hashvalue_9]]
                        - Aggregate(PARTIAL)[os][$hashvalue_10] => [[os, $hashvalue_10, count_8]]
                                count_8 := "count"(*)
                            - ScanFilterProject[table = awsdatacatalog:HiveTableHandle{schemaName=cm-haruta, tableName=cloudfront_logs, analyzePartitionValues=Optional.empty}, filterPredicate = ("date" BETWEEN DATE '2014-07-05' AND DATE '2014-08-05')] => [[os, $hashvalue_10]]
                                    $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("os"), 0))
                                    LAYOUT: cm-haruta.cloudfront_logs
                                    date := date:date:0:REGULAR
                                    os := os:string:10:REGULAR

DISTRIBUTED

続いて、TYPEにDISTRIBUTEDを指定します。出力されたテキストをdistributed.dotというファイル名で保存し、以下のコマンドを実行して可視化します。

EXPLAIN (
  FORMAT GRAPHVIZ,
  TYPE DISTRIBUTED
)
...

$ dot -Tpng distributed.dot -o distributed.png

あー、これはわかりやすいですね。TEXTだと以下のような感じです。

Query Plan
Fragment 0 [SINGLE]
    Output layout: [os, count]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    - Output[os, count] => [[os, count]]
        - RemoteSource[1] => [[os, count]]

Fragment 1 [HASH]
    Output layout: [os, count]
    Output partitioning: SINGLE []
    Stage Execution Strategy: UNGROUPED_EXECUTION
    - Project[] => [[os, count]]
        - Aggregate(FINAL)[os][$hashvalue] => [[os, $hashvalue, count]]
                count := "count"("count_8")
            - LocalExchange[HASH][$hashvalue] ("os") => [[os, count_8, $hashvalue]]
                - RemoteSource[2] => [[os, count_8, $hashvalue_9]]

Fragment 2 [SOURCE]
    Output layout: [os, count_8, $hashvalue_10]
    Output partitioning: HASH [os][$hashvalue_10]
    Stage Execution Strategy: UNGROUPED_EXECUTION
    - Aggregate(PARTIAL)[os][$hashvalue_10] => [[os, $hashvalue_10, count_8]]
            count_8 := "count"(*)
        - ScanFilterProject[table = awsdatacatalog:HiveTableHandle{schemaName=cm-haruta, tableName=cloudfront_logs, analyzePartitionValues=Optional.empty}, grouped = false, filterPredicate = ("date" BETWEEN DATE '2014-07-05' AND DATE '2014-08-05')] => [[os, $hashvalue_10]]
                $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("os"), 0))
                LAYOUT: cm-haruta.cloudfront_logs
                date := date:date:0:REGULAR
                os := os:string:10:REGULAR

VALIDATE

続いてはVALIDATEです。こちらはSQL文が有効かどうかで、TrueかFalseを返されます。GRAPHVIZでもTEXTでも、出力結果は同じでした。

EXPLAIN (
  FORMAT GRAPHVIZ,
  TYPE VALIDATE
)
...

Valid
true

IO

最後はIOです。IOはAthena engine version2かつ、JSON出力のみ対応しています。

EXPLAIN (
  FORMAT JSON,
  TYPE IO
)
...

出力結果は以下です。SQLでReadが発生したスキーマとテーブル情報をまとめてくれるみたいです。なるほど、これは使えそうですね!今後のアップデートで、カラム情報も追加してくれればさらに嬉しいです。

{
  "inputTableColumnInfos" : [ {
    "table" : {
      "catalog" : "awsdatacatalog",
      "schemaTable" : {
        "schema" : "cm-haruta",
        "table" : "cloudfront_logs"
      }
    },
    "columnConstraints" : [ ]
  } ]
}

最後に

EXPLAINでGlueのリクエスト回数に加算されてしまう点が少し残念ですが、Athena待望のEXPLAINということで個人的にかなり嬉しいです！ぜひご活用ください。