Athena での EXPLAIN および EXPLAIN ANALYZE の使用
EXPLAIN ステートメントは、指定された SQL ステートメントの論理実行または分散実行プランを表示、または SQL ステートメントを検証します。結果はテキスト形式での出力、またはグラフへのレンダリングのためのデータ形式での出力が可能です。
注記
EXPLAIN 構文を使用せずに、Athena コンソールでクエリの論理プランと分散プランのグラフィック表現を表示できます。詳細については、「SQL クエリの実行プランの表示」を参照してください。
EXPLAIN ANALYZE ステートメントでは、指定した SQL ステートメントの分散実行プランと、SQL クエリ内の各オペレーションに関する計算コストの両方を表示します。結果はテキスト形式または JSON 形式で出力することができます。
考慮事項と制約事項
Athena の EXPLAIN および EXPLAIN ANALYZE ステートメントには、以下の制限があります。
-
EXPLAINクエリはデータをスキャンしないため、Athena はそれらに対する料金を請求しません。ただし、EXPLAINクエリはテーブルメタデータの取得のために AWS Glue を呼び出すので、呼び出し回数が Glue の無料利用枠制限を超える場合に料金が発生する可能性があります。 -
この理由は、
EXPLAIN ANALYZEクエリが実行されデータをスキャンすると、Athena は、そのスキャンされたデータ量に対して課金をするためです。 -
Lake Formation で定義されている行またはセルのフィルタリング情報、およびクエリの統計情報は、
EXPLAINおよびEXPLAIN ANALYZEの出力には表示されません。
EXPLAIN 構文
EXPLAIN [ (option[, ...]) ]statement
option は以下のいずれかにすることができます。
FORMAT { TEXT | GRAPHVIZ | JSON } TYPE { LOGICAL | DISTRIBUTED | VALIDATE | IO }
FORMAT オプションを指定しない場合、出力はデフォルトで TEXT 形式となります。IO 型は、クエリが読み込むテーブルとスキーマに関する情報を提供します。IO は Athena エンジンバージョン 2 のみでサポートされており、JSON 形式のみで返すことができます。
EXPLAIN ANALYZE 構文
EXPLAIN からの出力に加えて、EXPLAIN
ANALYZE では、CPU 使用率、入力された行数、出力された行数など、指定したクエリの実行時に関する統計情報も出力します。
EXPLAIN ANALYZE [ (option[, ...]) ]statement
option は以下のいずれかにすることができます。
FORMAT { TEXT | JSON }
FORMAT オプションを指定しない場合、出力はデフォルトで TEXT 形式となります。EXPLAIN ANALYZE によるすべてのクエリは DISTRIBUTED なので、EXPLAIN ANALYZE では TYPE オプションを使用することはできません。
ステートメントは以下のいずれかになります。
SELECT CREATE TABLE AS SELECT INSERT UNLOAD
EXPLAIN での例
以下の EXPLAIN に関する例では、まず端的なものを示し、その後に、複合型のものへと進みます。
EXPLAIN の例 1: テキスト形式でクエリプランを表示するために、EXPLAIN ステートメントを使用する
次の例の EXPLAIN では、Elastic Load Balancing ログでの SELECT クエリについて、その実行プランを表示します 使用される形式はデフォルトのテキスト出力です。
EXPLAIN SELECT request_timestamp, elb_name, request_ip FROM sampledb.elb_logs;
結果
- Output[request_timestamp, elb_name, request_ip] => [[request_timestamp, elb_name, request_ip]]
- RemoteExchange[GATHER] => [[request_timestamp, elb_name, request_ip]]
- TableScan[awsdatacatalog:HiveTableHandle{schemaName=sampledb, tableName=elb_logs,
analyzePartitionValues=Optional.empty}] => [[request_timestamp, elb_name, request_ip]]
LAYOUT: sampledb.elb_logs
request_ip := request_ip:string:2:REGULAR
request_timestamp := request_timestamp:string:0:REGULAR
elb_name := elb_name:string:1:REGULAREXPLAIN 例 2: クエリプランをグラフ化する
Athena コンソールを使用してクエリプランをグラフ化できます。次のような SELECT ステートメントを Athena クエリエディタに入力し、[EXPLAIN] を選択します。
SELECT c.c_custkey, o.o_orderkey, o.o_orderstatus FROM tpch100.customer c JOIN tpch100.orders o ON c.c_custkey = o.o_custkey
Athena クエリエディタの [Explain] ページが開き、クエリの分散プランと論理プランが表示されます。次のグラフは、この例の論理プランを示します。
重要
現在、一部のパーティションフィルターは、Athena がクエリに適用しても、ネストされたオペレーターツリーグラフに表示されない場合があります。このようなフィルターの効果を検証するには、クエリで EXPLAIN または EXPLAIN ANALYZE を実行し、結果を表示します。
Athena コンソールでクエリプランのグラフ機能を使用する方法の詳細については、「SQL クエリの実行プランの表示」を参照してください。
EXPLAIN の例 3: EXPLAIN ステートメントを使用してパーティションプルーニングを検証する
パーティションされたキーにフィルタリング述語を使用してパーティションテーブルをクエリする場合、クエリエンジンはパーティションされたキーにこの述語を適用して、読み込むデータの量を減らします。
以下の例では、パーティションテーブルでの SELECT クエリについてパーティションプルーニングを検証するために EXPLAIN クエリを使用します。まず、CREATE TABLE ステートメントが tpch100.orders_partitioned テーブルを作成します。テーブルは、列 o_orderdate でパーティションされます。
CREATE TABLE `tpch100.orders_partitioned`( `o_orderkey` int, `o_custkey` int, `o_orderstatus` string, `o_totalprice` double, `o_orderpriority` string, `o_clerk` string, `o_shippriority` int, `o_comment` string) PARTITIONED BY ( `o_orderdate` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://<your_s3_bucket>/<your_directory_path>/'
SHOW PARTITIONS コマンドによる表示にあるように、tpch100.orders_partitioned テーブルには o_orderdate にいくつかのパーティションがあります。
SHOW PARTITIONS tpch100.orders_partitioned; o_orderdate=1994 o_orderdate=2015 o_orderdate=1998 o_orderdate=1995 o_orderdate=1993 o_orderdate=1997 o_orderdate=1992 o_orderdate=1996
以下の EXPLAIN クエリは、指定された SELECT ステートメントでパーティションプルーニングを検証します。
EXPLAIN SELECT o_orderkey, o_custkey, o_orderdate FROM tpch100.orders_partitioned WHERE o_orderdate = '1995'
結果
Query Plan
- Output[o_orderkey, o_custkey, o_orderdate] => [[o_orderkey, o_custkey, o_orderdate]]
- RemoteExchange[GATHER] => [[o_orderkey, o_custkey, o_orderdate]]
- TableScan[awsdatacatalog:HiveTableHandle{schemaName=tpch100, tableName=orders_partitioned,
analyzePartitionValues=Optional.empty}] => [[o_orderkey, o_custkey, o_orderdate]]
LAYOUT: tpch100.orders_partitioned
o_orderdate := o_orderdate:string:-1:PARTITION_KEY
:: [[1995]]
o_custkey := o_custkey:int:1:REGULAR
o_orderkey := o_orderkey:int:0:REGULAR結果にある太字のテキストは、PARTITION_KEY に述語 o_orderdate =
'1995' が適用されたことを示しています。
EXPLAIN の例 4: EXPLAIN クエリを使用して結合順序と結合タイプをチェックする
以下の EXPLAIN クエリは、SELECT ステートメントの結合順序と結合タイプをチェックします。EXCEEDED_LOCAL_MEMORY_LIMIT エラーが発生する確率を減らすことができるように、このようなクエリを使用してクエリのメモリ使用量を調べます。
EXPLAIN (TYPE DISTRIBUTED) SELECT c.c_custkey, o.o_orderkey, o.o_orderstatus FROM tpch100.customer c JOIN tpch100.orders o ON c.c_custkey = o.o_custkey WHERE c.c_custkey = 123
結果
Query Plan
Fragment 0 [SINGLE]
Output layout: [c_custkey, o_orderkey, o_orderstatus]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
- Output[c_custkey, o_orderkey, o_orderstatus] => [[c_custkey, o_orderkey, o_orderstatus]]
- RemoteSource[1] => [[c_custkey, o_orderstatus, o_orderkey]]
Fragment 1 [SOURCE]
Output layout: [c_custkey, o_orderstatus, o_orderkey]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
- CrossJoin => [[c_custkey, o_orderstatus, o_orderkey]]
Distribution: REPLICATED
- ScanFilter[table = awsdatacatalog:HiveTableHandle{schemaName=tpch100,
tableName=customer, analyzePartitionValues=Optional.empty}, grouped = false,
filterPredicate = ("c_custkey" = 123)] => [[c_custkey]]
LAYOUT: tpch100.customer
c_custkey := c_custkey:int:0:REGULAR
- LocalExchange[SINGLE] () => [[o_orderstatus, o_orderkey]]
- RemoteSource[2] => [[o_orderstatus, o_orderkey]]
Fragment 2 [SOURCE]
Output layout: [o_orderstatus, o_orderkey]
Output partitioning: BROADCAST []
Stage Execution Strategy: UNGROUPED_EXECUTION
- ScanFilterProject[table = awsdatacatalog:HiveTableHandle{schemaName=tpch100,
tableName=orders, analyzePartitionValues=Optional.empty}, grouped = false,
filterPredicate = ("o_custkey" = 123)] => [[o_orderstatus, o_orderkey]]
LAYOUT: tpch100.orders
o_orderstatus := o_orderstatus:string:2:REGULAR
o_custkey := o_custkey:int:1:REGULAR
o_orderkey := o_orderkey:int:0:REGULARこのクエリ例は、パフォーマンスを向上させるためにクロス結合に最適化されています。結果は、tpch100.orders が BROADCAST 分散タイプとして分散されることを示しています。これは、結合オペレーションを実行するすべてのノードに tpch100.orders テーブルが分散されることを意味します。BROADCAST 分散タイプでは、tpch100.orders テーブルのフィルタリングされた結果のすべてが、結合操作を実行する各ノードのメモリに収まることが必要とされています。
ただし、tpch100.customer テーブルは tpch100.orders よりも小さくなります。tpch100.customer に必要なメモリは少ないため、クエリを tpch100.orders ではなく BROADCAST
tpch100.customer に書き直すことができます。これにより、クエリが EXCEEDED_LOCAL_MEMORY_LIMIT エラーを受け取る確率が低くなります。この戦略では、以下の点を前提としています。
-
tpch100.customer.c_custkeyがtpch100.customerテーブルで一意である。 -
tpch100.customerとtpch100.ordersの間に 1 対多のマッピング関係がある。
以下の例は、書き直されたクエリを示しています。
SELECT c.c_custkey, o.o_orderkey, o.o_orderstatus FROM tpch100.orders o JOIN tpch100.customer c -- the filtered results of tpch100.customer are distributed to all nodes. ON c.c_custkey = o.o_custkey WHERE c.c_custkey = 123
EXPLAIN の例 5: EXPLAIN クエリを使用して効果がない述語を削除する
EXPLAIN クエリを使用して、フィルタリング述語の有効性をチェックすることができます。以下の例にあるように、結果を使用して効果がない述語を削除できます。
EXPLAIN SELECT c.c_name FROM tpch100.customer c WHERE c.c_custkey = CAST(RANDOM() * 1000 AS INT) AND c.c_custkey BETWEEN 1000 AND 2000 AND c.c_custkey = 1500
結果
Query Plan
- Output[c_name] => [[c_name]]
- RemoteExchange[GATHER] => [[c_name]]
- ScanFilterProject[table =
awsdatacatalog:HiveTableHandle{schemaName=tpch100,
tableName=customer, analyzePartitionValues=Optional.empty},
filterPredicate = (("c_custkey" = 1500) AND ("c_custkey" =
CAST(("random"() * 1E3) AS int)))] => [[c_name]]
LAYOUT: tpch100.customer
c_custkey := c_custkey:int:0:REGULAR
c_name := c_name:string:1:REGULAR結果にある filterPredicate は、オプティマイザが元の 3 つの述語を 2 つの述語にマージし、それらの適用順序を変更したことを示しています。
filterPredicate = (("c_custkey" = 1500) AND ("c_custkey" = CAST(("random"() * 1E3) AS int)))
結果が述語 AND c.c_custkey BETWEEN 1000
AND 2000 に効果がないことを示しているので、クエリ結果を変更することなくこの述語を削除できます。
EXPLAIN クエリの結果で使用された用語については、「Athena EXPLAIN ステートメントの結果について」を参照してください。
EXPLAIN ANALYZE での例
次に、EXPLAIN ANALYZE クエリとその出力に関する例を示します。
EXPLAIN ANALYZE の例 1: クエリプランとコンピューティングのコストをテキスト形式で表示するために、EXPLAIN ANALYZE を使用する
次の例の EXPLAIN ANALYZE は、CloudFront ログでの SELECT クエリについて、その実行プランとコンピューティングコストを表示します。使用される形式はデフォルトのテキスト出力です。
EXPLAIN ANALYZE SELECT FROM cloudfront_logs LIMIT 10
結果
Fragment 1
CPU: 24.60ms, Input: 10 rows (1.48kB); per task: std.dev.: 0.00, Output: 10 rows (1.48kB)
Output layout: [date, time, location, bytes, requestip, method, host, uri, status, referrer,\
os, browser, browserversion]
Limit[10] => [[date, time, location, bytes, requestip, method, host, uri, status, referrer, os,\
browser, browserversion]]
CPU: 1.00ms (0.03%), Output: 10 rows (1.48kB)
Input avg.: 10.00 rows, Input std.dev.: 0.00%
LocalExchange[SINGLE] () => [[date, time, location, bytes, requestip, method, host, uri, status, referrer, os,\
browser, browserversion]]
CPU: 0.00ns (0.00%), Output: 10 rows (1.48kB)
Input avg.: 0.63 rows, Input std.dev.: 387.30%
RemoteSource[2] => [[date, time, location, bytes, requestip, method, host, uri, status, referrer, os,\
browser, browserversion]]
CPU: 1.00ms (0.03%), Output: 10 rows (1.48kB)
Input avg.: 0.63 rows, Input std.dev.: 387.30%
Fragment 2
CPU: 3.83s, Input: 998 rows (147.21kB); per task: std.dev.: 0.00, Output: 20 rows (2.95kB)
Output layout: [date, time, location, bytes, requestip, method, host, uri, status, referrer, os,\
browser, browserversion]
LimitPartial[10] => [[date, time, location, bytes, requestip, method, host, uri, status, referrer, os,\
browser, browserversion]]
CPU: 5.00ms (0.13%), Output: 20 rows (2.95kB)
Input avg.: 166.33 rows, Input std.dev.: 141.42%
TableScan[awsdatacatalog:HiveTableHandle{schemaName=default, tableName=cloudfront_logs,\
analyzePartitionValues=Optional.empty},
grouped = false] => [[date, time, location, bytes, requestip, method, host, uri, st
CPU: 3.82s (99.82%), Output: 998 rows (147.21kB)
Input avg.: 166.33 rows, Input std.dev.: 141.42%
LAYOUT: default.cloudfront_logs
date := date:date:0:REGULAR
referrer := referrer:string:9:REGULAR
os := os:string:10:REGULAR
method := method:string:5:REGULAR
bytes := bytes:int:3:REGULAR
browser := browser:string:11:REGULAR
host := host:string:6:REGULAR
requestip := requestip:string:4:REGULAR
location := location:string:2:REGULAR
time := time:string:1:REGULAR
uri := uri:string:7:REGULAR
browserversion := browserversion:string:12:REGULAR
status := status:int:8:REGULAREXPLAIN ANALYZE の例 2: クエリプランを JSON 形式で表示するために、EXPLAIN ANALYZE を使用する
次の例は、CloudFront ログでの SELECT クエリについて、その実行プランとコンピューティングコストを表示します。この例では、出力形式として JSON を指定しています。
EXPLAIN ANALYZE (FORMAT JSON) SELECT * FROM cloudfront_logs LIMIT 10
結果
{
"fragments": [{
"id": "1",
"stageStats": {
"totalCpuTime": "3.31ms",
"inputRows": "10 rows",
"inputDataSize": "1514B",
"stdDevInputRows": "0.00",
"outputRows": "10 rows",
"outputDataSize": "1514B"
},
"outputLayout": "date, time, location, bytes, requestip, method, host,\
uri, status, referrer, os, browser, browserversion",
"logicalPlan": {
"1": [{
"name": "Limit",
"identifier": "[10]",
"outputs": ["date", "time", "location", "bytes", "requestip", "method", "host",\
"uri", "status", "referrer", "os", "browser", "browserversion"],
"details": "",
"distributedNodeStats": {
"nodeCpuTime": "0.00ns",
"nodeOutputRows": 10,
"nodeOutputDataSize": "1514B",
"operatorInputRowsStats": [{
"nodeInputRows": 10.0,
"nodeInputRowsStdDev": 0.0
}]
},
"children": [{
"name": "LocalExchange",
"identifier": "[SINGLE] ()",
"outputs": ["date", "time", "location", "bytes", "requestip", "method", "host",\
"uri", "status", "referrer", "os", "browser", "browserversion"],
"details": "",
"distributedNodeStats": {
"nodeCpuTime": "0.00ns",
"nodeOutputRows": 10,
"nodeOutputDataSize": "1514B",
"operatorInputRowsStats": [{
"nodeInputRows": 0.625,
"nodeInputRowsStdDev": 387.2983346207417
}]
},
"children": [{
"name": "RemoteSource",
"identifier": "[2]",
"outputs": ["date", "time", "location", "bytes", "requestip", "method", "host",\
"uri", "status", "referrer", "os", "browser", "browserversion"],
"details": "",
"distributedNodeStats": {
"nodeCpuTime": "0.00ns",
"nodeOutputRows": 10,
"nodeOutputDataSize": "1514B",
"operatorInputRowsStats": [{
"nodeInputRows": 0.625,
"nodeInputRowsStdDev": 387.2983346207417
}]
},
"children": []
}]
}]
}]
}
}, {
"id": "2",
"stageStats": {
"totalCpuTime": "1.62s",
"inputRows": "500 rows",
"inputDataSize": "75564B",
"stdDevInputRows": "0.00",
"outputRows": "10 rows",
"outputDataSize": "1514B"
},
"outputLayout": "date, time, location, bytes, requestip, method, host, uri, status,\
referrer, os, browser, browserversion",
"logicalPlan": {
"1": [{
"name": "LimitPartial",
"identifier": "[10]",
"outputs": ["date", "time", "location", "bytes", "requestip", "method", "host", "uri",\
"status", "referrer", "os", "browser", "browserversion"],
"details": "",
"distributedNodeStats": {
"nodeCpuTime": "0.00ns",
"nodeOutputRows": 10,
"nodeOutputDataSize": "1514B",
"operatorInputRowsStats": [{
"nodeInputRows": 83.33333333333333,
"nodeInputRowsStdDev": 223.60679774997897
}]
},
"children": [{
"name": "TableScan",
"identifier": "[awsdatacatalog:HiveTableHandle{schemaName=default,\
tableName=cloudfront_logs, analyzePartitionValues=Optional.empty},\
grouped = false]",
"outputs": ["date", "time", "location", "bytes", "requestip", "method", "host", "uri",\
"status", "referrer", "os", "browser", "browserversion"],
"details": "LAYOUT: default.cloudfront_logs\ndate := date:date:0:REGULAR\nreferrer :=\
referrer: string:9:REGULAR\nos := os:string:10:REGULAR\nmethod := method:string:5:\
REGULAR\nbytes := bytes:int:3:REGULAR\nbrowser := browser:string:11:REGULAR\nhost :=\
host:string:6:REGULAR\nrequestip := requestip:string:4:REGULAR\nlocation :=\
location:string:2:REGULAR\ntime := time:string:1: REGULAR\nuri := uri:string:7:\
REGULAR\nbrowserversion := browserversion:string:12:REGULAR\nstatus :=\
status:int:8:REGULAR\n",
"distributedNodeStats": {
"nodeCpuTime": "1.62s",
"nodeOutputRows": 500,
"nodeOutputDataSize": "75564B",
"operatorInputRowsStats": [{
"nodeInputRows": 83.33333333333333,
"nodeInputRowsStdDev": 223.60679774997897
}]
},
"children": []
}]
}]
}
}]
}追加リソース
詳細については、次のリソースを参照してください。
-
Trino の「
EXPLAIN」ドキュメント -
Trino の「
EXPLAIN ANALYZE」ドキュメント -
AWS Big Data Blog の「Optimize Federated Query Performance using EXPLAIN and EXPLAIN ANALYZE in Amazon Athena
」。
