自定义 Clean Room 模板参考

关于 Clean Room 模板

Clean Room 模板采用 JinjaSQL (https://github.com/sripathikrishnan/jinjasql).JinjaSQL 编写,是 Jinja 模板语言的扩展,可生成 SQL 查询作为输出。这允许模板使用逻辑语句和运行时变量解析,让用户指定运行时查询中使用的表名、表列和自定义值。

Snowflake 为常见用例提供了一系列预先设计的模板。但是,大多数用户更喜欢为其 Clean Room 创建自定义查询模板。自定义模板使用 Clean Room API 创建,但可以在代码中运行,也可以使用 Clean Room UI 运行。

模板一般有两种类型:

  • 分析模板,其计算结果为 SELECT 语句(或一组 SELECT 操作),向模板运行器显示结果。

  • 激活模板,用于将结果激活到 Snowflake 账户或第三方,而不是在直接环境中显示结果。激活模板与具有 一些额外要求 的分析模板非常相似。

    在 Clean Room UI 中,分析模板可以与激活模板相关联,以使调用方能够进行分析、查看结果,然后将数据发送给自己或第三方。激活模板无需解析为与关联分析模板相同的查询。

创建和运行自定义模板

在具有默认设置的 Clean Room 中,提供商向 Clean Room 添加模板,使用者运行该模板,如 自定义模板使用文档 所述。

快速示例

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

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

以下是该查询与允许调用方选择 JOIN 和 GROUP BY 列的 JinjaSQL 模板以及使用的表的相似之处:

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_colmy_tablesource_tableconsumer_join_colprovider_join_colgroup_by_col 都是调用方填充的自定义变量。

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

  • 在模板中,提供商表别名须为 小写 ` ``p`,使用者表别名须为 小写 ` ``c`。如果您有多个表,则可以将它们索引为 p1p2c1c2 等。

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

  • JinjaSQL 筛选器 可以应用于变量,以强制执行任何由任一侧设置的 联接或列策略。Snowflake 实现了自定义筛选器 join_policycolumn_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'
  )
);

要能够在 Clean Room UI 中使用此模板,提供商必须 为模板创建自定义 UI 表单。UI 表单具有与模板变量名称对应的命名表单元素,表单中提供的值传递到模板中。

