[失敗談]Partition Projectionの設定不足で1クエリ毎にS3 ListBucketが692,040回呼ばれていた話

こんにちは、洲崎です。Partition Projectionを手探りでやっていたら、S3がとんでもないことになってしまったので紹介します。。

いきなりまとめ

• Partition Projectionを正しく設定できていなかった
  • 一部設定出来ていたが故に、'projection.year.range'で設定していた2021年〜2100年までのパーティション(計692,040パーティション)を都度S3から呼び出してしまった
• パーティションを改善したらAthenaで30分かかってタイムアウトしてたのが、2秒程でクエリが完了するようになった

クエリをすると異様に時間がかかる。。

Amazon ConnectのCTRデータをAmazon S3に貯めて、QuickSight(Athena)で見にいく内容で検証していました。(構成自体はメインの話ではない為省きます)
year,month,day,hourでパーティション化したテーブルを作成し、QuickSightのデータセットをAthenaで設定し、カスタムSQLをセットしてました。
QuickSightで日付を指定(WHERE)したときはすぐに出てくるのですが、日付指定をしないとQuickSightは2分でタイムアウトになり、裏のAthenaは30分ほどクエリを実行しタイムアウトしてました。
その時、私の検証環境には2.5KB×4ファイル程度しかなかったため、なぜこんな時間がかかるのかを調査してました。
ただAthenaで検査するデータ量は大したことなかったので、QuickSightで色々な条件を当てて裏のAthenaを見て...といった形で検証していました。
(今思えばこの段階からPartition Projectionを疑うべきでした)

AWS Budgetのアラート

そんな時、AWS Budgetのアラートメールが届きました。
内容を見ると、指定した上限(＄50)を大幅に超えているという内容です。$240程発生してました。

(はじめてBudgetアラートを経験しました)
Cost Explorer等から原因を見ていくと、QuickSightで利用していたS3バケットの「ListBucket」が明らかにおかしい数字で跳ね上がっていました。(45,591,875回ListBucketを呼んでました)
この時、Athenaのクエリ料金ばかり気にしていた為、マジかよ！とかなり慌てました。

サブクエリが原因？

当初、クエリの時間がかかるのはサブクエリが原因かと考えてました。
QuickSightはビジュアルタイプによってその条件のクエリが実行されますが、カスタムSQLをセットしていた為、サブクエリの組み合わせによって悪さをしてるのではと考えてました。(下のような構成です)

SELECT /*QuickSightのクエリ*/
FROM (SELECT 
/*カスタムSQLのクエリ*/

ただ、ここの設定を変えてもクエリ時間は変わらず、、(Limitをサブクエリに入れてた別問題もありましたが、それは別で紹介します)
エラーが出ないが故に原因も分からず、クエリをする毎にS3で大量の「ListBucket」が発生する為、、社内の先輩方に相談しました。

Partition Projectionの設定が原因

結論、Partition Projectionの設定が原因と判明しました。
設定していたテーブル情報をSHOW CREATE TABLEコマンドで見たところ、PARTITONED BYは問題なさそうですが、TBLPROPERTIESの設定が怪しいことがわかりました。

/*TABLE情報を一部抜粋*/
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.IgnoreKeyTextOutputFormat'
LOCATION
  's3://xxxxxxxx/contact-trace-records'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.day.digits'='2', 
  'projection.day.range'='1,31', 
  'projection.day.type'='integer', 
  'projection.enabled'='true', 
  'projection.hour.digits'='2', 
  'projection.hour.range'='0,23', 
  'projection.hour.type'='integer', 
  'projection.month.digits'='2', 
  'projection.month.range'='1,12', 
  'projection.month.type'='integer', 
  'projection.year.digits'='4', 
  'projection.year.range'='2021,2100', 
  'projection.year.type'='integer', 
  'storage.location.template'='s3://xxxxxxxx/contact-trace-records/${year}/${month}/${day}/${hour}', 
  'transient_lastDdlTime'='1664947695')

この設定自体でも動くものの、WHEREでyear,month,day,hourを指定しないと、'projection.year.range'='2021,2100'の79年分(692,040パーティション)を都度見にいく設定になっていました。
まずはテーブル自体を作り直した方がいいだろうということで、Glue Crawler(クローラー)を利用してS3を指定してテーブルを作り直しました。(Glue Crawlerについてはこちら↓)

