clean roomのテーブルで自由形式の SQL クエリを実行する

コンシューマーは、clean room API または UI のいずれかを使用して、clean room内で選択したデータセットに対して、自由形式の SQL クエリを実行できます。

clean room API での自由形式のクエリ

clean roomを構成して、clean roomの外から共同研究者が特定のデータセットをクエリできるようにすることができます。共同研究者は、SnowsightやSnowflake API など、clean roomにアクセスできる環境であれば、これらのデータセットに対して自由形式のクエリを実行できます。自由形式のデータセットは、 SQL、 Python、その他サポートされているSnowflake言語を使用してクエリ可能な、標準の読み取り専用ビューとして動作します。

ポリシーと差分プライバシーサポート

自由形式クエリに対してclean roomデータを公開する場合、すべてのSnowflakeポリシーが 適用されます 。Clean roomポリシー(結合ポリシー、列ポリシー)は、自由形式のクエリでは 適用されません 。

自由形式クエリで公開されるデータには、差分プライバシーは 適用されません 。これには Snowflakeの差分プライバシー と、 clean roomの差分プライバシー が含まれます。

自由形式のクエリを有効にする

重要

clean roomが2025 年 6月以前に作成されている場合、プロバイダーは以下のパッチをインストールして、そのclean roomで自由形式のクエリを、 APIを使用して有効にする必要があります：

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);
Copy

プロバイダー

プロバイダーは、clean roomのコラボレーターが、自由形式のクエリを使ってclean room内のデータセットにアクセスするために、以下の手順を実行します：

  1. 標準的な方法でclean roomを作成します。

  2. 標準的な方法でデータセットをclean roomに登録し、リンクします。現在のところ、データは APIを使用して登録する必要があります。clean room UI にビューを登録し、自由形式のクエリに使用することはできません。データをclean room外で共有するには、Snowflakeの集計、結合、その他のポリシーを適用する必要があります。

  3. provider.enable_workflows_for_consumers を呼び出して、次のステップで指定するテーブルへの自由形式のアクセスを、特定のユーザーに許可します。

  4. provider.enable_datasets_for_workflow を呼び出して、前のステップのユーザーが、ここで指定するデータセットに自由形式でアクセスすることを許可します。

  5. provider.add_consumers を呼び出して、標準的な方法でコラボレーターを追加します。

  6. clean roomを公開します。

  7. これらのテーブルへのクエリ権限を取り消したい場合は、ユーザーレベルで provider.disable_consumer_run_analysis または provider.remove_consumers を呼び出すか、ビューレベルで library.unregister_objects または library.unregister_db を呼び出すか、clean roomを削除します。

clean roomが既に存在し、データが登録されている場合、 provider.enable_workflows_for_consumers と provider.enable_datasets_for_workflow を呼び出すだけで、指定のテーブルを指定のユーザーに公開できます。

以下のコードは、3つのサンプルテーブルを作成し、新しいclean roomを作成してテーブルにリンクし、clean roomのコラボレーターにこれらのテーブルへの自由形式のクエリのアクセスを、clean room経由で付与します。

----------------- Create sample data -----------------
USE ROLE MYROLE;
CREATE DATABASE freeform_db;

-- Create a table with an aggregation constraint.
CREATE OR REPLACE TABLE freeform_db.public.agg_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE AGGREGATION POLICY freeform_db.public.agg_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

ALTER TABLE freeform_db.public.agg_constrained_table
  SET AGGREGATION POLICY freeform_db.public.agg_policy;

-- Create a table with a projection constraint.
CREATE OR REPLACE TABLE freeform_db.public.proj_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE PROJECTION POLICY freeform_db.public.proj_policy AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => false);

ALTER TABLE freeform_db.public.proj_constrained_table MODIFY COLUMN hashed_email
  SET PROJECTION POLICY freeform_db.public.proj_policy;

-- Create a table with a masking policy.
CREATE OR REPLACE TABLE freeform_db.public.masked_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE MASKING POLICY freeform_db.public.masking_policy
  AS (val string) RETURNS STRING ->
  CASE
    WHEN current_account() IN ('DCR_PROVIDER_PP6') THEN VAL
    ELSE '*********'
  END;

ALTER TABLE freeform_db.public.masked_table MODIFY COLUMN hashed_email
  SET MASKING POLICY freeform_db.public.masking_policy;

----------------- Create and publish a clean room that supports -----------------
----------------- free-form queries against this data.          -----------------

