创建自定义 Clean Room 模板¶

数据保护¶

模板只能访问提供商和使用者链接到 Clean Room 的数据集。

提供商和使用者都能够对其数据设置联接、列和激活策略，以确保哪些列可以联接或投射到激活的结果中。

快速示例¶

下面是一个简单的 SQL 示例，它通过电子邮件加入提供商和使用者表，并显示每个城市的重叠计数：

SELECT COUNT(*), city FROM consumer_table
  INNER JOIN provider_table
  ON consumer_table.hashed_email = provider_table.hashed_email
  GROUP BY city;

下面是该查询的模板，允许调用方选择/分组以及联接列和表：

SELECT COUNT(*), IDENTIFIER({{ group_by_col | column_policy }})
  FROM IDENTIFIER({{ my_table[0] }}) AS C
  INNER JOIN IDENTIFIER({{ source_table[0] }}) AS P
  ON IDENTIFIER({{ consumer_join_col | join_policy }}) = IDENTIFIER({{ provider_join_col | join_policy }})
  GROUP BY IDENTIFIER({{ group_by_col | column_policy }});

模板备注：

• {{双括号对}} 内的值是自定义变量。group_by_col、my_table、source_table、consumer_join_col、provider_join_col 和 group_by_col 都是调用方填充的自定义变量。

• source_table 和 my_table 是由调用方填充的 Snowflake 定义的字符串数组变量。数组成员是链接到 Clean Room 的提供商表和使用者表的完全限定名称。调用方指定每个数组中应该包括哪些表。

• 在模板中，提供商表别名须为 P，使用者表别名须为 C。如果您有多个表，则可以将它们索引为 P1、P2、C1、C2 等。

• 所有列名和表名都需要 IDENTIFIER，因为 {{双括号}} 中的变量计算为字符串字面量，而其并非有效标识符。

• JinjaSQL 筛选器 可以应用于变量。Snowflake 实现了自定义筛选器 join_policy 和 column_policy，它们分别验证列是否符合 Clean Room 中的联接或列策略，如果不符合则查询失败。筛选器以 {{ column_name | filter_name }} 格式应用于列名称。

所有这些要点将在后面详细讨论。

下面介绍使用者如何在代码中运行此模板。请注意如何通过模板中声明的表别名来限定列名。

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.RUN_ANALYSIS(
  $cleanroom_name,
  $template_name,
  ['my_db.my_sch.consumer_table],       -- Populates the my_table variable
  ['my_db.my_sch.provider_table'],      -- Populates the source_table variable
  OBJECT_CONSTRUCT(                     -- Populates custom named variables
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.device_type'
  )
);

为了在 Web 应用程序中使用此模板，提供商必须 为模板创建自定义 UI 表单。UI 表单具有与模板变量名称对应的命名表单元素，表单中提供的值传递到模板中。

模板命名规则¶

创建模板时，名称必须全部为小写字母、数字、空格或下划线。激活模板（使用者运行的提供商激活除外）的名称必须以“activation”开头。调用 provider.add_custom_sql_template 或 consumer.create_template_request 时分配模板名称。

有效名称示例：

• my_template

• activation_template_1

无效名称示例：

• my template - 不允许存在空格

• My_Template - 只允许使用小写模板

模板变量¶

模板调用方可以将值传递给模板变量。JinjaSQL 语法允许对 {{ double_brackets }} 内的任何变量名进行变量绑定，但 Snowflake 保留了一些变量名，不应替换，如下所述。

SELECT income FROM my_db.my_sch.customers WHERE income < {{ max_income }};

必填表别名¶

在查询顶级，所有表或子查询必须使用别名 P``（用于提供商表）或 ``C``（用于使用者表），以便 Snowflake 在查询中正确验证联接和列策略。必须对照联接或列策略验证的任何列都属于别名为 ``P 或 C 的表。（指定 P 或 C 告诉后端是分别针对提供商还是使用者策略验证列。）

如果在查询中使用多个提供商表或使用者表，请在第一个表别名后为每个表别名添加一个从 1 开始的连续后缀。因此，P、P1、P2 等表示第一、第二和第三个提供商表，C、C1、C2 等表示第一、第二和第三个使用者表。P 或 C 索引应该是连续的且无间隙（即创建别名 P、P1 和 P2，而非 P、P2 和 P4）。

示例

SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS P;

模板筛选器¶

Snowflake 支持所有 标准 Jinja 筛选器 (https://jinja.palletsprojects.com/en/stable/templates/#builtin-filters) 和大多数标准 JinjaSQL 筛选器 (https://github.com/search?q=repo%3Asripathikrishnan%2Fjinjasql+self.env.filters+path%3Ajinjasql%2Fcore.py&type=code&path+jinjasql%2Fcore.py=)，以及一些扩展：

• join_policy：验证表的联接策略是否允许列，如果不允许则失败。

• column_policy：验证模板的列策略是否允许该列（允许投射）。

• activation_policy：验证 Clean Room 的激活策略（provider.set_activation_policy 或 consumer.set_activation_policy）是否允许筛选列。

• join_and_column_policy：验证联接、激活或列策略是否允许列。用于在 Clean Room 中提供更大的灵活性，允许合作者在不更改模板的情况下更新联接和列策略。

• Snowflake 模板 不支持 identifier JinjaSQL 筛选器。

JinjaSQL 筛选器从左到右进行分析：

• {{ my_col | column_policy }} 正确

• {{ my_col | sqlsafe | column_policy }} 正确

• {{ column_policy | my_col }} 不正确

• {{ my_col | column_policy | sqlsafe }} 不正确

强制执行 Clean Room 策略¶

Clean Room 不会自动根据模板中使用的列检查 Clean Room 策略。如果您要对某列强制执行策略，则必须对模板中的该列应用相应的 策略筛选器。例如：

JOIN IDENTIFIER({{ source_table[0] }}) AS p
  ON {{ c_join_col | sqlsafe | join_policy }} = {{ p_join_col | sqlsafe }}

这将针对传递到 c_join_col 的列测试联接策略，但不会针对 p_join_col 测试。

请注意，测试策略时，列名不能含糊不清，就像任何其他 SQL 用法一样。因此，如果两个表中有同名的列，则必须限定列名才能针对该列测试策略。

运行自定义 Python 代码¶

模板可以运行上传到 Clean Room的 Python 代码。模板可以调用 Python 函数，该函数接受一行数据中的值，并返回值以在查询中使用或投射。

• 当 提供商 将自定义的 Python 代码上传到 Clean Room 时，模板会使用语法 cleanroom.function_name 调用 Python 函数。更多详细信息请点击此处。

• 当 使用者 将自定义 Python 代码上传到 Clean Room 时，模板会直接使用传递给 consumer.generate_python_request_template 的裸 function_name 值调用该函数（不会像提供商代码那样限定在 Clean Room）。更多详细信息请点击此处。

提供商代码示例：

-- Provider uploads a Python function that takes two numbers and returns the sum.
call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
  $cleanroom_name,
  'simple_addition',                        -- Function name to use in the template
  ['someval integer', 'added_val integer'], -- Arguments
  [],                                       -- No packages needed
  'integer',                                -- Return type
  'main',                                   -- Handler for function name
  $$

def main(input, added_val):
  return input + int(added_val)
    $$
);

-- Template passes value from each row to the function, along with a
-- caller-supplied argument named 'increment'
call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_python_example',
$$
    SELECT val, cleanroom.simple_addition(val, {{ increment | sqlsafe }})
    FROM VALUES (5),(8),(12),(39) AS P(val);
$$
);