Tutoriel : Démarrez avec Snowflake Data Clean Rooms en code¶

Introduction¶

Ce tutoriel s’adresse aux développeurs qui créeront ou utiliseront des Snowflake Data Clean Rooms dans le code. Ce tutoriel utilise le code SQL, mais vous pouvez adapter les informations présentées ici pour créer et utiliser des clean room dans n’importe quelle langue de codage prise en charge par Snowflake.

Fournisseur : Aperçu¶

Voici un résumé des étapes à suivre pour créer une clean room :

1. Créez des données de test à partager dans votre clean room.

2. Créez votre clean room.

3. Apportez les données que vous avez créées dans la clean room.

4. Paramétrez les autorisations de jointure sur vos données afin de spécifier les colonnes qui peuvent être jointes lors des requêtes des consommateurs.

5. Créez un modèle pour votre clean room. Un modèle de clean room est écrit en JinjaSQL et s’évalue à une requête SQL au moment de l’exécution. La plupart des modèles comprennent des variables qui permettent aux collaborateurs de spécifier les noms des tables et des colonnes, les conditions de la clause WHERE, etc. au moment de l’exécution. Un collaborateur de clean room choisit et exécute un modèle dans une clean room.

6. Indiquez la version par défaut de la clean room.

7. Ajoutez les consommateurs qui peuvent accéder à votre clean room. Dans ce tutoriel, les consommateurs doivent être des utilisateurs de Snowflake avec des comptes approuvés par votre administrateur de clean room.

8. Publiez la clean room pour la mettre à la disposition de vos consommateurs invités.

Fournisseur : Créer des données de test¶

Commencez à travailler dans votre Notebooks du compte fournisseur.

Vous allez d’abord créer une table basée sur 1 000 lignes d’échantillons de données de test provenant de la table SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS. Vous utiliserez cette table comme données du fournisseur dans la clean room.

Utilisez un rôle qui vous permet de créer une base de données. L’exemple utilisé ici est ACCOUNTADMIN, mais vous pouvez utiliser n’importe quel rôle permettant de créer une table.

USE WAREHOUSE app_wh;
USE ROLE ACCOUNTADMIN;
-- Using ACCOUNTADMIN role because you need a role that allows you to create a database.
-- Feel free to use any other role that can create a database.

-- Generate a provider dataset based on the first 1,000 rows of sample data.
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

CREATE TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table AS
  SELECT TOP 1000 * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS ORDER BY HASHED_EMAIL ASC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;

Fournisseur : créez la clean room¶

Une clean room commence sans données, sans utilisateurs et uniquement avec des propriétés par défaut.

L’extrait suivant crée une clean room qui n’est accessible qu’au sein de l’organisation (elle est donc marquée comme INTERNAL). Partagez une clean room en dehors d’une organisation nécessite des exigences supplémentaires qui ne seront pas abordées dans ce tutoriel.

Vous devez utiliser le site SAMOOHA_APP_ROLE pour presque toutes les actions de clean room.

USE ROLE samooha_app_role;
SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

Fournisseur : Faites entrer les données dans la clean room¶

Amenez vos données de test dans la clean room.

L’introduction des données dans la clean room se fait en deux étapes :

1. Enregistrement des données

2. Importation des données dans la clean room

USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.provider.register_db('cleanroom_tut_db');

-- Back to samooha_app_role until you need to clean things up at the end.
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table']);

CALL samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

Fournisseur : Définissez les règles de jointure sur les données¶

Tant les fournisseurs que les consommateurs peuvent spécifier des règles de jointure sur leurs propres données. Une politique de jointure en clean room spécifie quelles colonnes d’une table peuvent être jointes par des requêtes dans cette clean room. Cela permet un niveau de contrôle supplémentaire sur la manière dont les autres peuvent utiliser vos données dans la clean room.

