Snowflakeで知っていると便利なシステム関数についてまとめてみた～情報関数編～ #SnowflakeDB

アドベントカレンダー2021

#Snowflake

さがら

2021.12.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の14日目の記事となります。

さがらです。

Snowflakeのシステム関数のうち情報を返す機能を持つ関数について、知っていると便利なものを本記事でまとめてみます。

システム関数とは

システム関数とは、以下の3種類に分類される、システムに関連した動作をする関数です。

システムでアクションを実行できるようにする制御機能（例：クエリの中止）
システムに関する情報を返す情報関数（例：テーブルのクラスタリングの深さの計算）
クエリに関する情報を返す情報関数（例： EXPLAIN プランに関する情報）

システム関数に関する公式Docは下記になります。

この記事では、このうちの「システムに関する情報を返す情報関数」について個人的に知っていると便利だと感じる関数を説明します。

クラスタリングに関する情報を得たい

前提知識：マイクロパーティションとクラスタリング

まず、Snowflakeはマイクロパーティションの形式でデータを保持しており、基本的にはロードされた順番と一部の日付系のフィールドに基づきマイクロパーティションが生成されていきます。これをユーザー特に設定することなく行ってくれるため、ナチュラルデータクラスタリングとも呼ばれています。

このナチュラルデータクラスタリングとマイクロパーティションのおかげで、クエリ実行時に必要なマイクロパーティションだけがクエリされるため、クエリの実行速度が早くなるというメリットを得ることが出来ます。

しかし、TB規模以上のテーブルとなると、ナチュラルデータクラスタリングではユーザーの意図通りにマイクロパーティションが生成されず、クエリの実行速度が思いの外早くならないケースがあります。

そんな時に、ユーザーが任意のクラスタリンキーを設定して、マイクロパーティションをユーザーが指定したフィールドの内容に応じて生成することができます。

このマイクロパーティションやクラスタリングに関する詳細は、下記の記事が参考になりますのでぜひご覧ください。

任意のクラスタリングを設定する時の困りごと

前置きが長くなりましたが、任意のクラスタリングを設定しようと思った時に、下記のような問題があります。

ナチュラルデータクラスタリングで適切にクラスタリングされているかわからない
任意のクラスタリングキーを設定した後でも、適切にクラスタリングされているかわからない

そこで、対象のテーブルのクラスタリングに関する情報を得られるSYSTEM$CLUSTERING_INFORMATIONが役に立ちます。

SYSTEM$CLUSTERING_INFORMATION

第1引数にいれたテーブル名のクラスタリング情報を確認し、返してくれます。

また、第2引数にカラム名を入れることも可能なのですが、こちらのオプションの仕様は以下のようになっています。（公式Docより引用）

クラスタリングキーのないテーブルの場合：この引数は必須です。この引数を省略すると、エラーが返されます。
クラスタリングキーを持つテーブルの場合：この引数はオプションです。引数を省略すると、Snowflakeは定義されたクラスタリングキーを使用してクラスタリング情報を返します。

以下は、クラスタリングキーを指定した際のクエリサンプルです。（公式Docより）

select system$clustering_information('test2', '(col1, col3)');

こちらの結果からこのテーブルではクラスタリングが適切にされていないということがわかるのですが、それを理解するポイントは以下です。

average_overlapsの値が大きい（average_overlapsは、テーブル内の各マイクロパーティションでの、重複するマイクロパーティションの平均数です。）
average_depthの値が大きい（average_depthはテーブル内の各マイクロパーティションの平均重複深度を表しています。）
partition_depth_histogramを見ると、00064や00128などヒストグラムの末端で各マイクロパーティションがグループ化されている。

他のパラメータについては、以下の公式Docにより詳細に説明が書いてあります。

検索最適化サービスのコスト見積がしたい

Snowflakeの検索最適化サービスは、数十億件とあるデータの中から一部のレコードだけを抽出したいようなクエリの動作速度の向上に役立つ機能です。（Enterpriseエディション以上でのみ使用可能）

しかし、サーバーレス機能であることに加え、動作速度向上のために使われる検索アクセスパスの構築にどれだけストレージとクレジットが消費されるか、コスト感がわかりづらい機能だと感じる方も多いと思います。

そこで、対象のテーブルを指定することで簡易的に見積を取得できるシステム関数SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSがあります。

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS

引数にいれたテーブルに対して、検索アクセスパス構築に必要な消費クレジット量、検索アクセスパスに必要なストレージ容量、直近のテーブルの更新状況に応じた今後のメンテナンス用の消費クレジット量、を見積して返してくれます。

以下は、検索最適化サービスが適用されていないテーブルに対してこの関数を実行した時のクエリサンプルです。（公式Doc）より

select system$estimate_search_optimization_costs('TABLE_WITHOUT_SEARCH_OPT');

この関数が返す結果の詳細については、下記の公式Docも併せてご覧ください。

ストリームがデータを持っているかどうか判別したい

ストリームは、テーブルやビュー等と同じように扱えるオブジェクトの1種です。このストリームをテーブルに対して設定すると、そのテーブルの変更履歴をストリームに追跡・記録してくれます。「変更データキャプチャ(Change Data Capture)」という手法・仕組みを実現できるものとなっています。

SQLをスケジューリングすることが出来るタスクと組み合わせることで、「ストリームにデータが入っている時」にタスクを実行することも可能です。

この「ストリームにデータが入っている時」の判別に役立つのが、SYSTEM$STREAM_HAS_DATAです。

SYSTEM$STREAM_HAS_DATA

引数にストリーム名を指定することで使用可能なシステム関数です。ストリームにデータがある場合にはTrueを返し、データがない場合にはFalseを返します。

以下はクエリサンプルです。（弊社ですでに検討しているストリームを用いたデータパイプライン構築記事より引用）

下記の通り、基本的にはCREATE TASKコマンドの中でWHEN句と併せて使用するシステム関数となっています。

CREATE OR REPLACE TASK log_to_stock
  WAREHOUSE = X_SMALL_WH
  SCHEDULE = '1 minute'
WHEN
  SYSTEM$STREAM_HAS_DATA('log_stream')
AS
  INSERT INTO stock
  SELECT
    record:ticker_symbol::VARCHAR AS ticker_symbol,
    record:sector::VARCHAR AS sector,
    record:price::NUMBER AS price,
    record:change::NUMBER AS change
  FROM
    log_stream

この関数についてより詳細な説明を知りたい場合は、下記の公式Docよりご確認ください。