在 Clean Room 表上运行自由格式 SQL 查询

您可以使用 Clean Room API 或 UI 允许使用者在 Clean Room 中对选定的数据集运行自由格式 SQL 查询。

Clean Room API 中的自由格式查询

您可以配置 Clean Room 以允许协作者从 Clean Room 外部查询特定的数据集。协作者可以在任何可以访问 Clean Room 的环境中对这些数据集运行自由格式查询，包括 Snowsight 或 Snowflake API。自由格式数据集的行为与标准的只读视图相同，可以使用 SQL、Python 或其他支持的 Snowflake 语言进行查询。

策略和差分隐私支持

当公开 Clean Room 数据以进行自由格式查询时，需 遵守所有 Snowflake 策略。自由格式查询中 不 强制执行 Clean Room 策略（联接策略、列策略）。

如果数据已公开以进行自由格式查询，则 不 对该数据强制执行差分隐私。其中包括 Snowflake 差分隐私 和 Clean Room 差分隐私。

启用自由格式查询

重要

如果在 2025 年 6 月之前创建了 Clean Room，则提供商必须安装以下补丁，以便使用 API 在该 Clean Room 中启用自由格式查询：

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 即可向指定用户公开指定的表。

以下代码创建了三个示例表，创建了一个新的 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：指定必须聚合哪些列才能出现在查询结果中。如果您对列应用聚合策略并在查询中使用一列，则必须对结果进行聚合。此处列出的任何列都将添加到 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. 查找有聚合策略标签的列。如果至少有一个聚合策略标签，则必须在查询中使用聚合。

有关如何对受聚合策略保护的数据编写成功查询的指南，请参阅：

制图要求

为了让 Snowflake 能够生成图表：

  • **结果表必须至少包括一个度量（数字）列和一个维度（类别）列。

  • 度量列名称必须包含以下前缀或后缀（不区分大小写）：

    • 列名前缀：

      • 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
语言: 中文