开发自定义模板

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

  • Jinja 模板基础知识 (https://jinja.palletsprojects.com/en/stable/)

  • Jinja 的 JinjaSQL 扩展 (https://github.com/sripathikrishnan/jinjasql)。

使用 consumer.get_jinja_sql 过程来测试模板的有效性,然后运行生成的模板,以查看其是否能产生您期望的结果。但请注意,此过程不支持 Clean Room 筛选器扩展,例如 join_policy,因此,您必须在不使用这些筛选器的情况下测试您的模板,稍后再添加这些筛选器。

示例:

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

生成的模板如下所示:

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;

尝试在您的环境中运行上述 SQL 语句,看看它是否能够生效并获得期望的结果。

然后测试您的模板,不要包含 WHERE 子句:

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

生成的模板:

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
  ;

将策略筛选器添加到模板,并将模板添加到 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 的数据集。

提供商和使用者都能够对其数据设置联接、列和激活策略,以确保哪些列可以联接、投射或激活;但是,模板 必须 包括针对列的 合适 JinjaSQL 筛选器,才能应用策略。

自定义模板语法

Snowflake Data Clean Room 支持 V3 JinjaSQL,并提供如前所述的一些扩展。

本部分包括以下主题:

模板命名规则

创建模板时,名称必须全部为小写字母、数字、空格或下划线。激活模板(使用者运行的提供商激活除外)的名称必须以 activation_ 开头。调用 provider.add_custom_sql_templateconsumer.create_template_request 时分配模板名称。

有效名称示例:

  • my_template

  • activation_template_1

无效名称示例:

  • my template - 不允许存在空格

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

模板变量

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

小心

所有变量,无论是 Snowflake 定义的还是自定义的,都是由用户填充的,因此应谨慎对待。Snowflake Data Clean Room 模板必须解析为单个 SELECT 语句,但您仍应记住所有变量均由调用方传入。

Snowflake 定义的变量

所有 Clean Room 模板都可以访问由 Snowflake 定义但由调用方传入的以下全局变量:

source_table:

一个从零开始的字符串数组,表示 Clean Room 中可由模板使用的提供商关联表和视图。表名是完全限定的,例如:my_db.my_sch.provider_customers

例如: SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS p;

my_table:

一个从零开始的字符串数组,表示 Clean Room 中可由模板使用的使用者表和视图。表名是完全限定的,例如:my_db.my_sch.consumer_customers

例如: SELECT col1 FROM IDENTIFIER({{ my_table[0] }}) AS c;

privacy:

一组与用户和模板关联的隐私相关值。查看可用子字段列表。对于用户,这些值可以 显式设置,但您可能希望在模板中设置默认值。直接在模板中访问子字段,例如 privacy.threshold

示例: 以下是模板的示例片段,该模板使用 threshold_value 在聚合子句中强制执行最小组规模。

SELECT
  IFF(a.overlap > ( {{ privacy.threshold_value | default(2)  | sqlsafe }} ),
                    a.overlap,1 ) AS overlap,
  c.total_count AS total_count
  ...
measure_column:

dimensions:

where_clause:

旧版 Clean Room 全局变量。尽管已不再建议使用,但由于其仍存在于部分旧版模板和文档中,因此为避免命名冲突,请勿将其用作表或列的别名。

如果您的模板使用 measure_columndimensions,则将根据传递给这些变量的任何列检查列策略。

如果您的模板使用具有联接条件的 ``where_clause``(例如,table1.column1 = table2.column2),则将根据其中已命名的任何列检查联接策略;否则,将对照其中已命名的任何列检查列策略。

自定义变量

模板创建者可以在模板中包含可由调用方填充的任意变量。除了 Snowflake 定义的变量或表别名之外,这些变量可以具有任何符合 Jinja 规范的任意名称。如果您希望模板在 Clean Room UI 中可用,还必须为 Clean Room UI 用户提供 UI 表单。对于 API 用户,您应为必填变量和可选变量提供完善的文档。

自定义变量可由模板访问,如这里所示的自定义变量 max_income

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

用户可以通过两种不同方式将变量传递给模板:

  • 在 Clean Room UI 中, 通过模板开发者创建的 UI 表单选择或提供值。此 UI 表单包含表单元素,用户可以在其中为您的模板提供值。表单元素的名称为变量的名称。模板仅使用表单元素的名称来访问值。使用 UIprovider.add_ui_form_customizations:ref:`<dcr_provider_add_ui_form_customizations> 创建 ` 表单。

  • 在代码中, 使用者调用 consumer.run_analysis 并将表名作为实参数组传入,将自定义变量作为名称-值对传入 analysis_arguments 实参。

备注

如果需要访问上传到 Clean Room 的任何自定义 Python 代码中用户提供的值,必须通过 Python 函数实参 将变量值显式传递 给代码;不能使用 {{jinja variable binding syntax}} 直接在 Python 代码中访问模板变量。

正确解析变量

传入模板的字符串值解析为最终模板中的字符串字面量。如果绑定变量处理不当,可能会导致 SQL 解析或逻辑错误:

  • SELECT {{ my_col }} FROM P; – 这解析为 SELECT 'my_col' from P;,仅返回字符串“my_col”,这可能不是您想要的。

  • SELECT age FROM {{ my_table[0] }} AS P; – 解析为 SELECT age FROM 'somedb.somesch.my_table' AS P;,这会导致分析错误,因为表必须为标识符,而非字面量字符串。

  • SELECT age FROM IDENTIFIER({{ my_table[0] }}) AS P {{ where_clause }}; – 传入 "WHERE age < 50",计算值为 SELECT age FROM mytable AS P 'WHERE age < 50';,这是由字面量字符串 WHERE 子句导致的解析错误。

因此,在适当情况下,您必须解析变量。下面介绍如何在模板中正确解析变量:

解析表名和列名

指定表或列名称的变量必须通过以下两种方式之一转换为模板中的标识符:

  • IDENTIFIER:例如:SELECT IDENTIFIER ({{ my_column }}) FROM P;

  • sqlsafe (https://github.com/sripathikrishnan/jinjasql?tab=readme-ov-file#sql-safe-strings):此 筛选器将标识符字符串解析为 文字。此 JinjaSQL 筛选器将标识符字符串解析为 SQL 文字。与前一个项目符号等效的语句是 SELECT {{ my_column | sqlsafe }} FROM P;

您的特定用法决定了何时使用 IDENTIFIER 或 sqlsafe。例如,c.{{ my_column | sqlsafe }} 不易使用 IDENTIFIER 重写。

解析动态 SQL

当有字符串变量用作字面量 SQL 时,例如 WHERE 子句,请使用模板中的 sqlsafe 筛选器。例如:

SELECT age FROM IDENTIFIER({{ my_table[0] }}) AS C WHERE {{ where_clause }};

如果用户将“age < 50”传递给 where_clause,查询将解析为 SELECT age FROM sometable AS C WHERE 'age < 50';,由于字面量字符串 SQL 条件导致其为无效 WHERE。在这种情况下,您应该使用 sqlsafe 筛选器:

SELECT age FROM IDENTIFIER( {{ my_table[0] }} ) as c {{ where_clause | sqlsafe }};

必填表别名

在查询顶级,所有表或子查询必须使用别名 p``(用于提供商表)或 ``c``(用于使用者表),以便 Snowflake 在查询中正确验证联接和列策略。任何必须根据联接或列策略进行验证的列都必须符合 :emph:`小写 ` ``pc 表别名。(指定 pc 告诉后端是分别针对提供商还是使用者策略验证列。)

如果在查询中使用多个提供商表或使用者表,请在第一个表别名后为每个表别名添加一个从 1 开始的连续后缀。因此:p `、``p1`p2 等表示第一、第二和第三个提供商表,cc1c2 等表示第一、第二和第三个使用者表。pc 索引应该是连续的且无间隙(即创建别名 pp1p2,而非 pp2p4)。

示例

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

自定义 Clean Room 模板筛选器

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:选择使用 时默认使用的角色和仓库。如果列在数据所有者的激活策略中,则成功;否则将失败。

  • join_and_column_policy:验证联接、激活或列策略是否允许列。如果列在数据所有者的联接或列策略中,则成功;否则将失败。

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

小技巧

JinjaSQL 语句是从左到右评估:

  • {{ my_col | column_policy }} 正确

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

  • {{ column_policy | my_col }} 不正确

  • {{ my_col | column_policy | sqlsafe }} 不正确: column_policy 将对照字符串形式的 my_col 值进行检查,这是一个错误。

强制实施 Clean Room 策略

Clean Room 不会自动根据模板中使用的列检查 Clean Room 策略。如果要对列强制执行策略,请执行以下操作:

  • 您必须为模板中的该列应用适当的 策略筛选器。例如:

JOIN IDENTIFIER({{ source_table[0] }}) AS p
  ON IDENTIFIER({{ c_join_col | join_policy }}) = IDENTIFIER({{ p_join_col | join_policy }})
  • 您必须将表的别名设为 小写 ` p 或 c。请参阅 :ref:`label-dcr_v1_required_template_table_aliases

仅根据其他协作者拥有的列检查策略;不会针对您自己的数据检查策略。

请注意,测试策略时,列名不能含糊不清。因此,如果两个表中有同名的列,则必须限定列名才能针对该列测试策略。

运行自定义 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);
$$
);

安全注意事项

Clean Room 模板不以当前用户的身份执行。

用户不能直接访问 Clean Room 内的任何数据;所有访问都是通过模板结果,借助原生应用程序进行的。

每当模板中使用列时,都应该应用策略筛选器,以确保您的策略以及所有协作者的策略得到遵守。

如果可能,使用 IDENTIFIER() 封装用户提供的变量以加强您的模板,防止受到 SQL 注入攻击。

激活模板

模板还可用于将查询结果保存到 Clean Room 外的表中;这称为 激活。目前,自定义模板支持的激活形式仅限提供商激活和使用者激活(分别将结果存储到提供商或使用者的 Snowflake 账户)。了解如何实施激活。

激活模板是具有以下附加要求的分析模板:

  • 激活模板是计算结果为 SQL 脚本块的 JinjaSQL 语句,可以是简单的 SELECT 语句,这点与分析模板不同。

  • 激活模板在 Clean Room 中创建一个表来存储结果,并将表名称(或名称的片段)返回给模板调用方。

  • 脚本块应以一条 RETURN 语句结尾,该语句返回生成的表的名称,减去任何 cleanroom.cleanroom.activation_data_ 前缀。

  • 模板的名称、模板创建的内部表的名称以及模板返回的表名称遵循以下模式:

激活类型

模板名称前缀

表名称前缀

返回的表名称

使用者运行的使用者

activation_

cleanroom.activation_data_*

不带前缀的表名称

使用者运行的提供商

无需前缀

cleanroom.activation_data_*

不带前缀的表名称

提供商运行的提供商

activation_

cleanroom.temp_result_data 是完整的表名称。

temp_result_data

  • 任何被激活的列都必须在已链接数据的提供商或使用者的 激活策略 中列出,并且应对其应用 activation_policy 筛选器。请注意,列既可以是激活列,也可以是联接列。

  • 如果要在 Clean Room UI 中运行模板,您应 提供一个 Web 表单,其中包含 activation_template_nameenabled_activations 字段。在 UI 中使用的模板必须同时具有分析模板和关联的激活模板。

  • 由于表正在生成,因此所有计算的列都必须显式使用别名,而不是推断的名称。即:

    SELECT COUNT(*), p.status from T AS P; FAILS,因为 COUNT 列名是推断出来的。

    SELECT COUNT(*) AS COUNT_OF_ITEMS, p.status from T AS P; SUCCEEDS,因为它为 COUNT 列显式设置了别名。

以下是两个基本激活模板示例。一个用于提供商运行的服务器激活,另一个用于其他激活类型。它们的不同之处在于两个突出显示的行,这两行包含结果表名。

表必须命名 cleanroom.temp_result_data

BEGIN
  CREATE OR REPLACE TABLE cleanroom.temp_result_data AS
    SELECT COUNT(c.status) AS ITEM_COUNT, c.status, c.age_band
      FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band
    ORDER BY c.age_band;
  RETURN 'temp_result_data';
END;

后续步骤

掌握模板系统后,请阅读使用模板类型实现 Clean Room 的详细说明:

  • 提供商模板 是提供商编写的模板。这是默认用例。

  • 使用者模板 是使用者编写的模板。在某些情况下,Clean Room 创建者希望让使用者能够创建、运行自己的模板并将其上传至 Clean Room。

  • 激活模板 在成功运行后创建一个结果表。根据激活模板,结果表可以保存到 Clean Room 之外的提供商或使用者账户,也可以发送到 Activation Hub 中所列的第三方激活提供商。

  • 链接模板 允许您将多个模板链接在一起,每个模板的输出由链中的下一个模板使用。

更多信息

  • Jinja 文档 (https://jinja.palletsprojects.com/en/stable/)

  • JinjaSQL 文档 (https://github.com/sripathikrishnan/jinjasql)