チュートリアル: Snowflake Data Clean Roomsのコーディングの基礎¶

概要¶

このチュートリアルは、Snowflake Data Clean Roomsをコーディングで作成または使用する開発者を対象としています。このチュートリアルでは SQL コードを使用しますが、ここでの情報を参照しながら、Snowflake でサポートされている任意のコーディング言語でclean roomを作成および使用することができます。

プロバイダー: 概要¶

ここでは、clean roomを作るための手順を簡単に説明します:

1. clean roomで共有するテストデータを作成します。

2. clean roomを作成します。

3. 作成したデータをclean roomに取り込みます。

4. コンシューマーがクエリを実行する際に、どの列の結合を許可するかを結合権限で自分のデータに設定します。

5. clean roomのテンプレートを作成します。clean roomテンプレートは JinjaSQL で作成され、実行時に SQL クエリの評価を行います。ほとんどのテンプレートには、実行時にコラボレーターがテーブル名や列名、 WHERE 句の条件などを指定するための変数が含まれています。clean roomのコラボレーターが、clean roomでテンプレートを選択して実行します。

6. clean roomのデフォルトバージョンを指定します。

7. clean roomにアクセスできるコンシューマーを追加します。このチュートリアルでは、コンシューマーはclean room管理者に承認されたアカウントを持つ Snowflake ユーザーである必要があります。

8. clean roomを公開すると、招待したコンシューマーがそれを利用できるようになります。

プロバイダー: テストデータを作成する¶

プロバイダーアカウントのノートブックで作業を開始します。

まず、 SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS テーブルから1,000行のサンプルテストデータを抽出し、テーブルを作成します。そのテーブルをclean roomのプロバイダーデータとして使用します。

データベースの作成を許可するロールを使用してください。この例では ACCOUNTADMIN を使用していますが、テーブルを作成できるロールであれば何でもかまいません。

USE WAREHOUSE app_wh;
USE ROLE ACCOUNTADMIN;
-- Using ACCOUNTADMIN role because you need a role that allows you to create a database.
-- Feel free to use any other role that can create a database.

-- Generate a provider dataset based on the first 1,000 rows of sample data.
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

CREATE TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table AS
  SELECT TOP 1000 * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS ORDER BY HASHED_EMAIL ASC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;

プロバイダー: clean roomを作成する¶

clean roomには最初はデータもユーザーもなく、デフォルトプロパティのみが存在します。

以下のコード例は、組織内でのみアクセス可能なclean roomを作成します (INTERNALとしてマークされていいるのはそのためです)。clean roomを組織外で共有するには、追加で操作が必要になりますが、このチュートリアルでは説明しません。

ほぼすべてのclean roomアクションに SAMOOHA_APP_ROLE が必要になります。

USE ROLE samooha_app_role;
SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

プロバイダー: clean roomにデータを取り込む¶

テストデータをclean roomに取り込みましょう。

次の2つのステップで、clean roomにデータを取り込むことができます:

1. データを登録する

2. clean roomにデータを取り込む

USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.provider.register_db('cleanroom_tut_db');

-- Back to samooha_app_role until you need to clean things up at the end.
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table']);

CALL samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

プロバイダー： データに結合ポリシーを設定する¶

プロバイダーもコンシューマーも各自のデータに 結合ポリシー を指定できます。clean roomの結合ポリシーでは、クエリ時にclean room内で、テーブルのどの列の結合を許可するかを指定します。これにより、clean room内で他のユーザーが自分のデータをどのように使用するかを、より細かく管理することができます。

clean roomの結合ポリシーは、 Snowflakeの結合ポリシー とは異なることに注意してください。clean roomのポリシーでは、どの列の結合を 許可 するかを指定し、Snowflakeの結合ポリシーでは、どの列の結合を 禁止 するかを指定します。

clean roomへの結合が可能な列のリストを作成します。このリストに含まれない列は、clean room内で INNER JOIN または OUTER JOIN ステートメントを使用して結合することができません。テーブルの結合可能な列を、各列に対して database_name.schema_name.table_or_view_name:column_name の形式で指定します。

-- Limit joinable columns in this table to age_band, region_code, and device_type
CALL samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:age_band',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:region_code',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:device_type']);

CALL samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

プロバイダー: テンプレートを追加する¶

clean roomテンプレートは、 SELECT クエリに対して評価を行う JinjaSQL テンプレートです。このクエリには、結合と列のポリシーに従って、clean roomにリンクされたすべてのデータセットへのアクセスが許可されます。

このチュートリアルでは、 JinjaSQL テンプレートの設計については詳しく説明しませんが、以下の SQL クエリを実装することについて説明します:

SELECT COUNT(*), group_by_col FROM Consumer_Table AS C
  INNER JOIN Provider_Table AS P
  ON C.join_col = P.join_col
  GROUP BY group_col;

このクエリは、単純に1つのプロバイダーテーブルと1つのコンシューマーテーブルを指定した結合列で結合し、指定したグループ化列でグループ化して、各グループのグループ値とカウントを投影します。これは、ユーザーがテンプレートを実行する際にclean roomで実行されるクエリです。

