Actualisation automatique des tables externes pour Azure Blob Storage

Cette rubrique fournit des instructions pour la création de tables externes et l’actualisation automatique des métadonnées de la table externe à l’aide des notifications Microsoft Azure Event Grid pour un conteneur Azure. Cette opération synchronise les métadonnées avec le dernier ensemble de fichiers associés de la zone de préparation et du chemin externes, à savoir :

  • Les nouveaux fichiers dans le chemin sont ajoutés aux métadonnées de la table.

  • Les modifications apportées aux fichiers dans le chemin sont mises à jour dans les métadonnées de la table.

  • Les fichiers qui ne figurent plus dans le chemin sont supprimés des métadonnées de la table.

Snowflake prend actuellement en charge le stockage d’objets Blob uniquement. Snowflake prend en charge les types de comptes de stockage suivants :

  • Stockage d’objets blob

  • Data Lake Storage Gen2 — Pris en charge en tant que fonctionnalité préliminaire.

  • Usage général v2

Notez que seuls les événements Microsoft.Storage.BlobCreated et Microsoft.Storage.BlobDeleted déclenchent l’actualisation automatique des tables externes. Renommer un répertoire ou un objet ne déclenche aucun de ces types d’événements.

Note

  • Cette fonctionnalité est limitée aux comptes Snowflake sur la plate-forme Cloud Microsoft Azure.

  • Pour effectuer les tâches décrites dans cette rubrique, vous devez utiliser un rôle disposant des privilèges CREATE STAGE et CREATE EXTERNAL TABLE sur un schéma.

    De plus, vous devez avoir un accès administratif à Microsoft Azure. Si vous n’êtes pas un administrateur Azure, demandez à votre administrateur Azure d’effectuer ces étapes dans Étape 1 : Configuration de l’abonnement à Event Grid.

Dans ce chapitre :

Configuration de l’accès sécurisé au stockage Cloud

Note

Si vous avez déjà configuré un accès sécurisé au conteneur de stockage blob Azure qui stocke vos fichiers de données, vous pouvez ignorer cette section.

Cette section décrit comment configurer un objet d’intégration de stockage Snowflake pour déléguer la responsabilité de l’authentification pour le stockage dans le Cloud à une entité Gestion des identités et des accès Snowflake (IAM).

Note

Nous vous recommandons vivement cette option, qui vous évite de fournir des informations d’identification IAM lors de l’accès à un stockage dans le Cloud. Voir Configuration d’un conteneur Azure pour le chargement de données pour des options d’accès au stockage supplémentaires.

Cette section décrit comment utiliser des intégrations de stockage pour permettre à Snowflake de lire et d’écrire des données dans un conteneur Azure référencé dans une zone de préparation externe (Azure). Les intégrations sont des objets Snowflake de première classe nommés, qui évitent de transmettre des informations d’identification explicites de fournisseur Cloud, telles que des clés secrètes ou des jetons d’accès. Les objets d’intégration stockent un ID d’utilisateur Azure de gestion des identités et des accès (IAM) appelé inscription d’application. Un administrateur de votre organisation accorde à cette application les autorisations nécessaires dans le compte Azure.

Une intégration peut également spécifier des conteneurs (et des chemins facultatifs) qui limitent les emplacements que les utilisateurs peuvent spécifier lors de la création de zones de préparation externes utilisant l’intégration.

Note

La réalisation des instructions de cette section nécessite des autorisations dans Azure pour gérer les comptes de stockage. Si vous n’êtes pas un administrateur Azure, demandez à votre administrateur Azure d’effectuer ces tâches.

Dans cette section :

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

Créez une intégration de stockage à l’aide de la commande CREATE STORAGE INTEGRATION. Une intégration de stockage est un objet Snowflake qui stocke un principal de service généré pour votre stockage Cloud Azure, ainsi qu’un ensemble facultatif d’emplacements de stockage autorisés ou bloqués (c.-à-d. des conteneurs). Les administrateurs de fournisseur de Cloud de votre entreprise accordent des autorisations sur les emplacements de stockage au principal de service généré. Cette option permet aux utilisateurs d’éviter de fournir des informations d’identification lors de la création de zones de préparation ou du chargement de données.

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

Note

Seuls les administrateurs de compte (utilisateurs dotés du rôle ACCOUNTADMIN) ou un rôle disposant du privilège global CREATE INTEGRATION peuvent exécuter cette commande SQL.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = AZURE
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant_id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/') ]

