Introduction aux tables externes¶

Une table externe est une fonctionnalité de Snowflake que vous pouvez utiliser pour interroger des données stockées dans une zone de préparation externe comme si les données se trouvaient dans une table de Snowflake. La zone de préparation externe ne fait pas partie de Snowflake, de sorte que Snowflake ne stocke pas et ne gère pas cette zone de préparation. Pour renforcer votre posture de sécurité, vous pouvez configurer la zone de préparation externe pour la connexion privée sortante afin d’accéder à la table externe à l’aide d’une connexion privée.

Les tables externes vous permettent de stocker (au sein de Snowflake) certaines métadonnées au niveau des fichiers, y compris les noms de fichiers, les identificateurs de version et les propriétés connexes. Les tables externes peuvent accéder aux données stockées dans tous les formats pris en charge par la commande COPY INTO <table>, à l’exception de XML.

Les tables externes sont en lecture seule. Vous ne pouvez pas exécuter d’opérations de langage de manipulation de données (DML) sur des tables externes. Toutefois, vous pouvez utiliser des tables externes pour les opérations de requête et de jointure. Vous pouvez également créer des vues sur des tables externes.

L’interrogation de données dans une table externe peut être plus lente que l’interrogation de données que vous stockez nativement dans une table dans Snowflake. Pour améliorer les performances des requêtes, vous pouvez utiliser une vue matérialisée basée sur une table externe. Pour des performances des requêtes optimales lorsque vous travaillez avec des fichiers Parquet, pensez à utiliser des Tables Apache Iceberg™ à la place.

Note

Si Snowflake rencontre une erreur lors de l’analyse d’un fichier dans le stockage Cloud pendant une opération d’interrogation, le fichier est ignoré et l’analyse se poursuit sur le fichier suivant. Une requête peut scanner partiellement un fichier et renvoyer les lignes scannées avant que l’erreur ne soit rencontrée.

Dans ce chapitre :

Planification du schéma d’une table externe¶

Les sections suivantes décrivent les options disponibles pour planifier vos 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.
METADATA$FILE_ROW_NUMBER:: Pseudocolonne qui indique le nombre de lignes pour chaque enregistrement d’un fichier de données en 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.

Note

SELECT * renvoie toujours la colonne VALUE, dans laquelle toutes les données régulières ou semi-structurées sont converties en lignes de variantes.

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 des pseudo-colonnes METADATA$FILENAME ou METADATA$FILE_ROW_NUMBER. 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. Cette exigence permet une vérification de type et une validation de schéma robustes sur les données externes.

Recommandations générales en matière de dimensionnement de fichiers¶

Pour optimiser le nombre d’opérations d’analyse parallèles lors de l’interrogation de tables externes, nous recommandons les tailles de fichiers ou de groupes de lignes par format suivantes :

Format	Plage de tailles recommandées	Remarques
Fichiers Parquet	256 - 512 MB
Groupes de lignes de parquets	16 - 256 MB	Lorsque les fichiers Parquet comprennent plusieurs groupes de lignes, Snowflake peut opérer sur chaque groupe de lignes dans un serveur différent. Pour améliorer les performances des requêtes, nous recommandons de dimensionner les fichiers Parquet dans la fourchette recommandée. Sinon, si des fichiers de grande taille sont nécessaires, nous conseillons d’inclure plusieurs groupes de lignes dans chaque fichier.
Tous les autres formats de fichiers pris en charge	16 - 256 MB

Pour des performances optimales lors de l’interrogation de fichiers de données volumineux, créez et interrogez des vues matérialisées sur des tables 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.

Une définition de table externe peut inclure plusieurs colonnes de partition, qui imposent une structure multidimensionnelle aux données externes. Les partitions sont stockées dans les métadonnées de la table externe.

Le partitionnement améliore les performances des requêtes. Les données externes étant partitionnées en plusieurs tranches ou parties, le temps de réponse à la requête est plus rapide lorsque vous traitez une petite portion de données au lieu d’analyser l’intégralité de l’ensemble de données.

