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 salle blanche en tant que fournisseur :

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. Définissez les autorisations de jointure sur vos données afin de spécifier les colonnes pouvant être jointes dans les 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¶

Connectez-vous à Snowsight en tant qu’utilisateur disposant du rôle SAMOOHA_APP_ROLE. Si vous ne disposez pas de ce rôle, veuillez demander à votre administrateur de compte de vous l’accorder.

Créez une nouvelle feuille de calcul SQL dans Snowsight pour stocker votre code de salle blanche. Nommez la feuille de calcul « Tutoriel API - Fournisseur ».

Créez un tableau basé sur 1 000 lignes de données de test échantillon à partir du tableau SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS. Vous utiliserez ce tableau comme données fournisseur dans la salle blanche.

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 a temporary table, in case you forget to delete it later.
CREATE TEMPORARY 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¶

L’extrait de code suivant crée une salle blanche accessible uniquement au sein de l’organisation (elle est donc marquée comme INTERNAL). Pour partager une salle blanche en dehors d’une organisation, des étapes supplémentaires sont nécessaires, qui ne seront pas abordées dans ce tutoriel. Lorsque vous partagez une salle blanche avec vous-même, elle doit bien sûr être INTERNAL.

Vous devez utiliser SAMOOHA_APP_ROLE pour la plupart des procédures de salle blanche.

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¶

Ensuite, importez vos données de test dans la salle blanche. L’importation de données dans une salle blanche se fait en deux étapes :

1. Enregistrez les données.

2. Importez (lien) les données dans la salle blanche.

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

-- Use 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¶

Les fournisseurs et les consommateurs peuvent spécifier des politiques de jointure sur leurs propres données. Une politique de jointure de salle blanche spécifie les colonnes d’un tableau qui peuvent être jointes par les requêtes de vos collaborateurs dans cette salle blanche. Cela vous offre un niveau de contrôle supplémentaire sur la manière dont les autres peuvent utiliser vos données dans la salle blanche. Vos propres politiques ne s’appliquent pas à vos propres requêtes, c’est-à-dire que les politiques de jointure que vous définissez sur vos propres données sont ignorées lorsque vous exécutez une requête ; vos politiques ne s’appliquent qu’aux requêtes exécutées par d’autres utilisateurs.

Les politiques de jointure de la salle blanche sont définies sur la table et s’appliquent à toutes les salles blanches où la table est utilisée. Les colonnes qui ne sont pas répertoriées ici ne peuvent pas être jointes à l’aide des instructions INNER JOIN ou OUTER JOIN dans la salle blanche. Si vous ne spécifiez pas de politique de jointure, toutes les colonnes peuvent être jointes.

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.

Spécifiez les colonnes pouvant être jointes pour une table en utilisant le format database_name.schema_name.table_or_view_name:column_name pour chaque colonne. L’exemple suivant permet de joindre trois colonnes de données du fournisseur :

-- 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 salle blanche peut limiter les colonnes que les autres parties peuvent projeter en définissant une column_policy. Une politique de colonne dans une salle blanche répertorie toutes les colonnes de vos données qui peuvent être projetées ; aucune autre colonne ne peut être projetée. Si vous ne spécifiez pas de politique de colonne pour vos données, toutes vos données peuvent être projetées.

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

Veuillez noter 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.

Voici comment autoriser la projection de trois colonnes de vos données dans le modèle que vous venez de créer. La syntaxe de la colonne est 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:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

Fournisseur : Ajoutez une directive de version¶

Chaque salle blanche possède un numéro de version, composé des valeurs majeure, mineure et corrective. Vous devez spécifier quelle version de la salle blanche est fournie aux consommateurs : 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 salle blanche chaque fois que vous téléchargez du code dans la salle blanche. Si vous souhaitez que les utilisateurs obtiennent la dernière version, vous devrez définir une nouvelle directive de version par défaut avec le numéro de version le plus récent. Vous ne téléchargerez pas de code, vous n’aurez donc pas besoin de rappeler cette directive pour ce tutoriel.

Fournisseur : Désignez les consommateurs¶