その上でPartition Projectionの設定情報を付け加えるのと、日付情報をdateに纏める形で修正することで、クエリのパフォーマンスやリクエスト数が劇的に改善しました。

/*TABLE情報を一部抜粋*/
PARTITIONED BY ( 
  `date` string)
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://xxxxxxxx/contact-trace-records'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='ctr', 
  'averageRecordSize'='2572', 
  'classification'='json', 
  'compressionType'='none', 
  'has_encrypted_data'='false', 
  'objectCount'='4', 
  'projection.date.format'='yyyy/MM/dd', 
  'projection.date.interval'='1', 
  'projection.date.interval.unit'='DAYS', 
  'projection.date.range'='2022/01/01,NOW+9HOURS', 
  'projection.date.type'='date', 
  'projection.enabled'='true', 
  'recordCount'='4', 
  'sizeKey'='10291', 
  'storage.location.template'='s3://xxxxxxxx/contact-trace-records/${date}', 
  'transient_lastDdlTime'='1673871968', 
  'typeOfData'='file')

↑のTBLPROPERTIESはCrawlerのデータ等も入ってますが、変更前と比べて下記内容が追記・修正した重要なポイントになっています。

PARTITIONED BY ( 
  `date` string)
...
'projection.date.format'='yyyy/MM/dd', 
'projection.date.interval'='1', 
'projection.date.interval.unit'='DAYS', 
'projection.date.range'='2022/01/01,NOW+9HOURS', 
'projection.date.type'='date', 
'projection.enabled'='true', 
'recordCount'='4', 

対策

Athenaのフルスキャンを避ける

今回の問題点は、WHEREでyear,month,day,hourを指定しないと、フルスキャンでAthenaが実行され大量のリクエストがS3に走り、コストとパフォーマンスに影響が出ることです。
その為、Partition Projectionの設定後は、フルスキャンで実行されないように、クエリ実行時にパーティションを必ず指定するようにします。

負荷軽減

この構成を取っても、スキャンの量によってパフォーマンスに影響がでる可能性があります。
例えば、'projection.year.range'を過度に長期間を設定してしまうと、その分パフォーマンスに影響が発生します。
また、時刻単位で絞ることが必須出なければ、dateのみに纏める等、細かすぎるパーティションを避けるようにします。

今回は'projection.year.range'が'2021,2100'と過度に長期間設定されていた為、'projection.date.range'='2022/01/01,NOW+9HOURS'に修正しました。
また、パーティションもyear,month,day,hourと細かく切っていた為、PARTITIONED BY ( `date` string)
でdateにまとめる形で修正しました。

反省

Partition Projection以外が原因だと思っていた

Partition Projectionは設定済みと考え、QuickSightによるサブクエリが原因だと思い込んでいました。
PARTITIONED BYの設定は入っていた為、テーブルで見るとyear,month,day,hourは(パーティション化済み)と記載されています。

これで勝手にPartition Projectionが正しく設定されていると思い込んでいたのが原因でした。
Partition Projectionの設定について理解を深め、SHOW CREATE TABLEコマンドで見て、ドキュメントと照らし合わせて確認すべきだと反省しました。

Glue CrawlerでGlueテーブルを作成する

今回のテーブルは人の手でCREATE EXTERNAL TABLEを書いて作成してました。
AWSのAthenaでGlueを利用するベストプラクティスには、テーブル作成でGlue Crawlerを利用することがあげられています。

基本的にテーブル作成をGlue Crawlerに任せて、Partition Projection等細かいところは後から追記する形がよさそうです。

最後に

Partition Projectionの設定不足の失敗談についてまとめました。
全体的にSQL周り含め勉強不足なところや、もう少しドキュメントや状況から予測できただろう、、といったところがあるので、勉強しつつ次に活かします。

最後にこの場を借りて、本件で相談にのっていただいた石川さん、いわささん、irbbbさん、のんピさん、suzuki.ryoさん、ありがとうございました。

ではまた！コンサルティング部の洲崎でした。

参考