사용자 지정 클린룸 템플릿 참조¶

간단한 예¶

다음은 이메일을 통해 공급자와 컨슈머 테이블을 조인하고 도시별 중복 수를 보여주는 간단한 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 }});

템플릿에 대한 참고 사항:

• {{ double bracket pairs }} 내의 값은 사용자 지정 변수입니다. 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.

• {{ double brackets }} 의 변수는 유효한 식별자가 아닌 문자열 리터럴로 평가되므로 모든 열 및 테이블 이름에 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.

사용자 지정 템플릿 개발¶

클린룸 템플릿은 JinjaSQL 템플릿입니다. 템플릿을 만들려면 다음 항목을 숙지해야 합니다.

• Jinja templating basics

• The JinjaSQL extension to Jinja.

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 필터 및 대부분의 표준 JinjaSQL 필터 를 몇 가지 확장 프로그램과 함께 지원합니다.

• 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 코드를 클린룸에 업로드하면 템플릿은 구문 cleanroom.function_name`을 사용하여 Python 함수를 호출합니다. :doc:`자세한 내용은 여기를 참조하세요.</user-guide/cleanrooms/demo-flows/custom-code>

• **컨슈머**가 사용자 지정 Python 코드를 클린룸에 업로드하면 템플릿은 consumer.generate_python_request_template``에 전달된 베어 ``function_name 값으로 함수를 호출합니다(공급자 코드와 같이 cleanroom`으로 범위가 지정되지 않음). :doc:`자세한 내용은 여기를 참조하세요.</user-guide/cleanrooms/demo-flows/custom-code>

공급자 코드 예시:

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