Vous allez maintenant spécifier qui a accès à votre salle blanche en tant que consommateur. Pour ce tutoriel, vous allez vous ajouter en tant que consommateur. Cela permet de marquer la salle blanche comme une salle blanche de test interne, utilisée uniquement à des fins de test, ce qui :ref:` limite certaines de ses fonctionnalités <label-dcr_self_share_for_developers>`, mais elle prendra en charge toutes les fonctionnalités nécessaires pour ce tutoriel.

La procédure nécessite deux arguments pour identifier chaque consommateur :

• Le localisateur de compte du consommateur. Obtenez votre localisateur de compte comme suit :

  SELECT CURRENT_ACCOUNT();
  

  Copy

• Le compte de partage de données consommateur ID du consommateur, au format org_name.account_name. Obtenez l’ID de votre compte de partage de données consommateur au format approprié comme suit :

  SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
  

  Copy

Partagez maintenant la salle blanche avec vous-même en tant que consommateur, en ajoutant votre localisateur de compte et l’ID de votre compte de partage de données consommateur aux emplacements indiqués :

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  '<CONSUMER_LOCATOR>',
  '<CONSUMER_DATA_SHARING_ACCOUNT_ID>');

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

Fournisseur : publiez la clean room¶

Enfin, publiez la salle blanche. Cela rend la salle blanche disponible pour le consommateur que vous avez ajouté ci-dessus. La procédure prend 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 salle blanche répertoriée dans l”UI des salles blanches, dans l’onglet Created de votre compte fournisseur et dans l’onglet Invited du compte consommateur, avec la mention « Powered by Dev Edition ». Le compte consommateur recevra un e-mail d’invitation. (N’installez pas la salle blanche à partir de l’onglet Invited ; vous l’installerez dans le code, à une étape ultérieure.)

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

Retirez maintenant votre casquette de fournisseur et enfilez celle de consommateur.

Consommateur : Installez (rejoignez) la clean room¶

Vous utiliserez le même compte pour les rôles de fournisseur et de consommateur dans ce tutoriel. Ajoutez donc une nouvelle feuille de calcul SQL nommée « Tutoriel API - Consommateur » dans Snowsight dans le même compte.

Configurez l’environnement de session, comme vous l’avez fait pour le fournisseur :

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

Ensuite, installez la salle blanche que vous avez publiée et partagée en tant que fournisseur. Pour installer une salle blanche, vous devez spécifier à la fois le nom de la salle blanche et le localisateur de compte du fournisseur qui a partagé la salle blanche avec vous. La spécification du nom de la salle blanche et du localisateur de compte permet de distinguer les salles blanches portant des noms identiques. Exécutez SELECT CURRENT_ACCOUNT(); pour obtenir votre localisateur de fournisseur.

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

L’installation peut prendre quelques minutes.

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

Créez et ajoutez maintenant un ensemble de données à cette salle blanche. Créez un tableau similaire à l’ensemble de données du fournisseur, mais utilisez les 1 000 lignes inférieures des données d’échantillon.

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

CREATE TEMPORARY 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 source, vous devez les enregistrer et les lier à la salle blanche, 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 : Pas besoin de définir des politiques¶

Vous pourriez définir des politiques sur vos données, comme l’a fait le fournisseur, mais ce modèle est approuvé pour être exécuté uniquement par le consommateur, alors pourquoi se donner la peine de restreindre vos propres requêtes. Dans tous les cas, vos propres politiques de lignes et de colonnes sont ignorées lorsque vous exécutez une requête.

Toutefois, si vous deviez approuver une demande de fournisseur pour exécuter ce modèle, vous devriez d’abord définir des politiques de jointure et de colonne sur vos données afin de contrôler ce que le fournisseur pourrait en faire.

Consommateur : exécutez l’analyse¶

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 à utiliser dans le modèle.

• Les noms des tables du fournisseur à utiliser dans le modèle.

• Toute autre variable nom/valeur à transmettre.

-- List templates in the clean room.
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

-- See the template code.
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition(
  $cleanroom_name,
  $template_name);

-- See what arguments can be passed in to the template:
CALL samooha_by_snowflake_local_db.consumer.get_arguments_from_template(
  $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'], -- Consumer table list.
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'], -- Provider table list.
  OBJECT_CONSTRUCT(                    -- Additional template arguments as name-value pairs.
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  )
);

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;