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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

CONFIDENTIAL ©2024 ARISE analytics 2 背景と目的背景 ✓ Snowflakeを活用したデータ基盤案件にて、ユーザよりクエリ速度改善の相談を受けた。 ✓ その調査・解決の中で、Snowflake のパフォーマンスチューニングについての学びを得た。本日の目的 ✓ 今回の事例を紹介しつつ、その中で得たSnowflakeのパフォーマンスチューニングに関する知見を紹介する。 (諸事情により、一部クエリや列名などは実際と異なる形になっています)

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

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基盤構築趣味・好きなもの旅行、バイク、日本酒、ダイビング

Slide 6

Slide 6 text

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日現在会社概要事業領域

Slide 7

Slide 7 text

Slide 8

Slide 8 text

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型でテーブルに保存している構造体データから、ビュー定義で列として抽出している値

Slide 9

Slide 9 text

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型で保持。 • ロードされる際、対象データの日付やファイル名はテーブルに列として追加 ✓ ビューにおいては、半構造化データを、より分析者が利用しやすい形に加工して公開

Slide 10

Slide 10 text

Slide 11

Slide 11 text

CONFIDENTIAL ©2024 ARISE analytics 11 https://docs.snowflake.com/ja/user-guide/tables-clustering-micropartitions Appendix1: パーティションプルーニング ✓ Snowflakeのテーブルはマイクロパーティションという分割されたストレージの集合で成り立っている。 ✓ SQLにおけるWhere句などの指定に応じて、データの取得に必要なマイクロパーティションだけを選択的に読み込むことをパーティションプルーニング(=剪定)という。

Slide 12

Slide 12 text

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';

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

CONFIDENTIAL ©2024 ARISE analytics 15 https://medium.com/snowflake/introducing-the-snowflake-visual-table-clustering-explorer- 6fbb66a15bd5 Appendix2: ナチュラルクラスタリング ✓ クラスタリングとは、マイクロパーティション内でデータを順序立てて整理すること。 ✓ Snowflakeにおいては、全てのデータが、取り込まれた順序によって、自動的かつ自然にクラスタ化される(ナチュラルクラスタリング)。 ✓ クラスタリングキーを指定することで、意図的にクラスタリングを実施することも可能。

Slide 16

Slide 16 text

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 | | }, | | } | +--------------------------------------------------------------------+

Slide 17

Slide 17 text

CONFIDENTIAL ©2024 ARISE analytics 17 https://docs.snowflake.com/ja/user-guide/tables-clustering-micropartitions#clustering-depth-illustrated Appendix3: SYSTEM$CLUSTERING_INFORMATION ✓ テーブルの1つ以上の列に基づいて、テーブルの平均クラスタリング深度を含むクラスタリング情報を返すシステム関数 ✓ クラスタリングキーとして定義していない列も指定可能(将来クラスタリングキーとして設定する値の決定にも利用可能)

Slide 18

Slide 18 text

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, | | }, | | } | +--------------------------------------------------------------------+

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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, | | }, | | } | +--------------------------------------------------------------------+

Slide 22

Slide 22 text

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 一括ロードでロードされた日付日次ロードでロードされた日付

Slide 23

Slide 23 text

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);

Slide 24

Slide 24 text

CONFIDENTIAL ©2024 ARISE analytics 24 まとめ ✓ Snowflakeのメリットを最大限に生かすためには、その仕組みを正しく理解することが重要であることを改めて痛感しました、、、 ✓ 今回の対応を行うにあたって、過去受講したSnowflakeの公式トレーニング、 Snowflake Advancedで学んだ内容が非常に役立ちました！(受講機会を提供してくれた会社に感謝) ✓ 今回の学びが他の方のお役に立てば幸いです。

Slide 25

Slide 25 text

No content