Introduction aux tables externes

Dans une table typique, les données sont stockées dans la base de données. Cependant, dans une table externe, les données sont stockées dans des fichiers d’une zone de préparation externe. Les tables externes stockent les métadonnées au niveau du fichier sur les fichiers de données, telles que le nom de fichier, un identificateur de version et les propriétés associées. Cela permet d’interroger les données stockées dans des fichiers d’une zone de préparation externe, comme si elles se trouvaient dans une base de données. Les tables externes peuvent accéder aux données stockées dans tout format pris en charge par les instructions COPY INTO <table>.

Les tables externes sont en lecture seule. Par conséquent, aucune opération DML ne peut être effectuée sur celles-ci. Cependant, des tables externes peuvent être utilisées pour les opérations de requête et de jointure. Les vues peuvent être créées sur des tables externes.

L’interrogation des données stockées en externe dans la base de données risque d’être plus lente que l’interrogation des tables de base de données natives. Cependant, les vues matérialisées basées sur des tables externes peuvent améliorer les performances des requêtes.

Dans ce chapitre :

Planification du schéma d’une table externe

Cette section décrit les options disponibles pour la conception de tables externes.

Schéma en lecture

Toutes les tables externes incluent les colonnes suivantes :

VALUE

Une colonne de type VARIANT qui représente une seule ligne du fichier externe.

METADATA$FILENAME

Une pseudo-colonne qui identifie le nom de chaque fichier de données en zone de préparation inclus dans la table externe, y compris son chemin dans la zone de préparation.

Pour créer des tables externes, il vous suffit de connaître le format de fichier et le format d’enregistrement des fichiers de données source. Connaître le schéma des fichiers de données n’est pas nécessaire. Lorsqu’elles sont interrogées, les tables externes transforment toutes les données ordinaires ou semi-structurées en une variante de la colonne VALUE.

Colonnes virtuelles

Si vous connaissez le schéma des fichiers de données source, vous pouvez créer d’autres colonnes virtuelles en tant qu’expressions à l’aide de la colonne VALUE et/ou de la pseudocolonne METADATA$FILENAME. Lorsque les données externes sont analysées, les types de données des champs spécifiés ou des éléments de données semi-structurés du fichier de données doivent correspondre aux types de données de ces colonnes supplémentaires dans la table externe. Cela permet une vérificationde type et une validation de schéma robustes sur les données externes.

Tables externes partitionnées

Nous vous recommandons vivement de partitionner vos tables externes, ce qui nécessite que vos données sous-jacentes soient organisées à l’aide de chemins logiques incluant la date, l’heure, le pays ou des dimensions similaires dans le chemin. Le partitionnement divise les données de votre table externe en plusieurs parties à l’aide de colonnes de partition. Les colonnes de partition doivent être évaluées en tant qu’expressions qui analysent les informations de chemin d’accès et/ou de nom de fichier stockées dans la pseudocolonne METADATA$FILENAME. Une partition est composée de tous les fichiers de données correspondant au chemin et/ou au nom de fichier dans l’expression de la colonne de partition.

Une définition de table externe peut inclure plusieurs colonnes de partition, qui imposent une structure multidimensionnelle aux données externes.

Les avantages du partitionnement incluent l’amélioration des performances des requêtes. Les données externes étant partitionnées en plusieurs tranches/parties, le temps de réponse à la requête est plus rapide lorsque vous traitez une petite partie des données au lieu d’analyser l’intégralité du jeu de données.

Les colonnes de partition peuvent uniquement être définies lors de la création d’une table externe, à l’aide de la syntaxe CREATE EXTERNAL TABLE … PARTITION BY avec une liste de définitions de colonnes pour le partitionnement.

Syntaxe

Définissez les partitions dans les données de la table externe en définissant les colonnes de la partition :

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Pour des exemples, voir CREATE EXTERNAL TABLE.

Ajouter ou détruire des colonnes

Modifiez une table externe existante pour ajouter ou supprimer des colonnes à l’aide de la syntaxe ALTER TABLE suivante :

  • Ajouter des colonnes : ALTER TABLE … ADD COLUMN.

  • Supprimer les colonnes : ALTER TABLE … DROP COLUMN.

