[Snowflake] Search Optimization Service（検索最適化サービス）を使用してDWH向きじゃないクエリのパフォーマンスを上げる #SnowflakeDB

大阪オフィス所属だが奈良県でリモートワークしている玉井です。

今回は、SnowflakeのSearch Optimization Serviceという使ってみました。これを使うと、ある条件に該当するクエリが爆速になります。

公式情報

注意事項

• Search Optimization Serviceは2020年9月現在、プレビュー版となります。
• Search Optimization ServiceはEnterprise Edition以上でのみ使用可能です。

Search Optimization Serviceとは

ざっくりいうと「DWH向きじゃないクエリのパフォーマンスを上げる」機能です。

…「そんなんで分かるか！」という声が聞こえてきそうなので、もう少し具体的にお話します。

DWHとDBの違い（それぞれに向いているクエリのタイプ）

先述したように「ざっくり」わかってもらうため、ここでは「DWH向き」「DWH向きじゃない」といった言葉をあえて使用しています。何が「DWH向き」で、何が「DWHに向いていない」のか、理解するための一番確実な方法は、下記の記事（と動画）を見ていただくことです。

上記の資料の一部を引用します。

トランザクション処理と分析処理では、データに対するアクセスパターンが異なる

DWHはデータ分析を前提として設計されているものですが、データ分析のクエリというのは、往々にして「対象が大量のレコード」「少数の列」であることが多いです（そしてDWHはそれに最適化されている）。当然っちゃ当然の話で、「分析」である以上、大量のデータをガッ！と集計することが多いからですね。

それに対してDBは「条件に該当する少数のレコードを見つける」というタイプのクエリに向いています。アプリケーションのバックエンドとして使われることが多いため、例えば、画面操作に応じて求められているデータを迅速に出す、みたいなケースにあっているという話ですね。

DWHは後者のDB型のクエリ（=DWHに向いていないクエリ）ってのが苦手です（パフォーマンスが出ない）。なぜなら、データ分析用に設計されているものだからです。

Snowflakeは両方いけるように

…が、しかし！SnowflakeのSearch Optimization Serviceを使うと、DWHに向いていないクエリ（条件に該当する少数のレコードを見つける）のパフォーマンスを上げることができます。

とりあえずやってみた

使用データ

Snowflakeにサンプルデータとして元々入っているTPCHを使います。今回はそれの一番デカいやつで検証します。

検証にあたって

• CUSTOMERテーブル（15億件）からWHERE句で絞るクエリで検証
  • サンプルデータ自体はデータシェアリングされているものなので、CTASで別途テーブルを用意
  • データシェアリングされているDBは当機能の対象外
• 仮想ウェアハウスはXS
• キャッシュは切る

Search Optimization Serviceを使用しない場合

下記のクエリを発行します。ちなみに、取得できる結果としては1行のみとなっています。

SELECT
    *
FROM
    "TAMAI_TEST_DB"."TPCH_SF10000"."CUSTOMER"
WHERE
    c_phone = '30-182-970-5754';

1分43秒かかりました。

Search Optimization Serviceを使用した場合

Search Optimization Serviceは、有効にしたいオブジェクトに対して、ALTER〜ADDを実施することで、有効にします。

ALTER TABLE "TAMAI_TEST_DB"."TPCH_SF10000"."CUSTOMER" ADD SEARCH OPTIMIZATION;

上記実行後、Search Optimization Serviceの効果が即反映されるというわけではないらしく、多少時間を要します（おそらくテーブルの大きさによる）。今回の15億件のテーブルで数十分待った感じでした。

ちなみに公式ドキュメントには下記のように書かれています。

検索最適化が最初にテーブルに追加されたとき、パフォーマンスの利点はすぐには現れません。検索最適化サービスは、バックグラウンドでデータの入力を開始します。メンテナンスがテーブルの現在の状態に追いつくにつれて、利点の増加が明らかになります。

Search Optimization Serviceが有効になっているかどうかは、テーブルの詳細情報でわかります（SHOW TABLES）。

カラム名が見切れてるんですけど、SEARCH_OPTIMIZATION_PROGRESSという値が100になっていればSearch Optimization Serviceが適用できていると判断できます（Search Optimization Serviceが適用できている割合なので、ここが100%だったらOKという感じ）。

Search Optimization Serviceを有効にしたところで、上記クエリを再実行します。

1.7秒で終わりました。

いいですか皆さん。Search Optimization Serviceを有効にする前は1分43秒（103秒）でした。約60倍のパフォーマンス向上です。もはや怖いレベルですね。

BIツールの立場から見ても嬉しい機能

「DWHに向いていないクエリ」ということで、データ分析とは違う領域で使う機能という印象を受けますが、Search Optimization Serviceは、BIツールで作成したダッシュボードにも役立ちます。

分析用のダッシュボードといっても、全部が全部「何かを集計する」っていうものではないことが多いです。「いくつかディメンションを選択して、条件に該当するデータを確認する」という用途のダッシュボードも結構見たことありますよね。

上記はLookerにあるサンプルダッシュボードですが、このように、閲覧側で項目を選べるようになっていて、それに該当するデータに絞り込んで表示する…というダッシュボードは多いと思います。こういうダッシュボードが参照するテーブルに、Search Optimization Serviceを適用しておくと、パフォーマンスの向上が見込めるかもしれません。

使う前に確認しておくこと

適用されるクエリの条件が細かいので要確認

目に見えて効果がわかるSearch Optimization Serviceですが、効果を出すための仕様というものが細かくあります。詳細は当記事冒頭から飛べる公式ドキュメントを参照してほしいですが、代表的なものを引用してみます。

• テーブル側
  • テーブルサイズが100GB以上ある
  • クラスタ化されていない
  • 主クラスターキー以外の列で頻繁にクエリされる
• クエリ側
  • デフォルトだと少なくとも数十秒間かかる
  • WHERE句で絞るカラムのうち、1つは、最低でも100,000〜200,000個のユニーク値がある

一番最後について補足します。例えば下記のクエリは実行時間が全く変わりませんでした。

SELECT
    *
FROM
    "TAMAI_TEST_DB"."TPCH_SF10000"."CUSTOMER"
WHERE
    c_mktsegment = 'BUILDING';

これはc_mktsegmentというカラムに、ユニーク値が少ないことが原因と考えられます。

実際調べてみると、圧倒的に少なかったです。

SELECT
    approx_count_distinct(c_mktsegment)
FROM
    "TAMAI_TEST_DB"."TPCH_SF10000"."CUSTOMER";

他にも「Search Optimization Serviceの恩恵を受けるクエリ」に関する条件等が色々あるので、公式ドキュメントをしっかり確認しましょう。

費用についても要確認

当然っちゃ当然ですが、コストに影響する機能となっています。詳細は、これまた公式ドキュメントに書いてあるのでチェックしておきましょう。

超ざっくりいうと、下記のような感じです。

• ストレージコスト
  • Search Optimization Serviceのための「検索アクセスパスデータ構造」が作成されるので、その分増える
• コンピューティングリソース
  • Search Optimization Serviceをテーブルに適用するとコンピュートリソースが消費される
  • サーバーレス機能扱い
  • なので、サーバーレス機能のコスト確認のところで、当機能のコストの確認が可能
  • サーバーレス機能の請求について — Snowflake Documentation

おわりに

Snowflakeは最近「データアプリケーション」というメッセージを打ち出してきており、Snowflakeが単なるデータウェアハウスだけの利用にとどまらないことを示しています。Search Optimization Serviceもそれの一環なのかなあと感じていますが、個人的には（前述しているように）BIツールにも嬉しい機能だと思いました。