Où :

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

  • id_client est l’ID de votre client Office 365 auquel appartiennent les comptes de stockage autorisés et bloqués. Une intégration de stockage peut s’authentifier auprès d’un seul client. Les emplacements de stockage autorisés et bloqués doivent donc faire référence aux comptes de stockage qui appartiennent tous à ce client.

    Pour trouver votre ID de client, connectez-vous au portail Azure et cliquez sur Azure Active Directory » Properties. L” ID de client s’affiche dans le champ Directory ID.

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

  • chemin est un chemin facultatif qui peut être utilisé pour fournir un contrôle granulaire sur les répertoires logiques du conteneur.

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 conteneurs 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 conteneurs et chemins. Plusieurs zones de préparation externes qui utilisent cette intégration peuvent référencer les conteneurs et les chemins autorisés :

CREATE STORAGE INTEGRATION azure_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = AZURE
  ENABLED = TRUE
  AZURE_TENANT_ID = 'a123b4c5-1234-123a-a12b-1a23b45678c9'
  STORAGE_ALLOWED_LOCATIONS = ('azure://myaccount.blob.core.windows.net/mycontainer1/mypath1/', 'azure://myaccount.blob.core.windows.net/mycontainer2/mypath2/')
  STORAGE_BLOCKED_LOCATIONS = ('azure://myaccount.blob.core.windows.net/mycontainer1/mypath1/sensitivedata/', 'azure://myaccount.blob.core.windows.net/mycontainer2/mypath2/sensitivedata/');

Étape 2 : Accorder un accès à Snowflake aux emplacements de stockage

  1. Exécutez la commande DESCRIBE INTEGRATION pour récupérer l’URL de consentement :

    DESC STORAGE INTEGRATION <integration_name>;
    

    Où :

Notez l’URL dans la colonne AZURE_CONSENT_URL, au format suivant :

https://login.microsoftonline.com/<tenant_id>/oauth2/authorize?client_id=<snowflake_application_id>

Notez également la valeur dans la colonne AZURE_MULTI_TENANT_APP_NAME. Il s’agit du nom de l’application client Snowflake créée pour votre compte. Plus loin dans ces instructions, vous devrez accorder à cette application les autorisations nécessaires pour obtenir un jeton d’accès sur vos emplacements de stockage autorisés.

  1. Dans un navigateur Web, accédez à l’URL dans la colonne AZURE_CONSENT_URL URL. La page affiche une page de demande d’autorisations Microsoft.

  2. Cliquez sur le bouton Accept. Cela permet au principal du service Azure créé pour votre compte Snowflake d’obtenir un jeton d’accès sur n’importe quelle ressource à l’intérieur de votre client. L’obtention d’un jeton d’accès ne réussit que si vous accordez au principal du service les autorisations appropriées sur le conteneur (voir l’étape suivante).

  3. Connectez-vous au portail Microsoft Azure.

  4. Accédez à Azure Services » Storage Accounts. Cliquez sur le nom du compte de stockage auquel vous accordez au principal du service Snowflake l’accès.

  5. Cliquez sur Access Control (IAM) » Add role assignment.

  6. Sélectionnez le rôle souhaité à accorder au principal du service Snowflake :

    • Storage Blob Data Reader accorde un accès en lecture uniquement. Cela permet de charger des données à partir de fichiers mis en zone de préparation dans le compte de stockage.

    • Storage Blob Data Contributor accorde un accès en lecture et en écriture. Cela permet de charger ou de décharger des données dans des fichiers stockés dans le compte de stockage.

  7. Search for the Snowflake service principal. This is the identity in the AZURE_MULTI_TENANT_APP_NAME property in the DESC STORAGE INTEGRATION output (in Step 1). Search for the string before the underscore in the AZURE_MULTI_TENANT_APP_NAME property.

    Important

    • Azure peut prendre une heure ou plus pour créer le principal de service Snowflake demandé via la page de demande Microsoft dans cette section. Si le principal de service n’est pas disponible immédiatement, nous vous recommandons d’attendre une heure ou deux, puis de relancer la recherche.

    • Si vous supprimez le principal de service, l’intégration de stockage cesse de fonctionner.

  8. Cliquez sur le bouton Save.

    Note

    Selon la documentation de Microsoft Azure, les affectations de rôles peuvent prendre jusqu’à cinq minutes pour se propager.

Étape 1 : Configuration de l’abonnement à Event Grid

Cette section explique comment configurer un abonnement à Event Grid pour les événements Azure Storage à l’aide d’Azure CLI. Pour plus d’informations sur les étapes décrites dans cette section, consultez les articles suivants dans la documentation Azure :

Créer un groupe de ressources

Un sujet Event Grid fournit un point de terminaison où la source (c.-à-d. Azure Storage) envoie des événements. Un sujet est utilisé pour une collection d’événements connexes. Les sujets Event Grid sont des ressources Azure et doivent être placés dans un groupe de ressources Azure.

Exécutez la commande suivante pour créer un groupe de ressources :

az group create --name <resource_group_name> --location <location>