Notez que les politiques de jointure en clean room ne sont pas les mêmes que les politiques de jointure en Snowflake; les politiques en clean room spécifient les colonnes sur lesquelles peut être joint ; les politiques de jointure en Snowflake spécifient les colonnes sur lesquelles ne peut pas être joint.

Fournir une liste des colonnes juxtaposables à la clean room. Toutes les colonnes qui ne sont pas listées ici ne peuvent pas être jointes à l’aide des instructions INNER JOIN ou OUTER JOIN dans la clean room. Spécifiez les colonnes joignables d’une table en utilisant le format database_name.schema_name.table_or_view_name:column_name pour chaque colonne.

-- Limit joinable columns in this table to age_band, region_code, and device_type
CALL samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:age_band',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:region_code',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:device_type']);

CALL samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

Fournisseur : Ajoutez votre modèle¶

Un modèle de clean room est un modèle JinjaSQL qui évalue une requête SELECT. Cette requête a accès à tous les ensembles de données liés à la clean room, sous réserve des politiques de jointure et de colonne.

Ce tutoriel ne couvre pas les détails de la conception d’un modèle JinjaSQL, mais voici la requête SQL que vous essayez de mettre en œuvre :

SELECT COUNT(*), group_by_col FROM Consumer_Table AS C
  INNER JOIN Provider_Table AS P
  ON C.join_col = P.join_col
  GROUP BY group_col;

La requête joint simplement une table fournisseur et une table consommateur sur une colonne de jointure spécifiée, regroupe par une colonne de regroupement spécifiée, et projette la valeur de groupe et le nombre de chaque groupe. Il s’agit de la requête qui sera exécutée dans la clean room lorsque l’utilisateur exécutera le modèle.

Voici le modèle JinjaSQL pour la même requête, avec des variables ajoutées où le consommateur peut spécifier des tables ou des colonnes. Une fois que le consommateur a spécifié les variables, il évalue une requête SQL similaire à la précédente, mais avec les noms de table et de colonne fournis par le consommateur.

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

Quelques remarques sur le modèle :

• Les contenus entourés de {{brackets}} sont des variables nommées transmises par le consommateur lorsqu’il exécute le modèle. Les variables suivantes sont transmises par le consommateur : group_by_col, consumer_join_col, provider_join_col

• Les tableaux my_table et source_table sont des variables globales créées par le système, alimentées par les noms des tables des consommateurs et des fournisseurs transmis par l’appelant. Ces tables doivent être reliées à la clean room par le consommateur et le fournisseur.

• Toutes les tables des fournisseurs doivent être appelées p dans la requête. Toutes les tables de consommateurs doivent avoir pour alias c. Si vous utilisez plusieurs tables, vous devez les aliaser avec un suffixe basé sur 1, par exemple : p p1, p2, p3 et ainsi de suite pour les tables de fournisseurs, et c, c1, c2, c3 et ainsi de suite pour les alias de tables de consommateurs. (p et p0 sont équivalents)

• Snowflake Data Clean Rooms supporte certains filtres personnalisés JinjaSQL ** qui agissent sur les variables. Les filtres column_policy et row_policy vérifient que les colonnes auxquelles ils s’appliquent sont conformes aux politiques relatives aux colonnes et aux lignes dans cette clean room, faute de quoi la requête d’exécution du modèle échouera. Ainsi, {{ consumer_join_col | join_policy }} vérifie que la valeur transmise à consumer_join_col est conforme aux politiques de jonction établies par le fournisseur et le consommateur dans cette clean room.

• Les variables utilisées comme identificateurs doivent être traitées par la fonction IDENTIFIER avant de pouvoir être utilisées dans SQL.

Ajoutez le modèle à la clean room :

-- Add the template
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $template_name,
    $$
    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}});
    $$);

CALL samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

Fournisseur : définissez les règles par colonne¶

