はじめに
この記事で「ナニコレ、スゴイ！！」って衝撃を受けたのでした。
QUALIFYとは何ぞやというのはこの記事にお任せしますので、とにかくまずはリンク先を読んでみてください。
この記事に全乗っかりして試してみたという話です。
実用例: 重複データの除外
ROW_NUMBER()とQUALIFYを組み合わせて、データセット内の重複データを除去し、特定の基準に基づいて優先する行だけを残すことができます。
特に↑この部分、私がSnowflakeで扱ってる対象のテーブルには重複したデータが入っているケースが実際にあるんです。
その「重複データの除外」をもうちょっと長めのクエリでやってたんです。
QUALIFYを使うように変えるとクエリがシンプルになりそうです。
得られる結果が同じ＆パフォーマンスに問題がなければQUALIFYを使う形式に変えたいと思い、検証してみました。
レッツ、リファクタリングです。
現クエリ
注）実際のテーブル名、カラム名をボカシています。
with numbering as
(
select
*,row_number() over (partition by ID order by UPDATETIME desc) as num
from TEST_LAKE_TABLE
)
select
UPDATETIME,
ID,
AAA,
BBB,
CCC
from numbering
where num = 1
group by all;
with句で先に対象テーブル全カラム＋タイムスタンプの降順で番号：numを付けてnumberingという一時Viewっぽいものを作るということをした上で、numberingからnum = 1で最新を取って、group by allで重複削除（タイムスタンプまで全く同じデータを削除）しています。
ちょっとめんどくさいし、脳ミソから拒絶反応が出ますね。
新クエリ
QUALIFYを使ってみます。
SELECT
UPDATETIME,
ID,
AAA,
BBB,
CCC
FROM TEST_LAKE_TABLE
QUALIFY ROW_NUMBER() OVER (partition by ID order by UPDATETIME desc) = 1;
with句を使わずに短くシンプルに書き換えできました。
15行⇒8行です。
- 得られる結果が同じか
- パフォーマンスに問題がないか
を見ていきます。
結果の検証
件数
まったく同じでした。
実際の全データ比較
Snowsightで出力された全件をcsvダウンロードしてDIFFツール（WinMerge）で比較して完全一致を確認しました。
※order by UPDATETIME,ID
をつけて同じ順序で出力されるようにして比較しています。
パフォーマンス
まずは結果出力の右側に出る情報でクエリ時間を確認しました。
現クエリ：2.1秒
新クエリ：1.7秒
誤差くらいかもしれませんが、パフォーマンスに問題はなさそうです。
クエリプロファイルを見ます。
クエリプロファイルの見方は（SnowProとったくせに）いまいちよくわかっていないのですが、ノードがつながってるGUI表示の部分は
現クエリ：ノードが6個
新クエリ：ノードが5個
とノードが1個減ったようです。
何が減ったかを見比べると「Aggregate」が減ってました。
現クエリにはgroup by allがあって、新クエリにはないからでした。
QUALIFYは「Aggregate」ではないんですね。
この「Aggregate」が減った分だけパフォーマンスはよくなるはずと思います。
クエリプロファイルの画面右側に出てくる値を下記表にまとめました。
クエリ内の割合で出てきた数値なので比較というのが難しく、パフォーマンス的にいいのか悪いのかの判断がつきませんでした。（比較するものじゃなさそう）
| 現クエリ | 新クエリ | |
|---|---|---|
| 処理中 | 35.30% | 29.20% |
| リモートディスクI/O | 45.10% | 50.00% |
| 同期 | 3.90% | 出力なし |
| 初期化 | 15.70% | 20.80% |
まとめ
QUALIFYを使うことで
- クエリがシンプルになる
- 集計関数を使わなくてよくなる
- 取得できる結果は全く同じ
- パフォーマンスは低下しない（よくなってるハズ）
ということが確認できました。
あえてデメリットを挙げると。。。
Snowflakeから別のデータ基盤に乗り換える必要が出てきたときに、QUALIFYはSnowflake独自の機能っぽいのでそのまま移植できないという、Snowflakeロックインの原因になることくらいかと思います。
追記
BigQuery、RedshiftでもQUALIFYは使えるみたいです。
ということはロックインにはならないのでデメリットではないと言えます。
実際のSnowflake環境の定義もQUALIFYを使うように変えていこうと思います。
元記事のモリユウキ様、ありがとうございます。