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

プロバイダーのステップ¶

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

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

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

3. provider.enable_workflows_for_consumers を呼び出して、次のステップで指定するテーブルへの自由形式のアクセスを特定のユーザーに許可します。 このワークフローには次の名前を付ける必要があります freeform_sql 。

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 を呼び出すか、クリーンルームを削除することによって実行できます。

クリーンルームが既に存在し、データが登録されている場合は、単に provider.enable_workflows_for_consumers および provider.enable_datasets_for_workflow を呼び出して、指定したデータセットを指定したユーザーに公開できます。

次のコードは、3つのサンプルテーブルを作成し、それらにSnowflakeポリシーを適用し、新しいクリーンルームを作成し、テーブル内にリンクを作成し、クリーンルームを介してクリーンルームのコラボレーターにそれらのテーブルへの自由形式のクエリアクセスを付与します。強調表示されたコードは、クリーンルームのどこで自由形式のクエリを有効にするかを示しています。

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

コンシューマーのステップ¶

プロバイダーが自由形式の SQL ワークフローを含むクリーンルームを公開すると、そのクリーンルームにアクセスできるコンシューマーは、以下のステップに従って、公開されたビューに対してクエリを実行できます。

1. クリーンルームを標準的な方法でインストールします。コンシューマーはクリーンルームではなくローカル環境でデータにアクセスするため、コンシューマーデータでリンクする必要はありません。

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;

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

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

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

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

投影列の選択¶

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

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

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

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

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

集計の要件¶

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

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

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

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

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

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

• 集計ポリシーのクエリの要件。例えば、このセクションを使用して、 MIN および MAX 集計関数がクエリの要件を満たさないため、使用できないと判断することができます。

• 集計ポリシーの制限

グラフ作成の要件¶

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