SnowflakeのData Clean Room(DCR)が超シンプルになってたのでまとめる
はじめに
２年くらい前から、キーワードとしてはよく耳にするデータクリーンルーム(以下DCR)。実現方法が日々進化して、最近のSnowflakeだと超簡単に実現出来るようになってたのでざっくり解説する。
Data Clean Room(DCR)が必要な背景とか、ビジネス的なメリットとかは説明しないので、ググって良さそうな記事でご確認ください。
なぜSnowflakeだとDCRが超簡単なのか？
元々Snowflakeのデータ共有が超簡単
そもそもSnowflakeのデータ共有方法が超簡単で(これが9割くらい)、それに個人情報が見られない設定である「制約ポリシー(後述)」をすると、簡単にData Clean Room(以下、DCR)が実現出来ますよというお話。画面ポチポチでわりと出来る。
将来的にはSnowflakeがどのクラウドプロバイダ上で動いていても気にせずに、DCRが実現出来るようになるのがうれしい(プライベートベータ時点ではAWS版のみサポート)
ちなみに今までは…
ちなみに今までは「行アクセスポリシーをちょっと工夫して使うことで、特定のSQLのみ許可する」という、いわゆるホワイトリストで特定のSQL(COUNTなど集計系クエリ)を許可する方式で実現していたが、後述の制約ポリシー機能の登場で大まかなルールを決めておくだけで、簡単にDCRが実現出来るようになった。うれしい。
いままでは複数のSQLをホワイトリストに登録するか、柔軟にしたい場合はクエリテンプレを用意して、都度ストアドで判断して…みたいな工夫が必要だったが後述の方式ではだいぶシンプルになりましたよーと。
どうやるか？
実現方法は、SnowflakeのQuickStartsのCreate a Native Data Clean Roomが詳しい。設定用のサンプルSQLも用意されている。やさしい。でもプライベートプレビューの機能使ってる…
以下、ざっくり流れを説明していきます(QuickStartが公開される前に9割方書いていたのです…)
1.大まかな設定の流れ(共有元)
実際にはSnowflakeのアプリ共有(NativeApps)の仕組みも利用しているけど、詳細は省略。
1-1.共有したいテーブルを選択
共有先に対して、DCRとして共有したいデータ(テーブル)を選ぶ。この時点では共有されてはないので大丈夫。
1-2.共有したいテーブルに制約ポリシーを割り当てる
先程選択した「共有したいテーブル」にDCR用の制約ポリシー(集計ポリシーと投影ポリシー)を割り当てる。[1]
設定の例はこんな感じ。これで列の値そのもの(例えばメールアドレスや名前など)が共有先に見られることはなくなくなります(共有先からクエリしてみた感じは後述)
集計ポリシーは集計クエリ(COUNTとかSUMとか)しか許可しないポリシー、投影ポリシーは特定の列の値を表示しないポリシー。2つ組み合わせることでより強固になるイメージ。
1-3.共有先にテーブル(ビュー)を共有する
Snowflakeのデータ共有機能を使って、テーブルを特定の相手にのみ共有する(裏側でSnowflake側がDCR用に色々工夫してくれて、実態としては制約がついたビューで共有される)
やっぱりSnowflakeのデータ共有が簡単なのがうれしい。共有方法は下記の記事でもうちょっと詳しく説明してる。そのなかで3番目に紹介している「プライベートリスト」を利用する。共有先が複数社あってもOKだし、クラウドやリージョンが共有元と同じでなくても共有できるのがうれしい(DCRはプライベートプレビュー時点ではAWSのみ対応なので注意)
2.大まかな利用の流れ(共有先)
2-0.共有されたDCRが表示されるので取得する
ここは通常のデータがプライベートリストで共有されたときと同じ感じで取得。自社データと似たような感じで表示される。
2-1.許可された集計系のクエリは通る
共有されたDCR(実態は共有された制約付きビュー)にクエリを投げてみる。Snowflakeなので、自社のテーブルと同じように扱うことが出来てうれしい。
集計系は値が返ってくる
共有元に集計クエリ→OK
SELECT gender, interest, count(*)
FROM nature_clips_dcr.nature_clips_schema.visitors
GROUP BY gender, interest;
DCRっぽくオーバーラップを見てみる
まず自社の顧客数が52621人で…
SELECT count(distinct customer_id)
FROM KAYAK_CO_DB.KAYAK_CO_SCHEMA.CUSTOMERS;
共有元と自社データのオーバーラップを見てみる(emailアドレスで2社のJOIN) → OK
SELECT
count(distinct customer_id) overlap_cnt,
count(distinct customer_id) / (SELECT count(distinct customer_id) FROM customers) overlap_ratio
FROM KAYAK_CO_DB.KAYAK_CO_SCHEMA.CUSTOMERS c
INNER JOIN
nature_clips_dcr.nature_clips_schema.visitors v
ON v.email = c.email ;
12872人が2社共有の顧客でした。↓のイメージ(この絵はQuickStartから引用)
もちろんWHERE句でオーバーラップを条件指定して分析することが可能。たぶんこれが多くの人がイメージするDCR
SELECT
count(distinct customer_id) overlap_cnt,
count(distinct customer_id) / (SELECT count(distinct customer_id) FROM customers) overlap_ratio
FROM KAYAK_CO_DB.KAYAK_CO_SCHEMA.CUSTOMERS c
INNER JOIN
nature_clips_dcr.nature_clips_schema.visitors v
ON v.email = c.email
WHERE v.interest = 'hiker' AND v.gender = 'F' AND v.zip_code = '94041';
項目が違うけど、よく見る↓のイメージ
それっぽいUIをStreamlitとかで作ってあげればいい気がする(自分TODO)
2-2.許可されて無いクエリは制限される
制限された列の値自体は見られませんよ(個人情報は社外に出ませんよ)という話。
無邪気にSELECT * 投げてみる → NG(投影ポリシー)
SELECT * FROM nature_clips_dcr.nature_clips_schema.visitors;
投影ポリシーで保護されてない列 → NG(集計ポリシー)
SELECT GENDER FROM nature_clips_dcr.nature_clips_schema.visitors;
NAMEを数えてみる → NG(投影ポリシー)
SELECT COUNT(NAME) FROM nature_clips_dcr.nature_clips_schema.visitors;
集計ポリシーのみの設定だと、ユーザ数は数えることが出来ちゃう。
COUNTの結果が設定値より小さい → NG(集計ポリシー)
COUNTした結果の数値が、集約ポリシーで設定した最小値(例えば100等)より小さいと、数値を返さない。[2]
3.まとめ
Snowflakeは元々データ共有の機能が簡単なので、シンプルに「共有元がデータ共有する際に制約ポリシー設定するだけ」で情報そのものを守りつつ、DCRでの集計には利用することが出来ますよという話でした。
言い換えると「SELECT 項目名〜」で各項目の値を見る投影クエリは許可せず、「SELECT COUNT〜」等の集計系クエリのみ許可することが出来る仕組み。共有はSnowflakeの仕組み全然OK。超シンプル。
Discussion