向 Clean Room 添加自定义模板¶

添加提供商编写的模板¶

提供商通过调用 provider.add_custom_sql_template 并将模板 JinjaSQL 作为字符串传入，一次添加一个自定义模板。自定义模板会出现在 Clean Room 的模板列表中，其行为与 Snowflake 提供的模板相同。Clean Room 可以包含自定义模板和 Snowflake 提供的模板的任意组合。

您也可以上传 自定义 Python UDFs，以供模板调用。

以下示例展示了提供商如何向 Clean Room 添加自定义模板：

CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $basic_template_name,
    $$
    SELECT
      COUNT(*) AS total_count
    FROM IDENTIFIER({{ my_table[0] }}) AS c
      INNER JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON IDENTIFIER({{ consumer_id | join_policy }}) = IDENTIFIER({{ provider_id | join_policy }})
    {% if where_clause %}
    WHERE {{ where_clause | sqlsafe }}
    {% endif %};
  $$
);

在大多数模板中，包括前面的示例中，列名必须使用表名完全限定，以免列名冲突。这是因为要将表名前缀连接到前缀中的列名并获得有效的标识符（IDENTIFIER(p.{{col_name | sqlsafe }}) 就是一个错误）并不容易。因此，您可能需要调用方提供完全限定的表名，而不仅仅是列名。表名应使用批准的 p 和 c 别名。

运行提供商编写的模板¶

使用 Clean Room API 时，使用者调用 consumer.run_analysis 来运行模板，提供商为 提供商运行的分析 调用 provider.submit_analysis_request。

如果您希望模板可以在 Clean Room UI 中运行，则提供商必须为该模板 创建用户输入表单。只有提供商编写的模板才能在 Clean Room UI 中运行。

除非提供商 对模板进行了模糊处理，否则 Clean Room 协作者可以通过调用 consumer.view_template_definition 查看 Clean Room 中任何模板的 JinjaSQL。只能对提供商编写的模板进行模糊处理。

您可以调用 consumer.get_arguments_from_template，解析和列出模板中使用的变量。但对于大型或复杂的模板，此过程可能不会列出所有模板变量，因此请确保为模板用户提供有用的文档。

以下示例显示了使用者如何运行前面显示的提供商的自定义模板：

 CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  'basic_template',
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],  -- Populates the my_table array.
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],  -- Populates the source_table array.
  OBJECT_CONSTRUCT(
  'consumer_id', 'c.hashed_email',  -- hashed_email column from the consumer's table.
  'provider_id', 'p.hashed_email',  -- hashed_email column from the provider's table.
  'where_clause','c.status = $$MEMBER$$ AND c.age_band > 30' -- $$...$$ is used to stringify the column value.
  )
);

提供商模板示例代码¶

以下是完整的代码示例，展示了提供商如何添加自定义模板，以及使用者如何运行该模板。您需要两个安装了 Clean Room API 的独立账户来运行代码；一个账户充当提供商，另一个账户充当使用者。

• Provider example code

• Consumer example code

使用者模板示例¶

以下是完整的代码示例，展示了使用者如何提交和运行自定义模板。将以下工作簿文件上传到您的 Snowflake 账户。您需要两个安装了 Clean Room API 的独立账户来运行代码；一个账户充当提供商，另一个充当使用者。

• Provider example code

• Consumer example code

填充 source_table 和 my_table¶

标准 source_table 和 my_table 模板变量可按如下方式填充：

• 启用默认表选择器下拉菜单： 这些下拉菜单为单选菜单。您可以使用 render_provider_table_dropdown 和 render_consumer_table_dropdown 设置显示或隐藏它们。下拉菜单将完全限定表名分别传递给 source_table 和 my_table 模板变量。

限定列名¶

大多数模板要求所有列名必须为完全限定形式，以避免列名出现歧义。

模板必须将所有表的别名设为 p 或 c，具体取决于它们是提供商表还是使用者表。模板应使用所有列的 p 或 c 别名引用它们。了解有关别名的更多信息。

如果创建下拉列选择器，则必须在下拉菜单的 choices 数组中明确提供 p 或 c 表别名，或者必须在模板中添加别名。

以下示例显示如何在下拉菜单中提供表别名：

  'provider_join_col': {
    'display_name': 'Provider Join Column',
    'choices': ['p.HASHED_EMAIL', 'p.HASHED_SSN'],
    'type': 'dropdown',
    'description': 'Select the provider column to join users on.',
    'infoMessage': 'We recommend using HASHED_EMAIL.',
    'size': 'M',
    'group': 'Enable Provider Features'
}

但这种方法存在一定的局限性，因为您必须事先知道所有列名。

作为替代方案，您可以通过提供 references 属性来动态填充列下拉菜单。但这样的选择器返回的是裸列名，例如 hashed_email，而非完全限定列名，例如 p.hashed_email。如果返回的是裸列名，则必须在模板中明确地将列范围限定为表。例如，以下代码创建了一个下拉菜单，用户可以在其中从提供商的联接策略中选择一列：

'p_join_col': {
  'type': 'dropdown',
  'references': ['PROVIDER_JOIN_POLICY']
}

Copy

要在模板中使用列名，模板必须将表别名硬编码到列名前面，如以下示例所示：

SELECT p.{{ p_join_col | sqlsafe }} FROM table_col AS p;

Copy

关于开发可在 Clean Room UI 中运行的模板的建议¶

以下步骤显示了开发可在 Clean Room UI 中运行的模板的推荐工作流程：

SELECT {{ col1 | sqlsafe | column_policy }}, {{ col2 | sqlsafe | column_policy }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c

SELECT
  {{ col1 | default('Undefined')}},
  {{ col2 | default('Undefined') }},
  {{ source_table[0] | default('Undefined') }},
  {{ my_table[0] | default('Undefined') }},
  {{ provider_join_col | default('Undefined') }},
  {{ consumer_join_col | default('Undefined') }}
;