Actualisation automatique des tables externes pour Google Cloud Storage¶

Étape 1 : Création d’une intégration Cloud Storage dans Snowflake¶

Créez une intégration à l’aide de la commande CREATE STORAGE INTEGRATION. Une intégration est un objet Snowflake qui délègue la responsabilité de l’authentification pour un stockage externe dans le Cloud à une entité générée par Snowflake (c’est-à-dire un compte de service Cloud Storage). Pour accéder aux compartiments Cloud Storage, Snowflake crée un compte de service auquel des autorisations peuvent être accordées pour accéder aux compartiments dans lesquels vos fichiers de données sont stockés.

Une seule intégration de stockage peut prendre en charge plusieurs zones de préparation externes (c.-à-d. GCS). L’URL dans la définition de zone de préparation doit correspondre aux compartiments GCS (et aux chemins facultatifs) spécifiés pour le paramètre STORAGE_ALLOWED_LOCATIONS.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/') ]

Où :

• integration_name est le nom de la nouvelle intégration.

• bucket est le nom d’un compartiment Cloud Storage qui stocke vos fichiers de données (par exemple, mybucket). Les paramètres STORAGE_ALLOWED_LOCATIONS requis et STORAGE_BLOCKED_LOCATIONS facultatif limitent ou bloquent l’accès à ces compartiments, respectivement, lors de la création ou de la modification de zones de préparation faisant référence à cette intégration.

• path est un chemin facultatif qui peut être utilisé pour fournir un contrôle granulaire sur les objets du compartiment.

L’exemple suivant crée une intégration qui limite explicitement les zones de préparation externes utilisant l’intégration pour faire référence à l’un des deux compartiments et des chemins. Dans une étape ultérieure, nous allons créer une zone de préparation externe qui fait référence à l’un de ces compartiments et chemins.

Les zones de préparation externes supplémentaires qui utilisent également cette intégration peuvent faire référence aux compartiments et aux chemins autorisés :

CREATE STORAGE INTEGRATION gcs_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://mybucket1/path1/', 'gcs://mybucket2/path2/')
  STORAGE_BLOCKED_LOCATIONS = ('gcs://mybucket1/path1/sensitivedata/', 'gcs://mybucket2/path2/sensitivedata/');

Copy

Étape 2 : Récupération du compte de service Cloud Storage pour votre compte Snowflake¶

Exécutez la commande DESCRIBE INTEGRATION pour extraire l’ID du compte de service Cloud Storage créé automatiquement pour votre compte Snowflake :

DESC STORAGE INTEGRATION <integration_name>;

Où :

• integration_name est le nom de l’intégration créée à l”étape 1 : Création d’une intégration Cloud Storage dans Snowflake (ce chapitre).

Par exemple :

DESC STORAGE INTEGRATION gcs_int;

+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+
| property                    | property_type | property_value                                                              | property_default |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------|
| ENABLED                     | Boolean       | true                                                                        | false            |
| STORAGE_ALLOWED_LOCATIONS   | List          | gcs://mybucket1/path1/,gcs://mybucket2/path2/                               | []               |
| STORAGE_BLOCKED_LOCATIONS   | List          | gcs://mybucket1/path1/sensitivedata/,gcs://mybucket2/path2/sensitivedata/   | []               |
| STORAGE_GCP_SERVICE_ACCOUNT | String        | service-account-id@project1-123456.iam.gserviceaccount.com                  |                  |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+

Copy

La propriété STORAGE_GCP_SERVICE_ACCOUNT de la sortie affiche le compte de service Cloud Storage créé pour votre compte Snowflake (par exemple, service-account-id@project1-123456.iam.gserviceaccount.com). Nous fournissons un seul compte de service Cloud Storage pour l’ensemble de votre compte Snowflake. Toutes les intégrations Cloud Storage utilisent ce compte de service.

Étape 3 : Octroi d’autorisations au compte de service pour accéder à des objets de compartiment¶