En fonction de vos cas d’utilisation, vous pouvez procéder de l’une ou l’autre des manières suivantes :

Ajouter automatiquement de nouvelles partitions en actualisant une table externe qui définit une expression pour chaque colonne de partition.
Ajouter de nouvelles partitions manuellement.

Les colonnes de partition sont définies lors de la création d’une table externe, à l’aide de la syntaxe CREATE EXTERNAL TABLE … PARTITION BY. Après la création d’une table externe, la méthode selon laquelle les partitions sont ajoutées ne peut pas être modifiée.

Les sections suivantes expliquent plus en détail les différentes options d’ajout de partitions. Pour des exemples, voir CREATE EXTERNAL TABLE.

Ajout automatique de partitions¶

Un créateur de tables externes définit les colonnes de partition d’une nouvelle table externe comme des expressions qui analysent les informations liées au chemin ou au nom de fichier stockées dans la pseudo-colonne METADATA$FILENAME. Une partition est composée de tous les fichiers de données correspondant au chemin ou au nom de fichier dans l’expression de la colonne de partition.

La syntaxe CREATE EXTERNAL TABLE suivante ajoute automatiquement des partitions basées sur des expressions :

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

Copy

Snowflake calcule et ajoute des partitions basées sur des expressions de colonnes de partition définies lorsque les métadonnées d’une table externe sont actualisées. Par défaut, les métadonnées sont actualisées automatiquement lorsque l’objet est créé. En outre, le propriétaire de l’objet peut configurer les métadonnées pour qu’elles soient actualisées automatiquement lorsque des fichiers de données nouveaux ou mis à jour sont disponibles dans la zone de préparation externe. Le propriétaire peut également actualiser les métadonnées manuellement en exécutant la commande ALTER EXTERNAL TABLE … REFRESH.

Partitions ajoutées manuellement¶

Un créateur de table externe détermine le type de partition d’une nouvelle table externe comme étant défini par l’utilisateur et ne spécifie que les types de données des colonnes de partition. Utilisez cette option lorsque vous préférez ajouter et supprimer des partitions de manière sélective plutôt que d’ajouter automatiquement des partitions pour tous les nouveaux fichiers dans un emplacement de stockage externe qui correspondent à une expression.

Vous choisissez généralement cette option pour synchroniser les tables externes avec d’autres métadonnées (par exemple, AWS Glue ou Apache Hive).

La syntaxe CREATE EXTERNAL TABLE suivante ajoute manuellement des partitions :

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

Copy

Inclure le paramètre PARTITION_TYPE = USER_SPECIFIED requis.

Les définitions de la colonne de partition sont des expressions qui analysent les métadonnées de la colonne interne (cachée) METADATA$EXTERNAL_TABLE_PARTITION.

Le propriétaire d’objet ajoute manuellement des partitions aux métadonnées de la table externe en exécutant la commande ALTER EXTERNAL TABLE … ADD PARTITION :

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

Copy

L’actualisation automatique d’une table externe avec des partitions définies par l’utilisateur n’est pas prise en charge. La tentative d’actualiser manuellement ce type de table externe produit une erreur d’utilisateur.

Prise en charge de Delta Lake¶

Note

Cette fonction est toujours prise en charge, mais elle sera obsolète dans une prochaine version.

Envisagez plutôt d’utiliser une table Apache Iceberg™. Les tables Iceberg utilisent un volume externe pour se connecter aux fichiers de tables Delta dans votre stockage Cloud.

Pour plus d’informations, voir Tables Iceberg et CREATE ICEBERG TABLE (fichiers Delta dans le stockage d’objets). Vous pouvez également Migration d’une table externe Delta vers Apache Iceberg™.

Delta Lake est un format de table sur votre data lake qui prend en charge les transactions ACID (atomicité, cohérence, isolation, durabilité), entre autres fonctionnalités. Toutes les données de Delta Lake sont stockées au format Apache Parquet. Vous pouvez créer des tables externes qui référencent vos emplacements de stockage Cloud améliorés avec Delta Lake.

