Mise en place de l’environnement¶

Exécutez les commandes suivantes pour configurer l’environnement Snowflake avant d’utiliser les APIs du développeur pour travailler avec une Snowflake Data Clean Room. Si vous n’avez pas le rôle SAMOOHA_APP_ROLE, contactez votre administrateur de compte.

use role samooha_app_role;
use warehouse app_wh;

Créer la salle blanche¶

Créez un nom pour la salle blanche. Saisissez un nouveau nom de salle blanche pour éviter toute collision avec des noms de salle blanche existants. Notez que les noms des salles blanches ne peuvent être que alphanumériques. Les noms des salles blanches ne peuvent pas contenir de caractères spéciaux autres que des espaces et des traits de soulignement.

set cleanroom_name = 'Snowpark Demo clean room';

Vous pouvez créer une nouvelle salle blanche avec le nom de la salle blanche défini ci-dessus. Si le nom de la salle blanche défini ci-dessus existe déjà en tant que salle blanche, ce processus échouera.

Cette procédure dure environ 45 secondes.

Le deuxième argument de provider.cleanroom_init est la distribution de la salle blanche. Il peut s’agir de INTERNAL ou de EXTERNAL. À des fins de test, si vous partagez la salle blanche avec un compte de la même organisation, vous pouvez utiliser INTERNAL pour contourner l’analyse de sécurité automatisée qui doit avoir lieu avant qu’un paquet d’applications ne soit publié aux collaborateurs. Cependant, si vous partagez cette salle blanche avec un compte dans une autre organisation, vous devez utiliser une distribution de salle blanche EXTERNAL.

call samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

Pour voir le statut de l’analyse de sécurité, utilisez :

call samooha_by_snowflake_local_db.provider.view_cleanroom_scan_status($cleanroom_name);

Une fois que vous avez créé votre salle blanche, vous devez définir sa directive de version avant de pouvoir la partager avec un collaborateur. Toutefois, si votre distribution a été définie sur EXTERNAL, vous devez d’abord attendre la fin de l’analyse de sécurité avant de définir la directive de version. Vous pouvez continuer à exécuter le reste des étapes et revenir ici avant l’étape provider.create_cleanroom_listing pendant que l’analyse s’exécute.

Pour définir la directive de version, appelez :

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

Partage interrégional¶

Pour partager une salle blanche avec un client de Snowflake dont le compte se trouve dans une région différente de votre compte, vous devez activer [l’exécution automatique inter-Cloud] (https://other-docs.snowflake.com/fr/collaboration/provider-listings-auto-fulfillment). Pour des informations sur les coûts supplémentaires liés à la collaboration avec des consommateurs d’autres régions, voir Coûts de l’exécution automatique inter-Cloud.

Lorsque vous utilisez les APIs du développeur, l’activation du partage interrégional se fait en deux étapes :

1. Un administrateur Snowflake ayant le rôle ACCOUNTADMIN active l’exécution automatique inter-Cloud pour votre compte Snowflake. Pour plus d’informations, voir Collaborer avec des comptes situés dans des régions différentes.

2. Vous exécutez la commande provider.enable_laf_for_cleanroom pour activer l’exécution automatique inter-Cloud pour la salle blanche. Par exemple :

   call samooha_by_snowflake_local_db.provider.enable_laf_for_cleanroom($cleanroom_name);
   

   Copy

Après avoir activé l’exécution automatique inter-Cloud pour la salle blanche, vous pouvez ajouter des consommateurs à votre annonce comme d’habitude à l’aide de la commande provider.create_cleanroom_listing. L’annonce est automatiquement répliquée vers des cloud et des régions distantes, selon les besoins.

Liez l’ensemble de données et définissez la politique de jointure pour l’ensemble de données¶

Liez des tables Snowflake dans la salle blanche. Ces ensembles de données seront mis à votre disposition sur votre compte grâce au dernier correctif. Parcourez la liste des tables de votre compte Snowflake et saisissez les noms de table entièrement qualifiés (Database.Schema.Table) sous forme de tableau. La procédure rend automatiquement la table accessible à la salle blanche en créant une vue sécurisée de la table depuis l’intérieur de la salle blanche, évitant ainsi tout besoin de faire une copie de votre table.

call samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name, ['<IMPRESSIONS_TABLE>']);

