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 JOIN provider_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 permettant à l’appelant de choisir les colonnes de sélection/groupe et de jointure ainsi que les tables :

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 fournisseurs doivent être appelées P et les tables de consommateurs C dans un modèle. Si vous avez plusieurs tables, vous pouvez les indexer comme suit : P1, P2, C1, C2, etc.

• 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. Snowflake implémente les 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 clean room, respectivement, et qui font échouer la requête si ce n’est pas le cas. Un filtre est appliqué au nom d’une 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.

Suivez la procédure get_sql_jinja pour tester la validité de votre modèle et le rendu d’un modèle et exécutez le modèle rendu pour vérifier qu’il produit les résultats que vous souhaitez. 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 {{ 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',
'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;

-- Run it.

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

-- Output
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
  ;

-- Put in the policy filters and declare the template
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 JinjaSQL approprié sur une colonne pour la politique à appliquer.

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

Lors de la création d’un modèle, les noms doivent être composés de lettres minuscules, de chiffres, d’espaces ou de traits de soulignement. Les modèles d’activation (à l’exception de l’activation du fournisseur gérée par le consommateur) doivent avoir un nom commençant par « activation ». Les noms de 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 être appelées 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 appartient à une table dont l’alias est P ou C. (La spécification de P ou C indique au back-end s’il doit valider une colonne par rapport à la politique du fournisseur ou du consommateur, respectivement)

Si vous utilisez plusieurs tables de fournisseurs ou de consommateurs dans votre requête, ajoutez un suffixe numérique séquentiel basé sur 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 et sans trous (c’est-à-dire que vous devez créer les alias P, P1, et P2, et non P, P2, et P4).

Exemple

SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS P;

Filtres Template]¶

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

• join_policy: Vérifie si la colonne est autorisée par la politique de jointure de la table et échoue si ce n’est pas le cas.

• column_policy: Vérifie si la colonne est autorisée par la politique de colonne du modèle (est autorisée à être projetée).

• activation_policy: Vérifie si la colonne filtrée est autorisée par les politiques d’activation de la clean room (provider.set_activation_policy ou consumer.set_activation_policy).

• join_and_column_policy: Vérifie si la colonne est autorisée par les politiques de jointure, d’activation ou de colonne. Utilisé pour offrir plus de flexibilité dans la clean room, pour permettre aux collaborateurs de mettre à jour les politiques de jointure et de colonne sans modifier le modèle.

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

L’évaluation des expressions JinjaSQL s’effectue de gauche à droite :

• {{ my_col | column_policy }} Correct

• {{ my_col | sqlsafe | column_policy }} Correct

• {{ column_policy | my_col }} Incorrect

• {{ my_col | column_policy | sqlsafe }} Incorrect : column_policy sera vérifié par rapport à la valeur de my_col en tant que chaîne, ce qui est une erreur.

Appliquer les politiques des clean rooms¶

Clean rooms ne vérifie pas automatiquement les politiques de clean rooms par rapport aux colonnes utilisées dans un modèle. Si vous souhaitez appliquer une politique à une colonne, vous devez appliquer le filtre de politique <label-dcr_template_filters> approprié à cette colonne dans le modèle. Par exemple :

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

Cela permet de tester la politique de jonction par rapport à la colonne transmise à c_join_col, mais pas par rapport à p_join_col.

Notez que les noms de colonnes ne peuvent pas être ambigus lors des tests de politiques, comme pour toute autre utilisation de SQL. 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);
$$
);