はじめまして！
JINSでデータエンジニアやネットワーク周りをやっているh0ri0です。
SnowflakeやLookerを見ることが多く、直近でコンピューティングコストの削減を行ったのでまとめてみようと思います。
背景
JINSではLookerおよびSnowflakeを導入してそれぞれ数年が経過していますが、使用量の増加と共にSnowflakeのクエリコストも増加傾向にありました。
データの活用が求められる中で今後更なるデータ量の増加が見込まれており、このままのペースでコストが増え続けていくのはまずい、、ということでLookerの処理を見直しました。
現状のコンピューティングコスト
Snowflakeのコンピューティングコストは以下のようになっており、Lookerが4～5割を消費していました。
上記の状況があるため、まずはLookerに絞ってコンピューティングコストにドラスティックな変化を与える対策がないか検討を行いました。
Lookerのクエリ分析
まずLookerで実行されるクエリのうち、どのクエリに時間がかかっているかの特定を行いました。
時間のかかっているクエリ≒Snowflakeのコンピューティングコストがかかるためです。
Lookerでは管理者画面でパフォーマンスに関する推奨事項を確認したり、クエリログを抽出したりすることが出来るのでそれらの機能を有効活用しました。
調査を行うといくつかのエクスプロールで毎回数分かかるようなものもあり、複数のエクスプロールに問題があることが分かりました！
改善策
Lookerのクエリコスト削減を行うにあたり、以下のようなアプローチがあります。
- persist_withパラメータなどを使用しキャッシュをなるべく使用するようにする
- 不要になったスケジューラなどを削除し余計なクエリが走らないようにする
- エクスプロール、ダッシュボードのフィルタ設定でデフォルトの期間設定が出来ているか見直す
- 複雑・重い結合をしているエクスプロールに対しPDT(永続化テーブル)の機能を使用する
今回は抽出に時間のかかるエクスプロールで効果の高かった(4.)PDTの設定について記載します。
Lookerではデータを腹持ちせず都度データベースにデータをリクエストするため、リクエスト時間を短くすることがSnowflakeのクエリコストを削減することにつながります。
該当のエクスプロールではLookerで派生テーブル(derived_table)の機能を使用しています。派生テーブルはクエリ結果をデータベース内の実際のテーブルのように使用できるクエリのことを指します。
この時、派生テーブルに対してはフィルタの期間指定などの条件が適用されず、全期間の集計を毎回行っていたため数分かかる処理となっていました。
PDT(永続化テーブル)を設定する
PDTの設定は簡単で、派生テーブルを使っている個所にdatagroup_triggerの設定を行うだけです。
設定例
view: order_summary {
derived_table: {
sql: ... ;;
datagroup_trigger: sales_datagroup
}
}
データグループにはキャッシュのポリシーを事前に定義したものを指定します。
改善効果
PDTを適用した改善効果は次の通りです。
Lookerのコンピューティングコストとしては45%程度削減することが出来ました！
コスト削減の他、今まで5,6分程度掛かっていたクエリもキャッシュ後は十秒程度になるなど動作面も改善しました。実行時の速度はユーザーの評価に直結するため、今回の改善を行うことで速くなったね！というフィードバックも頂くことが出来ました！
まとめ
パフォーマンスチューニングを行うときは細かくSQLの実行計画を確認して、、のようなイメージを持つことも多いですが、BIツールの機能を使用して改善出来ることもあるよーという例になったんじゃないかと思っています。
PDTに関してはキャッシュポリシーさえ押さえてけば基本的にノーリスクで実施が出来るので、データの更新頻度が高くない場合は設定をおススメします！
他に取り組んでいる内容としてコスト削減(ストレージコスト編)やSQL Sever→SnowflakeのAccessユーザーの移行を行っているので、今後記事を投稿予定です。
データの活用も進めていく中で、日々のコストも意識して今後も最適なコストで最高の価値を提供するために頑張っていこうと思います！
Discussion