Si vous souhaitez lier une vue à la salle blanche qui a des dépendances en aval, utilisez plutôt provider.link_datasets_advanced :

call samooha_by_snowflake_local_db.provider.link_datasets_advanced($cleanroom_name, ['<VIEW_NAME>'], ['<SOURCE_DB_NAMES>']);

use role accountadmin;
call samooha_by_snowflake_local_db.provider.register_db('<DATABASE_NAME>');
use role samooha_app_role;

Vous pouvez voir les ensembles de données liés à la salle blanche en suivant la procédure suivante :

call samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

Afin de déterminer les colonnes à utiliser pour la politique de jointure, vous pouvez consulter votre ensemble de données pour déterminer les colonnes PII. Pour voir les 10 premières lignes, utilisez la requête suivante :

select * from <IMPRESSIONS_TABLE> limit 10;

Chargez de manière confidentielle la procédure Snowpark dans la salle blanche¶

Cette section vous montre comment charger la procédure Snowpark dans la salle blanche. La procédure se déroule selon les étapes suivantes :

1. Prétraitement des données d’impression : le SQL dynamique est créé pour relier les données d’impression du fournisseur aux données des utilisateurs du consommateur, si la table du consommateur est fournie, et pour calculer le nombre distinct d’impressions et la portée par date, et les stocker dans une table intermédiaire à l’intérieur de la salle blanche. Si la table des consommateurs n’est pas fournie, il utilise l’intégralité de la table des impressions du fournisseur.

2. Chargement de la table intermédiaire : la table intermédiaire est chargée dans la procédure Snowpark sous la forme d’un dataframe pandas.

3. Effectuer une régression : la régression est calculée à l’aide de la bibliothèque statsmodels et les résultats sont renvoyés sous la forme d’un dataframe pandas.

4. Écrire les résultats dans une table Snowflake : les résultats sont écrits dans une table de résultats à l’intérieur de la salle blanche et le suffixe de l’ID est renvoyé au consommateur.

   a. Comme la procédure Snowpark est exécutée dans la salle blanche, sa capacité à écrire directement sur le client du consommateur est limitée. Au lieu de cela, pour sécuriser les résultats, ils sont inscrits dans une table à l’intérieur de la salle blanche et les consommateurs ont la possibilité de les lire à partir de cette table.

5. Supprimer des tables intermédiaires : les tables intermédiaires créées lors du calcul dans la salle blanche et qui ne sont plus nécessaires sont supprimées avant la fin de la procédure Snowpark.

L’API suivante vous permet de définir vos fonctions Python directement en tant que fonctions en ligne dans la salle blanche. Vous pouvez également charger Python à partir de fichiers en zone de préparation que vous avez chargés dans la zone de préparation de la salle blanche. Voir le Guide de référence de l’API pour un exemple.

call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
    $cleanroom_name,
    'reach_impression_regression',
    ['source_table string', 'my_table string'],
    ['snowflake-snowpark-python', 'pandas', 'statsmodels', 'numpy'],
    'string',
    'main',
    $$
import traceback
import pandas as pd
import numpy as np

import statsmodels.formula.api as sm


def drop_tables(session, table_names):
    """
    Drop the tables passed in
    """
    for tbl in table_names:
        session.sql(f'drop table {tbl}').collect()

def preprocess_regression_data(session, source_table, my_table, suffix):
    """
    Preprocess the impressions and customer data into an intermediary table for regression
    """
    table_name = f'cleanroom.intermediary_{suffix}'

    my_table_statement = f'inner join {my_table} c on p.hem = c.hem' if my_table != 'NONE' else ''
    session.sql(f"""
    create or replace table {table_name} as (
        with joint_data as (
            select
                date,
                p.hem as hem,
                impression_id
            from {source_table} p
            {my_table_statement}
        )
        select
            date,
            count(distinct hem) as reach,
            count(distinct impression_id) as num_impressions
        from joint_data
        group by date
        order by date
    );
    """).collect()

    return table_name