-- Create the clean room. Nothing new here.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in the policy-protected tables from above. Nothing new here.
USE ROLE MYROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.register_db('freeform_db');
USE ROLE SAMOOHA_APP_ROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.link_datasets($cleanroom_name,
  ['freeform_db.public.agg_constrained_table',
  'freeform_db.public.proj_constrained_table',
  'freeform_db.public.masked_table']);

-- Grant the following consumer access to the tables specified next.
SET flow_name = freeform_sql;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_workflows_for_consumers($cleanroom_name,
  [$flow_name],
  ['<CONSUMER_LOCATOR>']);

-- Grant the consumer specified above access to the specified tables.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_datasets_for_workflow($cleanroom_name,
  $flow_name,
  ['freeform_db.public.agg_constrained_table',
   'freeform_db.public.proj_constrained_table',
    'freeform_db.public.masked_table']);

-- Add collaborators and publish, in the standard way.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.add_consumers(
  $cleanroom_name, '<CONSUMER_LOCATOR>', '<ORG_NAME>.<CONSUMER_LOCATOR>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', '0');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);
Copy

コンシューマー

プロバイダーが自由形式の SQL ワークフローを持つclean roomを公開した後、そのclean roomへのアクセスが許可されたコンシューマーは、公開されたビューに対してクエリを実行できます。

  1. clean roomを標準的な方法でインストールします。コンシューマーから直接テーブルにアクセスできるため、データをリンクする必要はありません。

  2. consumer.get_provider_freeform_sql_views を呼び出して、現在のアカウントとロールで使用できる自由形式の SQL ビューのリストを表示します。

  3. データに対して標準の SQL クエリを実行します。プロデューサーのポリシーがデータに対して適用されます。

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- List free form views available in the clean room.
CALL samooha_by_snowflake_local_db.consumer.GET_PROVIDER_FREEFORM_SQL_VIEWS($cleanroom_name);

-- Run queries on the views
SELECT * FROM <PROJECTION_POLICY_VIEW_NAME>;
SELECT * FROM <MASKING_POLICY_VIEW_NAME>;
SELECT COUNT(hashed_email), age_band
  FROM <AGGREGATION_POLICY_VIEW_NAME> group by age_band;
Copy

clean room UI での自由形式のクエリ

clean roomの SQL クエリテンプレートを使用すると、コンシューマーは自由形式の SQL を作成し、clean roomのデータをクエリできます。SQL クエリテンプレートを使用する場合、コンシューマークエリが正常に結果を返すためには、一定の要件を満たす必要があります。これらの要件は、データプロバイダーがデータプライバシーポリシーでどのようにテーブルを保護するかによって決定されます。

UI でclean roomを作成または更新する場合は、 SQL クエリテンプレートをclean roomに追加し、以下のように構成します。

プロバイダー: clean roomを作成してポリシーをセットする

  1. clean roomを作成するか、既存のclean roomを編集し、テーブルを指定するか、テーブルのビューを指定します。

  2. SQL クエリテンプレートを使用する場合、clean room作成プロセスで指定した結合ポリシーは無視されますが、その他のテンプレートでは適用されます。

  3. Configure Analysis & Query で Horizontal » SQL Query を選択します。

  4. SQL Query を設定するセクションで、以下のプロパティをセットします：

    1. Tables で、clean roomのコラボレーターが自由形式のクエリで使用できるテーブルを選択します。デフォルトでは、選択したテーブルのすべての列を投影できます。また、集計ポリシーを適用する必要はありません。どの列の投影を許可し、どの列の集約を必須とするかを制御するには、次のセクションで列ポリシーをセットする必要があります。

    2. Column Policies セクションで、クエリに列を使用するかどうかを制御する、以下の値をセットします：

      1. Aggregation policy columns: クエリ結果の表示で集計を必須とする列を指定します。列に集計ポリシーを適用し、クエリで1つの列を使用する場合、結果の集計が必須になります。ここにリストされた列は、 Privacy settings セクションに追加されます。

      2. Projection policy columns: 投影ポリシーが設定されている列を、 投影することはできません (つまり、 SELECT ステートメントに含めることはできません)。しかし、コンシューマーは投影ポリシーで列をフィルターしたり、結合したりすることができます。

      3. Fully permitted columns: コンシューマーは、これらの列の SELECT、フィルター、または結合を制限なく行えます(集計やその他で)。

    3. Privacy settings セクションには、集計ポリシーが適用されたすべての列がリストされます。 Threshold の値は、その値を結果に表示するために必要なエンティティの数を示します。例えば、 FIRST_NAME 列にしきい値 5 をセットした場合、"Erasmus" という名前がテーブルに 4 回だけ現れると、"Erasmus" を含むすべての行は、処理が行われる前にフィルターから除外されます(そのため、例えば、このようなテーブルで COUNT(*) を使用すると、しきい値を下回るグループサイズの 4 行が除外されます)。