Pour créer une table externe qui fait référence à un Delta Lake, définissez le paramètre TABLE_FORMAT = DELTA dans l’instruction CREATE EXTERNAL TABLE.

Lorsque vous définissez ce paramètre, la table externe analyse les fichiers journaux des transactions Delta Lake dans l’emplacement [ WITH ] LOCATION. Les fichiers journaux Delta ont des noms comme _delta_log/00000000000000000010.checkpoint.parquet ou _delta_log/00000000000000000000.json. Lorsque les métadonnées d’une table externe sont actualisées, Snowflake analyse les journaux des transactions Delta Lake et détermine quels fichiers Parquet sont à jour. En arrière-plan, l’actualisation effectue des opérations d’ajout et de suppression de fichiers pour maintenir la synchronisation des métadonnées de la table externe.

Pour plus d’informations, y compris des exemples, voir CREATE EXTERNAL TABLE.

Note

Les tables externes qui font référence aux fichiers Delta Lake ne prennent pas en charge les vecteurs de suppression.
Les actualisations automatiques ne sont pas prises en charge pour cette fonctionnalité, car l’ordre des notifications d’événements déclenchées par des opérations de langages de définitions de données (DDL) dans le stockage Cloud n’est pas garanti. Pour enregistrer tout fichier ajouté ou supprimé, exécutez périodiquement une instruction :doc:` ALTER EXTERNALTABLE … REFRESH</sql-reference/sql/alter-external-table>`.

Migration d’une table externe Delta vers Apache Iceberg™¶

Pour migrer une ou plusieurs tables externes qui font référence à un fichier Delta Lake vers Tables Apache Iceberg™, procédez comme suit :

Utilisez la commande SHOW EXTERNAL TABLES pour récupérer la location (zone de préparation externe et le chemin du dossier) pour la ou les tables externes.

Par exemple, la commande suivante renvoie des informations sur les tables externes et les filtres sur des noms comme my_delta_ext_table :
```
SHOW EXTERNAL TABLES LIKE 'my_delta_ext_table';
```
Copy
Créez un volume externe ; spécifiez l’emplacement que vous avez récupéré à l’étape précédente comme STORAGE_BASE_URL.

Pour créer un seul volume externe pour plusieurs tables Delta sous le même emplacement de stockage, définissez l’emplacement actif du volume externe (STORAGE_BASE_URL) comme répertoire racine commun.

Par exemple, considérez les emplacements suivants pour trois tables Delta qui se ramifient à partir du même emplacement de stockage :
- s3://my-bucket/delta-ext-table-1/
- s3://my-bucket/delta-ext-table-2/
- s3://my-bucket/delta-ext-table-3/
Comme indiqué dans l’exemple suivant, spécifiez le compartiment comme STORAGE_BASE_URL lorsque vous créez le volume externe. Plus tard, vous pouvez spécifier le chemin relatif aux fichiers de table (par exemple, delta-ext-table-1/) comme BASE_LOCATION lorsque vous créez une table Iceberg :
```
CREATE OR REPLACE EXTERNAL VOLUME delta_migration_ext_vol
STORAGE_LOCATIONS = (
  (
    NAME = storage_location_1
    STORAGE_PROVIDER = 'S3'
    STORAGE_BASE_URL = 's3://my-bucket/'
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789123:role/my-storage-role' )
);
```
Copy
Créez une intégration de catalogue pour les tables Delta.
Créez une table Iceberg via la commande CREATE ICEBERG TABLE (fichiers Delta dans le stockage d’objets). L”BASE_LOCATION du volume externe doit pointer vers l’emplacement de la table externe existante.

L’exemple suivant crée une table Iceberg basée sur des fichiers de table externes situés dans s3://my-bucket/delta-ext-table-1/ et fait référence au volume externe créé précédemment. Pour déterminer l’emplacement de stockage complet de la table, Snowflake ajoute l’BASE_LOCATION à l’STORAGE_BASE_URL du volume externe :
```
CREATE ICEBERG TABLE my_delta_table_1
  BASE_LOCATION = 'delta-ext-table-1'
  EXTERNAL_VOLUME = 'delta_migration_ext_vol'
  CATALOG = 'delta_catalog_integration';
```
Copy

