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

概要¶

このチュートリアルは、Snowflake Data Clean Roomsをコーディングで作成または使用する開発者を対象としています。このチュートリアルでは SQL コードを使用しますが、ここでの情報を参照しながら、Snowflake でサポートされている任意のコーディング言語で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_APP_ROLE ロールを付与されたユーザーとして、`Snowsight<https://app.snowflake.com>`_にサインインします。そのロールを持ってない場合は、アカウント管理者に付与を依頼してください。

クリーンルームのコードを保持するためのSnowsight 内で:doc:`新しい SQL ワークシート </user-guide/ui-snowsight-worksheets-gs>`を作成します。ワークシートの名前を「API チュートリアル - プロバイダー」とします。

SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS テーブルからのサンプルテストデータの1,000行を基にテーブルを作成します。そのテーブルをクリーンルームのプロバイダーデータとして使用します。

データベースの作成を許可するロールを使用してください。この例では 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 a temporary table, in case you forget to delete it later.
CREATE TEMPORARY 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を作成する¶

次のスニペットは、組織内でのみアクセスできるクリーンルームを作成します（したがって、INTERNALとしてマークされます）。組織外でクリーンルームを共有するには、このチュートリアルでは説明していない追加の手順が必要です。自分とクリーンルームを共有する場合は、当然ながらINTERNALである必要があります。

ほとんどのクリーンルームプロシージャに対して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にデータを取り込む¶

次に、テストデータをクリーンルームに取り込みます。クリーンルームにデータを取り込むには2つのステップがあります。

1. データを登録します。

2. クリーンルームにデータをインポート（リンク）します。

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

-- Use 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);

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

プロバイダーもコンシューマーも、自分のデータに対して 結合ポリシー を指定できます。クリーンルームの結合ポリシーは、そのクリーンルームのコラボレーターのクエリによって、テーブルのどの列を結合できるかを指定します。これにより、他の人がクリーンルームであなたのデータをどのように使用できるかについて、さらなるレベルの制御が提供されます。自分のポリシーは自分のクエリに適用されません。つまり、自分のデータに設定した結合ポリシーは、クエリの実行時に無視されます。ポリシーは他のユーザーが実行したクエリに対してのみ適用されます。

クリーンルームの結合ポリシーはテーブルに設定され、テーブルが使用されるすべてのクリーンルームに適用されます。ここにリストされていない列は、クリーンルームの INNERJOIN または OUTERJOIN ステートメントを使用して結合できません。結合ポリシーを指定しない場合、すべての列が結合可能です。

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

各列に対して、:samp:`{database_name}.{schema_name}.{table_or_view_name}:{column_name}`形式を使用して、テーブルの結合可能な列を指定します。次の例では、プロバイダーデータの3列を結合可能にしています。

-- 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);

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

クリーンルームの各関係者は、*column_policy*を設定することで、他の関係者が表示できる列を制限することができます。クリーンルームの列ポリシーは、表示できるデータのすべての列を一覧表示します。他の列は表示できません。データの列ポリシーを指定しない場合、すべてのデータが表示されます。

列ポリシーは、クリーンルームの特定のテーブルとテンプレートに関連付けられています。異なる列を異なるテンプレートに表示することができます。同じ列を結合ポリシーと列ポリシーの両方に適用することはできません。

列ポリシーと結合ポリシーは、テンプレート内の``column_policy`` および row_policy フィルターをテンプレートが使用する場合にのみ適用されることに注意してください。

