こんにちは，AI事業本部Dynalystの暮石です．

今回は CyberAgent Developers Advent Calendar 2021 19日目の記事として，snowflakeというDWH(データウェアハウス)上でクエリのパフォーマンスチューニングに取り組んだ話について紹介します．

本投稿から，snowflake上でクエリのパフォーマンスを改善するためにどんなことをするといいかについて知ってもらえたら幸いです．

snowflakeとは？

snowflakeとは，クラウド型のDWHサービスです．

最大の特徴は，コンピュートとストレージでリソースを完全に分離したアーキテクチャを採用しており，計算リソースを増やすために簡単にインスタンスのスケールアップ・スケールアウトができる点です．そのため，必要な時に必要な分だけ計算リソースを増やすことができます．

また課金形態として，従量課金を採用していいます．

パフォーマンス改善に取り組んだ背景

snowflakeでは計算リソースを増やすために簡単にインスタンスのスケールアップができるので，通常では困難な計算リソースの確保が容易になり，エンジニアはクエリの応答時間やスループットの向上のためのチューニングから開放されます．ウェアハウスのスケールアップはクエリのパフォーマンス改善のための簡単な方法となりますが，コストの増大をまねきます．したがって，最適なウェアハウスを都度選択することが重要になります．

我々のプロダクトでは，snowflakeを運用していくなかで「仮想ウェアハウスを不要に大きくしてしまった結果，コストパフォーマンスの悪化を招いてしまっている」といった事例に遭遇しました．その中で「そもそも現状snowflakeの性能を最大限まで引き出せているのだろうか」といった疑念が出てきました．

そこで，snowflakeの性能を最大限まで引き出せているか検証するためクエリのパフォーマンス改善に取り組みました．

クエリのパフォーマンスチューニングの方針

クエリのパフォーマンスを改善することを目指すわけだが，パフォーマンスを改善する切り口として単一のクエリを改善するアプローチとクエリが走るワークロードを改善するアプローチがある．前者は，DBMSの性質に合わせたクエリの記述を変更することによって単一のクエリの応答時間の改善を目指のに対して，後者は単一のクエリ自体を改善するのではなくクエリが実行されるタイミングを最適化したり，キャッシュ機能を効率的に使ったりすることによって，全体でスループットの向上を目指します．

snowflakeの性能を適切に引き出してあげることが最もやるべきことなので，単一のクエリの応答時間の改善に取り組みます．

優れたパフォーマンスを達成するためには以下の要素が大事だと言われています．

データの型とスキーマの設計
インデックスの設計
クエリの設計

これらは密接に関係しているため，切り離して考えることはできません．

加えてsnowflake上でクエリのパフォーマンスを改善するためには，snowflakeのアーキテクチャや機能を踏まえてパフォーマンスを改善する必要があります.

したがって，snowflakeのアーキテクチャや肝となる機能を抑えて実際にどう改善するといいかを考えます．

snowflakeのパフォーマンンスを改善する上で重要なのは，マイクロパーティションです．

（マイクロパーティションは，コンピュートとストレージでリソースを完全に分離したアーキテクチャの弱みを回避するのに役立っています．）

コンピュートとストレージでリソースが分離しているアーキテクチャの下では, データの読み込みがボトルネックとなるケースが多いです. snowflake ではマイクロパーティショニングと呼ばれるパーティショニング形式を採用することによって，効果的なプルーニングを可能にし, 不要な読み込みを回避しています.

マイクロパーティションがどのような構成になっているかは省略しますが，snowflakeの特徴をいかすためにはデータの読み込みをいかに抑えてあげるかが重要です．

マイクロパーティショニングはインデックスを構成することとほとんど同じことを行なっているので，インデックスが設定された下でのクエリの設計と同様のアプローチをとることでパフォーマンスの改善が見込まれます．

クエリチューニングのベストプラクティスの紹介

ここまでsnowflakeのアーキテクチャと機能として抑えておかなければいけないポイントについて紹介してきました．ここでは，それを踏まえてどのようにsnowflakeの性能を引き出すかを記述し，なぜそれが重要かを説明します．

以下で説明するベストプラクティスは個人の見解ではなくsnowflake summit 2021のセッションの中で紹介されていたものになります．

以下はsnowflakeの性能を引き出すための重要な考え方です．

プルーニングを行うため効果的なカラムをフィルターの条件に記述する
フィルターの条件に式を入れない

また，以下で記述するベストプラクティスは必ずしもsnowflakeだけに特化した最適化ではありませんが，snowflakeでも効果があることを確認できました．