Note

Les colonnes VALUE et METADATA$FILENAME par défaut ne peuvent pas être détruites.

Voir l’exemple dans ALTER TABLE .

Vues matérialisées sur des tables externes

Dans de nombreux cas, les vues matérialisées sur des tables externes peuvent fournir des performances plus rapides que les requêtes équivalentes sur la table externe sous-jacente. Cette différence de performances peut être significative lorsqu’une requête est exécutée fréquemment ou est suffisamment complexe.

Actualisation automatique des métadonnées de table externe

Les métadonnées d’une table externe peuvent être actualisées automatiquement à l’aide du service de notification d’événements suivant pour chaque emplacement de stockage :

L’opération d’actualisation synchronise les métadonnées avec le dernier ensemble de fichiers associés dans la zone de préparation et le chemin externes, c’est-à-dire :

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

Note

Actuellement, la possibilité d’actualiser automatiquement les métadonnées n’est pas disponible pour les tables externes faisant référence à des zones de préparation de Google Cloud Storage.

Comme solution de contournement, nous suggérons de suivre nos bonnes pratiques pour mettre en zone de préparation vos fichiers de données et d’exécuter de façon intermittente une instruction ALTER EXTERNAL TABLE … REFRESH pour enregistrer les fichiers manquants. Pour des performances satisfaisantes, nous recommandons également d’utiliser un préfixe de chemin sélectif avec ALTER EXTERNAL TABLE pour réduire le nombre de fichiers qui doivent être répertoriés et vérifiés s’ils ont déjà été enregistrés (par exemple, nom_compartiment/YYYY/MM/DD/ ou même nom_compartiment/YYYY/MM/DD/HH/ en fonction de votre volume).

Facturation pour l’actualisation des métadonnées de table externe

Une surcharge pour gérer les notifications d’événement (pour les fichiers ajoutés au stockage dans le Cloud) est incluse dans vos frais. Cette surcharge augmente en fonction du nombre de fichiers ajoutés dans le stockage Cloud pour les zones de préparation externes et les chemins spécifiés pour vos tables externes. Snowflake facture 0,06 crédit par 1 000 notifications d’événements reçues.

De plus, lorsque des tables externes sont généralement disponibles (date TBD), des frais généraux de maintenance sont facturés pour l’actualisation manuelle des métadonnées de la table externe (avec ALTER EXTERNAL TABLE … REFRESH). Cette surcharge est incluse pour les ressources utilisées lors de l’extraction de la liste d’objets, ainsi que des informations descriptives sur chaque objet, dans la zone de préparation et le chemin externes. Le taux pour cette surcharge n’a pas encore été défini.

Workflow

Amazon S3

Cette section fournit une vue d’ensemble de haut niveau du workflow de configuration et de chargement pour des tables externes qui font référence aux zones de préparation Amazon S3. Pour des instructions complètes, voir Actualisation automatique des tables externes Amazon S3 .

  1. Créez un objet de zone de préparation nommé (à l’aide de CREATE STAGE) faisant référence à l’emplacement externe (c’est-à-dire compartiment S3) où sont stockés vos fichiers de données.

  2. Créez une table externe (à l’aide de CREATE EXTERNAL TABLE) faisant référence à la zone de préparation nommée.

  3. Actualisez manuellement les métadonnées de la table externe avec ALTER EXTERNAL TABLE … REFRESH pour synchroniser les métadonnées avec la liste actuelle des fichiers dans le chemin de la zone de préparation. Cette étape vérifie également les paramètres de votre définition de table externe.

  4. Configurez une notification d’événement pour le compartiment S3. Snowflake s’appuie sur les notifications d’événements pour actualiser en permanence les métadonnées de la table externe afin de maintenir la cohérence avec les fichiers mis en zone de préparation.

  5. Actualisez manuellement les métadonnées de la table externe une nouvelle fois à l’aide de ALTER EXTERNAL TABLE … REFRESH pour synchroniser les métadonnées avec toutes les modifications qui se sont produites depuis l’étape 3. Ensuite, les notifications d’événements S3 déclenchent automatiquement l’actualisation des métadonnées.

  6. Configurez les privilèges de contrôle d’accès Snowflake pour tous les rôles supplémentaires afin de leur accorder un accès de requête à la table externe.

