Snowflakeでスロークエリ改善に取り組んだ話

2024/10/08に実施されたSnowVillage Unconference #3にて登壇した際の投影資料です。
現在関わっているSnowflakeを用いたDMP基盤構築案件でスロークエリの改善に取り組んだので、その概要をまとめました。

Transcript

1. CONFIDENTIAL ©2024 ARISE analytics 株式会社ARISE analytics 田畑 幹 Snowflakeでスロークエリ改善に取り組んだ話 2024/10/20
2. CONFIDENTIAL ©2024 ARISE analytics 2 背景と目的 背景 ✓ Snowflakeを活用したデータ基盤案 件にて、ユーザよりクエリ速度改善の 相談を受けた。 ✓ その調査・解決の中で、Snowflake のパフォーマンスチューニングについての 学びを得た。 本日の目的 ✓ 今回の事例を紹介しつつ、その中で得 たSnowflakeのパフォーマンスチューニ ングに関する知見を紹介する。 (諸事情により、一部クエリや列名などは実際と異な る形になっています)
3. CONFIDENTIAL ©2024 ARISE analytics 3 目次 2 自己紹介 1 3 取組紹介 余談
4. CONFIDENTIAL ©2024 ARISE analytics 4 目次 2 自己紹介 1 3 取組紹介 余談
5. CONFIDENTIAL ©2024 ARISE analytics 5 自己紹介 株式会社ARISE analytics Team Lead 田畑 幹 経歴 ➢ 銀行 • 法人営業 ➢ Web企業 • Python x Django x AWSのWebアプリ開発をメインで担当。 • Reactを使用したSPA開発にも参画。 ➢ ARISE analytics プロジェクト • 店頭端末でのレコメンド機能PoC • 会員向け特典サービスの分析 • Terraform, Kubernetes Kubernetesを使用したアプリ展開・管理基盤 のPoC • BigQueryを中心としたGAアクセスログ集約GCP基盤の構築 • Snowflakeを用いたDMP基盤構築 趣味・好きなもの 旅行、バイク、日本酒、ダイビング
6. CONFIDENTIAL ©2024 ARISE analytics 6 ARISE analytics とは？ • 社名 • 資本構成 • 営業開始 • 所在地 • 役員 • 社員数 ： 株式会社ARISE analytics ： KDDI 85% Accenture 15% ： 2017年4月1日 ： 渋谷区 渋谷2-21-2 渋谷ヒカリエ31F ： 代表取締役 坂本 崇 / KDDI ： 170名/業務委託・派遣含め約560名 ※2024年4月1日現在 会社概要 事業領域
7. CONFIDENTIAL ©2024 ARISE analytics 7 目次 2 自己紹介 1 3 取組紹介 余談 発端 システム構成 調査 改善
8. CONFIDENTIAL ©2024 ARISE analytics 8 発端 クエリ SELECT COUNT(*) FROM GA_EVENT_VIEW WHERE TO_CHAR("EVENT_TIMESTAMP",'YYYYMMDD’) = TO_CHAR(DATEADD('month’, 1,CURRENT_TIMESTAMP),'YYYYMMDD'); 実行時間 3m54s ✓ 運用中のSnowflake基盤にて、利用者から特定 のビューに対してクエリを実行した際に、パフォーマン スが出ないとの調査依頼をいただいた。 ✓ BigQueryのGAログをSnowflakeに格納した ビューに対してのクエリ。 • ただ1ヶ月前の日付のデータをCOUNT()で集 計するだけなのに、4分弱かかる。。。 • WHERE文に使用している EVENT_TIMESTAMP列は、VARIANT型で テーブルに保存している構造体データから、 ビュー定義で列として抽出している値
9. CONFIDENTIAL ©2024 ARISE analytics 9 Google Cloud Google Cloud Storage events └y=2023 └y=2024 └m＝01 └m＝02 └d＝01 └d＝02 └00001.parquet.snappy └00002.parquet.snappy └00003.parquet.snappy └... BigQuery システム構成 凡例 データの流れ テーブル ビュー 日次テーブル events_yyyymmdd ユーザ 分析用 マート (月次) 分析用 マート (日次) Snowflake 分析者向け 区画 生データ区 画 GAログ テーブル GAログ ビュー ✓ 対象はGoogle Analytics(=GA)ログのテーブル ✓ GAからBigQueryについてはGA側のマネージド機能で あるBigQuery Exportで連携。日次で日付毎にテー ブルが作成される。 ✓ 当該データを日次でGoogle Cloud Storage(GCS) にエクスポート。Hive形式で日付毎に切られたフォルダ 配下に、複数ファイルに分割した形で出力される。 ✓ GCSはSnowflake側から外部ステージとして登録 ✓ 当該GCSからSnowflakeのタスク機能で日次ロードを 実施。 • ロードされた半構造データはVARIANT型で保持。 • ロードされる際、対象データの日付やファイル名は テーブルに列として追加 ✓ ビューにおいては、半構造化データを、より分析者が利 用しやすい形に加工して公開
10. CONFIDENTIAL ©2024 ARISE analytics 10 調査: 方針検討 ✓ まずは問題となっているクエリのクエリプロファイルを確認する ✓ パフォーマンスに特に大きな影響を与えるパーティションプルーニングの状況を理 解するため、スキャンされたパーティション数を確認。
11. CONFIDENTIAL ©2024 ARISE analytics 11 https://docs.snowflake.com/ja/user-guide/tables-clustering-micropartitions Appendix1: パーティションプルーニング ✓ Snowflakeのテーブルはマ イクロパーティションという分 割されたストレージの集合で 成り立っている。 ✓ SQLにおけるWhere句など の指定に応じて、データの取 得に必要なマイクロパーティ ションだけを選択的に読み 込むことをパーティションプ ルーニング(=剪定)という。
12. CONFIDENTIAL ©2024 ARISE analytics 12 https://articles.analytics.today/snowflake-cluster-keys-and-micro-partition-elimination-best-practices Appendix1: パーティションプルーニング ✓ Snowflakeのテーブルはマ イクロパーティションという分 割されたストレージの集合で 成り立っている。 ✓ SQLにおけるWhere句など の指定に応じて、データの取 得に必要なマイクロパーティ ションだけを選択的に読み 込むことをパーティションプ ルーニング(=剪定)という。 select * from sales where sales_date = '14-Feb-22';
13. CONFIDENTIAL ©2024 ARISE analytics 13 調査: 結果 ✓ 問題となったクエリのクエリ プロファイルを確認したとこ ろ、全てのパーティションを スキャンしていることを確 認。 ✓ つまり、パーティション プルーニングは全く効いて いない状況。。 43933 43933
14. CONFIDENTIAL ©2024 ARISE analytics 14 改善: 方針検討 ✓ パーティションプルーニングを効果的に活用するため、where文に指定する値を検 討。 ✓ 現状のクエリをみる限り、特定の日付に絞り込んでいる様子。 ✓ 本テーブルは日次でロードしているので、日付でのナチュラルクラスタリングが効い ているのでは？
15. CONFIDENTIAL ©2024 ARISE analytics 15 https://medium.com/snowflake/introducing-the-snowflake-visual-table-clustering-explorer- 6fbb66a15bd5 Appendix2: ナチュラルクラスタリング ✓ クラスタリングとは、マイクロ パーティション内でデータを順 序立てて整理すること。 ✓ Snowflakeにおいては、全 てのデータが、取り込まれた 順序によって、自動的かつ自 然にクラスタ化される(ナチュ ラルクラスタリング)。 ✓ クラスタリングキーを指定する ことで、意図的にクラスタリン グを実施することも可能。
16. CONFIDENTIAL ©2024 ARISE analytics 16 Appendix3: SYSTEM$CLUSTERING_INFORMATION ✓ テーブルの1つ以上の列に基 づいて、テーブルの平均クラス タリング深度を含むクラスタリ ング情報を返すシステム関 数 ✓ クラスタリングキーとして定義 していない列も指定可能(将 来クラスタリングキーとして設 定する値の決定にも利用可 能) > SELECT SYSTEM$CLUSTERING_INFORMATION('test2', '(col1, col3)'); a+--------------------------------------------------------------------+ | SYSTEM$CLUSTERING_INFORMATION('TEST2', '(COL1, COL3)') | |--------------------------------------------------------------------| | { | | "cluster_by_keys" : "LINEAR(COL1, COL3)", | | "total_partition_count" : 1156, | | "total_constant_partition_count" : 0, | | "average_overlaps" : 117.5484, | | "average_depth" : 64.0701, | | "partition_depth_histogram" : { | | "00000" : 0, | | "00001" : 0, | | "00002" : 3, | | "00003" : 3, | | "00004" : 4, | | ..... | | "00032" : 98, | | "00064" : 269, | | "00128" : 698 | | }, | | } | +--------------------------------------------------------------------+
17. CONFIDENTIAL ©2024 ARISE analytics 17 https://docs.snowflake.com/ja/user-guide/tables-clustering-micropartitions#clustering-depth-illustrated Appendix3: SYSTEM$CLUSTERING_INFORMATION ✓ テーブルの1つ以上の列に基 づいて、テーブルの平均クラス タリング深度を含むクラスタリ ング情報を返すシステム関 数 ✓ クラスタリングキーとして定義 していない列も指定可能(将 来クラスタリングキーとして設 定する値の決定にも利用可 能)
18. CONFIDENTIAL ©2024 ARISE analytics 18 改善: 方針決定 ✓ ロード時に付与しているEVENT_DATE(= データの対象日付)を表す列を、引数に 「SYSTEM$CLUSTERING_INFORMAT ION」を実行してみたところ、パーティションの 深さが1のものが大きな割合を占めていること が判明。 ✓ 当該列と、元のクエリで使っていた日時の値 (=TO_CHAR("EVENT_TIMESTAMP",' YYYYMMDD’))の値は一致することも確認。 ✓ 元のクエリの条件文を、当該列を用いたもの に置き換えることに決定！ a+--------------------------------------------------------------------+ | SYSTEM$CLUSTERING_INFORMATION(‘TARGET_TABLE', ‘(EVENT_DATE)') | |--------------------------------------------------------------------| | { | | "cluster_by_keys" : "LINEAR(EVENT_DATE)", | | "total_partition_count" :30860, | | "total_constant_partition_count" : 28021, | | "average_overlaps" : 35.3227, | | "average_depth" :21.8815, | | "partition_depth_histogram" : { | | "00000" : 0, | | "00001" : 28019, | | "00002" : 0, | | "00003" : 6, | | "00004" : 4, | | ..... | | "00032" : 352, | | "00064" : 324, | | "00128" : 5, | | "00256" : 9, | | "00512" : 2042, | | }, | | } | +--------------------------------------------------------------------+
19. CONFIDENTIAL ©2024 ARISE analytics 19 改善: 実施 ✓ 元のクエリの条件文を、当該 列を用いたものに置き換えて、 実行してみたところ、パーティ ションプルーニングが効果的に 実行され、速度は大きく改 善！ ✓ クエリの結果の件数も同じに なっていることを確認！！ ✓ 追加改修・デグレなしでのパ フォーマンス改善に成功！！ 改善後 改善前 クエリ SELECT COUNT(*) FROM GA_EVENT_VIEW WHERE EVENT_DATE = DATEADD('month',- 1,CURRENT_DATE); SELECT COUNT(*) FROM GA_EVENT_VIEW WHERE TO_CHAR("EVENT_TIMESTAMP",' YYYYMMDD') = TO_CHAR(DATEADD('month',- 1,CURRENT_TIMESTAMP),'YYYY MMDD'); スキャンされた パーティション数 32/43933 43933/43933 実行時間 799ms!! 3m54s
20. CONFIDENTIAL ©2024 ARISE analytics 20 目次 2 自己紹介 1 3 取組紹介 余談
21. CONFIDENTIAL ©2024 ARISE analytics 21 余談1: ロード時のファイル毎のクラスタリング ✓ COPY INTOでのロード時に、ロード対象のデータが 存在していたファイルの名称は 「metadata$filename」で取得可能。 ✓ デバッグ用途で当該項目も今回の対象テーブルに実 装していた。 ✓ その項目で、先述の 「SYSTEM$CLUSTERING_INFORMATION」を 実行してみたところ、なんと平均深度が「1.0001」と いう結果に！ ✓ 基本的には同一ファイルのデータは同一パーティション にロードされる？(公式ドキュメントでそのような記載は 見受けられなかったので要検証) a+--------------------------------------------------------------------+ | SYSTEM$CLUSTERING_INFORMATION(‘TARGET_TABLE’, ‘(FILE_NAME)') | |--------------------------------------------------------------------| | { | | "cluster_by_keys" : "LINEAR(FILE_NAME)", | | "total_partition_count" :30860, | | "total_constant_partition_count" :30860, | | "average_overlaps" :1.0E-4(0.00010), | | "average_depth" :1.0001, | | "partition_depth_histogram" : { | | "00000" : 0, | | "00001" : 30858, | | "00002" : 2, | | "00003" : 0, | | "00004" : 0, | | ..... | | "00032" : 0, | | "00064" : 0, | | "00128" : 0, | | "00256" : 0, | | "00512" : 0, | | }, | | } | +--------------------------------------------------------------------+
22. CONFIDENTIAL ©2024 ARISE analytics 22 余談2: 一括ロードした場合のナチュラルクラスタリング ✓ 今回のデータ連携においては、リ リース時にリリース日までの複数日 付データの一括ロードを実施、以 降のデータを日次ロードする形を とった。 ✓ 一括ロード対象の日付で、 SELECTしたところ、定期ロード開 始した後の日付でクエリをした時対 比、読まれるパーティションは多く なった ✓ 一括ロード時は、日次ロード時ほど ナチュラルクラスタリングは効かない 模様(それでも十分プルーニングは 効いている)。 where句に 設定する日付 2024/12/17 2024/12/18 2024/12/19 2024/12/20 2024/12/21 読まれた パーティション数 287 245 194 115 98 全体の パーティション数 30860 一括ロードでロードされた日付 日次ロードでロードされた日付
23. CONFIDENTIAL ©2024 ARISE analytics 23 余談3: パーティションプルーニングの注意点 ✓ プルーニングが効果的な列を where文で使う場合も、型変換 を実行すると、プルーニングが効か なくなるので要注意！(RDBにお けるインデックスと同様) • where文での加工が必要な 場合は、加工した値をクラスタ リングキーとして明示的に設定 すること ✓ 日付データを右辺をdate from parts()で作成した場合もパーティ ションが効かなくなることも観察した のでご共有、、、 SELECT * FROM ga_events WHERE event_date = to_date(‘2024-10-01’ ); SELECT * FROM ga_events WHERE to_char(event_date, ‘YYYYMMDD’) = ‘20241001’; SELECT * FROM ga_events WHERE event_date = date_from_parts(2024, 10, 1);
24. CONFIDENTIAL ©2024 ARISE analytics 24 まとめ ✓ Snowflakeのメリットを最大限に生かすためには、その仕組みを正しく理解するこ とが重要であることを改めて痛感しました、、、 ✓ 今回の対応を行うにあたって、過去受講したSnowflakeの公式トレーニング、 Snowflake Advancedで学んだ内容が非常に役立ちました！(受講機会を提 供してくれた会社に感謝) ✓ 今回の学びが他の方のお役に立てば幸いです。