Où :

  • nom_groupe_ressources est le nom du nouveau groupe de ressources.

  • emplacement est l’emplacement ou la région dans la terminologie Snowflake de votre compte Azure Storage.

Activer le fournisseur de ressources Event Grid

Exécutez la commande suivante pour inscrire le fournisseur de ressources d’Event Grid. Notez que cette étape n’est requise que si vous n’avez pas déjà utilisé Event Grid avec votre compte Azure :

az provider register --namespace Microsoft.EventGrid
az provider show --namespace Microsoft.EventGrid --query "registrationState"

Créer un compte de stockage pour les fichiers de données

Exécutez la commande suivante pour créer un compte de stockage pour stocker vos fichiers de données. Ce compte doit être un compte de stockage Blob (type BlobStorage) ou GPv2 (type StorageV2), car seuls ces deux types de compte prennent en charge les messages d’événement.

Note

Si vous avez déjà un stockage Blob ou un compte GPv2, vous pouvez utiliser ce compte à la place.

Par exemple, créez un compte de stockage Blob :

az storage account create --resource-group <resource_group_name> --name <storage_account_name> --sku Standard_LRS --location <location> --kind BlobStorage --access-tier Hot

Où :

  • nom_groupe_ressource est le nom du groupe de ressources que vous avez créé dans Créer un groupe de ressources.

  • nom_compte_stockage est le nom du nouveau compte de stockage.

  • emplacement correspond à l’emplacement de votre compte Azure Storage.

Créer un compte de stockage pour la file d’attente de stockage

Exécutez la commande suivante pour créer un compte de stockage pour héberger votre file d’attente de stockage. Ce compte doit être un compte GPv2, car seul ce type de compte prend en charge les messages d’événement dans une file d’attente de stockage.

Note

Si vous avez déjà un compte GPv2, vous pouvez utiliser ce compte pour héberger à la fois vos fichiers de données et votre file d’attente de stockage.

Par exemple, créez un compte GPv2 :

az storage account create --resource-group <resource_group_name> --name <storage_account_name> --sku Standard_LRS --location <location> --kind StorageV2

Où :

  • nom_groupe_ressource est le nom du groupe de ressources que vous avez créé dans Créer un groupe de ressources.

  • nom_compte_stockage est le nom du nouveau compte de stockage.

  • emplacement correspond à l’emplacement de votre compte Azure Storage.

Créer une file d’attente de stockage

Une seule file d’attente Azure Queue Storage peut collecter les messages d’événement pour de nombreux abonnements à Event Grid. Pour optimiser les performances, Snowflake recommande de créer une seule file d’attente de stockage afin de prendre en charge tous vos abonnements liés à Snowflake.

Exécutez la commande suivante pour créer une file d’attente de stockage. Une file d’attente de stockage stocke un ensemble de messages, en l’occurrence des messages d’événement d’Event Grid :

az storage queue create --name <storage_queue_name> --account-name <storage_account_name>

Où :

Exporter le compte de stockage et les IDs de file d’attente pour référence

