この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
さがらです。
SnowflakeのRELY制約プロパティについて、Tableauから参照する場合でも効果があるか確かめてみたのでその内容をまとめてみます。
結論
先に結論を書きますが、事前に関係するテーブルすべてをJOINしたViewをTableauから参照するときにRELY制約プロパティの効果があるため、UNIQUE、 PRIMARY KEY、FOREIGN KEYの各制約の整合性をユーザー側で担保できる場合には、積極的にRELY制約プロパティをかけていきましょう！
逆に、Tableau上でリレーションシップや結合を用いてJOINした場合は、Tableau側でクエリの最適化が行われるのでRELY制約プロパティの効果はありません。他のBIツールだと結果は変わるかもしれませんね。
以下、実際に検証した内容について記します。
RELY制約プロパティとは
まず、「RELY制約プロパティ」について説明します。
RELY制約プロパティは、Snowflake上でUNIQUE、PRIMARY KEY、FOREIGN KEYの各制約をかける場合に設定できるオプションで、RELY制約プロパティがかけられたテーブル同士をJOINしたクエリを実行するとき不要なテーブルのJOINをなくすことができるオプションです。
以下の図はBenjaminさんがRELY制約プロパティを試された記事からの引用で、「5つのテーブルをすべてJOINしているが、SELECT句で指定するカラムは1つのテーブルから」というクエリを実行したときのクエリプロファイルの画像なのですが、RELY制約プロパティをかけている場合は1つのテーブルしかスキャンしていませんが、RELY制約プロパティをかけていない場合は5つのテーブルすべてをスキャンしてJOINしています。
- RELY制約プロパティをかけている場合
- RELY制約プロパティをかけていない場合
RELY制約プロパティの注意点
一見便利そうに見えるため、「すべてのテーブルにRELY制約かけないと！」と思ってしまいそうですが、気をつけなければいけないこともあります。
それは、公式Docにも書かれている通り、各制約の整合性はSnowflake側で担保してくれないので、ユーザー側で担保しないといけないことです。
制約（UNIQUE、 PRIMARY KEY、および FOREIGN KEY）の整合性を維持する責任はご自身にあります。制約の整合性が維持されていない場合、 RELY 制約プロパティが設定されていると、クエリの結果が異なる場合があります（NORELY を使用した結果と比較して）。
Snowflake単体であれば2023年2月にリリースされたAlertを使用したり、dbtと併せて使っているのであればdbtのtestなどを用いて、各制約の整合性が取れているかどうか確認するのが良いと思います。
（これは余談ですが、各制約をSnowflake側で担保してくれるHybrid Tableならば、RELY制約プロパティがより運用しやすくなるかもしれませんね）
事前準備
まず事前に各制約とRELY制約プロパティをかけたテーブルを準備する必要があるのですが、私は下記のBenjaminさんの記事のサンプルクエリを丸パクリして準備しました。（Benjaminさん、事前に検証頂き本当にありがとうございます…）
実際には比較検証をするため、各制約とRELY制約プロパティをかけた場合・かけない場合、の2パターンでスキーマとテーブルを準備しました。
また、結果キャッシュを使わないように事前にアカウントレベルで結果キャッシュを無効化しておきます。検証後に有効化するのを忘れないようにだけ注意です！
-- アカウントレベルで結果キャッシュを無効化
use role accountadmin;
alter account set use_cached_result = false;
-- 結果キャッシュのパラメータの値の確認
show parameters like '%USE_CACHED_RESULT%';試してみた：関係するすべてのテーブルをJOINしたViewをTableauから参照
まず、関係するすべてのテーブルをJOINしたViewを用意し、そのViewをTableauから参照した場合にどうなるかを見てみます。
関係するすべてのテーブルをJOINしたViewは、以下のクエリで準備しました。このViewでは、JOINしたすべてのテーブルからカラムを1つ以上SELECTしています。
関係するすべてのテーブルをJOINするViewを定義するクエリ ※「▶」を押して展開
create or replace view all_join_view as (
with all_products as (
select
*
from
products as p
left join
countries as c
on
p.country_code = c.code
)
select
p.customer_id,
p.item_id,
p.purchase_date,
c.customer_name,
c.status,
cal.oyasumi,
ap.item_name,
ap.price,
ap.country_code,
ap.name as country_name
from
purchase as p
left join
customers as c
on
p.customer_id = c.customer_id
left join
calendar as cal
on
p.purchase_date = cal.date
left join
all_products as ap
on
p.item_id = ap.item_id
);この上で、TableauからはこのViewを参照し、下図のように1つだけcustomer_idというフィールドを選択したときにSnowflakeのクエリ履歴上のプロファイルがどう変わっているかを見てみます。
各制約とRELY制約プロパティをかけた場合
RELY制約プロパティをかけた場合は、purchaseテーブルとproductsテーブル、2つのテーブルをスキャンした上でJOINする処理が実行されました。
JOINを完全になくすことは出来なかったのですが、Viewに定義したクエリ内容やTableauから発行される集計クエリ内容を元に、JOINをなるべく減らして最適化しようとしていることが伺えます。
各制約とRELY制約プロパティをかけない場合
RELY制約プロパティをかけない場合は、下図のようにViewで定義されている5つのテーブルすべてをスキャンした上でJOINする処理が実行されました。
1つのテーブルのフィールドしか用いないのに、都度すべてのテーブルをJOINしているのは無駄ですよね。
試してみた：Tableauのデータソース上でリレーションシップ・結合の定義してフィールド選択
次に、事前にSnowflake上でJOIN定義したViewではなく、Tableau上でリレーションシップや結合を用いて各テーブルをJOINした場合に、Snowflakeのクエリ履歴上のプロファイルがどう変わっているかを見てみます。
リレーションシップ
下図のように、5つのテーブルをリレーションシップでJOINしたとき、purchaseテーブルのitem_idフィールドだけを選択したときの、プロファイルを確認していきます。
各制約とRELY制約プロパティをかけた場合
RELY制約プロパティをかけた場合は、purchaseテーブルだけがスキャンされました。
各制約とRELY制約プロパティをかけない場合
RELY制約プロパティをかけない場合は、purchaseテーブルだけがスキャンされました。
RELY制約プロパティをかけてもかけなくてもpurchaseテーブルしかスキャンしなかったのは、Tableau側で発行されるクエリが最適化されているためだと思います。
結合
下図のように、5つのテーブルを結合でJOINしたとき、purchaseテーブルのpurchase_dateフィールドだけを選択したときの、プロファイルを確認していきます。
各制約とRELY制約プロパティをかけた場合
RELY制約プロパティをかけた場合は、purchaseテーブルだけがスキャンされました。
各制約とRELY制約プロパティをかけない場合
RELY制約プロパティをかけない場合は、purchaseテーブルだけがスキャンされました。
RELY制約プロパティをかけてもかけなくてもpurchaseテーブルしかスキャンしなかったのは、Tableau側で発行されるクエリが最適化されているためだと思います。
まとめ
冒頭の結論の繰り返しですが、今回の検証でわかったことは以下の2点です。
- Tableauから参照する場合は、事前に関係するテーブルすべてをJOINしたViewをTableauから参照するときにRELY制約プロパティの効果がある
- 逆に、Tableau上でリレーションシップや結合を用いてJOINした場合は、Tableau側でクエリの最適化が行われるのでRELY制約プロパティの効果はない
そのため、ユーザーにJOINなどを考えさせず、事前に関係するテーブルすべてをJOINしたViewを用意してユーザーに使用させる場合にはRELY制約プロパティが活きてくると思いました！テーブル数が尋常でなくユーザー側でJOINの定義が難しい場合などは役立ちそうですね。
また改めてですが、RELY制約を使用する場合にはユーザー側で各制約の整合性を担保する必要があるので、この点だけ注意しましょう！
5