def calculate_regression(df):
    """
    Calculate the regression data from the dataframe we put together
    """
    result = sm.ols('REACH ~ 1 + NUM_IMPRESSIONS', data=df).fit()
    retval = pd.concat([
        result.params,
        result.tvalues,
        result.pvalues
    ], keys=['params', 't-stat', 'p-value'], names=['STATISTIC', 'PARAMETER']).rename('VALUE').reset_index()
    return retval

def main(session, source_table, my_table):
    """
    First compute the regression data from an overlap between customer and provider data, and counting
    the number of impressions and reach per day. Next regress these locally and compute the regression
    statistics. Finally write it to a results table which can be queried to get the output.
    """
    suffix = f'regression_results_{abs(hash((source_table, my_table))) % 10000}'

    try:
        # Preprocess impressions and customer data into an intermediary form to use for regression
        intermediary_table_name = preprocess_regression_data(session, source_table, my_table, suffix)

        # Load the data into Python locally
        df = session.table(intermediary_table_name).to_pandas()

        # Carry out the regression and get statistics as an output
        regression_output = calculate_regression(df)

        # Write the statistics to an output table
        # The table and the schema names should be in upper case to quoted identifier related issues.
        table = f'results_{suffix}'.upper()
        retval_df = session.write_pandas(regression_output, table,  schema = 'CLEANROOM', auto_create_table = True)

        # Drop any intermediary tables
        drop_tables(session, [intermediary_table_name])

        # Tell the user the name of the table the results have been written to
        return f'Done, results have been written to the following suffix: {suffix}'
    except:
        return traceback.format_exc()
$$
);

-- See the versions available inside the clean room
show versions in application package samooha_cleanroom_Snowpark_Demo_clean_room;

-- Once the security scan is approved, update the release directive to the latest version
call samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '1');

Ajouter un modèle personnalisé à l’aide des UDFs¶

Pour ajouter un modèle d’analyse personnalisé à la salle blanche, vous avez besoin d’un espace réservé pour les noms des tables, tant du côté du fournisseur que du côté du consommateur, ainsi que des colonnes de jointure du côté du fournisseur. Dans les modèles SQL Jinja, ces espaces réservés doivent toujours être :

• source_table : un tableau de noms de tables du fournisseur

• my_table : un tableau de noms de tables du consommateur

Les noms des tables peuvent être rendus dynamiques par l’utilisation de ces variables, mais ils peuvent également être codés en dur dans le modèle si vous le souhaitez, en utilisant le nom de la vue liée à la salle blanche. Les noms des colonnes peuvent être codés en dur dans le modèle, si vous le souhaitez, ou définis dynamiquement par le biais de paramètres. S’ils sont définis par des paramètres, rappelez-vous que vous devez appeler les paramètres dimensions ou measure_column, qui doivent être des tableaux, pour qu’ils soient vérifiés par rapport à la politique de colonne. Vous les ajoutez en tant que paramètres SQL Jinja dans le modèle, qui seront transmis ultérieurement par le consommateur lors de la requête. Les politiques de jointure garantissent que le consommateur ne peut pas effectuer de jointure sur des colonnes autres que celles qui sont autorisées.

Il est également possible de vérifier la conformité de tout argument d’un modèle SQL Jinja personnalisé avec les politiques de jointure et de colonne à l’aide des filtres suivants :

• join_policy : vérifie si une valeur de chaîne ou une clause de filtre est conforme à la politique de jointure

• politique_de_colonne : vérifie si une valeur de chaîne ou une clause de filtrage est conforme à la politique de colonne

• join_and_column_policy : vérifie que les colonnes utilisées pour une jointure dans une clause de filtrage sont conformes à la politique de jointure et que les colonnes utilisées comme filtre sont conformes à la politique de colonne.

