Exécution de requêtes SQL de forme libre sur les tables des clean rooms¶

Politiques et soutien différentiel en matière de protection de la vie privée¶

Lorsque vous exposez des données de clean room pour des requêtes de forme libre, toutes les politiques de Snowflake Data Clean Rooms sont respectées. Les politiques Clean room (politiques de jointure, politiques de colonne) ne sont pas appliquées dans les requêtes de forme libre.

La confidentialité différentielle est et non appliquée aux données exposées à des requêtes de forme libre. Cela comprend à la fois Confidentialité différentielle dans Snowflake et Confidentialité différentielle des clean rooms.

Permettre des requêtes de forme libre¶

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);

Fournisseur

Le fournisseur prend les mesures suivantes pour mettre les ensembles de données d’une clean room à la disposition des collaborateurs de la clean room à l’aide de requêtes en forme libre :

1. Créez la clean room de la manière habituelle.

2. Enregistrez et liez les ensembles de données dans la clean room de la manière habituelle. Notez qu’actuellement vos données doivent être enregistrées à l’aide de l’API ; vous ne pouvez pas enregistrer des vues dans l’UI de clean room et les utiliser pour des requêtes de forme libre. Vous devez appliquer les politiques d’agrégation, de jointure ou autres de Snowflake avant de partager vos données en dehors de la clean room.

3. Appelez provider.enable_workflows_for_consumers pour permettre à des utilisateurs spécifiques d’accéder librement aux tables que vous spécifierez à l’étape suivante.

4. Appelez provider.enable_datasets_for_workflow pour permettre aux utilisateurs de l’étape précédente d’accéder librement aux ensembles de données spécifiés ici.

5. Ajoutez vos collaborateurs de manière standard en appelant provider.add_consumers.

6. Publiez votre clean room.

7. Si vous souhaitez révoquer l’autorisation d’interroger ces tables, vous pouvez le faire au niveau de l’utilisateur en appelant provider.disable_consumer_run_analysis ou provider.remove_consumers, au niveau de la vue en appelant library.unregister_objects ou library.unregister_db, ou en supprimant la clean room.

Si une clean room existe déjà et que les données sont enregistrées, vous pouvez simplement appeler provider.enable_workflows_for_consumers et provider.enable_datasets_for_workflow pour exposer les tables spécifiées aux utilisateurs spécifiés.

Le code suivant crée trois tables d’échantillons, crée une nouvelle clean room, établit des liens avec les tables et accorde aux collaborateurs de la clean room l’accès à ces tables sous forme de requêtes libres via la clean room.

----------------- Create sample data -----------------
USE ROLE MYROLE;
CREATE DATABASE freeform_db;

-- Create a table with an aggregation constraint.
CREATE OR REPLACE TABLE freeform_db.public.agg_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE AGGREGATION POLICY freeform_db.public.agg_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

ALTER TABLE freeform_db.public.agg_constrained_table
  SET AGGREGATION POLICY freeform_db.public.agg_policy;

-- Create a table with a projection constraint.
CREATE OR REPLACE TABLE freeform_db.public.proj_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE PROJECTION POLICY freeform_db.public.proj_policy AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => false);

ALTER TABLE freeform_db.public.proj_constrained_table MODIFY COLUMN hashed_email
  SET PROJECTION POLICY freeform_db.public.proj_policy;

-- Create a table with a masking policy.
CREATE OR REPLACE TABLE freeform_db.public.masked_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE MASKING POLICY freeform_db.public.masking_policy
  AS (val string) RETURNS STRING ->
  CASE
    WHEN current_account() IN ('DCR_PROVIDER_PP6') THEN VAL
    ELSE '*********'
  END;

ALTER TABLE freeform_db.public.masked_table MODIFY COLUMN hashed_email
  SET MASKING POLICY freeform_db.public.masking_policy;

----------------- Create and publish a clean room that supports -----------------
----------------- free-form queries against this data.          -----------------

-- Create the clean room. Nothing new here.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in the policy-protected tables from above. Nothing new here.
USE ROLE MYROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.register_db('freeform_db');
USE ROLE SAMOOHA_APP_ROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.link_datasets($cleanroom_name,
  ['freeform_db.public.agg_constrained_table',
  'freeform_db.public.proj_constrained_table',
  'freeform_db.public.masked_table']);

-- Grant the following consumer access to the tables specified next.
SET flow_name = freeform_sql;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_workflows_for_consumers($cleanroom_name,
  [$flow_name],
  ['<CONSUMER_LOCATOR>']);