Les instructions étape par étape suivantes décrivent comment configurer les permissions d’accès IAM pour Snowflake dans votre console Google Cloud Platform de sorte que vous puissiez utiliser un compartiment Cloud Storage pour charger et décharger les données :

Conditions préalables¶

Les instructions de cette rubrique supposent que les éléments suivants ont été créés et configurés :

Compte GCP:

• Sujet Pub/Sub qui reçoit des messages d’événement depuis le compartiment GCS. Pour plus d’informations, voir Création du sujet Pub/Sub (dans cette rubrique).

• Abonnement qui reçoit les messages d’événement du sujet Pub/Sub. Pour plus d’informations, voir Création de l’abonnement Pub/Sub (dans cette rubrique).

Pour obtenir des instructions, voir Documentation Pub/Sub.

Snowflake:

• Table cible dans la base de données Snowflake où vos données seront chargées.

$ gsutil notification create -t <topic> -f json gs://<bucket-name> -e OBJECT_FINALIZE -e OBJECT_DELETE

Étape 1 : créer une intégration de notification dans Snowflake¶

Créez une intégration de notification à l’aide de la commande CREATE NOTIFICATION INTEGRATION. L’intégration des notifications fait référence à votre abonnement Pub/Sub. Snowflake associe l’intégration de la notification à un compte de service GCS créé pour votre compte. Snowflake crée un seul compte de service référencé par toutes les notifications de stockage GCS de votre compte Snowflake.

CREATE NOTIFICATION INTEGRATION <integration_name>
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = '<subscription_id>';

Où :

• integration_name est le nom de la nouvelle intégration.

• subscription_id est le nom de l’abonnement que vous avez enregistré dans Récupération de l’ID d’abonnement Pub/Sub.

Par exemple :

CREATE NOTIFICATION INTEGRATION my_notification_int
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = 'projects/project-1234/subscriptions/sub2';

Étape 2 : Accorder un accès à Snowflake à l’abonnement Pub/Sub¶

1. Exécutez la commande DESCRIBE INTEGRATION pour récupérer l’ID de compte de service Snowflake :

   DESC NOTIFICATION INTEGRATION <integration_name>;
   

   Copy

   Où :

   • integration_name est le nom de l’intégration créée à l”étape 1 : Créer une intégration de notification dans Snowflake.

   Par exemple :

   DESC NOTIFICATION INTEGRATION my_notification_int;
   

   Copy

2. Enregistrez le nom du compte de service dans la colonne GCP_PUBSUB_SERVICE_ACCOUNT, qui a le format suivant :

   <service_account>@<project_id>.iam.gserviceaccount.com
   

   Copy

3. Connectez-vous à la console Google Cloud Platform en tant qu’éditeur de projet.

4. Dans le tableau de bord d’accueil, sélectionnez Big Data » Pub/Sub » Subscriptions.

5. Sélectionnez l’abonnement à configurer pour l’accès.

6. Cliquez sur SHOW INFO PANEL dans le coin supérieur droit. Le panneau d’information de l’abonnement s’affiche en coulissant.

7. Cliquez sur le bouton ADD PRINCIPAL.

8. Dans le champ New principals, recherchez le nom du compte de service que vous avez enregistré.

9. Dans la liste déroulante Select a role, sélectionnez Pub/Sub Subscriber.

10. Cliquez sur le bouton Save. Le nom du compte de service est ajouté à la liste déroulante des rôles Pub/Sub Subscriber dans le panneau d’informations.

11. Accédez à la page Dashboard de la console Cloud et sélectionnez votre projet dans la liste déroulante.

12. Cliquez sur le bouton ADD PEOPLE TO THIS PROJECT .

13. Ajoutez le nom du compte de service que vous avez enregistré.

14. Dans la liste déroulante Select a role, sélectionnez Monitoring Viewer.

