Referenz für benutzerdefinierte Clean Room-Vorlagen¶

Ein kurzes Beispiel¶

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

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

So würde diese Abfrage als JinjaSQL-Vorlage aussehen, die es dem Aufrufer ermöglicht, die Spalten JOIN und GROUP BY sowie die verwendeten Tabellen auszuwählen:

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.

• Anbietertabellen müssen mit dem Kleinbuchstaben p und Verbrauchertabellen mit dem Kleinbuchstaben c als Alias in einer Vorlage verwendet werden. Wenn Sie mehrere Tabellen haben, können Sie diese als p1, p2, c1, c2 und so weiter indizieren.

• 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-Filter können auf Variablen angewendet werden, um beliebige, von einer der beiden Seiten festgelegte Join- oder Spaltenrichtlinien zu erzwingen. Snowflake implementiert die kundenspezifischen Filter join_policy und column_policy, die überprüfen, ob eine Spalte den Verknüpfungs- bzw. Spaltenrichtlinien im Clean Room entspricht, und die Abfrage fehlschlagen lässt, wenn dies nicht der Fall ist. Ein Filter wird auf einen Spaltennamen als {{ column_name | filter_name }} angewendet.

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

Hier wird gezeigt, wie ein Verbrauchender diese Vorlage im Code ausführen könnte. Beachten Sie, wie Spaltennamen durch die in der Vorlage deklarierten Tabellenaliasse 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'
  )
);

Um diese Vorlage in der Clean Rooms-UI zu verwenden, muss der Anbieter ein kundenspezifisches UI-Formular für die Vorlage erstellen. Das UI-Formular hat benannte Formularelemente, die den Namen der Vorlagenvariablen entsprechen, und die im Formular angegebenen Werte werden an die Vorlage übergeben.

Entwickeln einer benutzerdefinierten Vorlage¶

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

• Grundlagen zu Jinja-Vorlagen

• Die JinjaSQL-Erweiterung für Jinja.

Verwenden Sie die Prozedur consumer.get_jinja_sql, um die Gültigkeit Ihrer Vorlage zu testen, und führen Sie dann die gerenderte Vorlage aus, um zu sehen, ob sie die erwarteten Ergebnisse liefert. Beachten Sie, dass diese Prozedur keine Clean Room-Filtererweiterungen unterstützt, wie z. B. join_policy, sodass Sie Ihre Vorlage ohne diese Filter testen müssen und diese später hinzufügen müssen.

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 Datensets zugreifen, die vom Anbietenden und Verbrauchenden mit dem Clean Room verknüpft sind.

Sowohl Anbieter als auch Verbraucher können Verknüpfungs-, Spalten- und Aktivierungsrichtlinien für ihre Daten festlegen, um zu definieren, welche Spalten verknüpft, projiziert oder aktiviert werden können. Die Vorlage muss aber den entsprechenden JinjaSQL-Richtlinienfilter auf einer Spalte enthalten, damit die Richtlinie angewendet wird.

Regeln für die Benennung von Vorlagen¶

Beim Erstellen einer Vorlage dürfen Namen nur aus Kleinbuchstaben, Zahlen, Leerzeichen oder Unterstrichen bestehen. Aktivierungsvorlagen (mit Ausnahme der vom Verbraucher ausgeführten Anbieteraktivierungen) müssen einen Namen haben, der mit activation_ beginnt. Die Namen der Vorlagen werden zugewiesen, wenn Sie provider.add_custom_sql_template oder consumer.create_template_request aufrufen.

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¶

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

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

Erforderliche Tabellen-Aliasse¶

Auf der obersten Ebene Ihrer Abfrage müssen alle Tabellen oder Unterabfragen mit dem Alias p (für Anbietertabellen) oder c (für Verbrauchertabellen) versehen werden, damit Snowflake die Verknüpfungs- und Spaltenrichtlinien in der Abfrage korrekt überprüfen kann. Jede Spalte, die anhand von Verknüpfungs- oder Spaltenrichtlinien verifiziert werden soll, muss mit dem Tabellenalias kleines p oder c qualifiziert sein. (Die Angabe von p oder c teilt dem Backend mit, ob eine Spalte anhand der Anbieter- oder der Verbraucherrichtlinie validiert werden soll).

Wenn Sie in Ihrer Abfrage mehrere Anbieter- oder Verbrauchertabellen verwenden, fügen Sie jedem Tabellenalias nach der ersten ein numerisches, fortlaufendes 1-basiertes Suffix hinzu. Also: p, p1, p2``usw. für die erste, zweite und dritte Anbietertabelle oder ``c, c1, c2 usw. für die erste, zweite und dritte Verbrauchertabelle. Der p- oder c-Index sollte ohne Lücken sequenziell sein (d. h. nutzen Sie die Aliasnamen p, p1 und p2, nicht p, p2 und p4).

Beispiel

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

Kundenspezifische Clean Room-Filter¶

Snowflake unterstützt alle Standard-Jinja-Filter und die meisten der Standard-JinjaSQL-Filter sowie einige Erweiterungen:

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

• 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 identifier JinjaSQL-Filter wird von Snowflake-Vorlagen nicht unterstützt.

Clean Room-Richtlinien durchsetzen¶

Clean Rooms überprüfen nicht automatisch Clean Room-Richtlinien für die Spalten, die in einer Vorlage verwendet werden. Wenn Sie eine Richtlinie für eine Spalte erzwingen möchten:

• Sie müssen den entsprechenden Richtlinienfilter auf diese Spalte in der Vorlage anwenden. Beispiel:

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.

Beachten Sie beim Testen von Richtlinien, dass Spaltennamen nicht mehrdeutig sein dürfen. Wenn Sie also Spalten mit demselben Namen in zwei Tabellen haben, müssen Sie den Spaltennamen qualifizieren, um die Richtlinie für diese Spalte testen zu können.

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