Activation des notifications d’erreur Snowpipe pour Google Pub/Sub¶

Step 1: Creating the Pub/Sub Topic¶

Create a Pub/Sub topic that can receive error notification messages from Snowflake, or reuse an existing topic. You can create the topic using Cloud Shell or Cloud SDK. For more information, see Create and use topics in the Pub/Sub documentation.

For example, execute the following command to create an empty topic:

$ gsutil notification create -t <topic>

If the topic already exists, the command uses it; otherwise, a new topic is created.

Step 2: Creating the Pub/Sub Subscription¶

Optionally create a subscription to the Pub/Sub topic to retrieve error notifications. You can create a subscription with pull delivery using the Cloud Console, gcloud command-line tool, or the Cloud Pub/Sub API. For instructions, see Managing topics and subscriptions in the Pub/Sub documentation.

Step 3: Creating a Notification Integration in Snowflake¶

Create a notification integration using the CREATE NOTIFICATION INTEGRATION command. The notification integration references your Pub/Sub topic. Snowflake associates the notification integration with a Goodle Cloud Platform (GCP) service account created for your account. Snowflake creates a single service account that is referenced by all GCP notification integrations in your Snowflake account.

CREATE NOTIFICATION INTEGRATION <integration_name>
  ENABLED = TRUE
  TYPE = QUEUE
  DIRECTION = OUTBOUND
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  GCP_PUBSUB_TOPIC_NAME = '<topic_id>'

Où :

• integration_name is the name of the new integration.

• topic_id is the Pub/Sub topic to which Snowflake sends error notifications. For more information, see Step 1: Creating the Pub/Sub Topic (in this topic).

Par exemple :

CREATE NOTIFICATION INTEGRATION my_notification_int
  TYPE = QUEUE
  DIRECTION = OUTBOUND
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_TOPIC_NAME = 'projects/sdm-prod/topics/mytopic';

Step 4: Granting Snowflake Access to the Pub/Sub Subscription¶

1. Execute the DESCRIBE INTEGRATION command to retrieve the Snowflake service account ID:

   DESC NOTIFICATION INTEGRATION <integration_name>;
   

   Copy

   Où :

   • integration_name is the name of the integration you created in « Step 1: Create a Notification Integration in Snowflake ».

   Par exemple :

   DESC NOTIFICATION INTEGRATION my_notification_int;
   

   Copy

2. Record the service account name in the GCP_PUBSUB_SERVICE_ACCOUNT column, which has the following format:

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

   Copy

3. Log into the Google Cloud Platform Console as a project editor.

4. From the home dashboard, choose Big Data » Pub/Sub » Subscriptions.

5. Select the subscription to configure for access.

6. Click SHOW INFO PANEL in the upper-right corner. The information panel for the subscription slides out.

7. In the Add members field, search for the service account name you recorded.

8. From the Select a role dropdown, select Pub/Sub Publisher.

9. Click the Add button. The service account name is added to the Pub/Sub Publisher role dropdown in the information panel.

Step 5: Enabling error notifications in pipes¶

Une seule intégration de notification peut être partagée par plusieurs canaux. Le corps des messages d’erreur identifie le canal, la zone de préparation externe et le chemin, ainsi que le fichier d’où provient l’erreur, entre autres détails.

Pour activer les notifications d’erreur pour un canal, spécifiez une valeur de paramètre ERROR_INTEGRATION.

Notez que l’exploitation d’un objet dans un schéma requiert également le privilège USAGE sur la base de données et le schéma parents.

Pour obtenir des instructions sur la création d’un rôle personnalisé avec un ensemble spécifique de privilèges, voir Création de rôles personnalisés.

Pour des informations générales sur les rôles et les privilèges accordés pour effectuer des actions SQL sur des objets sécurisables, voir Aperçu du contrôle d’accès.

CREATE PIPE <name>
  AUTO_INGEST = TRUE
  [ INTEGRATION = '<string>' ]
  ERROR_INTEGRATION = <integration_name>
  AS <copy_statement>

CREATE PIPE mypipe
  AUTO_INGEST = TRUE
  INTEGRATION = 'my_storage_int'
  ERROR_INTEGRATION = my_notification_int
  AS
  COPY INTO mydb.public.mytable
  FROM @mydb.public.mystage;

ALTER PIPE <name> SET ERROR_INTEGRATION = <integration_name>;

ALTER PIPE mypipe SET ERROR_INTEGRATION = my_notification_int;