Referenz für benutzerdefinierte Clean Room-Vorlagen¶

Ein kurzes Beispiel¶

Hier ist ein einfaches SQL-Beispiel, das eine Anbieter- und eine Verbrauchertabelle nach E-Mail verknüpft und die Anzahl der Überlappungen pro Stadt anzeigt:

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

Anmerkungen zur Vorlage:

• Werte innerhalb von {{ double bracket pairs }} sind benutzerdefinierte Variablen. group_by_col, my_table, source_table, consumer_join_col, provider_join_col und group_by_col sind alle benutzerdefinierten Variablen, die vom Aufrufer eingegeben werden.

• source_table und my_table sind von Snowflake definierte Zeichenfolgen-Array-Variablen, die vom Aufrufer aufgefüllt werden. Array-Mitglieder sind vollqualifizierte Namen von Anbieter- und Verbrauchertabellen, die mit dem Clean Room verknüpft sind. Der Aufrufer gibt an, welche Tabellen in jedes Array aufgenommen werden sollen.

• 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 wird für alle Spalten- und Tabellennamen benötigt, da Variablen in {{ double brackets }} als Zeichenfolgenliterale ausgewertet werden, die keine gültigen Bezeichner sind.

• 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 }}.

All diese Punkte werden wir später im Detail besprechen.

Hier sehen Sie, wie ein Verbraucher diese Vorlage im Code ausführen könnte. Beachten Sie, wie die Spaltennamen durch die in der Vorlage deklarierten Tabellen-Aliasse qualifiziert werden.

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.

Entwickeln einer benutzerdefinierten Vorlage¶

Clean Room-Vorlagen sind JinjaSQL-Vorlagen. Um eine -Vorlage zu erstellen, sollten Sie mit den folgenden Themen vertraut sein:

• 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.

Beispiel:

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

Die gerenderte Vorlage sieht wie folgt aus:

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;

Versuchen Sie, die obige SQL-Anweisung in Ihrer Umgebung auszuführen, um zu sehen, ob sie funktioniert und die erwarteten Ergebnisse liefert.

Dann testen Sie Ihre Vorlage ohne WHERE-Klausel:

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

Gerenderte Vorlage:

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
  ;

Fügen Sie die Richtlinienfilter zur Vorlage hinzu, und fügen Sie die Vorlage zu Ihrem Clean Room hinzu:

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 %};
    $$,
);

Datenschutz¶

Vorlagen können nur auf Datensätze zugreifen, die vom Anbieter und vom Verbraucher mit dem Clean Room verknüpft wurden.

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.

Regeln für die Benennung von Vorlagen¶

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.

Beispiel für gültige Namen:

• my_template

• activation_template_1

Beispiel für ungültige Namen:

• my template - Leerzeichen nicht erlaubt

• My_Template - Nur Vorlagen mit Kleinschreibung erlaubt

Vorlagenvariablen¶

Aufrufer von Vorlagen können Werte an Vorlagenvariablen übergeben. Die JinjaSQL-Syntax ermöglicht die Variablenbindung für jeden Variablennamen innerhalb von {{ double_brackets }}, aber Snowflake behält sich einige Variablennamen vor, die Sie nicht überschreiben sollten, wie unten beschrieben.

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

Erforderliche Tabellen-Aliasse¶

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).

Beispiel

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 unterstützt alle Standard-Jinja-Filter und die meisten der Standard-JinjaSQL-Filter sowie einige Erweiterungen:

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

• column_policy: Erfolgreich, wenn die Spalte in der Spaltenrichtlinie des Dateneigentümers enthalten ist; schlägt andernfalls fehl.

• activation_policy: Erfolgreich, wenn die Spalte in der Aktivierungsrichtlinie des Dateneigentümers enthalten ist; schlägt andernfalls fehl.

• join_and_column_policy: Erfolgreich, wenn die Spalte in der Verknüpfungs- oder Spaltenrichtlinie des Dateneigentümers enthalten ist; schlägt andernfalls fehl.

• Der JinjaSQL-Filter identifier wird nicht von Snowflake-Vorlagen unterstützt.

Clean Room-Richtlinien durchsetzen¶

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 }})

• Sie müssen für die Tabelle den Alias mit kleinem p oder c verwenden. Siehe Erforderliche Tabellen-Aliasse.

Richtlinien werden nur für Spalten geprüft, die anderen Teilnehmern gehören. Richtlinien werden nicht auf Ihre eigenen Daten geprüft.

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.

Benutzerdefinierten Python-Code ausführen¶

Vorlagen können Python-Code ausführen, der in den Clean Room hochgeladen wurde. Die Vorlage kann eine Python-Funktion aufrufen, die Werte aus einer Datenzeile entgegennimmt und Werte zurückgibt, die in der Abfrage verwendet oder projiziert werden können.

• Wenn ein Anbieter benutzerdefinierten Python-Code in einen Clean Room hochlädt, ruft die Vorlage Python-Funktionen mit der Syntax cleanroom.function_name auf. Weitere Details dazu finden Sie hier.

• Wenn ein Verbraucher benutzerdefinierten Python-Code in einen Clean Room hochlädt, ruft die Vorlage die Funktion mit dem leeren function_name-Wert auf, der an consumer.generate_python_request_template übergeben wird (nicht wie der Anbietercode auf Clean Room beschränkt). Weitere Details dazu finden Sie hier.

Beispiel für einen Anbietercode:

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