次は、同じクエリの JinjaSQL テンプレートです。コンシューマーがテーブルや列を指定するための変数が追加されています。コンシューマーが変数を指定すると、このテンプレートは先ほどのような SQL クエリに対して評価を行います。ただし、テーブル名と列名はコンシューマーが指定します。

SELECT COUNT(*), IDENTIFIER({{group_by_col | column_policy}}) FROM IDENTIFIER({{my_table[0]}}) AS C
  INNER JOIN IDENTIFIER({{source_table[0]}}) AS P
  ON IDENTIFIER({{consumer_join_col | join_policy}}) = IDENTIFIER({{provider_join_col | join_policy}})
  GROUP BY IDENTIFIER({{group_by_col | column_policy}});

このテンプレートの注意点:

• {{brackets}} で囲まれた内容は、コンシューマーがテンプレートを実行するときに渡す名前付き変数です。 group_by_col、 consumer_join_col、 provider_join_col の変数が、コンシューマーによって渡されます

• my_table と source_table の配列はシステムが作成するグローバル変数で、呼び出し元から渡されるコンシューマーテーブル名とプロバイダーテーブル名が格納されます。これらのテーブルは、コンシューマーとプロバイダーによってclean roomにリンクされている必要があります。

• クエリでは、すべてのプロバイダーテーブルのエイリアスを p で作成する必要があります。すべてのコンシューマーテーブルのエイリアスは c で作成する必要があります。複数のテーブルを使用する場合は、1をベースとした接尾辞でエイリアスをプロバイダーテーブルに指定し (p、 p1、 p2、 p3 など)、コンシューマーテーブルにも指定します (c、 c1、 c2、 c3 など)。 (p と p0 は同じです。)

• Snowflake Data Clean Roomsは、変数に作用するカスタムな JinjaSQL フィルター をいくつかサポートしています。 column_policy と row_policy のフィルターを適用した列では、そのclean roomの列と行のポリシーに適合しているかが検証されます。適合していない場合は、テンプレート実行のリクエストが失敗します。そのため、 {{ consumer_join_col | join_policy }} は consumer_join_col に渡された値が、このclean roomのプロバイダーとコンシューマーからセットされた結合ポリシーに適合しているかを検証します。

• 識別子として使用される変数は、 IDENTIFIER 関数で処理してから、 SQL で使用する必要があります。

clean roomにテンプレートを追加します:

-- Add the template
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $template_name,
    $$
    SELECT COUNT(*), IDENTIFIER({{group_by_col | column_policy}}) FROM IDENTIFIER({{my_table[0]}}) AS C
      INNER JOIN IDENTIFIER({{source_table[0]}}) AS P
      ON IDENTIFIER({{consumer_join_col | join_policy}}) = IDENTIFIER({{provider_join_col | join_policy}})
      GROUP BY IDENTIFIER({{group_by_col | column_policy}});
    $$);

CALL samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

プロバイダー: 列のポリシーを設定する¶

clean roomの各当事者は、 column_policy を設定することで、他の当事者が結果に表示できる列を制限することができます。clean roomの列ポリシーには、投影可能な列のリストが入力されており、リストにない列の投影はできません。プロバイダーは自身のテーブルの列ポリシーを設定し、コンシューマーも自身のテーブルの列ポリシーを設定します。

列ポリシーは、clean room内の特定のテーブルとテンプレートに関連付けられます。テンプレートごとに異なる列の表示を許可することができます。同じ列を結合と列の両方のポリシーに含めることはできません。

列と結合のポリシーは、テンプレートで column_policy と row_policy のフィルターを使用している場合にのみ適用されます。また、列ポリシーを指定しない場合は、すべての列の投影が許可されます。同様に、結合ポリシーを指定しない場合は、すべての列の結合が許可されます。

以下は、先ほど作成したテンプレートで、3列のデータの投影を許可する方法を示しています。列の構文は template_name:table_name:column_name です

-- Set column policies. Column policies are tied to a specific template and table, so we
-- needed to add the template first.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name,
  [$template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:STATUS',
   $template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:AGE_BAND',
   $template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

プロバイダー: リリースディレクティブを追加する¶

すべてのclean roomには、メジャー、マイナー、パッチの値からなるバージョン番号があります。コンシューマーに提供するclean roomのバージョンを指定する必要があり、これは デフォルトリリースディレクティブ と呼ばれます。

これは最初のバージョンであるため、バージョン番号は1.0.0になります。

CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');

Snowflakeでは、clean roomにコードをアップロードするたびに新しいバージョンが作成されるため、ユーザーに最新バージョンを取得させたい場合は、新しいバージョンでこのプロシージャを再度呼び出す必要があります。コードはアップロードしないため、これを再度呼び出す必要はありません。

プロバイダー: コンシューマーを追加する¶

ここでは、clean roomをコンシューマーとして使用できるアカウントを指定します。clean roomの共有には、いくつか要件があります:

• ユーザーにはclean room環境がインストールされたSnowflakeアカウントが必要です。Snowflake以外のアカウントとclean roomを共有することも可能ですが、ここでは説明しません。

• コンシューマーアカウントのクラウドリージョンが、プロバイダーアカウントと同じであることが必要です。異なるリージョン間で共有することもできますが、その場合は特別な構成が必要になります (説明は省略します)。

• コンシューマーアカウントを、プロバイダーアカウントの Collaborators ページに追加する必要があります。あるアカウントのコラボレーターリストに追加されたアカウントのみ、そのアカウントで作成されたclean roomへの招待を受けることができます。

• アカウントロケーターを取得するには、コンシューマーアカウントで以下のプロシージャを実行します: SELECT CURRENT_ACCOUNT();

• アカウント名の形式は org_name.account_name です。これらの値を取得するには、コンシューマーアカウントで以下のプロシージャを実行します:

  • SELECT CURRENT_ORGANIZATION_NAME();

  • SELECT CURRENT_ACCOUNT_NAME();

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  <CONSUMER_LOCATOR>,
  <ORG_NAME>.<ACCOUNT_NAME>);

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

