自定义 Clean Room 模板参考¶

快速示例¶

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

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

Here is how that query would look as a JinjaSQL template that allows the caller to choose the JOIN and GROUP BY columns, as well as the tables used:

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 的提供商表和使用者表的完全限定名称。调用方指定每个数组中应该包括哪些表。

• Provider tables must be aliased as lowercase p and consumer tables as lowercase c in a template. If you have multiple tables, you can index them as p1, p2, c1, c2, and so on.

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

• JinjaSQL filters can be applied to variables to enforce any join or column policies set by either side. Snowflake implements custom filters join_policy and column_policy, which verify whether a column complies with join or column policies in the clean room respectively, and fail the query if it does not. A filter is applied to a column name as {{ 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'
  )
);

To be able to use this template in the clean rooms UI, the provider must create a custom UI form for the template. The UI form has named form elements that correspond to template variable names, and the values provided in the form are passed into the template.

开发自定义模板¶

Clean Room 模板就是 JinjaSQL 模板。要创建模板，您应该熟悉以下主题：

• Jinja templating basics (https://jinja.palletsprojects.com/en/stable/)

• The JinjaSQL extension to Jinja (https://github.com/sripathikrishnan/jinjasql).

Use the consumer.get_jinja_sql procedure to test the validity of your template, then run the rendered template to see that it produces the results that you expect. Note that this procedure doesn't support clean room filter extensions, such as join_policy, so you must test your template without those filters, and add them later.

示例：

-- Template to test
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe}}{% endif %};

-- Render the template.
USE WAREHOUSE app_wh;
USE ROLE SAMOOHA_APP_ROLE;

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON IDENTIFIER({{ provider_join_col }}) = IDENTIFIER({{ consumer_join_col }})
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'where_phrase', 'p.household_size > 2',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

The rendered template looks like this:

SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  WHERE p.household_size > 2;

Try running the SQL statement above in your environment to see if it works, and gets the expected results.

Then test your template without a WHERE clause:

-- Render the template without a WHERE clause
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

Rendered template:

SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  ;

Add the policy filters to the template, and add the template to your clean room:

CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_template',
    $$
    SELECT {{ col1 | sqlsafe | column_policy }}, {{ col2 | sqlsafe | column_policy }}
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      JOIN IDENTIFIER({{ my_table[0] }}) AS c
      ON {{ provider_join_col | sqlsafe | join_policy }} = {{ consumer_join_col | sqlsafe | join_policy }}
      {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
    $$,
);

数据保护¶

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

Both the provider and consumer can set join, column, and activation policies on their data to protect which columns can be joined on, projected, or activated; however, the template must include the appropriate JinjaSQL policy filter on a column for the policy to be applied.

模板命名规则¶

When creating a template, names must be all lowercase letters, numbers, spaces, or underscores. Activation templates (except for consumer-run provider activation) must have a name beginning with activation_. Template names are assigned when you call provider.add_custom_sql_template or 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 }};

必填表别名¶

At the top level of your query, all tables or subqueries must be aliased as either p (for provider-tables) or c (for consumer tables) in order for Snowflake to validate join and column policies correctly in the query. Any column that must be verified against join or column policies must be qualified with the lowercase p or c table alias. (Specifying p or c tells the back end whether to validate a column against the provider or the consumer policy respectively.)

If you use multiple provider or consumer tables in your query, add a numeric, sequential 1-based suffix to each table alias after the first. So: p, p1, p2, and so on for the first, second, and third provider tables, and c, c1, c2, and so on for the first, second, and third consumer tables. The p or c index should be sequential without gaps (that is, create the aliases p, p1, and p2, not p, p2, and p4).

示例

SELECT p.col1 FROM IDENTIFIER({{ source_table[0] }}) AS P
UNION
SELECT p1.col1 FROM IDENTIFIER({{ source_table[1] }}) AS P1;

Custom clean room template filters¶

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: Succeeds if the column is in the join policy of the data owner; fails otherwise.

• column_policy: Succeeds if the column is in the column policy of the data owner; fails otherwise.

• activation_policy: Succeeds if the column is in the activation policy of the data owner; fails otherwise.

• join_and_column_policy: Succeeds if the column is in the join or column policy of the data owner; fails otherwise.

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

强制实施 Clean Room 策略¶

Clean rooms do not automatically check clean room policies against columns used in a template. If you want to enforce a policy against a column:

• You must apply the appropriate policy filter to that column in the template. For example:

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

• You must alias the table as lowercase p or c. See 必填表别名.

Policies are checked only against columns owned by other collaborators; policies are not checked for your own data.

Note that column names cannot be ambiguous when testing policies. So if you have columns with the same name in two tables, you must qualify the column name in order to test the policy against that column.

运行自定义 Python 代码¶

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

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

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

提供商代码示例：

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