複数テーブルで結合してから集約する場合，CTE(Common Table Expression)の中で集約しておく
Group By句を利用する場合，一意であることがわかっているカラムは記述しないようにする
選択できるデータの型のうち，データサイズが最も小さいものを使用する

プルーニングに効果的なカラムをフィルター条件に記述する

snowflakeにおいて最も重要なのはいかにディスクIOを減らすかです．

snowflakeはマイクロパーティションごとにデータを格納していることから，いかに不必要なマイクロパーティションの読み取りを削減できるかが重要です．

各パーティションは各カラムの最初値，最大値などのメタデータを保持しているため，その情報を使って不要なスキャンを回避することができます．

フィルターの条件に式を入れない

フィルターの条件に式を追加してしまうとフルテーブルスキャンになってしまうため，プルーニングに効果的なカラムを条件に用いる場合であっても式による評価は良い方法とは言えません．

おまけ

ここまでは，snowflake上でクエリのパフォーマンスを引き出すためにどのような戦略が取れるかについて紹介してきました．

我々はDWHを

アドホックな分析
各種バッチ処理のデータソース
BIツール上での可視化

といった用途で利用しています．

アドホックな分析や各種バッチ処理を実装するためのSQLは，多くのケースで複雑になることがあると思います．SQLを記述するときにはクエリが高速で返ってくることも大事ですが，正確に記述できているかも大事だと思います．

SQLのパフォーマンスを改善することも大事ですが、一方で欲しいデータを正確に取得するためにはクエリの可読性も大事な要素です．

ここではおまけとして，snowflake上でSQLを簡潔に書くためのTipsを紹介します．

サブクエリ内で全選択(SELECT * FROM …)を使用して，簡潔に記述する

列指向ストレージを採用しているDBMSでSQLを記述するときによくあるTipsとして

「列思考のデータは列ごとに格納されているから，必要な列のみの読み取りを可能にしているので，安易にクエリ内で全選択(SELECT * FROM …)を不要に使用しないようにしよう」

とよく耳にします．

この記述の仕方は，パフォーマンスを向上できる一方でクエリが長くなり視認性が悪くなりがちです．このようなケースでsnowflakeのオプティマイザは視認性を保ちつつ，パフォーマンスを向上させてくれます．

言葉だけではわからないと思うので，実際に例を記述したいと思います．

サブクエリを使った例としては以下を使いたいと思います．


select
  logged_at,
  uid,
  advertiser_id
from (
  select
    logged_at,
    uid,
    advertiser_id
  from imp_log
  where logged_at between '2021-11-01 00:00:00' and '2021-11-01 01:00:00'
)

この記述をした場合の実行プランは以下のようになっています．

ストレージから指定したカラムだけ読み取ることができていることがわかります．

このような記述の仕方，列指向のストレージのDSMS上でのクエリのパフォーマンスを向上してくれますが，サブクエリと外側のSELECTで同じカラムを指定しているので冗長です．

次に，Snowflakeのオプティマイザを最大限利用した記述をします．


select
  logged_at,
  uid,
  advertiser_id
from (
  select
    *
  from imp_log
  where logged_at between '2021-11-01 00:00:00' and '2021-11-01 01:00:00'
)

この記述をした場合の実行プランは以下のようになっています．

実行プランで見るとサブクエリ内は全検索を使用したにもかかわらず，外側のSELECT句で指定したカラムだけを読み取ることができていることがわかります．

サブクエリを使っているクエリでは，多くの場合必要なカラムだけを取り出そうとすると冗長な記述になってしまいますが，snowflakeではサブクエリ内で全検索(SELECT * FROM …)を使って記述しても，参照されている列だけを読み取ってくれます．

最後に

本項では，クエリの応答時間を改善することを目的にクエリのパフォーマンスチューニングとして効果的だった項目とクエリを簡潔に書くためのTipsも紹介しました．

実際取り組んでみるとプルーニングの効果が支配的であり，プルーニングが効果的に働くように記述することが以外の工夫は思った以上の効果は見込めませんでした．

しかしながら，snowflakeを使いこなせているかと言われると使いこなせているわけはありません．

今後はsnowflakeを快適に使っていくため，データのサイズとウェアハウスの選択の問題や標準の単一クラスタウェアハウスについて取り組んでいけたらと考えています．

なぜあなたのA/Bテストはうまくいくのか？A/Bテストの分析で注意すること

「ピグパーティ」で数百に及ぶFlash製のモーションデータをSpineに変換した話

snowflakeでのクエリのパフォーマンス改善に向けた取り組み

目次