Par exemple, dans la clause {{ provider_id | sqlsafe | join_policy }}, une entrée p.HEM sera analysée pour vérifier si p.HEM figure dans la politique de jointure. Remarque : n’utilisez le filtre sqlsafe qu’avec précaution, car il permet aux collaborateurs d’introduire du code SQL pur dans le modèle.

Notez que cette fonction remplace tout modèle existant portant le même nom. Si vous souhaitez mettre à jour un modèle existant, il vous suffit de rappeler cette fonction avec le modèle mis à jour.

call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
        $cleanroom_name,
        'prod_calculate_regression',
        $$
call cleanroom.reach_impression_regression({{ source_table[0] }}, {{ my_table[0] | default('NONE') }});
$$
);

Enfin, un modèle personnalisé est ajouté pour permettre au consommateur de récupérer les résultats de son analyse à partir de l’ID du suffixe de résultat qui lui est renvoyé par le modèle calculate_regression.

call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
        $cleanroom_name,
        'prod_get_results',
        $$
select * from cleanroom.results_{{ results_suffix | sqlsafe }};
$$
);

Si vous voulez voir les modèles qui sont actuellement actifs dans la salle blanche, appelez la procédure suivante.

call samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

Partager avec un consommateur¶

Enfin, ajoutez un consommateur de données à la salle blanche en ajoutant son localisateur de compte Snowflake et ses noms de compte, comme indiqué ci-dessous. Le nom du compte Snowflake doit être de la forme suivante : <ORGANIZATION>.<ACCOUNT_NAME>.

show versions in application package samooha_cleanroom_Snowpark_Demo_clean_room;

call samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, '<CONSUMER_ACCOUNT_LOCATOR>', '<CONSUMER_ACCOUNT_NAME>');
call samooha_By_snowflake_local_db.provider.create_cleanroom_listing($cleanroom_name, '<CONSUMER_ACCOUNT_NAME>');

Plusieurs localisateurs de comptes de consommateurs peuvent être transmis à la fonction provider.add_consumers sous la forme d’une chaîne séparée par des virgules ou sous la forme d’appels distincts à provider.add_consumers.

Si vous souhaitez voir les consommateurs qui ont été ajoutés à cette salle blanche, appelez la procédure suivante.

call samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

Afficher les salles blanches qui ont été récemment créées en suivant la procédure suivante :

call samooha_by_snowflake_local_db.provider.view_cleanrooms();

Afficher plus en détail la salle blanche récemment créée via la procédure suivante.

call samooha_by_snowflake_local_db.provider.describe_cleanroom($cleanroom_name);

Toute salle blanche créée peut également être supprimée. La commande suivante supprime entièrement la salle blanche, de sorte que les consommateurs qui avaient auparavant accès à la salle blanche ne pourront plus l’utiliser. Si une salle blanche portant le même nom est souhaitée à l’avenir, elle doit être réinitialisée à l’aide du flux ci-dessus.

call samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);

Mise en place de l’environnement¶

Exécutez les commandes suivantes pour configurer l’environnement Snowflake avant d’utiliser les APIs du développeur pour travailler avec une Snowflake Data Clean Room. Si vous n’avez pas le rôle SAMOOHA_APP_ROLE, contactez votre administrateur de compte.

use role samooha_app_role;
use warehouse app_wh;

Installer la salle blanche¶

Une fois qu’un partage de salle blanche a été installé, la liste des salles blanches disponibles peut être vue à l’aide de la commande ci-dessous.

call samooha_by_snowflake_local_db.consumer.view_cleanrooms();

Attribuez un nom à la salle blanche que le fournisseur vous a partagée.

set cleanroom_name = 'Snowpark Demo clean room';

La commande suivante installe la salle blanche sur le compte du consommateur avec le fournisseur associé et la salle blanche sélectionnée.

Cette procédure dure environ 45 secondes.

call samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_ACCOUNT_LOCATOR>');

