背景・目的
しばらく前ですが、AthenaでGlueのパーティションインデックスが利用できるようになったので効果を確認したいと思います。
以下の観点で検証を行います。
- 観点1:パーティションインデックスを使った場合と、使わなかった場合の違い
- 観点2:パーティションインデックスが効かないクエリを調べる
まとめ
-
パーティションインデックスは有効と考えます。
- 特にパーティション数が多くなるほど、効果は絶大です。
- 対して絞り込まない、クエリの場合は差異は少ないですが、絞り込みが多いほどクエリの性能改善に繋がります。
-
パーティションのバッドプラクティスは確かに存在します。
- 一般的なRDBと同じ考え方で、NOTやORなどでは、パーティションインデックスは効きません。
- クエリを書く際には注意が必要です。
-
結果は考察にまとめました。
-
その他
- MSCK REPAIRによるパーティション作成では、なんと、6時間9分46秒かかりました。
- Crawlerで作成するほうが実行時間は短いとも聞いたことがあるので、次回は、Crawlerでパーティションを作成し、比較してみたいと思います。
概要
パーティションインデックスとは？
- そもそも、「パーティションインデックスとは何か？」について、簡単にまとめます。
課題と対応
- パーティション数が極端に多い場合、クエリの処理時間に影響が出ます。
- 例えば、年月日時分のように、毎分S3パーティションがふえていくようなVPCFlowログなどは、一年間で、525,600パーティションになります。(365日＊24時間*60分)
- パーティションの取得では、カタログに問い合わせ、テーブルの全てのパーティションを一度取得してからフィルタリングされます。あまりにもパーティションが多い場合、この取得処理がボトルネックによりクエリ全体のパーフォーマンスに影響を及ぼします。
- パーティションインデックスを利用した場合は、クエリにマッチしたパーティションのみ取得する（場合によっては、取得後にフィルタリングも行われる。）ため、このパーティション取得のオーバヘッドが下がります。そのため、数が多いパーティションではパーティションインデックスを積極的に採用していくことが重要になります。
注意点
- パーティションインデックスは、一つのテーブルあたり最大3つまで作成が可能です。
- パーティションインデックスが効く、クエリには条件があります。以下のクエリの場合、パーティションインデックスは効果がありません。
- データ型が文字列、数値以外。例えば日付型など。
- LIKE、IN、OR、NOTの演算子を使用したクエリ。
- パーティションインデックスを作成後に、パーティションキー名を変更することができません。
- インデックスに登録されているキーのデータ型、順序は変更できません。
実践
準備
S3ファイルの用意
-
以下のパーティションを用意するため、あらかじめS3オブジェクトを用意します。
- 年は、2013年〜2022年
- 月は、1月〜12月
- 日は、1日〜31日（※面倒なので、1月〜12月すべて31日分あります。。。）
- 時間は、0時〜23時
-
パーティションキーは、year,month,day,hourとします。
-
以下のデータとコマンドで、実行します。
// 対象のファイル
$ cat test.json
{"key":"a","value":1}
// これを10回(10年分)実行します。
// 最初から、yearもループにしておけばよかったと後悔。
$
for i in `seq 1 12`;do for j in `seq 1 31`;do for k in `seq 0 23`;do MONTH=`printf "%02d" $i`; DAY=`printf "%02d" $j`; HOUR=`printf "%02d" $k `;aws s3api put-object --bucket {バケット名} --key "year=2021/month=$MONTH/day=$DAY/hour=$HOUR/test.json" --body ./test.json ;done;done;done
$
- 実行結果は、以下の通りです。
- 件数が若干想定外でしたが、まぁいいでしょう。
$ aws s3 ls {バケット名} --recursive | grep json | wc -l
89279
$
Athenaのテーブルを用意
- パーティションインデックスを作成するには、以下の対応が必要です。
- パーティションキーに加えて、TBLPROPERTIESに、以下を追加します。
partition_filtering.enabled'='true'
以下のDDLを実行し、テーブルを作成します。
CREATE EXTERNAL TABLE part_db.`test`(
`key` string,
`value` bigint
)
PARTITIONED BY (
year int,
month int,
day int,
hour int)
ROW FORMAT SERDE
"org.openx.data.jsonserde.JsonSerDe"
STORED AS INPUTFORMAT
"org.apache.hadoop.mapred.TextInputFormat"
OUTPUTFORMAT
"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
LOCATION
"s3://{バケット名}/"
TBLPROPERTIES (
'partition_filtering.enabled'='true'
)
- 実行結果は、以下の通りです。
- この時点では、パーティションもパーティションインデックスも作成されていません。
SHOW CREATE TABLE `test`;
CREATE EXTERNAL TABLE `test`(
`key` string COMMENT 'from deserializer',
`value` bigint COMMENT 'from deserializer')
PARTITIONED BY (
`year` int,
`month` int,
`day` int,
`hour` int)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://{バケット名}/'
TBLPROPERTIES (
'partition_filtering.enabled'='true',
'transient_lastDdlTime'='1650438649')
パーティションの作成
-
以下のコマンドで、パーティションをカタログに登録します。
-
なんと、6時間9分46秒かかりました。
-
以下のクエリで実行結果を確認します。
-- 値の確認
aws glue get-partitions --database-name part_db --table-name test --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d "\""
===
-- 件数の確認
aws glue get-partitions --database-name part_db --table-name test | jq -r '.Partitions[].Values| @csv ' | tr -d "\"" | wc -l
- 結果は、以下の様になります。
- 89,279件でした。S3のパーティション数と同様なので問題ありません。
-- 値の確認
aws glue get-partitions --database-name part_db --table-name test --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d "\""
===
$ aws glue get-partitions --database-name part_db --table-name test --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d "\""
2013,01,03,17
2013,01,06,10
2013,01,07,11
$
-- 結果の確認
$ aws glue get-partitions --database-name part_db --table-name test | jq -r '.Partitions[].Values| @csv ' | tr -d "\"" | wc -l
89279
$
- 準備ができましたので、次よりクエリを実行したいと思います。
パーティションインデックスの確認
- 以下のクエリにより、この時点で、パーティションインデックスが存在しないことを確認します。
aws glue get-partition-indexes --database-name part_db --table-name test
- 実行結果は、以下のとおりです。
$ aws glue get-partition-indexes --database-name part_db --table-name test
{
"PartitionIndexDescriptorList": []
}
$
観点1:パーティションインデックスを使った場合と、使わなかった場合の違い
- まずは、パーティションインデックスがない状態で、クエリを実行します。
パーティションインデックスがない状態
クエリ1（指定なし）
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
{
"QueryExecutionId": "0331f1f8-a035-4a15-aa5d-6c94f7159062"
}
$ aws athena get-query-execution --query-execution-id 0331f1f8-a035-4a15-aa5d-6c94f7159062 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 1964138 | 99286 | 23353 | 176 | 37 | 99499 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
クエリ2（yearのみ指定）
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary
{
"QueryExecutionId": "216b9c98-68f3-4cd6-8935-75d297f20c42"
}
$ aws athena get-query-execution --query-execution-id 216b9c98-68f3-4cd6-8935-75d297f20c42 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 196416 | 11991 | 7701 | 137 | 20 | 12148 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ3（yearとmonthを指定）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary
{
"QueryExecutionId": "1f8f0238-1958-488d-ab67-bf00a7146e26"
}
$ aws athena get-query-execution --query-execution-id 1f8f0238-1958-488d-ab67-bf00a7146e26 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 16368 | 18193 | 14011 | 197 | 20 | 18410 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ4（yearとmonth、dayを指定）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary
- 結果は、以下のとおりです。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary
{
"QueryExecutionId": "ee12a138-cf0c-4019-a9c0-05c6ad643e37"
}
$ aws athena get-query-execution --query-execution-id ee12a138-cf0c-4019-a9c0-05c6ad643e37 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 528 | 14696 | 14304 | 176 | 54 | 14926 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ5（yearとmonthとday、hourを指定）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary
{
"QueryExecutionId": "ba5ea9ba-3d48-4342-add3-80cc2f784870"
}
$ aws athena get-query-execution --query-execution-id ba5ea9ba-3d48-4342-add3-80cc2f784870 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 22 | 13018 | 12656 | 187 | 30 | 13235 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ6（範囲検索）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary
{
"QueryExecutionId": "48959f44-f135-4e53-96a4-5a6aae9a39c5"
}
$
$ aws athena get-query-execution --query-execution-id 48959f44-f135-4e53-96a4-5a6aae9a39c5 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 506 | 16742 | 13069 | 173 | 30 | 16945 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
パーティションインデックスの作成
- 以下のコマンドで、パーティションインデックスを作成します。
- 作成に要した時間は、不明ですが15分程度でActiveになりました。
$ aws glue create-partition-index --database-name part_db --table-name test --partition-index Keys=year,month,day,hour,IndexName=year-month-day-hour
- 実行結果は、以下のとおりです。
$ aws glue create-partition-index --database-name part_db --table-name test --partition-index Keys=year,month,day,hour,IndexName=year-month-day-hour
$
-- 最初はCREATINGになります。
$ aws glue get-partition-indexes --database-name part_db --table-name test
{
"PartitionIndexDescriptorList": [
{
"IndexName": "year-month-day-hour",
"Keys": [
{
"Name": "year",
"Type": "int"
},
{
"Name": "month",
"Type": "int"
},
{
"Name": "day",
"Type": "int"
},
{
"Name": "hour",
"Type": "int"
}
],
"IndexStatus": "CREATING"
}
]
}
$
-- ACTIVEになると完了です。
$ aws glue get-partition-indexes --database-name part_db --table-name test
{
"PartitionIndexDescriptorList": [
{
"IndexName": "year-month-day-hour",
"Keys": [
{
"Name": "year",
"Type": "int"
},
{
"Name": "month",
"Type": "int"
},
{
"Name": "day",
"Type": "int"
},
{
"Name": "hour",
"Type": "int"
}
],
"IndexStatus": "ACTIVE"
}
]
}
$
パーティションインデックスがある状態
クエリ1（指定なし）
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
{
"QueryExecutionId": "27d08a76-194f-46ad-95bb-0b0d841d7911"
}
$ aws athena get-query-execution --query-execution-id 27d08a76-194f-46ad-95bb-0b0d841d7911 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 1964138 | 106746 | 23691 | 280 | 79 | 107105 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ2（yearのみ指定）
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary
{
"QueryExecutionId": "7887a578-7dc3-4ae6-8727-ed8fdaad6a77"
}
aws athena get-query-execution --query-execution-id 7887a578-7dc3-4ae6-8727-ed8fdaad6a77 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 196416 | 10979 | 7532 | 135 | 192 | 11306 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ3（yearとmonthを指定）
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary
{
"QueryExecutionId": "65a0ad4f-41cc-4768-b0f2-131a7e5f76a8"
}
$ aws athena get-query-execution --query-execution-id 65a0ad4f-41cc-4768-b0f2-131a7e5f76a8 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 16368 | 2419 | 1575 | 173 | 25 | 2617 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ4（yearとmonth、dayを指定）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary
{
"QueryExecutionId": "2a2a9e11-201d-4d65-9471-8a4d2c5a7864"
}
$ aws athena get-query-execution --query-execution-id 2a2a9e11-201d-4d65-9471-8a4d2c5a7864 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 528 | 1300 | 529 | 259 | 105 | 1664 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ5（yearとmonthとday、hourを指定）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary
{
"QueryExecutionId": "5b48240b-eb51-4799-b470-2aee63fb36e5"
}
$ aws athena get-query-execution --query-execution-id 5b48240b-eb51-4799-b470-2aee63fb36e5 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 22 | 562 | 260 | 184 | 28 | 774 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ6（範囲検索）
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary
{
"QueryExecutionId": "2a4f48eb-e1d9-4c74-b210-9735be389626"
}
$ aws athena get-query-execution --query-execution-id 2a4f48eb-e1d9-4c74-b210-9735be389626 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 506 | 1139 | 506 | 257 | 93 | 1489 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
観点2:パーティションインデックスが効くクエリと、効かないクエリの違い
- 次に、パーティションインデックスがある状態で、クエリの指定による違いを確認します。
クエリ1.インデックスを順番通り使わない（month,day,hourを指定）
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE month=01 AND day=01 AND hour=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE month=01 AND day=01 AND hour=01" --work-group primary
{
"QueryExecutionId": "c9da4008-dbc3-41dc-ba89-36b23eae2f27"
}
$
$ aws athena get-query-execution --query-execution-id c9da4008-dbc3-41dc-ba89-36b23eae2f27 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 220 | 20473 | 20037 | 196 | 63 | 20732 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ2.ORを使う場合
- 以下のクエリを実行します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2021 AND month=01 AND day=01 AND hour=01 OR hour=02" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2021 AND month=01 AND day=01 AND hour=01 OR hour=02" --work-group primary
{
"QueryExecutionId": "c719e1d8-985d-4a62-9946-2918592db99a"
}
$
$ aws athena get-query-execution --query-execution-id c719e1d8-985d-4a62-9946-2918592db99a --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 81862 | 26795 | 24914 | 262 | 74 | 27131 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ3.INを使う場合
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year IN(2021,2022) AND month=01 AND day=01 AND hour=01" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year IN(2021,2022) AND month=01 AND day=01 AND hour=01" --work-group primary
{
"QueryExecutionId": "ab5fb26c-6845-445c-968c-b45e00f36409"
}
$
$ aws athena get-query-execution --query-execution-id ab5fb26c-6845-445c-968c-b45e00f36409 --query QueryExecution.Statistics --output table
$ aws athena get-query-execution --query-execution-id ab5fb26c-6845-445c-968c-b45e00f36409 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 44 | 14321 | 13974 | 202 | 34 | 14557 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ4.NOTを使う場合
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year= 2020 AND month=01 AND day=01 AND hour NOT BETWEEN 01 AND 02" --work-group primary
- 結果は、以下のとおりです。
$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year NOT IN(2022,2020,2019,2018,2017,2016,2015,2014,2013) AND month=01 AND day=01 AND hour =01" --work-group primary
{
"QueryExecutionId": "56d9445f-34bf-40fb-a552-988c5491de01"
}
$
$ aws athena get-query-execution --query-execution-id 56d9445f-34bf-40fb-a552-988c5491de01 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GetQueryExecution |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis | QueryPlanningTimeInMillis | QueryQueueTimeInMillis | ServiceProcessingTimeInMillis | TotalExecutionTimeInMillis |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| 22 | 16605 | 16273 | 167 | 15 | 16787 |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$
クエリ5.LIKEを使う場合
- 今回、パーティションをindexで設定したため確認できず。。後ほど確認します。
aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year like '202%' AND month=01 AND day=01 AND hour=01" --work-group primary
- 結果確認用のクエリ
aws athena get-query-execution --query-execution-id {} --query QueryExecution.Statistics --output table
考察
- 観点1の結果
- パーティションインデックスで絞り込める量が大きいほど、効果はみられました。（観点1の#4.QueryPlanningTimeInMillisで、98.18%の削減効果）
- 観点2の結果
- マニュアルにあるパーティションインデックスが使用されないクエリのケースでは、いずれも全体に対して90%以上QueryPlannningTimeInMills(パーティション取得の時間と考えられる。)が占めていた。
結果の整理
- 今回、検証した結果を以下のように整理します。
観点1:パーティションインデックスを使った場合と、使わなかった場合の違い
| # | クエリのパターン | 内訳 | PartIndexなしの時間(ms) | PartIndexありの時間（ms） | QueryPlanningTimeInMillis 削減時間(削減率) |
|---|---|---|---|---|---|
| 1. | 指定なし | DataScannedInBytes | 1964138 | 1964138 | |
| EngineExecutionTimeInMillis | 99286 | 106746 | |||
| QueryPlanningTimeInMillis | 23353 | 23691 | -338（-1.44%） | ||
| QueryQueueTimeInMillis | 176 | 280 | |||
| ServiceProcessingTimeInMillis | 37 | 79 | |||
| TotalExecutionTimeInMillis | 99499 | 107105 | |||
| 2. | yearのみ | DataScannedInBytes | 196416 | 196416 | |
| EngineExecutionTimeInMillis | 11991 | 10979 | |||
| QueryPlanningTimeInMillis | 7701 | 7532 | 169(0.08%) | ||
| QueryQueueTimeInMillis | 137 | 135 | |||
| ServiceProcessingTimeInMillis | 20 | 192 | |||
| TotalExecutionTimeInMillis | 12148 | 11306 | |||
| 3. | yearとmonthを指定 | DataScannedInBytes | 16368 | 16368 | |
| EngineExecutionTimeInMillis | 18193 | 2419 | |||
| QueryPlanningTimeInMillis | 14011 | 1575 | 12436(88.75%) | ||
| QueryQueueTimeInMillis | 197 | 173 | |||
| ServiceProcessingTimeInMillis | 20 | 25 | |||
| TotalExecutionTimeInMillis | 18410 | 2617 | |||
| 4. | yearとmonthとdayを指定 | DataScannedInBytes | 528 | 528 | |
| EngineExecutionTimeInMillis | 14696 | 1300 | |||
| QueryPlanningTimeInMillis | 14304 | 260 | 14044(98.18%) | ||
| QueryQueueTimeInMillis | 176 | 259 | |||
| ServiceProcessingTimeInMillis | 54 | 105 | |||
| TotalExecutionTimeInMillis | 14926 | 1664 | |||
| 5. | year,month,day,hourを指定 | DataScannedInBytes | 22 | 22 | |
| EngineExecutionTimeInMillis | 13018 | 562 | |||
| QueryPlanningTimeInMillis | 12656 | 260 | 12,396(97.94%) | ||
| QueryQueueTimeInMillis | 187 | 184 | |||
| ServiceProcessingTimeInMillis | 30 | 28 | |||
| TotalExecutionTimeInMillis | 13235 | 774 | |||
| 6. | 範囲検索 | DataScannedInBytes | 506 | 506 | |
| EngineExecutionTimeInMillis | 16742 | 1139 | |||
| QueryPlanningTimeInMillis | 13069 | 506 | 12,563(96.12%) | ||
| QueryQueueTimeInMillis | 173 | 257 | |||
| ServiceProcessingTimeInMillis | 30 | 93 | |||
| TotalExecutionTimeInMillis | 16945 | 1489 |
観点2:パーティションインデックスが効かないクエリの時間
| # | クエリのパターン | 内訳 | 時間(ms) | QueryPlanningTimeInMillisが 全体の実行時間に占める割合 |
|---|---|---|---|---|
| 1. | month,day,hourのみ指定 | DataScannedInBytes | 220 | |
| EngineExecutionTimeInMillis | 20473 | |||
| QueryPlanningTimeInMillis | 20037 | 96.64% | ||
| QueryQueueTimeInMillis | 196 | |||
| ServiceProcessingTimeInMillis | 63 | |||
| TotalExecutionTimeInMillis | 20732 | |||
| 2. | ORを指定 | DataScannedInBytes | 81862 | |
| EngineExecutionTimeInMillis | 26795 | |||
| QueryPlanningTimeInMillis | 24914 | 91.82% | ||
| QueryQueueTimeInMillis | 262 | |||
| ServiceProcessingTimeInMillis | 74 | |||
| TotalExecutionTimeInMillis | 27131 | |||
| 3. | INを指定 | DataScannedInBytes | 44 | |
| EngineExecutionTimeInMillis | 14321 | |||
| QueryPlanningTimeInMillis | 13974 | 95.99% | ||
| QueryQueueTimeInMillis | 202 | |||
| ServiceProcessingTimeInMillis | 34 | |||
| TotalExecutionTimeInMillis | 14557 | |||
| 4. | NOTを指定 | DataScannedInBytes | 22 | |
| EngineExecutionTimeInMillis | 16605 | |||
| QueryPlanningTimeInMillis | 16273 | 96.93% | ||
| QueryQueueTimeInMillis | 167 | |||
| ServiceProcessingTimeInMillis | 15 | |||
| TotalExecutionTimeInMillis | 16787 |
参考