Détruire la table externe :

DROP EXTERNAL TABLE my_delta_ext_table_1;

Copy

Ajouter ou supprimer des colonnes¶

Pour modifier une table externe existante afin d’ajouter ou de supprimer des colonnes, utilisez la syntaxe ALTER TABLE suivante :

Ajouter des colonnes : ALTER TABLE … ADD COLUMN.
Supprimer les colonnes : ALTER TABLE … DROP COLUMN.

Note

La colonne VALUE par défaut et les pseudo-colonnes METADATA$FILENAME et METADATA$FILE_ROW_NUMBER ne peuvent pas être supprimées.

Pour plus d’informations, consultez l’exemple dans ALTER TABLE.

Protection des tables externes¶

Vous pouvez protéger une table externe à l’aide d’une politique de masquage et d’une politique d’accès aux lignes. Pour plus d’informations, consultez les rubriques suivantes :

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. Lorsque vous exécutez une requête fréquemment ou que votre requête est suffisamment complexe, les vues matérialisées peuvent être significativement plus rapides.

Actualisez les métadonnées de niveau fichier dans toutes les tables externes interrogées de sorte que vos vues matérialisées reflètent l’ensemble actuel de fichiers dans l’emplacement de stockage Cloud référencé.

Vous pouvez actualiser les métadonnées d’une table externe automatiquement à l’aide du service de notification d’événements de votre service de stockage Cloud ou en utilisant des instructions ALTER EXTERNAL TABLE … REFRESH.

Actualisation automatique des métadonnées de tables externes¶

Vous pouvez actualiser automatiquement les métadonnées d’une table externe à l’aide du service de notification d’événements de votre service de stockage Cloud.

L’opération d’actualisation synchronise les métadonnées avec le dernier ensemble de fichiers associés se trouvant dans la zone de préparation externe et dans le chemin. Autrement dit :

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.

Pour plus d’informations, voir Actualisation automatique des tables externes.

Facturation des tables externes¶

Snowflake inclut dans vos charges des frais de gestion des notifications d’événements pour l’actualisation automatique des métadonnées de tables externes. 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. Ces frais généraux apparaissent comme des frais de Snowpipe dans votre relevé de facturation parce que Snowpipe est utilisé pour les notifications d’événements pour les rafraîchissements automatiques des tables externes. Vous pouvez estimer ces frais en interrogeant la fonction PIPE_USAGE_HISTORY ou en examinant la page Vue PIPE_USAGE_HISTORY Account Usage.

En outre, Snowflake facture une petite surcharge de maintenance pour l’actualisation manuelle des métadonnées de table externe (en utilisant ALTER EXTERNAL TABLE … REFRESH). Ces frais généraux sont facturés conformément au modèle standard de facturation des services Cloud, comme toutes les activités similaires dans Snowflake. Les actualisations manuelles des tables externes standard ne sont que des opérations de services dans le Cloud ; toutefois, les actualisations manuelles des tables externes améliorées par Delta Lake reposent sur des ressources de calcul gérées par l’utilisateur (c’est-à-dire un entrepôt virtuel).

Les utilisateurs ayant le rôle ACCOUNTADMIN ou un rôle avec le privilège global MONITOR USAGE peuvent interroger la fonction de table AUTO_REFRESH_REGISTRATION_HISTORY pour récupérer l’historique des fichiers de données enregistrés dans les métadonnées des objets spécifiés et les crédits facturés pour ces opérations.

Vue d’ensemble des workflows de configuration et de chargement¶

Note

Les tables externes ne prennent pas en charge le versionnage du stockage (versionnage S3, versionnage d’objets dans Google Cloud Storage ou versionnage pour Azure Storage).

Workflow Amazon S3¶

Les étapes suivantes fournissent 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, consultez Actualiser automatiquement les tables externes pour Amazon S3 :

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 le compartiment S3) où sont stockés vos fichiers de données.
Créez une table externe (à l’aide de CREATE EXTERNAL TABLE) faisant référence à la zone de préparation nommée.
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.
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.
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.
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.