-- Grant the consumer specified above access to the specified tables.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_datasets_for_workflow($cleanroom_name,
  $flow_name,
  ['freeform_db.public.agg_constrained_table',
   'freeform_db.public.proj_constrained_table',
    'freeform_db.public.masked_table']);

-- Add collaborators and publish, in the standard way.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.add_consumers(
  $cleanroom_name, '<CONSUMER_LOCATOR>', '<ORG_NAME>.<CONSUMER_LOCATOR>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', '0');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);

Consommateur

Une fois que le fournisseur a publié une clean room avec des flux de travail de forme libre sur SQL, les consommateurs ayant accès à cette clean room peuvent exécuter des requêtes sur les vues exposées.

1. Installez la clean room de la manière habituelle. Il n’est pas nécessaire de créer des liens dans les données, le consommateur pouvant accéder directement à ses tables.

2. Appelez consumer.get_provider_freeform_sql_views pour dresser la liste des vues SQL de forme libre disponibles pour le compte et le rôle actuels.

3. Exécutez des requêtes standard sur le site SQL à partir des données. Les politiques du producteur seront appliquées aux données.

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- List free form views available in the clean room.
CALL samooha_by_snowflake_local_db.consumer.GET_PROVIDER_FREEFORM_SQL_VIEWS($cleanroom_name);

-- Run queries on the views
SELECT * FROM <PROJECTION_POLICY_VIEW_NAME>;
SELECT * FROM <MASKING_POLICY_VIEW_NAME>;
SELECT COUNT(hashed_email), age_band
  FROM <AGGREGATION_POLICY_VIEW_NAME> group by age_band;

Fournisseur : Créez une clean room et définissez des politiques¶

1. Créez une clean room ou modifiez une clean room existante, et spécifiez des tables ou des vues pour votre table.

2. Les politiques de jonction spécifiées au cours du processus de création de la clean room sont ignorées lors de l’utilisation du modèle de requête SQL, mais respectées pour tous les autres modèles.

3. Dans Configure Analysis & Query sélectionnez Horizontal » SQL Query.

4. Dans la section des paramètres de SQL Query, définissez les propriétés suivantes :

   1. Sous Tables, sélectionnez les tables qui devraient être disponibles pour les collaborateurs de la clean room sous forme de requêtes libres. Par défaut, toutes les colonnes des tables sélectionnées peuvent être projetées et il n’est pas nécessaire d’appliquer des politiques d’agrégation. Pour contrôler les colonnes qui peuvent être projetées et celles qui doivent être agrégées, vous devez établir des politiques de colonnes dans la section suivante.

   2. Dans la section Column Policies, définissez les valeurs suivantes pour contrôler si ou comment vos colonnes peuvent être utilisées dans une requête :

      1. Aggregation policy columns: indiquez quelles colonnes doivent être agrégées pour apparaître dans les résultats de la requête. Si vous appliquez une politique d’agrégation à une colonne et qu’une colonne est utilisée dans une requête, les résultats doivent être agrégés. Toutes les colonnes annoncées ici seront ajoutées à la section Privacy settings.

      2. Projection policy columns: les colonnes dotées d’une politique de projection ne peuvent pas être projetées (c’est-à-dire incluses dans une SELECT instruction). Toutefois, les consommateurs peuvent filtrer ou effectuer une jointure sur une colonne à l’aide d’une politique de projection.

      3. Fully permitted columns: le consommateur peut SELECT, filtrer ou effectuer une jointure sur ces colonnes sans restriction (agrégation ou autre).

   3. La section Privacy settings dresse la liste de toutes les colonnes auxquelles une politique d’agrégation a été appliquée. La valeur Threshold indique combien d’entités doivent exister pour que cette valeur apparaisse dans les résultats. Par exemple, si vous avez fixé un seuil de 5 pour la colonne FIRST_NAME et que le nom Erasmus n’apparaît que 4 fois dans la table, toutes les lignes contenant Erasmus seront filtrées avant tout traitement (ainsi, par exemple, un COUNT(*) sur une telle table omettra les 4 lignes dont la taille du groupe est inférieure au seuil).

Consommateur : effectuez une requête de forme libre¶

1. Rejoignez ou modifiez la clean room dans l’UI de clean room.

2. Dans la section Configure Analysis & Query, choisissez les tables que vous utiliserez pour les requêtes en forme libre.

3. Sélectionnez Finish pour enregistrer vos modifications.

4. Pour exécuter une requête, sélectionnez Run dans la clean room avec le modèle de requête SQL et sélectionnez le modèle de requête SQL.