Snowflake Data Clean Room：自定义分析模板¶

本主题介绍了以编程方式建立 Clean Room、与使用者共享 Clean Room 以及使用其中的自定义模板运行提供商数据分析所需的提供商和使用者流。使用自定义模板的提供商数据分析是指使用者无需引入自己的数据集，而只是希望使用提供商添加的自定义模板来获得有关提供商数据集的汇总见解。

与端到端：重叠分析教程相比，它将涵盖以下内容：

提供商：

a.创建新的自定义模板。

b.将其添加到 Clean Room。
使用者：

a.查看自定义模板定义。

b.使用辅助函数来理解必要的实参。

c.运行自定义模板。

先决条件¶

您需要两个独立的 Snowflake 账户才能完成此流程。使用第一个账户执行提供商的命令，然后切换到第二个账户执行使用者的命令。

提供商¶

备注

应在提供商账户的 Snowflake 工作表中运行以下命令。

设置环境¶

在利用开发者 APIs 使用 Snowflake Data Clean Room 之前，请执行以下命令来设置 Snowflake 环境。如果您没有 SAMOOHA_APP_ROLE 角色，请联系账户管理员。

use role samooha_app_role;
use warehouse app_wh;

Copy

创建 Clean Room¶

为 Clean Room 创建名称。输入新的 Clean Room 名称，避免与现有 Clean Room 名称冲突。请注意，Clean Room 名称只能是 字母数字。除空格和下划线外，Clean Room 名称不能包含特殊字符。

set cleanroom_name = 'Custom Template Demo Clean room';

Copy

您可以使用上面设置的 Clean Room 名称创建新的 Clean Room。如果上面设置的 Clean Room 名称已经作为现有 Clean Room 存在，则此过程将失败。

运行此过程大约需要 45 秒。

provider.cleanroom_init 的第二个实参是 Clean Room 的分发。它可以是 INTERNAL 或 EXTERNAL。出于测试目的，如果您将 Clean Room 共享给同一组织中的账户，则可以使用 INTERNAL 绕过自动安全扫描，该扫描必须在将应用程序包发布给协作者前进行。但是，如果要将此 Clean Room 共享给其他组织中的账户，则必须使用 EXTERNAL Clean Room 分发。

call samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

Copy

要查看安全扫描的状态，请使用：

call samooha_by_snowflake_local_db.provider.view_cleanroom_scan_status($cleanroom_name);

Copy

创建 Clean Room 之后，必须先设置其发布指令，然后才能与协作者共享。但是，如果您的分发设置为 EXTERNAL，则必须先等待安全扫描完成，然后再设置发布指令。您可以在扫描运行时继续运行其余步骤，并在执行 provider.create_cleanroom_listing 步骤之前返回此处。

要设置发布指令，请调用：

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

Copy

关联数据集，并设置数据集的联接策略¶

将 Snowflake 表关联到 Clean Room。浏览 Snowflake 账户中的表列表，并以数组形式输入完全限定的表名称 (Database.Schema.Table)。该过程在 Clean Room 内创建表的安全视图，自动使 Clean Room 可以访问表，从而无需制作表副本。

call samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS']);

Copy

备注

如果即使表存在，此步骤也不起作用，则很可能 SAMOOHA_APP_ROLE 角色还没有被授予访问该表的权限。如果是，请切换到 ACCOUNTADMIN 角色，对数据库调用以下过程，然后切换回来执行流程的剩余部分：

use role accountadmin;
call samooha_by_snowflake_local_db.provider.register_db('<DATABASE_NAME>');
use role samooha_app_role;

Copy

您可以使用以下过程查看关联到 Clean Room 的数据集：

call samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

Copy

为了确定可以将哪些列用作联接策略，您可以查看数据集以确定 PII 列。要查看前 10 行，请使用此查询：

select * from SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS limit 10;

Copy

指定使用者在 Clean Room 内运行模板时可以联接哪些列。应对身份列（如电子邮件）调用此过程。联接策略是“仅替换”，因此如果再次调用函数，新的联接策略会完全替换之前设置的联接策略。

call samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:HEM']);

Copy

如果要查看已添加到 Clean Room 的联接策略，请调用以下过程。

call samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

Copy

向 Clean Room 添加自定义分析模板¶

要将自定义分析模板添加到 Clean Room 中，您需要在提供商端和使用者端提供表名占位符，以及提供者端的联接列。在 SQL Jinja 模板中，这些占位符必须始终为以下值：

source_table：来自提供商的表名称数组
my_table：来自使用者的表名称数组