Flux de travail Google Cloud Storage¶

Configurer un abonnement Google Pub/Sub pour les événements GCS.
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 tiers de mise en file d’attente de messages dans le Cloud, comme un Pub/Sub.
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 le compartiment GCS) où sont stockés vos fichiers de données.
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.
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 Pub/Sub déclenchent automatiquement l’actualisation des métadonnées.
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.

Flux de travail Microsoft Azure¶

Les étapes suivantes fournissent 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 Azure. Pour des instructions complètes, consultez Actualiser automatiquement les tables externes pour le Stockage Blob Azure :

Configurez un abonnement à Event Grid pour les événements Azure Storage.
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.
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 le conteneur Azure) où sont stockés vos fichiers de données.
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.
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.
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.

Interrogation de tables externes¶

Interrogez les tables externes comme vous le feriez avec des tables standard.

Snowflake ne tient pas compte des résultats de la requête pour les enregistrements contenant des données UTF-8 non valides. Après avoir rencontré des données non valides, Snowflake continue d’analyser le fichier sans renvoyer de message d’erreur.

Pour éviter des enregistrements manquants dans les résultats de votre requête en raison de données UTF-8 non valides, indiquez REPLACE_INVALID_CHARACTERS = TRUE pour le format de fichier. Cela permet de remplacer tout caractère UTF-8 non valide par le caractère de remplacement Unicode (�) lorsque vous interrogez la table.

Pour les fichiers Parquet, vous pouvez également définir BINARY_AS_TEXT = FALSE pour votre format de fichier afin que Snowflake interprète les colonnes sans type de données logique défini comme des données binaires au lieu de les interpréter comme du texte UTF-8.

Filtrage des enregistrements dans les fichiers Parquet¶

Pour utiliser les statistiques des groupes de lignes afin d’élaguer les données dans les fichiers Parquet, vous pouvez inclure soit des colonnes de partition, soit des colonnes régulières, soit les deux dans une clause WHERE. Les limitations suivantes s’appliquent :

La clause ne peut pas inclure de colonnes VARIANT.
La clause ne peut inclure qu’un ou plusieurs des opérateurs de comparaison suivants :
- =
- >
- <
La clause ne peut inclure qu’un ou plusieurs opérateurs logiques/booléens, ainsi que la fonction STARTSWITH SQL.

En outre, les requêtes sous la forme « valeur :<path>::<data type> » (ou l’équivalent de la fonction GET/ GET_PATH , :) utilisent le scanner vectorisé. Les requêtes sous la forme "value" ou simplement « valeur :<path> » sont traitées par le scanner non vectorisé. Convertissez l’ensemble des données de fuseau horaire en fuseau horaire standard en utilisant la fonction CONVERT_TIMEZONE pour les requêtes qui utilisent le scanner vectorisé.

Vous pouvez obtenir de meilleurs résultats d’élagage lorsque les fichiers sont triés par une clé incluse dans un filtre de requête et s’il y a plusieurs groupes de lignes dans les fichiers.

Le tableau suivant présente des structures de requêtes similaires qui montrent les comportements de cette section, où et est une table externe et c1, c2 et c3 sont des colonnes virtuelles :

Avec optimisation	Sans optimisation
`SELECT c1, c2, c3 FROM et;`	`SELECT value:c1, c2, c3 FROM et;`
`SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';` `SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';`	`SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';`

Avec optimisation

Sans optimisation

SELECT c1, c2, c3 FROM et;

SELECT value:c1, c2, c3 FROM et;

SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';

Résultats de requête persistants¶

Comme pour les tables, les résultats des requêtes pour les tables externes persistent pendant 24 heures. Au cours de cette période de 24 heures, les opérations suivantes invalident et purgent le cache des résultats des requêtes pour les tables externes :