15. Cliquez sur le bouton Save . Le nom du compte de service est ajouté au rôle Monitoring Viewer.

Étape 3 : Création d’une zone de préparation (si nécessaire)¶

Créez une zone de préparation externe qui fait référence à votre compartiment GCS à l’aide de la commande CREATE STAGE. Snowflake lit vos fichiers de données en zone de préparation dans les métadonnées de la table externe. Vous pouvez aussi utiliser une zone de préparation externe.

L’exemple suivant crée une zone de préparation nommée mystage dans le schéma actif de la session utilisateur. L’URL de stockage Cloud inclut le chemin files. La zone de préparation fait référence à une intégration de stockage nommée my_storage_int.

USE SCHEMA mydb.public;

CREATE STAGE mystage
  URL='gcs://load/files/'
  STORAGE_INTEGRATION = my_storage_int;

Copy

Étape 4 : Création d’une table externe¶

Créez une table externe à l’aide de la commande CREATE EXTERNAL TABLE.

Par exemple, créez une table externe dans le schéma mydb.public qui lit les données JSON à partir de fichiers gérés à l’étape mystage avec le chemin path1/.

Le paramètre INTEGRATION fait référence à my_notification_int l’intégration de notification créée à l” étape 1 : création d’une intégration Cloud Storage dans Snowflake. Le nom de l’intégration doit être fourni en majuscule.

Notez que le paramètre AUTO_REFRESH est TRUE par défaut :

CREATE OR REPLACE EXTERNAL TABLE ext_table
 INTEGRATION = 'MY_NOTIFICATION_INT'
 WITH LOCATION = @mystage/path1/
 FILE_FORMAT = (TYPE = JSON);

La zone de préparation externe avec actualisation automatique est maintenant configurée !

Lorsque des fichiers de données nouveaux ou mis à jour sont ajoutés au compartiment GCS, la notification d’événement demande à Snowflake de les analyser dans les métadonnées de la table externe.

Étape 5 : Actualisation manuelle des métadonnées de tables externes¶

Actualisez manuellement les métadonnées de la table externe une fois à l’aide de ALTER EXTERNAL TABLE avec le paramètre REFRESH, par exemple :

ALTER EXTERNAL TABLE ext_table REFRESH;

+---------------------------------------------+----------------+-------------------------------+
| file                                        | status         | description                   |
|---------------------------------------------+----------------+-------------------------------|
| files/path1/file1.json                      | REGISTERED_NEW | File registered successfully. |
| files/path1/file2.json                      | REGISTERED_NEW | File registered successfully. |
| files/path1/file3.json                      | REGISTERED_NEW | File registered successfully. |
+---------------------------------------------+----------------+-------------------------------+

Copy

Cette étape synchronise les métadonnées avec la liste des fichiers de la zone de préparation et du chemin dans la définition de la table externe. De plus, cette zone de préparation permet de s’assurer que la table externe peut lire les fichiers de données dans la zone de préparation et le chemin spécifiés, et qu’aucun fichier n’a été oublié dans la définition de la table externe.

Si la liste des fichiers de la colonne file ne correspond pas à vos attentes, vérifiez les chemins dans la définition de table externe et la définition de zone de préparation externe. Tout chemin dans la définition de table externe est ajouté à tout chemin spécifié dans la définition de zone de préparation. Pour plus d’informations, voir CREATE EXTERNAL TABLE.

Cette étape permet de s’assurer que les métadonnées sont synchronisées avec toutes les modifications apportées à la liste de fichiers depuis l’étape 4. Par la suite, les notifications Pub/Sub déclenchent automatiquement l’actualisation des métadonnées.

Étape 6 : Configuration de la sécurité¶

Pour chaque rôle supplémentaire qui sera utilisé pour interroger la table externe, accordez des privilèges de contrôle d’accès suffisants sur les différents objets (c.-à-d. la ou les bases de données, le ou les schémas, la zone de préparation et la table) avec GRANT <privilèges> :