通过使用这些变量，可以使表名称动态化，但是如果需要，也可以使用链接到 Clean Room 的视图的名称将其硬编码到模板中。如果需要，列名称可以硬编码到模板中，也可以通过参数动态设置。如果通过参数设置列名称，请记住，您需要调用参数 dimensions 或 measure_column，这些参数必须是数组，以便根据列策略进行检查。您可以将其作为 SQL Jinja 参数添加到模板中，之后在查询时由使用者传递。联接策略确保使用者不能联接除授权列之外的列。

另外，还可以使用以下筛选器检查自定义 SQL Jinja 模板中的实参是否符合联接和列策略：

join_policy：检查字符串值或筛选器子句是否符合联接策略
column_policy：检查字符串值或筛选器子句是否符合列策略
join_and_column_policy：检查筛选器子句中用于联接的列是否符合联接策略，以及用作筛选器的列是否符合列策略

例如，在子句 {{ provider_id | sqlsafe | join_policy }} 中，将解析 HEM 的输入以检查联接策略中是否有 HEM。注意：只能谨慎使用 sqlsafe 筛选器，因为它允许合作者将纯 SQL 放入模板。

备注

必须使用这些实参来引用所有提供商/使用者表，因为实际链接到 Clean Room 的安全视图名称与表名不同。重要的是，提供商表别名 MUST 为 p（或 p1）、p2、p3、p4 等。使用者表别名必须为 c（或 c1）、c2、c3 等。要在 Clean Room 中执行安全策略，必须满足此条件。

请注意，此函数将替换任何具有相同名称的现有模板。如果您想更新任何现有模板，只需使用更新后的模板再次调用这个函数。

call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'prod_custom_template',    // Name of the template
    $$
select
count(*) as total_count
from identifier({{ my_table[0] }}) c
inner join identifier({{ source_table[0] }}) p
on identifier({{ consumer_id | join_policy }}) = identifier({{ provider_id | join_policy }})
{% if where_clause %}
where {{ where_clause | sqlsafe | column_policy }}
{% endif %};
    $$                         // A string representing the SQL Jinja template
);

Copy

如果要查看 Clean Room 中当前活跃的模板，请调用以下过程。

call samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

Copy

如果需要，也可以将任何添加到 Clean Room 的模板清除。请参阅提供商 API 参考指南，了解更多详情。

对每个表设置列策略¶

显示关联的数据以查看表中存在的列。要查看前 10 行，请调用以下过程。

select * from SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS limit 10;

Copy

为每个表和模板组合设置使用者可以分组、汇总（例如 SUM/AVG）和通常用于分析的列。这提供了灵活性，使同一个表可以根据基础模板允许不同的列选择。只有在添加模板后才可调用此函数。

请注意，列策略是 仅替换，因此如果再次调用函数，新的列策略会完全替换之前设置的列策略。

不应将列策略用于 email、HEM、RampID 等身份列，因为您不希望使用者能够按这些列进行分组。在生产环境中，系统会智能推断 PII 列并阻止此操作，但在沙盒环境中此功能不可用。它应该只用于您希望使用者能够汇总和分组的列，如状态、年龄段、地区代码、活动天数等。

请注意，要使“column_policy”和“join_policy”对使用者分析请求执行检查，在 SQL Jinja 模版中，所有列名 MUST 称为 dimensions 或 measure_columns。请确保使用这些标签来引用自定义 SQL Jinja 模板中要检查的列。

call samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name, [
    'prod_custom_template:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:STATUS',
    'prod_custom_template:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:AGE_BAND',
    'prod_custom_template:SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS:DAYS_ACTIVE']);

Copy

如果要查看已添加到 Clean Room 的列策略，请调用以下过程。

call samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

Copy

使用者¶

备注

应在使用者账户的 Snowflake 工作表中运行以下命令

设置环境¶

在利用开发者 APIs 使用 Snowflake Data Clean Room 之前，请执行以下命令来设置 Snowflake 环境。如果您没有 SAMOOHA_APP_ROLE 角色，请联系账户管理员。

use role samooha_app_role;
use warehouse app_wh;

Copy

安装 Clean Room¶

安装 Clean Room 共享后，可以使用以下命令查看可用的 Clean Room 列表。

call samooha_by_snowflake_local_db.consumer.view_cleanrooms();

Copy

为提供商与您共享的 Clean Room 命名。

set cleanroom_name = 'Custom Template Demo Clean room';

Copy

以下命令将 Clean Room 安装到具有相关提供商和所选 Clean Room 的使用者账户中。

运行此过程大约需要 45 秒。

call samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_ACCOUNT_LOCATOR>');

Copy