Toute opération DDL qui modifie la définition de la table externe. Cela inclut la modification explicite de la définition de la table externe (en utilisant ALTER EXTERNALTABLE) ou la recréation de la table externe (en utilisant :doc:` CREATE OR REPLACE EXTERNAL TABLE</sql-reference/sql/create-external-table>`).
Changements dans l’ensemble des fichiers du stockage Cloud qui sont enregistrés dans les métadonnées de la table externe. Les opérations d’actualisation automatique à l’aide du service de notification d’événements pour le lieu de stockage ou les opérations d’actualisation manuelle (à l’aide de ALTER EXTERNAL TABLE … REFRESH) invalident le cache des résultats.

Note

Les modifications apportées aux fichiers référencés dans le stockage Cloud n’invalident pas le cache des résultats de la requête dans les circonstances suivantes, ce qui entraîne des résultats de requête obsolètes :

L’opération d’actualisation automatique est désactivée (c’est-à-dire AUTO_REFRESH = FALSE) ou n’est pas configurée correctement.
Les métadonnées de la table externe ne sont pas actualisées manuellement.

Exemple : Suppression des anciens fichiers en zone de préparation des métadonnées de tables externes¶

Les étapes suivantes fournissent un exemple de la manière dont vous pouvez utiliser une instruction ALTER EXTERNAL TABLE … REMOVE FILES pour supprimer les anciens fichiers en zone de préparation des métadonnées dans une table externe. La procédure stockée supprime les fichiers des métadonnées sur la base de leur dernière date de modification dans la zone de préparation :

Créez la procédure stockée en utilisant une instruction CREATE PROCEDURE :

CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  // 1. Get the relative path of the external table
  // 2. Find all files registered before the specified time period
  // 3. Remove the files


  var resultSet1 = snowflake.execute({ sqlText:
    `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
  });
  resultSet1.next();
  var relPath = resultSet1.getColumnValue(1);


  var resultSet2 = snowflake.execute({ sqlText:
    `select file_name
     from table(information_schema.EXTERNAL_TABLE_FILES (
         TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
     where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
  });

  var fileNames = [];
  while (resultSet2.next())
  {
    fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
  }

  if (fileNames.length == 0)
  {
    return 'nothing to do';
  }


  var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;

  var resultSet3 = snowflake.execute({ sqlText: alterCommand });

  var results = [];
  while (resultSet3.next())
  {
    results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
  }

  return results.length + ' files: \n' + results.join('\n');

  $$;

  CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  var resultSet = snowflake.execute({ sqlText:
    `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
  });

  resultSet.next();
  var location = resultSet.getColumnValue(10);

  var relPath = location.split('/').slice(3).join('/');
  return relPath.endsWith("/") ? relPath : relPath + "/";

  $$;

Copy

Appelez la procédure stockée :

-- Remove all files from the exttable external table metadata:
call remove_old_files('exttable', 0);

-- Remove files staged longer than 90 days ago from the exttable external table metadata:
call remove_old_files('exttable', 90);

Copy

Vous pouvez également créer une tâche en utilisant CREATE TASK pour appeler périodiquement la procédure stockée afin de supprimer les anciens fichiers des métadonnées de la table externe.

Intégration d’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 metastores et les transmet à Snowflake pour que les tables externes restent synchronisées avec le metastore Hive. Grâce à cette capacité, les utilisateurs peuvent gérer leurs données dans Hive tout en les interrogeant à partir de Snowflake.

Pour obtenir des instructions, voir Intégrer 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¶

Le Schéma d’information de Snowflake inclut des vues et des fonctions de tables que vous pouvez interroger pour récupérer des informations sur vos tables externes et leurs fichiers de données placés en zone de préparation.

Vue¶

Vue EXTERNAL_TABLES: Affiche des informations sur les tables externes de la base de données spécifiée (ou actuelle).

Fonctions de table¶

AUTO_REFRESH_REGISTRATION_HISTORY: Récupérez l’historique des fichiers de données enregistrés dans les métadonnées des objets spécifiés et les crédits facturés pour ces opérations.
EXTERNAL_TABLE_FILES: Récupérez des informations sur les fichiers de données en zone de préparation 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.