コンシューマー： 自由形式のクエリを実行する

  1. clean room UI でclean roomに参加または編集してください。

  2. Configure Analysis & Query セクションで、自由形式のクエリに使用するテーブルを選択します。

  3. Finish を選択して変更を保存します。

  4. クエリを実行するには、 SQL クエリテンプレートを持つclean roomで Run を選択し、 SQL クエリテンプレートを選択します。

結合列とフィルター列の選択

ポリシーがある列、または完全に許可されている列であれば、結合やフィルターが可能です。列が結合可能か、フィルターで使用可能かを判断するには、次のようにします。

  1. Query Configurations セクションで、 Tables タイルを見つけます。

  2. ドロップダウンリストを使用してテーブルを選択します。リストされたすべての列を結合し、フィルターをかけることができます。

投影列の選択

SQL クエリテンプレートを使用して実行されるクエリには、投影できる（SELECT ステートメントで使用できる）列の制限があります。

クエリが列を投影できるかどうかを判断するには:

  1. Query Configurations セクションで、 Tables タイルを見つけます。

  2. ドロップダウンリストを使用してテーブルを選択します。

  3. 投影できないことを意味する投影ポリシーラベルが付いている列を探してください。投影ポリシーラベルの付いた列を除くすべての列を投影することができます。

集計の要件

プロバイダーが列に集計ポリシーを割り当てた場合、 SQL クエリテンプレートを使用して実行されるすべてのクエリは、集計された結果を返さなければなりません。

クエリの結果を集計する必要があるかどうかを判断するには、次のようにします。

  1. Query Configurations セクションで、 Tables タイルを見つけます。

  2. ドロップダウンリストを使用してテーブルを選択します。

  3. 集計ポリシーラベルを持つ列を探します。集計ポリシーラベルが少なくとも1つある場合は、クエリで集計を使用する必要があります。

集計ポリシーによって保護されたデータに対するクエリを成功させる方法については、ガイドラインをご参照ください。

グラフ作成の要件

Snowflakeがグラフを生成できるようにするには:

  • 結果テーブルには、少なくとも1つの測定（数値）列と1つのディメンジョン（カテゴリ）列を含める必要があります。

  • 測定列名には、以下の接頭語または接尾語（大文字と小文字は区別されません）が必要です。

    • 列名の接頭辞:

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • 列名の接尾辞:

      • _OVERLAP

Snowflakeは、結果テーブルの最初の適格測定列と最初のディメンジョン列を使用してチャートを生成します。

制限事項

  • ORDER BY 句は、分析結果の表示方法には影響しません。

サンプルクエリ

このセクションでは、 SQL クエリテンプレートを使用して分析を実行する際に、クエリに含めることができる内容と含めることができない内容を理解します。

集計関数のないクエリ

状況によっては、集計関数を使わずに値を返すこともできます。

許可済み

不可

 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer
  GROUP BY gender, region;
Copy
 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Copy
共通テーブル式（CTEs）

許可済み

不可

 
WITH audience AS
  (SELECT COUNT(DISTINCT t1.hashed_email),
    t1.status
    FROM provider_db.overlap.customers t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status);

SELECT * FROM audience;
Copy
 
WITH audience AS
  (SELECT t1.hashed_email,
    t1.status
    FROM provider_db.overlap.customers quoted t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status)

SELECT * FROM audience
Copy
CREATE, ALTER, TRUNCATE

クエリは、 CREATE、 ALTER、 TRUNCATE を使用することはできません。

結合を含むクエリ

許可済み

 
SELECT p.education_level,
  c.status,
  AVG(p.days_active),
  COUNT(DISTINCT p.age_band)
  FROM  sample_database_preprod.demo.customers c
  INNER JOIN
  sample_database_preprod.demo.customers p
    ON  c.hashed_email = p.hashed_email
  GROUP BY ALL;
Copy
DATE_TRUNC

許可済み

 
SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Copy
引用符で囲まれている識別子

許可済み

 
SELECT COUNT(DISTINCT t1.”hashed_email”)
  FROM provider_sample_database.audience_overlap."customers quoted" t1
  INNER JOIN
  consumer_sample_database.audience_overlap.customers t2
    ON t1."hashed_email" = t2.hashed_email;
Copy
言語: 日本語