次に、作成したテンプレートでデータの3列の投影を許可する方法を説明します。列の構文は :samp:`{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:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

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

すべてのクリーンルームには:doc:`バージョン番号 </user-guide/cleanrooms/dcr-versions>`があり、メジャー値、マイナー値、パッチ値で構成されます。クリーンルームのどのバージョンをコンシューマーに提供するか指定する必要があります。これは、デフォルトリリースディレクティブ と呼ばれます。

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

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

Snowflakeでは、クリーンルームにコードをアップロードするたびに、クリーンルームの新しいバージョンが作成されます。ユーザーに最新バージョンを入手してもらう場合は、最新バージョン番号の新しいデフォルトリリースディレクティブを設定する必要があります。コードをアップロードしないため、このチュートリアルで再度呼び出す必要はありません。

プロバイダー: 指定コンシューマー¶

ここで、コンシューマーとしてクリーンルームにアクセスできる人を指定します。このチュートリアルでは、コンシューマーとして自分を追加します。そうすることで、クリーンルームは内部テスト用のクリーンルームとしてマークされ、テストのみに使用されます。 :ref:`一部の機能が制限されます<label-dcr_self_share_for_developers>`が、このチュートリアルに必要なすべての機能はサポートされます。

プロシージャには、各コンシューマーを識別する2つの引数が必要です。

• コンシューマーのアカウントロケーター。次のようにアカウントロケーターを取得します。

  SELECT CURRENT_ACCOUNT();
  

  Copy

• org_name.account_name`形式のコンシューマーの:ref:`コンシューマーデータ共有アカウントID<label-account_name_data_sharing>。コンシューマーデータ共有アカウントIDを 次のような適切な形式で取得します。

  SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
  

  Copy

コンシューマーとしてクリーンルームを自分と共有し、指定された場所ににアカウントロケーターとコンシューマーデータ共有アカウントIDを追加します。

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  '<CONSUMER_LOCATOR>',
  '<CONSUMER_DATA_SHARING_ACCOUNT_ID>');

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

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

最後に、クリーンルームを公開します。これで、上記で追加したコンシューマーがクリーンルームを利用できるようになります。この手順の完了には1分以上かかります。

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

手順が終了すると、プロバイダーアカウントの:ui:`Created`タブの:ref:`クリーンルームUI<label-cleanroom_web_app_sign_in>`と、コンシューマーアカウントの:ui:`Invited`タブに、一覧表示されたクリーンルームが「Powered by Dev Edition」というラベルとともに確認できます。コンシューマーアカウントには招待メールが届きます。（:ui:`Invited`タブからクリーンルームをインストールしないでください。後のステップで、コード内でインストールします。）

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

では、プロバイダーの上限を外して、コンシューマーの上限を設定しましょう。

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

このチュートリアルでは、プロバイダーとコンシューマーのロールに同じアカウントを使用します。したがって、Snowsightの同じアカウント内に新しいSQLワークシートを「APIチュートリアル - コンシューマー」という名前で追加してください。

プロバイダーの場合と同様に、セッション環境を設定します。

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

次に、プロバイダーとして公開し共有したクリーンルームをインストールします。クリーンルームをインストールするには、クリーンルーム名と、クリーンルームを共有したプロバイダーのアカウントロケーターの両方を指定する必要があります。クリーンルーム名とアカウントロケーターを指定すると、同じ名前のクリーンルームを区別できます。SELECT CURRENT_ACCOUNT(); を実行してプロバイダーロケーターを取得します。

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

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

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

次に、このクリーンルームにデータセットを作成して追加します。プロバイダーのデータセットと類似したテーブルを作成しますが、サンプルデータから下位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 TEMPORARY 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;

ソースデータを作成したら、プロバイダーの場合と同じように、クリーンルームにソースデータをリンクする必要があります。

-- 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);

コンシューマー: ポリシーの設定は必要なし¶

プロバイダーと同じ方法でデータにポリシーを設定できますが、このテンプレートはコンシューマーのみが実行することを承認されているため、自分のクエリを制限する必要がありません。いずれの場合も、クエリの実行時に独自の行ポリシーと列ポリシーは無視されます。

しかし、このテンプレートを実行するプロバイダーのリクエストを承認する場合は、まず結合ポリシーと列ポリシーをデータに設定し、プロバイダーがそのデータをどう利用できるのかを制御します。

コンシューマー: 分析を実行する¶

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

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

• テンプレートで使用するテーブル名。

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

• 渡したいその他の名前/値の変数。

-- List templates in the clean room.
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

-- See the template code.
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition(
  $cleanroom_name,
  $template_name);

-- See what arguments can be passed in to the template:
CALL samooha_by_snowflake_local_db.consumer.get_arguments_from_template(
  $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'], -- Consumer table list.
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'], -- Provider table list.
  OBJECT_CONSTRUCT(                    -- Additional template arguments as name-value pairs.
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  )
);

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

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

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

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;