Chaque partie dans la clean room peut limiter les colonnes que les autres parties peuvent afficher dans les résultats en paramétrant une column_policy. Une politique des colonnes dans une clean room annonce toutes les colonnes qui peuvent être projetées ; aucune autre colonne ne peut être projetée. Le fournisseur définit les politiques de colonne pour ses tables ; le consommateur définit les politiques de colonne pour ses tables.

Une politique de colonne est liée à une table et à un modèle spécifiques dans une clean room. Vous pouvez autoriser l’affichage de différentes colonnes dans différents modèles. La même colonne ne peut pas figurer à la fois dans une politique de jointure et dans une politique de colonne.

Notez que les politiques de colonne et de jointure ne sont appliquées que si le modèle utilise les filtres column_policy et row_policy dans le modèle. De même, si vous ne spécifiez pas de politique de colonne, cela signifie que toutes vos colonnes peuvent être projetées ; de même, si vous ne spécifiez pas de politique de jointure, cela signifie que toutes vos colonnes peuvent être jointes.

Voici comment permettre la projection de trois colonnes de vos données dans le modèle que nous venons de créer. La syntaxe des colonnes est la suivante template_name:table_name:column_name

-- Set column policies. Column policies are tied to a specific template and table, so we
-- needed to add the template first.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name,
  [$template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:STATUS',
   $template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:AGE_BAND',
   $template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

Fournisseur : Ajoutez une directive de version¶

Chaque clean room possède un numéro de version, composé de valeurs majeures, mineures et de correctifs. Vous devez spécifier quelle version de la clean room est servie à un consommateur : c’est ce qu’on appelle la directive de version par défaut.

Il s’agit de la première version, le numéro de version est donc 1.0.0.

CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');

Snowflake crée une nouvelle version de la clean room chaque fois que vous téléchargez du code dans la clean room. Si vous voulez que les utilisateurs obtiennent la dernière version, vous devrez appeler à nouveau cette procédure avec la nouvelle version. Vous ne téléchargerez pas de code, vous n’aurez donc pas besoin d’appeler cette fonction à nouveau.

Fournisseur : Ajoutez des consommateurs¶

Indiquez maintenant quels comptes peuvent utiliser votre clean room en tant que consommateurs. Quelques exigences lors du partage d’une clean room :

• Les utilisateurs doivent avoir un compte Snowflake avec l’environnement de clean room installé. Il est possible de partager une clean room avec un compte non Snowflake, mais cela n’est pas abordé ici.

• Le compte du consommateur doit se trouver dans la même région Cloud que le compte du fournisseur. Vous pouvez partager entre régions, mais cela nécessite une configuration supplémentaire qui n’est pas abordée ici.

• Le compte du consommateur doit être ajouté à la page Collaborators du compte du fournisseur. Seuls les comptes ajoutés à la liste des collaborateurs d’un compte peuvent recevoir une invitation à rejoindre une clean room créée dans ce compte.

• Vous pouvez obtenir l’emplacement du compte en exécutant la procédure suivante dans votre compte consommateur : SELECT CURRENT_ACCOUNT();

• Le nom du compte se présente sous le format org_name.account_name. Vous pouvez obtenir ces valeurs en exécutant les procédures suivantes dans votre compte consommateur :

  • SELECT CURRENT_ORGANIZATION_NAME();

  • SELECT CURRENT_ACCOUNT_NAME();

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  <CONSUMER_LOCATOR>,
  <ORG_NAME>.<ACCOUNT_NAME>);

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

Fournisseur : publiez la clean room¶

Enfin, vous pouvez publier la clean room. La clean room est ainsi mise à la disposition des consommateurs que vous avez ajoutés ci-dessus. La procédure dure une minute ou plus.

-- Publish the clean room.
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

Une fois la procédure terminée, vous devriez voir la clean room annoncée dans l’appli web des clean rooms de votre compte fournisseur dans l’onglet Created, et dans l’onglet Invited de l’appli web de votre compte consommateur, avec l’étiquette Powered by Dev Edition. Le compte du consommateur recevra un courriel d’invitation et la clean room devrait apparaître dans l’onglet Invited de l’application web du compte du consommateur.