Une fois la salle blanche installée, le fournisseur doit terminer la mise en place de la salle blanche de son côté avant de pouvoir l’utiliser. La fonction ci-dessous vous permet de vérifier le statut de la salle blanche. Une fois qu’elle a été activée, vous devriez être en mesure d’exécuter la commande Run Analysis ci-dessous. Il faut généralement environ une minute pour que la salle blanche soit activée.

Assurez-vous que la fonction install_cleanroom est terminée avant d’exécuter cette fonction.

call samooha_by_snowflake_local_db.consumer.is_enabled($cleanroom_name);

Lier l’ensemble de données¶

Liez les ensembles de données dans la salle blanche pour effectuer des calculs sécurisés avec les données du fournisseur. Ces ensembles de données seront mis à votre disposition sur votre compte grâce au dernier correctif.

call samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['<USERS_TABLE>']);

use role accountadmin;
call samooha_by_snowflake_local_db.consumer.register_db('<DATABASE_NAME>');
use role samooha_app_role;

Pour effectuer l’analyse, vous devez transmettre la table des consommateurs. Si vous souhaitez voir les ensembles de données que vous avez ajoutés à la salle blanche, appelez la procédure suivante.

call samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

Effectuer l’analyse¶

Maintenant que la salle blanche est installée, vous pouvez exécuter le modèle d’analyse donné à la salle blanche par le fournisseur à l’aide d’une commande « run_analysis ». Vous pouvez voir comment chaque champ est déterminé dans les sections ci-dessous.

Le nombre d’ensembles de données transmissibles est limité par le modèle que le fournisseur a mis en œuvre. Certains modèles requièrent un nombre spécifique de tables. Le créateur du modèle peut mettre en œuvre les exigences qu’il souhaite prendre en charge.

call samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,               -- cleanroom
  'prod_calculate_regression',    -- template name

  ['<USERS_TABLE>'],    -- consumer tables

  ['<IMPRESSSIONS_TABLE>'],     -- provider tables

  object_construct()     -- Rest of the custom arguments needed for the template
);

Le résultat de cette analyse sera un ID qui peut être utilisé pour récupérer les résultats de la régression à l’aide du modèle suivant :

set result_suffix = 'regression_results_<ID>';

call samooha_by_snowflake_local_db.consumer.run_analysis(
    $cleanroom_name,        -- cleanroom
    'prod_get_results',     -- template name
    [],                     -- consumer tables
    [],                     -- provider tables
    object_construct(
        'results_suffix', $result_suffix  -- The suffix with the results
    )
);

Comment déterminer les entrées de run_analysis ?¶

Pour exécuter l’analyse, vous devez transmettre certains paramètres à la fonction run_analysis. Cette section vous montrera comment déterminer les paramètres à transmettre.

Noms de modèles

Premièrement, vous pouvez voir les modèles d’analyse pris en charge en appelant la procédure suivante.

call samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

Pour exécuter l’analyse, vous devez transmettre certains paramètres à la fonction run_analysis. Cette section vous montre comment déterminer les paramètres à transmettre.

call samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, 'prod_calculate_regression');

Celui-ci peut souvent contenir un grand nombre de paramètres Jinja SQL différents. La fonctionnalité suivante analyse le modèle Jinja SQL et extrait les arguments qui doivent être spécifiés dans run_analysis dans une liste.

call samooha_by_snowflake_local_db.consumer.get_arguments_from_template($cleanroom_name, 'prod_calculate_regression');

Noms des ensembles de données

Si vous souhaitez voir les noms des ensembles de données qui ont été ajoutés à la salle blanche par le fournisseur, appelez la procédure suivante. Notez que vous ne pouvez pas voir les données présentes dans les ensembles de données qui ont été ajoutés à la salle blanche par le fournisseur en raison des propriétés de sécurité de la salle blanche.

call samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

Vous pouvez également voir les tables que vous avez liées à la salle blanche en utilisant l’appel suivant :

call samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);