安装了 Clean Room 后，提供商必须在启用前完成提供商侧的 Clean Room 设置。您可以通过以下函数查看 Clean Room 的状态。启用 Clean Room 后，您应该能够运行下面的“运行分析”命令。启用 Clean Room 通常需要大约 1 分钟。

call samooha_by_snowflake_local_db.consumer.is_enabled($cleanroom_name);

Copy

关联数据集¶

将数据集链接到 Clean Room，使用提供商的数据进行安全计算。

call samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS']);

Copy

备注

use role accountadmin;
call samooha_by_snowflake_local_db.consumer.register_db('<DATABASE_NAME>');
use role samooha_app_role;

Copy

要运行分析，您需要传入使用者表。如果要查看您已添加到 Clean Room 的数据集，请调用以下过程。

call samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

Copy

运行分析¶

现在，Clean Room 已经安装完毕，您可以使用“run_analysis”命令运行由提供商提供给 Clean Room 的分析模板。您可以在以下各节中看到如何确定每个字段。

可传递的数据集数量受提供商实施的模板的限制。某些模板需要特定数量的表。模板创建者可以强制使用其希望支持的要求。

备注

在运行分析之前，您可以更改仓库规模；如果表较大，也可以使用新的、更大的仓库规模。

call samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,              -- cleanroom
  'prod_custom_template',       -- template name

  ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS'],    -- your tables
  ['SAMOOHA_SAMPLE_DATABASE_NAV2.DEMO.CUSTOMERS'],       -- provider tables

  object_construct(                              -- The keyword arguments needed for the SQL Jinja template

    'consumer_id', 'c.hem',                    -- Consumer column to join on, needed by template

    'provider_id', 'p.hem',                    -- Provider column to join on, needed by template

    'where_clause', 'p.STATUS = $$MEMBER$$' -- Boolean filter for custom template
  )
);

Copy

对于在筛选“where_clause”、“dimensions”或“measure_columns”的数据集中引用的每一列，您可以使用 p. 引用提供商表中的字段，使用 c. 引用使用者表中的字段。对多个提供商表，可以使用 p2、p3 等；对多个使用者表，可以使用 c2、c3 等。

如何确定 run_analysis 的输入¶

要运行分析，需要向 run_analysis 函数传递一些参数。本节将向您展示如何确定要传入的参数。

模板名称

首先，您可以通过调用以下过程来查看支持的分析模板。

call samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

Copy

在使用模板运行分析之前，您需要知道要指定哪些实参以及需要哪些类型。对于自定义模板，您可以执行以下操作。

call samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, 'prod_custom_template');

Copy

这通常还可以包含大量不同的 SQL Jinja 参数。以下功能可解析 SQL Jinja 模板，并将需要在 run_analysis 中指定的实参提取到列表中。

call samooha_by_snowflake_local_db.consumer.get_arguments_from_template($cleanroom_name, 'prod_custom_template');

Copy

数据集名称

如果要查看提供商已添加到 Clean Room 的数据集名称，请调用以下过程。请注意，由于 Clean Room 的安全属性，您无法查看提供商添加到 Clean Room 的数据集中的数据。

call samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

Copy

您还可以使用以下调用，查看已关联到 Clean Room 的表：

call samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

Copy

维度和度量列

在运行分析时，您可能希望对某些列进行筛选、分组和汇总。如果要查看提供商已添加到 Clean Room 的列策略，请调用以下过程。

call samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);

Copy

常见错误

如果运行分析后出现 Not approved: unauthorized columns used 错误，您可能需要再次查看提供商设置的联接策略和列策略。

call samooha_by_snowflake_local_db.consumer.view_provider_join_policy($cleanroom_name);
call samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);

Copy

也有可能您已经耗尽了隐私预算，这样一来系统会阻止您执行更多查询。您可以使用以下命令查看剩余的隐私预算。预算会每天重置；Clean Room 提供商如果需要，也可以将其重置。

call samooha_by_snowflake_local_db.consumer.view_remaining_privacy_budget($cleanroom_name);

Copy

您可以通过以下 API 检查 Clean Room 是否启用了差分隐私：

call samooha_by_snowflake_local_db.consumer.is_dp_enabled($cleanroom_name);

Copy

Snowflake Data Clean Room：自定义分析模板¶

先决条件¶

提供商¶

设置环境¶

创建 Clean Room¶

跨区域共享¶

关联数据集，并设置数据集的联接策略¶

向 Clean Room 添加自定义分析模板¶

对每个表设置列策略¶

与使用者共享¶

使用者¶

设置环境¶

安装 Clean Room¶

关联数据集¶

运行分析¶

如何确定 run_analysis 的输入¶