Référence d’un modèle de salle blanche personnalisé¶

Un exemple rapide¶

Voici un exemple simple de SQL qui joint une table de fournisseurs et une table de consommateurs par courriel et affiche le nombre de chevauchements par ville :

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

Voici à quoi ressemblerait cette requête sous la forme d’un modèle JinjaSQL qui permet à l’appelant de choisir les colonnes JOIN et GROUP BY, ainsi que les tables utilisées :

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

Notes sur le modèle :

• Les valeurs contenues dans {{ double bracket pairs }} sont des variables personnalisées. group_by_col les valeurs de my_table, source_table, consumer_join_col, provider_join_col et group_by_col sont toutes des variables personnalisées renseignées par l’appelant.

• source_table et my_table sont des variables de type tableau de chaînes définies par Snowflake et renseignées par l’appelant. Les membres du tableau sont les noms pleinement qualifiés des tables de fournisseurs et de consommateurs liées à la clean room. L’appelant spécifie les tables à inclure dans chaque tableau.

• Les tables de fournisseur doivent avoir un alias en minuscules p et les tables de consommateurs en minuscules c dans un modèle. Si vous avez plusieurs tables, vous pouvez les indexer comme p1, p2, c1, c2, et ainsi de suite.

• IDENTIFIER est nécessaire pour tous les noms de colonnes et de tables, car les variables dans {{ double brackets }} évaluent des chaînes littérales, qui ne sont pas des identificateurs valides.

• Les filtres JinjaSQL peuvent être appliqués aux variables pour appliquer n’importe quelle politique de jointure ou de colonne définie de chaque côté. Snowflake implémente des filtres personnalisés join_policy et column_policy, qui vérifient si une colonne est conforme aux politiques de jointure ou de colonne dans la salle blanche respectivement, et font échouer la requête si ce n’est pas le cas. Un filtre est appliqué à un nom de colonne comme {{ column_name | filter_name }}.

Tous ces points seront examinés en détail ultérieurement.

Voici comment un consommateur pourrait exécuter ce modèle dans le code. Notez que les noms de colonnes sont qualifiés par les alias de table déclarés dans le modèle.

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

Afin d’utiliser ce modèle dans l’UI des salles blanches, le fournisseur doit créer un formulaire UI personnalisé pour le modèle. Le formulaire d’UI comporte des éléments de formulaire nommés qui correspondent aux noms des variables du modèle, et les valeurs fournies dans le formulaire sont transmises dans le modèle.

Développement d’un modèle personnalisé¶

Les modèles de salle blanche sont des modèles JinjaSQL. Pour créer un modèle, vous devez connaître les rubriques suivantes :

• Notions de base sur les modèles Jinja

• L’extension JinjaSQL de Jinja.

Utilisez la procédure consumer.get_jinja_sql pour tester la validité de votre modèle, puis exécutez le modèle rendu pour voir s’il produit les résultats que vous attendez. Notez que cette procédure ne prend pas en charge les extensions de filtre de salle blanche, telles que join_policy, vous devez donc tester votre modèle sans ces filtres et les ajouter plus tard.

Exemple :

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

Le modèle rendu ressemble à ceci :

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;

Essayez d’exécuter l’instruction SQL ci-dessus dans votre environnement pour voir si cela fonctionne et permet d’obtenir les résultats attendus.

Testez ensuite votre modèle sans clause 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']
));

Modèle renvoyé :

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
  ;

Ajoutez les filtres de la politique au modèle, et ajoutez le modèle à votre salle blanche :

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

Protection des données¶

Les modèles ne peuvent accéder qu’aux ensembles de données liés à la clean room par le fournisseur et le consommateur.

Le fournisseur et le consommateur peuvent tous deux définir des politiques de jointure, de colonne et d’activation sur leurs données pour protéger les colonnes pouvant être jointes, projetées ou activées ; cependant, le modèle doit inclure le filtre de politique JinjaSQL approprié sur une colonne pour que la politique soit appliquée.

Règles de dénomination des modèles¶

Lors de la création d’un modèle, les noms doivent être tous des lettres minuscules, des chiffres, des espaces ou des traits de soulignement. Les modèles d’activation (à l’exception de l’activation du fournisseur exécutée par le consommateur) doivent avoir un nom commençant par activation_. Les noms des modèles sont attribués lorsque vous appelez provider.add_custom_sql_template ou consumer.create_template_request.

