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

You can enable consumers to run free-form SQL queries on selected datasets in your clean room using either the clean room API or UI.

Free-form queries using the clean rooms API

You can configure a clean room to allow collaborators to query specific linked datasets from outside the clean room. Collaborators can run free-form queries on these datasets in any environment where they can access the clean room, including Snowsight or Snowflake CLI. Free-form datasets behave as standard, read-only views that can be queried using SQL, Python, or other supported Snowflake languages.

注釈

When you grant a consumer permission to run free-form SQL queries in a clean room, that consumer can query the data from that clean room against any other data that they can access from their account.

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

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

Clean room differential privacy is not enforced on data exposed to free-form queries. This includes both Snowflake differential privacy and clean room differential privacy.

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

重要

If a clean room was created before June, 2025 the provider must patch their clean room by running the following code to enable free-form queries in that clean room:

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

Provider steps

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

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

  2. Register and link the datasets into the clean room in the standard way using the API. Note that currently your data must be registered using the API; you cannot register views in the clean room UI and use them for free-form queries. You should apply any Snowflake aggregation, join, or other policies before sharing your data outside the clean room.

  3. Call provider.enable_workflows_for_consumers to allow specific users free-form access to the tables that you will specify in the next step. You must name this work flow freeform_sql.

  4. Call provider.enable_datasets_for_workflow to specify which datasets in the clean room can be queried.

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

  6. clean roomを公開します。

  7. If you want to revoke permission to query these tables, you can do this at the user level by calling provider.disable_consumer_run_analysis or provider.remove_consumers, at the dataset level by calling library.unregister_objects or library.unregister_db, or by deleting the clean room.

If a clean room already exists and data is registered, you can simply call provider.enable_workflows_for_consumers and provider.enable_datasets_for_workflow to expose the specified datasets to the specified users.

The following code creates three sample tables and applies Snowflake policies to them, creates a new clean room, links in the tables, and grants free-form query access to those tables for clean room collaborators via the clean room. The highlighted code shows where you enable free-form queries in the 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.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.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.
-- The flow name must be 'freeform_sql'
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

Consumer steps

After the provider has published a clean room with free-form SQL work flows, consumers with access to that clean room can run queries against the exposed views by following these steps:

  1. Install the clean room in the standard way. No need to link in consumer data, as the consumer will access their data in their local environment, not in the clean room.

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

  3. Run standard SQL queries against the data.

-- 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. Under Tables, select tables that should be available to clean room collaborators in free-form queries. By default, aggregation policies do not need to be applied. To control which columns can be projected, and which must be aggregated, you must set column policies in the next section.

      重要

      クリーンルーム UIでの無料クエリの場合、「LIST（大文字または小文字）」で終わる名前のテーブルは使用できません。

    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 セクションで、自由形式のクエリに使用するテーブルを選択します。

    重要

    クリーンルーム UIでの無料クエリの場合、「LIST（大文字または小文字）」で終わる名前のテーブルは使用できません。

  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  samooha_sample_database.demo.customers c
  INNER JOIN
  samooha_sample_database.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
言語: 日本語