Athenaを使ったバッチ処理のTIPS

https://jawsug-bigdata.connpass.com/event/200841/

BigData-JAWS 勉強会#16 LT 資料

UZOUでのAthenaの設計の話はブログにも書いていますので興味があればぜひ
https://tech.speee.jp/entry/2020/11/10/111154

1. Athenaを使ったバッチ処理のTIPS kagawa shoichi (@kanga333) 2021/3/1 BigData-JAWS 勉強会#16 LT
2. ⾃⼰紹介 Kagawa Shoichi(@kanga333) Infra Engineer @Speee,Inc ネイティブアドプラットフォームのUZOUの開発/運⽤ 主にインフラやデータ基盤周りを⾒ています 好きなAWSサービスはAthenaとCost Exploreとサポート
3. はじめに UZOUではデータ基盤にAthenaを使っていています Daily3万件くらいのクエリが流れてます ほとんどがバッチ処理 この資料ではAthenaの設計/運⽤で培ったTIPSについて取り留めなく話ます
4. Athenaが遅いと感じたら
5. まずは基本を抑える データをパーティショニングする partition projection を使うとパーティション管理不要で便利 列指向フォーマット+圧縮を使⽤する 列指向 : ORC or Parquet おすすめは Parquet 各種SaaSやミドルウェアのサポートが⼿厚い S3 Select とか 圧縮: Snappy or Gzip おすすめは Gzip Athenaはスキャン量課⾦なのでCPUコストより圧縮率が⼤事
6. それでも遅いケース（ GetQueryExecution API は遅い） バッチ処理でAthenaにクエリを投げて結果を習得したい場合を想定 使っているライブラリがAPIからデータを取得していると遅い GetQueryExecution API はクエリの実⾏結果をページネーションして返す 結果の件数が⼤きいとページネーションにより何度もリクエストを送る必要があるので 遅い
7. 対応: クエリ結果ファイル(csv)をダイレクトにDLして使う Athenaはクエリの実⾏結果をcsvで Output Location に書き出す このファイルをS3から直接DLして使⽤すると通信回数が抑えられる いくつかのクライアントライブラリ にこの機能を実装しているものがあります（ありが たや） awslabs/aws-data-wrangler laughingman7743/PyAthena burtcorp/athena-jdbc speee/go-athena など
8. それでも尚遅い場合（クエリ結果のサイズが⼤きいと遅い） Athenaはクエリの結果のデータサイズが⼤きくなるほど遅くなる特性がある そもそもクエリが完了するまでにかかる時間が遅くなる
9. クエリ結果のサイズが⼤きいと遅い実例 Parquet形式のALBログ（30ファイル、計1.8GB）から request_url を取るクエリ SELECT request_url FROM alb_log 結果 Run time: 1 minute 27 seconds Data scanned: 976.47 MB クエリ結果ファイル: 5GB
10. クエリ結果のサイズが⼤きいと遅い実例 Parquet形式のALBログ（30ファイル、計1.8GB）から request_url のパス別の件数を 取得するクエリ SELECT url_extract_path(request_url), count(*) FROM alb_logs GROUP BY url_extract_path(request_url) 結果 Run time: 4.64 seconds Data scanned: 976.47 MB クエリ結果ファイル: 6KB クエリとしては複雑になってるが素朴なSELECTより遥かに早い クエリ結果ファイルがデカくなるとAthenaは遅くなる
11. クエリ結果ファイルが⼤きくなると遅くなる原因（想像） なぜこのような特性となるか？ Athena(のベースとなってるPresto)は分散処理のSQL実⾏エンジン ⼤量のデータを複数のWorkerが処理するから早い しかしクエリ結果ファイルは最終的に1つのCSVとして出す必要がある 出⼒の箇所を分散できないから遅い おまけに結果ファイルは無圧縮なのでS3とのIO時間もかかる
12. 対応: CTASクエリでデータを書き出してダイレクトにDLする CREATE TABLE AS SELECT ( CTAS )クエリはSELECT結果を新しいテーブルとして作成 できる CTASクエリは参照結果を新しいテーブル定義+S3上のデータとして作成する データは分散されたワーカーから複数個に分割されて作成されるので効率が良い バッチのクライアントはCTASでS3に⽣成したデータを直接DLして使⽤する クエリ実⾏時に透過的にCTASで実⾏してくれるモードを持つライブラリ awslabs/aws-data-wrangler speee/go-athena
13. 実例のクエリをCTASで実⾏すると CREATE TABLE tmp_table WITH (format='PARQUET') AS SELECT request_url FROM alb_logs 結果 Run time: 11.68 seconds Data scanned: 976.47 MB CTASで⽣成したデータ: 30ファイル、計977.3 MB 同じデータを出⼒するにしてもCTASの⽅が7倍以上早い
14. パーティションについてのTIPS
15. 時系列パーティション、細かく切るか？ざっくり切るか？ 時系列データのパーティションをどの粒度で切るか？ 細かく切ると year=yyyy/month=MM/day=dd のような形式 ざっくり切ると dt=yyyyMMdd のような形式 年⽉を跨いだクエリがシンプルに書けるのでざっくり切るほうがオススメ！ だけども最終的にはどっちでもある程度シンプルに書けます
16. 時系列パーティション、細かく切るか？ざっくり切るか？ 例: 2021年1⽉30⽇から2021年2⽉1⽇までのデータを参照したい -- 細く切った場合 year='2021' AND ( (month='01' AND day >= '30') OR (month='02' AND day <= '01') ) ) -- ざっくり切った場合 dt BETWEEN '20210130' AND '20210201' ざっくり切った場合は開始と終了だけ指定すれば良いので楽
17. 時系列パーティション、細かく切るか？ざっくり切るか？ 例: 2021年1⽉のデータを参照したい この場合どっちでもシンプルに書ける -- 細く切った場合 year='2021' AND month='01' -- ざっくり切った場合 dt LIKE '202101%'
18. 細く切っても⼤丈夫！パーティションに複雑な時間指定をする ケース パーティションをUTCの⽇付( dt )と時間( hour )で区切って格納している 但し集計ではJSTの 2021-02-01 ~ 2021-02-03 の値を出したい 時差を考慮するのがめんどい
19. 細く切っても⼤丈夫！複雑な時間指定でパーティションを絞る DATE_PARSEのような関数を通してもパーティションのフィルタリングは効く WHERE DATE_PARSE(concat(dt, hour),'%Y%m%d%H') >= timestamp '2021-02-01 00:00:00 Asia/Tokyo' AND DATE_PARSE(concat(dt, hour),'%Y%m%d%H') < timestamp '2021-02-04 00:00:00 Asia/Tokyo' このクエリとスキャン量の絞り込みは同じ WHERE (dt = '20210131' AND hour >= '15' ) OR dt = '20210201' OR dt = '20210202' OR (dt = '20210203' AND hour < '15' ) ただし関数を通しているケースだとシンプルな絞り込みと⽐べて多少遅い
20. Parquetを使っていればパーティションはざっくり指定でも⼤丈夫 Parquetを使っていてデータ本体に時刻カラムがある場合パーティションカラムでざっく り絞って時刻カラムでちゃんと絞ってもスキャン量は抑えることでできる こんな感じのクエリ（timeはデータ本体にあるdatetime型のカラムと仮定） WHERE dt BETWEEN '20210131' AND '20210203' AND time >= timestamp '2021-02-01 00:00:00 Asia/Tokyo' AND time < timestamp '2021-02-04 00:00:00 Asia/Tokyo' Parquetファイルはフッターに各カラムの統計情報（Max/Min/Count）を持っており Athenaはそこを参照して効率よくスキャンをスキップできる 但しS3のGET APIのコストは余分にかかるので注意
21. コストについてのTIPS
22. 意外とS3のGetリクエストのコストがかかる AthenaのコストといえばScanのコストばかり気にしがちだがS3へのGetリクエストのコ ストも意外とチリツモでかかる 実験 Parquet形式のALBログ（30ファイル、計1.8GB）に以下のクエリを実⾏ SELECT url_extract_path(request_url), count(*) FROM alb_logs_access WHERE elb_status_code = '200' このクエリがS3にアクセスした回数は160回（S3のメトリクスフィルタ調べ） Athenaはスキャン対象の個数 = S3へのリクエスト回数となる訳では無い クエリが早くなるように⾊々最適なアクセスを試みてくれる
23. 意外とS3のGetリクエストのコストがかかる S3のGetリクエストの単価は超安いけれども Athenaでスキャンするデータはビッグデータなので件数が多い そこにクエリ実⾏件数が多い&複雑なクエリでS3へのアクセス回数が多いとかが積 み重なっていくとチリツモでコストが増える ⼀度AWS Cost Exploreで APN1-Requests-Tier2 とかのコストを⾒てみることを オススメします
24. S3のGetリクエストが結構かかっていた場合の対応 ⼩さいファイルを作りすぎない ストリームからのデータをそのまま使わない 時間単位で列指向フォーマットに変換したり コンパクションして複数ファイルをまとめる パーティションで区切って指定する パーティションを区切らないと全ファイルに対してアクセスされる
25. おわり 今回紹介したTIPSはワークアラウンドがほとんどであるため⽤法⽤量を守ってお使い下 さい この資料での例⽰のクエリは全てAthena version2で検証しています