Google Cloud Storage

Cette section fournit une vue d’ensemble de haut niveau du workflow de configuration et de chargement pour les tables externes qui font référence aux zones de préparation Google Cloud Storage (GCS).

  1. Créez un objet de zone de préparation nommé (à l’aide de CREATE STAGE) faisant référence à l’emplacement externe (c’est-à-dire compartiment GCS) où sont stockés vos fichiers de données.

  2. Créez une table externe (à l’aide de CREATE EXTERNAL TABLE) faisant référence à la zone de préparation nommée.

  3. Configurez les privilèges de contrôle d’accès Snowflake pour tous les rôles supplémentaires afin de leur accorder un accès de requête à la table externe.

  4. Actualisez régulièrement les métadonnées de la table externe à l’aide de ALTER EXTERNAL TABLE … REFRESH pour synchroniser les métadonnées avec la liste actuelle des fichiers dans le chemin de la zone de préparation. Cette étape vérifie également les paramètres de votre définition de table externe.

Microsoft Azure

Cette section fournit une vue d’ensemble de haut niveau du workflow de configuration et de chargement des tables externes qui font référence à des zones de préparation Azure. Pour des instructions complètes, voir Actualisation automatique des tables externes pour Azure Blob Storage.

  1. Configurez un abonnement à Event Grid pour les événements Azure Storage.

  2. Créez une intégration de notification dans Snowflake. Une intégration de notification est un objet Snowflake qui fournit une interface entre Snowflake et des services de mise en file d’attente de messages dans le Cloud tiers, tels que Microsoft Event Grid.

  3. Créez un objet de zone de préparation nommé (à l’aide de CREATE STAGE) faisant référence à l’emplacement externe (c’est-à-dire conteneur Azure) où sont stockés vos fichiers de données.

  4. Créez une table externe (à l’aide de CREATE EXTERNAL TABLE) faisant référence à la zone de préparation et à l’intégration nommées.

  5. Actualisez manuellement les métadonnées de la table externe une fois à l’aide de ALTER EXTERNAL TABLE … REFRESH pour synchroniser les métadonnées avec toutes les modifications qui se sont produites depuis l’étape 4. Ensuite, les notifications Event Grid déclenchent automatiquement l’actualisation des métadonnées.

  6. Configurez les privilèges de contrôle d’accès Snowflake pour tous les rôles supplémentaires afin de leur accorder un accès de requête à la table externe.

Intégration Apache Hive Metastore

Snowflake prend en charge l’intégration des metastores Apache Hive à Snowflake à l’aide de tables externes. Le connecteur Hive détecte les événements de metastore et les transmet à Snowflake pour que les tables externes soient synchronisées avec le metastore Hive. Cela permet aux utilisateurs de gérer leurs données dans Hive tout en les interrogeant à partir de Snowflake.

Pour obtenir des instructions, voir Intégration d’Apache Hive Metastores à Snowflake.

DDL de table externe

Pour faciliter la création et la gestion des tables externes, Snowflake fournit l’ensemble suivant de commandes spéciales DDL :

Privilèges d’accès requis

La création et la gestion de tables externes nécessitent un rôle avec au minimum les autorisations de rôle suivantes :

Objet

Privilège

Base de données

USAGE

Schéma

USAGE, CREATE STAGE (si vous créez une nouvelle zone de préparation), CREATE EXTERNAL TABLE

Zone de préparation (si vous utilisez une zone de préparation existante)

USAGE

Schéma d’information

Snowflake Schéma d’information inclut des vues et des fonctions de tables sur lesquelles vous pouvez effectuer une requête pour récupérer des informations sur vos tables externes et leurs fichiers de données préparés.

Vue

Vue EXTERNAL_TABLES

Récupérez une liste de tables externes dans la base de données spécifiée (ou actuelle).

Fonctions de table

EXTERNAL_TABLE_FILES

Récupérez des informations sur les fichiers de données préparés inclus dans les métadonnées d’une table externe spécifiée.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Récupérez des informations sur l’historique des métadonnées d’une table externe, y compris les erreurs détectées lors de l’actualisation des métadonnées.