Félicitations ! Vous avez publié votre première clean room !

Passez maintenant au compte consommateur pour utiliser la clean room.

Consommateur : Installez (rejoignez) la clean room¶

Passez au compte consommateur dans Snowsight. Vous pouvez ouvrir un deuxième onglet dans votre navigateur ou utiliser le commutateur de compte.

Une fois que vous êtes connecté avec votre compte consommateur, configurez votre environnement dans votre notebook consommateur :

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

Installez la clean room que vous venez de publier et de partager.

Pour installer une clean room, vous devez indiquer à la fois le nom de la clean room et l’emplacement du compte du fournisseur qui a partagé la clean room avec vous. Le fait de préciser le nom de la clean room et l’emplacement du compte permet de désambiguïser les invitations à plusieurs clean rooms. Vous pouvez lancer SELECT CURRENT_ACCOUNT(); dans le compte du fournisseur pour obtenir l’emplacement du fournisseur.

L’installation peut prendre quelques minutes.

SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, <PROVIDER_LOCATOR>);

Consommateur : Créez et liez vos données¶

Créez et ajoutez maintenant un ensemble de données dans cette clean room, similaire à l’ensemble de données du fournisseur, mais en utilisant les 1 000 dernières lignes des données de l’échantillon.

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

CREATE TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table AS
  SELECT TOP 1000 * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS ORDER BY HASHED_EMAIL DESC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;

Après avoir créé vos données sources, vous devez les enregistrer et les lier à la clean room, tout comme vous l’avez fait en tant que fournisseur.

-- You need to use a role that has ownership of the object to be registered, probably not samooha_app_role.
USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.library.register_objects(['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

-- Drop back down to samooha_app_role for the other actions.
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

CALL samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

Consommateur : Définissez des politiques d’adhésion à vos données¶

Vous pouvez désormais établir des politiques d’adhésion à vos données, tout comme vous le faisiez dans le compte fournisseur. Définir des règles de jointure côté consommateur est redondant dans cet exemple, car seuls les consommateurs peuvent exécuter un modèle dans cette clean room. Et comme vous exécutez vous-même le modèle, vous savez lesquelles de vos colonnes doivent être joignables. Toutefois, dans le cadre d’une utilisation réelle, il est judicieux de définir des politiques de jonction sur vos données au cas où la clean room autoriserait le fournisseur à exécuter un modèle.

-- Allow same three columns in your data to be joined.
CALL samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:age_band',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:region_code',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:device_type']);

Consommateur : Préparez votre requête¶

Pour exécuter une requête, vous avez besoin des informations suivantes :

• Le nom du modèle que vous souhaitez exécuter.

• Les noms de vos tables à mettre à la disposition du modèle.

• Les noms des tables du fournisseur à mettre à la disposition du modèle.

• Toute autre variable nom/valeur à transmettre. Dans notre modèle, nous devons transmettre les noms des colonnes de jointure des tables du fournisseur et du consommateur.

-- List templates in the clean room, then examine the template details
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, $template_name);

-- Table name to use is in the LINKED_TABLE column in the results.
CALL samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

-- See which provider columns can be joined on
CALL samooha_by_snowflake_local_db.consumer.view_provider_join_policy($cleanroom_name);

-- See which provider columns can be projected
CALL samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);

CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  $template_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table'],
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'],
  OBJECT_CONSTRUCT(
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  ),
  FALSE
);

Les deux comptes : nettoyage¶

Nettoyons maintenant toutes les ressources que vous avez créées.

Notez que vous devrez utiliser le même rôle pour supprimer vos tables sources que celui que vous avez utilisé pour les créer.

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);

USE role ACCOUNTADMIN;
DROP TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;
DROP DATABASE cleanroom_tut_db;

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);

USE ROLE ACCOUNTADMIN;
DROP VIEW cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;
DROP DATABASE cleanroom_tut_db;