Exemples de noms valides :

• my_template

• activation_template_1

Exemple de noms non valides :

• my template - Espaces non autorisés

• My_Template - Seuls les modèles en minuscules sont autorisés

Variables de modèles¶

Les appelants d’un modèle peuvent transmettre des valeurs aux variables du modèle. La syntaxe JinjaSQL permet la liaison de variables pour n’importe quel nom de variable dans {{ double_brackets }}, mais Snowflake réserve quelques noms de variables que vous ne devez pas remplacer, comme décrit ci-dessous.

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

Alias de table requis¶

Au niveau supérieur de votre requête, toutes les tables ou sous-requêtes doivent avoir un alias p (pour les tables de fournisseurs) ou c (pour les tables de consommateurs) afin que Snowflake valide correctement les politiques de jointure et de colonne dans la requête. Toute colonne qui doit être vérifiée par rapport aux politiques de jointure ou de colonne doit avoir l’alias de table p ou c en minuscule. (Spécifier p ou c indique au back-end s’il faut valider une colonne par rapport au fournisseur ou à la politique du consommateur).

Si vous utilisez plusieurs tables de fournisseurs ou de consommateurs dans votre requête, ajoutez un suffixe numérique et séquentiel de base 1 à chaque alias de table après le premier. Ainsi : p, p1, p2, et ainsi de suite pour les première, deuxième et troisième tables de fournisseurs, et c, c1, c2, et ainsi de suite pour les première, deuxième et troisième tables de consommateurs. L’index p ou c doit être séquentiel sans espaces (c’est-à-dire avec des alias p, p1 et p2, pas p, p2, et p4).

Exemple

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

Filtres de modèles de salle blanche personnalisés¶

Snowflake prend en charge tous les filtres standard Jinja et la plupart des filtres JinjaSQL standard, ainsi que quelques extensions :

• join_policy : Réussit si la colonne est dans la politique de jointure du propriétaire des données ; échoue dans le cas contraire.

• column_policy : Réussit si la colonne figure dans la politique de colonne du propriétaire des données ; échoue dans le cas contraire.

• activation_policy : Réussit si la colonne se trouve dans la politique d’activation du propriétaire des données ; échoue dans le cas contraire.

• join_and_column_policy : Réussit si la colonne figure dans la politique de jointure ou de colonne du propriétaire des données ; échoue dans le cas contraire.

• Le filtre identifier JinjaSQL n’est pas supporté par les modèles Snowflake.

Appliquer les politiques des clean rooms¶

Les salles blanches ne vérifient pas automatiquement les politiques de salle blanche par rapport aux colonnes utilisées dans un modèle. Si vous voulez appliquer une politique à une colonne :

• Vous devez appliquer le Filtre de politique approprié à cette colonne du modèle. Par exemple :

JOIN IDENTIFIER({{ source_table[0] }}) AS p
  ON IDENTIFIER({{ c_join_col | join_policy }}) = IDENTIFIER({{ p_join_col | join_policy }})

• Vous devez ajouter à la table un alias p ou c en minuscule. Voir Alias de table requis.

Les politiques ne sont vérifiées qu’en fonction des colonnes appartenant à d’autres collaborateurs. Les politiques ne sont pas vérifiées pour vos propres données.

Notez que les noms des colonnes ne peuvent pas être ambigus lors du test des politiques. Ainsi, si vous avez des colonnes portant le même nom dans deux tables, vous devez qualifier le nom de la colonne afin de tester la politique par rapport à cette colonne.

Exécution de code Python personnalisé¶

Les modèles peuvent exécuter le code Python téléchargé dans la clean room. Le modèle peut appeler une fonction Python qui accepte les valeurs d’une ligne de données et renvoie des valeurs à utiliser ou à projeter dans la requête.

• Lorsqu’un fournisseur télécharge du code Python personnalisé dans une salle blanche, le modèle appelle des fonctions Python avec la syntaxe cleanroom.function_name. Plus de détails ici.

• Lorsqu’un consommateur télécharge du code Python personnalisé dans une salle blanche, le modèle appelle la fonction avec la valeur function_name de base transmise à consumer.generate_python_request_template (non limité à cleanroom comme l’est le code du fournisseur). Plus de détails ici.

Exemple de code fournisseur :

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