プロバイダー: clean roomを公開する¶

最後にclean roomを公開します。これにより、clean roomは先ほど追加したコンシューマーにより使用可能になります。この処理には1分以上かかります。

-- Publish the clean room.
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

処理が終了すると、プロバイダーアカウントのclean roomウェブアプリの Created タブと、コンシューマーアカウントのウェブアプリの Invited タブに、"Powered by Dev Edition" というラベルで、clean roomがリストが表示されます。コンシューマーアカウントに招待メールが届き、コンシューマーアカウントのウェブアプリの Invited タブにclean roomが表示されます。

おめでとうございます: 最初のclean roomが公開されました！

その後、コンシューマーアカウントに移動して、clean roomを使用します。

コンシューマー: clean roomインストールする (参加する)¶

Snowsightでコンシューマーアカウントに切り替えます。ブラウザで2つ目のタブを開くか、 アカウントスイッチャー を使用してください。

コンシューマーアカウントでサインインした後、コンシューマーノートブックで環境をセットアップします：

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

先ほど公開および共有したclean roomをインストールします。

clean roomをインストールするには、clean room名と、clean roomを自分と共有するプロバイダーのアカウントロケーターを指定する必要があります。clean room名とアカウントロケータを指定することで、複数のclean roomを明確に招待できます。プロバイダーアカウントで SELECT CURRENT_ACCOUNT(); を実行すると、プロバイダーロケーターを取得できます。

インストールには数分かかります。

SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, <PROVIDER_LOCATOR>);

コンシューマー: データを作成してリンクする¶

ここではデータセットをこのclean roomに作成し、追加します。方法はプロバイダーのデータセットと同じですが、サンプルデータの末尾から1,000行を使用します。

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

CREATE TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table AS
  SELECT TOP 1000 * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS ORDER BY HASHED_EMAIL DESC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;

ソースデータを作成したら、プロバイダーの際と同様にそれをclean roomに登録し、リンクする必要があります。

-- You need to use a role that has ownership of the object to be registered, probably not samooha_app_role.
USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.library.register_objects(['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

-- Drop back down to samooha_app_role for the other actions.
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

CALL samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

コンシューマー： データに結合ポリシーを設定する¶

ここでは、結合ポリシーを自分のデータに、プロバイダーアカウントの際と同じ方法で設定します。このclean roomでテンプレートを実行できるのはコンシューマーだけなので、コンシューマーの結合ポリシーを設定するのは、この例の場合は無駄です。また、テンプレートを自分で実行するため、どの列を結合可能すればよいかわかっているはずです。しかし、実際に使用する際には、プロバイダーによるテンプレートの実行にclean roomが対応している場合もあるため、データには結合ポリシーを設定すると考えておいた方がよいでしょう。

-- Allow same three columns in your data to be joined.
CALL samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:age_band',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:region_code',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:device_type']);

コンシューマー: クエリを準備する¶

クエリを実行するには、以下の情報が必要です:

• 実行したいテンプレートの名前。

• テンプレートで使用可能にするテーブルの名前。

• テンプレートで使用可能にするプロバイダーのテーブルの名前。

• その他の名前や、変数に渡す値。テンプレートでは、プロバイダーテーブルとコンシューマーテーブルから結合列名を渡す必要があります。

-- List templates in the clean room, then examine the template details
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, $template_name);

-- Table name to use is in the LINKED_TABLE column in the results.
CALL samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

-- See which provider columns can be joined on
CALL samooha_by_snowflake_local_db.consumer.view_provider_join_policy($cleanroom_name);

-- See which provider columns can be projected
CALL samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);

CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  $template_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table'],
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'],
  OBJECT_CONSTRUCT(
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  ),
  FALSE
);

両方のアカウント: クリーンアップ¶

ここで、作成したすべてのリソースをクリーンアップしましょう。

ソーステーブルを削除するには、そのソーステーブルの作成に使用したロールを使用する必要があります。

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);

USE role ACCOUNTADMIN;
DROP TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;
DROP DATABASE cleanroom_tut_db;

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);

USE ROLE ACCOUNTADMIN;
DROP VIEW cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;
DROP DATABASE cleanroom_tut_db;