Exécutez les commandes suivantes pour définir les variables d’environnement des IDs de compte de stockage et de file d’attente qui seront demandés ultérieurement dans ces instructions :

  • Linux ou macOS :

    export storageid=$(az storage account show --name <data_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
    export queuestorageid=$(az storage account show --name <queue_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
    export queueid="$queuestorageid/queueservices/default/queues/<storage_queue_name>"
    
  • Windows :

    set storageid=$(az storage account show --name <data_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
    set queuestorageid=$(az storage account show --name <queue_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
    set queueid="%queuestorageid%/queueservices/default/queues/<storage_queue_name>"
    

Où :

Installer l’extension Event Grid

Exécutez la commande suivante pour installer l’extension Event Grid pour Azure CLI :

az extension add --name eventgrid

Créer l’abonnement à Event Grid

Exécutez la commande suivante pour créer l’abonnement à Event Grid. L’abonnement à un sujet indique à Event Grid quels événements doivent être suivis :

  • Linux ou macOS :

    az eventgrid event-subscription create \
    --source-resource-id $storageid \
    --name <subscription_name> --endpoint-type storagequeue \
    --endpoint $queueid
    
  • Windows :

    az eventgrid event-subscription create \
    --source-resource-id %storageid% \
    --name <subscription_name> --endpoint-type storagequeue \
    --endpoint %queueid%
    

Où :

Étape 2 : Création d’une intégration de notification dans Snowflake

Une intégration de notification est un objet Snowflake qui fournit une interface entre Snowflake et un service de mise en file d’attente de messages dans le Cloud tiers, tel que Azure Event Grid.

Note

Une file d’attente Azure Queue Storage prend en charge une intégration de notification unique. Le fait de référencer une file d’attente de stockage unique dans plusieurs intégrations de notification peut entraîner des données manquantes dans les tables cibles, car les notifications d’événements sont réparties entre les intégrations de notification.

Récupérer l’URL de file d’attente de stockage et l’ID du client

  1. Connectez-vous au portail Microsoft Azure.

  2. Accédez à Storage account » Queue service » Queues. Enregistrez l’URL pour la file d’attente que vous avez créée dans Créer une file d’attente de stockage pour référence ultérieure. L’URL a le format suivant :

    https://<storage_account_name>.queue.core.windows.net/<storage_queue_name>
    
  3. Accédez à Azure Active Directory » Properties. Enregistrez la valeur Directory ID pour référence plus tard. L’ID de répertoire , ou ID de client, est nécessaire pour générer l’URL de consentement qui accorde à Snowflake l’accès à l’abonnement à Event Grid.

Créer l’intégration

Créez une intégration à l’aide de la commande CREATE NOTIFICATION INTEGRATION.

Note

Seuls les administrateurs de compte (utilisateurs dotés du rôle ACCOUNTADMIN) ou un rôle disposant du privilège global CREATE INTEGRATION peuvent exécuter cette commande SQL.

CREATE NOTIFICATION INTEGRATION <integration_name>
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
  AZURE_STORAGE_QUEUE_PRIMARY_URI = '<queue_URL>'
  AZURE_TENANT_ID = '<directory_ID>';

Où :

Par exemple :

CREATE NOTIFICATION INTEGRATION my_azure_int
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
  AZURE_STORAGE_QUEUE_PRIMARY_URI = 'https://myqueue.queue.core.windows.net/mystoragequeue'
  AZURE_TENANT_ID = 'a123bcde-1234-5678-abc1-9abc12345678';

Accorder un accès à Snowflake à la file d’attente de stockage

  1. Exécutez la commande DESCRIBE INTEGRATION pour récupérer l’URL de consentement :

    DESC NOTIFICATION INTEGRATION <integration_name>;
    

    Où :

  2. Notez l’URL dans la colonne AZURE_CONSENT_URL, au format suivant :

    https://login.microsoftonline.com/<tenant_id>/oauth2/authorize?client_id=<snowflake_application_id>
    
  3. Accédez à l’URL dans un navigateur Web. La page affiche une page de demande d’autorisations Microsoft.

  4. Cliquez sur le bouton Accept pour enregistrer Snowflake dans Active Directory.

  5. Connectez-vous au portail Microsoft Azure.

  6. Accédez à Azure Active Directory » Enterprise applications. Vérifiez que l’application Snowflake est répertoriée.

    Important

    Si vous supprimez l’application Snowflake dans Azure Active Directory, l’intégration des notifications cesse de fonctionner.

  7. Accédez à Queues » nom_file_stockage, où nom_file_stockage est le nom de la file d’attente de stockage que vous avez créée dans Créer une file d’attente de stockage.

  8. Cliquez sur Access Control (IAM) » Add role assignment.

  9. Recherchez l’application Snowflake.

  10. Accordez à l’application Snowflake les autorisations suivantes :

    • Role: Contributeur de données de la file de stockage (aperçu)

    • Assign access to: Principal de service, groupe ou utilisateur AD Azure

    • Select: id_application_snowflake

    L’application Snowflake devrait maintenant figurer dans la liste sous Storage Queue Data Contributor (dans la même boîte de dialogue).

É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 conteneur Azure à 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.

Note

Pour configurer un accès sécurisé à l’emplacement de stockage Cloud, voir Configuration de l’accès sécurisé au stockage Cloud (dans cette rubrique).

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 myint.

USE SCHEMA mydb.public;

CREATE STAGE mystage
  URL='azure://myaccount.blob.core.windows.net/mycontainer/files/'
  STORAGE_INTEGRATION = myint;

Note

Utilisez le point de terminaison blob.core.windows.net pour tous les types de comptes de stockage Azure Blob pris en charge, dont Data Lake Storage Gen2.

É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 à l’intégration my_azure_int que vous avez créée dans Créer l’intégration. 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_AZURE_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 conteneur Azure, 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 la table externe

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. |
+---------------------------------------------+----------------+-------------------------------+

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.

Important

Si cette étape ne réussit pas au moins une fois après la création de la table externe, l’interrogation de la table externe ne renvoie aucun résultat jusqu’à ce qu’une notification Event Grid actualise automatiquement les métadonnées de la table externe pour la première fois.

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. Ensuite, les notifications Event Grid 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 <privileges> … TO ROLE :

Objet

Privilège

Remarques

Base de données

USAGE

Schéma

USAGE

Zone de préparation nommée

USAGE , READ

Format de fichier nommé

USAGE

Facultatif ; nécessaire uniquement si la zone de préparation que vous avez créée dans Étape 3 : Création d’une zone de préparation (si nécessaire) fait référence à un format de fichier nommé.

Table externe

SELECT