Catégories :: Table, vue et séquence DDL

CREATE EXTERNAL TABLE¶

Crée une nouvelle table externe dans le schéma actuel/spécifié ou remplace une table externe existante. Lorsqu’elle est interrogée, une table externe lit les données d’un ensemble d’un ou de plusieurs fichiers d’une zone de préparation externe spécifiée et les génère dans une seule colonne VARIANT.

Des colonnes supplémentaires peuvent être définies, chaque définition de colonne comprenant un nom, un type de données et, éventuellement, si la colonne nécessite une valeur (NOT NULL) ou comporte des contraintes d’intégrité référentielle (clé principale, clé étrangère, etc.). Voir les notes d’utilisation pour plus d’informations.

Voir aussi :: ALTER EXTERNAL TABLE , DROP EXTERNAL TABLE , SHOW EXTERNAL TABLES

Dans ce chapitre :

Syntaxe
Paramètres requis
Paramètres facultatifs
- Paramètres de partitionnement
Paramètres du fournisseur Cloud (cloudProviderParams)
Options de type de format (formatTypeOptions)
Notes sur l’utilisation
Exemples

Syntaxe¶

CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  [ REFRESH_ON_CREATE =  { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ PATTERN = '<regex_pattern>' ]
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ AWS_SNS_TOPIC = <string> ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]

Où :

inlineConstraint ::=
  [ NOT NULL ]
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) } }
  [ <constraint_properties> ]

Pour plus de détails sur les contraintes en ligne, voir CREATE | ALTER TABLE … CONSTRAINT.

cloudProviderParams (for Microsoft Azure) ::=
  [ INTEGRATION = '<integration_name>' ]

externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>]

formatTypeOptions ::=
-- If FILE_FORMAT = ( TYPE = CSV ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     RECORD_DELIMITER = '<character>' | NONE
     FIELD_DELIMITER = '<character>' | NONE
     SKIP_HEADER = <integer>
     SKIP_BLANK_LINES = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = JSON ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If FILE_FORMAT = ( TYPE = AVRO ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If FILE_FORMAT = ( TYPE = PARQUET ... )
     COMPRESSION = AUTO | SNAPPY | NONE

Paramètres requis¶

nom_table

Chaîne qui indique l’identificateur (c’est-à-dire le nom) de la table ; doit être unique pour le schéma dans lequel la table est créée.

De plus, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (p. ex. "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus de détails, voir Exigences relatives à l’identificateur.

[ WITH ] LOCATION =

Spécifie la zone de préparation externe où sont stockés les fichiers contenant les données à lire :

@[espace_noms.]nom_zone_de_préparation_externe[/chemin]

Les fichiers se trouvent dans la zone de préparation externe nommée spécifiée.

Où :

espace_noms est la base de données et/ou le schéma dans lequel réside la zone de préparation externe, sous la forme nom_basededonnées.nom_schéma ou nom_schéma. Il est facultatif si une base de données et un schéma sont actuellement utilisés dans la session utilisateur. Dans le cas contraire, il est nécessaire.

chemin est un chemin facultatif respectant la casse pour les fichiers dans l’emplacement de stockage Cloud (c’est-à-dire que les fichiers ont des noms qui commencent par une chaîne en commun) qui limite l’ensemble de fichiers à charger. Les chemins sont appelés préfixes ou dossiers selon les services de stockage Cloud.

Notez que la table externe ajoute ce chemin à tout chemin spécifié dans la définition de la zone de préparation. Pour afficher la définition de la zone de préparation, exécutez DESC STAGE nom_zone_préparation et vérifiez la valeur de la propriété url. Par exemple, si l’URL de la zone de préparation inclut le chemin d’accès a et que l’emplacement de la table externe inclut le chemin d’accès b , la table externe lit les fichiers stockés dans la zone de préparation/a/b.

FILE_FORMAT = ( FORMAT_NAME = 'nom_format_fichier' ) ou . FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ] )

Chaîne (constante) spécifiant le format de fichier :

FORMAT_NAME = nom_format_fichier
Spécifie un format de fichier nommé existant qui décrit les fichiers de données mis en zone de préparation à analyser. Le format de fichier nommé détermine le type de format (CSV, JSON, etc.), ainsi que toute autre option de format, pour les fichiers de données.

TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]
Spécifie le type de format des fichiers de données mis en zone de préparation à analyser lors de l’interrogation de la table externe.

Si un type de format de fichier est spécifié, des options supplémentaires spécifiques au format peuvent être spécifiées. Pour plus d’informations, voir les Options de type de format (dans cette rubrique).

Les options de format de fichier peuvent être configurées au niveau de la table externe ou de la zone de préparation. Tous les paramètres spécifiés au niveau de la table externe sont prioritaires. Tous les paramètres non spécifiés à l’un ou l’autre niveau prennent les valeurs par défaut.

Par défaut : TYPE = CSV.

Important

La table externe n’hérite pas du format de fichier, le cas échéant, dans la définition de la zone de préparation. Vous devez spécifier explicitement les options de format de fichier pour la table externe à l’aide du paramètre FILE_FORMAT.

Note

FORMAT_NAME et TYPE s’excluent mutuellement ; pour éviter tout comportement indésirable, vous ne devez spécifier que l’une ou l’autre lors de la création d’une zone de préparation externe.

Paramètres facultatifs¶

nom_colonne

Chaîne qui indique l’identificateur de colonne (c’est-à-dire le nom). Toutes les exigences relatives aux identificateurs de table s’appliquent également aux identificateurs de colonne.

Les colonnes de table externes sont des colonnes virtuelles définies à l’aide d’une expression explicite.

Pour plus de détails, voir Exigences relatives à l’identificateur.

type_col

Chaîne (constante) qui spécifie le type de données pour la colonne. Le type de données doit correspondre au résultat de expr pour la colonne.

Pour plus de détails sur les types de données qui peuvent être spécifiés pour les colonnes de la table, voir Types de données.

expr

Chaîne qui spécifie l’expression de la colonne. Lorsqu’elle est interrogée, la colonne renvoie les résultats dérivés de cette expression.

CONSTRAINT ...

Chaîne qui définit une contrainte en ligne ou hors ligne pour la ou les colonnes spécifiées dans la table.

Pour plus d’informations sur la syntaxe, voir CREATE | ALTER TABLE … CONSTRAINT. Pour plus d’informations sur les contraintes, voir Contraintes.

REFRESH_ON_CREATE = TRUE | FALSE

Indique s’il faut actualiser automatiquement les métadonnées de la table externe une fois, immédiatement après la création de la table externe. L’actualisation des métadonnées de la table externe synchronise les métadonnées avec la liste actuelle des fichiers de données dans le chemin de zone de préparation spécifié. Cette action est requise pour que les métadonnées enregistrent tout fichier de données existant dans la zone de préparation externe nommée spécifiée dans le paramètre [ WITH ] LOCATION =.

TRUE: Snowflake actualise automatiquement les métadonnées de la table externe une fois, après leur création.
FALSE: Snowflake n’actualise pas automatiquement les métadonnées de la table externe. Pour enregistrer des fichiers de données existants dans la zone de préparation, vous devez actualiser manuellement les métadonnées de la table externe une fois à l’aide de ALTER EXTERNAL TABLE … REFRESH.

Par défaut : TRUE

AUTO_REFRESH = TRUE | FALSE

Spécifie si Snowflake doit activer le déclenchement des actualisations automatiques des métadonnées de la table externe lorsque de nouveaux fichiers de données ou des fichiers de données mis à jour sont disponibles dans la zone de préparation externe nommée spécifiée dans le paramètre [ WITH ] LOCATION =.

Note

Vous devez configurer une notification d’événement pour votre emplacement de stockage (Amazon S3 ou Microsoft Azure) pour notifier Snowflake lorsque des données nouvelles ou mises à jour sont disponibles pour être lues dans les métadonnées de la table externe. Pour plus d’informations, voir Actualisation automatique des tables externes Amazon S3 (S3) ou Actualisation automatique des tables externes pour Azure Blob Storage (Azure).
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).
Lorsqu’une table externe est créée, ses métadonnées sont actualisées automatiquement une fois, sauf si REFRESH_ON_CREATE = FALSE.

TRUE: Snowflake permet de déclencher des actualisations automatiques des métadonnées de la table externe.
FALSE: Snowflake n’active pas le déclenchement d’actualisations automatiques des métadonnées de la table externe. Vous devez actualiser 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.

Par défaut : TRUE

PATTERN = 'motif_regex'

Chaîne de motifs d’expressions régulières, délimitée par des guillemets simples, spécifiant les noms de fichiers et/ou les chemins sur la zone de préparation externe à associer.

Astuce

Pour de meilleures performances, évitez d’appliquer des motifs qui filtrent un grand nombre de fichiers.

Note

Actuellement, ce paramètre n’est pris en charge que lorsque les métadonnées de table externe sont actualisées manuellement en exécutant une instruction ALTER EXTERNAL TABLE ... REFRESH pour enregistrer des fichiers. Le paramètre n’est pas pris en charge lorsque les métadonnées sont actualisées à l’aide de notifications d’événement.

AWS_SNS_TOPIC = chaîne

Obligatoire uniquement lors de la configuration de AUTO_REFRESH pour les zones de préparation Amazon S3 à l’aide d’Amazon Simple Notification Service (SNS). Spécifie le nom Amazon Resource Name (ARN) pour la rubrique SNS de votre compartiment S3. L’instruction CREATE EXTERNAL TABLE souscrit la file d’attente Amazon Simple Queue Service (SQS) au sujet SNS spécifié. Les notifications d’événements via le sujet SNS déclenchent l’actualisation des métadonnées. Pour plus d’informations, voir Actualisation automatique des tables externes Amazon S3.

COPY GRANTS

Spécifie de conserver les autorisations d’accès de la table d’origine lorsqu’une table externe est recréée à l’aide de la variante CREATE OR REPLACE TABLE. Ce paramètre copie toutes les autorisations, excepté OWNERSHIP, de la table existante vers la nouvelle table. Par défaut, le rôle qui exécute la commande CREATE EXTERNAL TABLE possède la nouvelle table externe.

Remarque :

L’opération de copie des accords s’effectue atomiquement dans la commande CREATE EXTERNAL TABLE (c’est-à-dire dans la même transaction).

COMMENT = 'litéral_chaine'

Chaîne (littéral) qui spécifie un commentaire pour la table externe.

Par défaut : aucune valeur

Paramètres de partitionnement¶

Utilisez ces paramètres pour partitionner votre table externe.

nom_col_part type_col AS expr_part

Nécessaire pour partitionner les données dans une table externe

Spécifie une ou plusieurs colonnes de partition dans la table externe.

Une colonne de partition doit être évaluée en tant qu’expression qui analyse les informations de chemin d’accès et/ou de nom de fichier de la pseudocolonne METADATA$FILENAME. Les colonnes de partition optimisent les performances des requêtes en supprimant les fichiers de données qu’il n’est pas nécessaire d’analyser (par exemple, en partitionnant la table externe). 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.

`nom_col_part`	Chaîne spécifiant l’identificateur de la colonne de partition (c’est-à-dire son nom). Toutes les exigences relatives aux identificateurs de table s’appliquent également aux identificateurs de colonne.
`type_col`	Chaîne (constante) qui spécifie le type de données pour la colonne. Le type de données doit correspondre au résultat de `expr_part` pour la colonne.
`expr_part`	Chaîne qui spécifie l’expression de la colonne. L’expression doit inclure la pseudocolonne METADATA$FILENAME.

Les tables externes prennent actuellement en charge le sous-ensemble de fonctions suivant dans les expressions de partition :

Liste des fonctions prises en charge :

=, <>, >, >=, <, <=
||
+, -
- (nier)
*
AND, OR
ARRAY_CONSTRUCT
CASE
CAST , ::
CONCAT , ||
ENDSWITH
IS [ NOT ] NULL
IFF
IFNULL
[ NOT ] IN
LOWER
NOT
NULLIF
NVL2
SPLIT_PART
STARTSWITH
SUBSTR , SUBSTRING
UPPER
ZEROIFNULL

Après avoir défini les colonnes de partition pour la table, identifiez ces colonnes à l’aide de la clause PARTITION BY.

[ PARTITION BY ( nom_colonne_part [, nom_colonne_part ... ] ) ]

Spécifie les colonnes de partition à évaluer pour la table externe.

Utilisation

Lorsque vous interrogez une table externe, incluez une ou plusieurs colonnes de partition dans une clause WHERE , par exemple :

... WHERE nom_colonne_part = 'valeur_filtre'

Snowflake filtre les colonnes de partition pour limiter l’ensemble des fichiers de données à analyser. Notez que toutes les lignes de ces fichiers sont analysées. Si une clause WHERE inclut des colonnes non-partitionnées, ces filtres sont évalués une fois que les fichiers de données ont été filtrés.

Une pratique courante consiste à partitionner les fichiers de données en fonction d’incréments de temps ; ou, si les fichiers de données sont stockés à partir de plusieurs sources, à partitionner avec un identificateur de source de données et une date ou un horodatage.

Paramètres du fournisseur Cloud (`cloudProviderParams`)¶

Microsoft Azure

INTEGRATION = nom_intégration
Spécifie le nom de l’intégration de notification utilisée pour actualiser automatiquement les métadonnées de la table externe à l’aide des notifications Azure Event Grid. 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.

Ce paramètre est requis pour activer les opérations d’actualisation automatique pour la table externe. Pour obtenir des instructions sur la configuration de la fonctionnalité d’actualisation automatique, voir Actualisation automatique des tables externes pour Azure Blob Storage.

Options de type de format (`formatTypeOptions`)¶

Les options de type de format sont utilisées pour charger des données dans et décharger des données de tables.

En fonction du type de format de fichier spécifié (FILE_FORMAT = ( TYPE = ... )), vous pouvez inclure une ou plusieurs des options suivantes, spécifiques au format (séparées par des espaces, des virgules ou de nouvelles lignes) :

TYPE = CSV¶

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les fichiers de données à charger. Snowflake utilise cette option pour détecter comment les fichiers de données déjà compressés ont été compressés afin que les données compressées dans les fichiers puissent être extraites en vue d’un chargement.

Valeurs prises en charge	Remarques
`AUTO`	Algorithme de compression détecté automatiquement, sauf pour les fichiers compressés par Brotli, qui ne peuvent actuellement pas être détectés automatiquement. Si vous chargez des fichiers compressés via Brotli, utilisez explicitement `BROTLI` au lieu de `AUTO`.
`GZIP`
`BZ2`
`BROTLI`	Doit être spécifié lors du chargement des fichiers compressés Brotli.
`ZSTD`	Zstandard v0.8 (et supérieur) est pris en charge.
`DEFLATE`	Fichiers compressés Deflate (avec en-tête zlib, RFC1950).
`RAW_DEFLATE`	Fichiers bruts compressés Deflate (sans en-tête, RFC1951).
`NONE`	Les fichiers de données à charger n’ont pas été compressés.

RECORD_DELIMITER = 'caractère' | NONE

Un ou plusieurs caractères à un octet ou à plusieurs octets qui séparent les enregistrements dans un fichier d’entrée.

Accepte les séquences d’échappement courantes, les valeurs octales (préfixées par \\) ou les valeurs hexadécimales (préfixées par 0x). Par exemple, pour les enregistrements délimités par le caractère thorn (Þ), spécifiez la valeur octale (\\336) ou hexadécimale (0xDE). Accepte également une valeur de NONE.

Le délimiteur spécifié doit être un caractère UTF-8 valide et non une séquence aléatoire d’octets.

Les délimiteurs à plusieurs caractères sont également pris en charge ; cependant, le délimiteur pour RECORD_DELIMITER ou FIELD_DELIMITER ne peut pas être une sous-chaîne du délimiteur pour l’autre option de format de fichier (par exemple FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb'). Le délimiteur est limité à un maximum de 20 caractères.

Par défaut : caractère de nouvelle ligne. Notez que « nouvelle ligne » fait sens, de sorte que \r\n sera compris comme une nouvelle ligne pour les fichiers sur une plate-forme Windows.

FIELD_DELIMITER = 'caractère' | NONE

Un ou plusieurs caractères à un octet ou à plusieurs octets qui séparent les champs dans un fichier d’entrée.

Accepte les séquences d’échappement courantes, les valeurs octales (préfixées par \\) ou les valeurs hexadécimales (préfixées par 0x). Par exemple, pour les champs délimités par le caractère thorn (Þ), spécifiez la valeur octale (\\336) ou hexadécimale (0xDE). Accepte également une valeur de NONE.

Le délimiteur spécifié doit être un caractère UTF-8 valide et non une séquence aléatoire d’octets.

Les délimiteurs à plusieurs caractères sont également pris en charge ; cependant, le délimiteur pour RECORD_DELIMITER ou FIELD_DELIMITER ne peut pas être une sous-chaîne du délimiteur pour l’autre option de format de fichier (par exemple FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb'). Le délimiteur est limité à un maximum de 20 caractères.

Valeur par défaut : virgule (,)

SKIP_HEADER = entier

Nombre de lignes au début du fichier à ignorer.

Notez que SKIP_HEADER n’utilise pas les valeurs RECORD_DELIMITER ou FIELD_DELIMITER pour déterminer la nature d’une ligne d’en-tête. Au lieu de cela, il ignore simplement le nombre spécifié de lignes délimitées par CRLF (retour chariot, saut de ligne) dans le fichier. RECORD_DELIMITER et FIELD_DELIMITER sont ensuite utilisés pour déterminer les lignes de données à charger.

Par défaut : 0

SKIP_BLANK_LINES = TRUE | FALSE

Utilisation: Chargement des données uniquement
Définition: Booléen qui indique d’ignorer toutes les lignes vides rencontrées dans les fichiers de données ; sinon, les lignes vides produisent une erreur de fin d’enregistrement (comportement par défaut).

Par défaut : FALSE

TYPE = JSON¶

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les fichiers de données à charger. Snowflake utilise cette option pour détecter comment les fichiers de données déjà compressés ont été compressés afin que les données compressées dans les fichiers puissent être extraites en vue d’un chargement.

Valeurs prises en charge	Remarques
`AUTO`	Algorithme de compression détecté automatiquement, sauf pour les fichiers compressés par Brotli, qui ne peuvent actuellement pas être détectés automatiquement. Si vous chargez des fichiers compressés via Brotli, utilisez explicitement `BROTLI` au lieu de `AUTO`.
`GZIP`
`BZ2`
`BROTLI`
`ZSTD`
`DEFLATE`	Fichiers compressés Deflate (avec en-tête zlib, RFC1950).
`RAW_DEFLATE`	Fichiers bruts compressés Deflate (sans en-tête, RFC1951).
`NONE`	Indique que les fichiers de chargement des données n’ont pas été compressés.

Par défaut : AUTO

TYPE = AVRO¶

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les fichiers de données à charger. Snowflake utilise cette option pour détecter comment les fichiers de données déjà compressés ont été compressés afin que les données compressées dans les fichiers puissent être extraites en vue d’un chargement.

Valeurs prises en charge	Remarques
`AUTO`	Algorithme de compression détecté automatiquement, sauf pour les fichiers compressés par Brotli, qui ne peuvent actuellement pas être détectés automatiquement. Si vous chargez des fichiers compressés via Brotli, utilisez explicitement `BROTLI` au lieu de `AUTO`.
`GZIP`
`BZ2`
`BROTLI`
`ZSTD`
`DEFLATE`	Fichiers compressés Deflate (avec en-tête zlib, RFC1950).
`RAW_DEFLATE`	Fichiers bruts compressés Deflate (sans en-tête, RFC1951).
`NONE`	Les fichiers de données à charger n’ont pas été compressés.

Par défaut : AUTO

TYPE = ORC¶

Non pris en charge.

TYPE = PARQUET¶

COMPRESSION = AUTO | SNAPPY | NONE

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les colonnes des fichiers Parquet.

Valeurs prises en charge	Remarques
`AUTO`	Algorithme de compression détecté automatiquement. Prend en charge les algorithmes de compression suivants : Brotli, gzip, Lempel–Ziv–Oberhumer (LZO), LZ4, Snappy ou Zstandard v0.8 (et versions ultérieures).
`SNAPPY`
`NONE`	Les fichiers de données à charger n’ont pas été compressés.

Par défaut : AUTO

TYPE = XML¶

Non pris en charge.

Notes sur l’utilisation¶

Les tables externes prennent en charge uniquement les zones de préparation externes (c.-à-d. S3, Azure ou GCS). Les zones de préparation internes (c.-à-d. Snowflake) ne sont pas prises en charge.
Chaque table externe contient une colonne nommée VALUE de type VARIANT. Des colonnes supplémentaires peuvent être spécifiées. Toutes les colonnes sont traitées comme des colonnes virtuelles.
- La colonne VALUE structure les lignes d’un fichier de données CSV en tant qu’objets JSON avec des éléments identifiés par la position de la colonne, par exemple {c1: col_1_value, c2: col_2_value, c3: col_3_value ...}.
Snowflake n’impose pas de contraintes d’intégrité sur les tables externes. En particulier, contrairement aux tables normales, Snowflake n’applique pas les contraintes NOT NULL.
Les tables externes incluent la colonne de métadonnées suivante :
- METADATA$FILENAME : Nom de chaque fichier de données mis en zone de préparation inclus dans la table externe. Inclut le chemin d’accès au fichier de données dans la zone de préparation.

Les éléments suivants ne sont pas pris en charge pour les tables externes :
- Clés de clustering
- Clonage
- Données au format XML
Time Travel n’est pas pris en charge pour les tables externes.

Exemples¶

Table externe simple¶

Créez une zone de préparation externe nommée mystage pour l’emplacement de stockage où est stocké un ensemble de fichiers de données Parquet. Pour plus d’informations, voir CREATE STAGE.

Amazon S3
Créez une zone de préparation externe en utilisant un compartiment S3 privé/protégé nommé mybucket avec un chemin de dossier nommé files :
CREATE OR REPLACE STAGE mystage URL='s3://mybucket/files/' .. ;
Google Cloud Storage
Créez une zone de préparation externe à l’aide d’un conteneur Google Cloud Storage nommé mybucket avec un chemin de dossier nommé files :
CREATE OR REPLACE STAGE mystage URL='gcs://mybucket/files' .. ;
Microsoft Azure
Créez une zone de préparation externe à l’aide d’un compte de stockage Azure nommé myaccount et un conteneur nommé mycontainer avec un chemin de dossier nommé files :
CREATE OR REPLACE STAGE mystage URL='azure://myaccount.blob.core.windows.net/mycontainer/files' .. ;
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.
Créez une table externe nommée ext_twitter_feed qui fait référence aux fichiers Parquet de la zone de préparation externe mystage. La référence de la zone de préparation inclut un chemin de dossier nommé daily. La table externe ajoute ce chemin à la définition de la zone de préparation. En d’autres termes, la table externe fait référence aux fichiers de données dans @mystage/files/daily.

La commande SQL spécifie Parquet comme type de format de fichier. En outre, la correspondance de motifs de fichier est appliquée pour inclure uniquement les fichiers Parquet dont les noms incluent la chaîne sales :

Amazon S3
CREATE OR REPLACE EXTERNAL TABLE ext_twitter_feed WITH LOCATION = @mystage/daily/ AUTO_REFRESH = true FILE_FORMAT = (TYPE = PARQUET) PATTERN='.*sales.*[.]parquet';
Google Cloud Storage
CREATE OR REPLACE EXTERNAL TABLE ext_twitter_feed WITH LOCATION = @mystage/daily/ FILE_FORMAT = (TYPE = PARQUET) PATTERN='.*sales.*[.]parquet';
Microsoft Azure
CREATE OR REPLACE EXTERNAL TABLE ext_twitter_feed INTEGRATION = 'MY_AZURE_INT' WITH LOCATION = @mystage/daily/ AUTO_REFRESH = true FILE_FORMAT = (TYPE = PARQUET) PATTERN='.*sales.*[.]parquet';

Actualisez les métadonnées de la table externe :

ALTER EXTERNAL TABLE ext_twitter_feed REFRESH;

Table externe partitionnée¶

Créez une table externe partitionnée qui partitionne les données en fonction de détails logiques et granulaires dans le chemin de la zone de préparation.

Dans l’exemple suivant, les fichiers de données sont organisés dans un stockage dans le Cloud avec la structure suivante : logs/YYYY/MM/DD/HH24, par exemple :

logs/2018/08/05/0524/
logs/2018/08/27/1408/

Créez une zone de préparation externe nommée exttable_part_stage pour l’emplacement de stockage où sont stockés les fichiers de données. Pour plus d’informations, voir CREATE STAGE.

La définition de la zone de préparation inclut le chemin /files/logs/ :

Amazon S3
CREATE STAGE exttable_part_stage URL='s3://mybucket/files/logs/' .. ;
Google Cloud Storage
CREATE STAGE exttable_part_stage URL='gcs://mybucket/files/logs/' .. ;
Microsoft Azure
CREATE STAGE exttable_part_stage URL='azure://mycontainer/files/logs/' .. ;

Interrogez la pseudocolonne METADATA$FILENAME dans les données mises en zone de préparation. Utilisez les résultats pour développer vos colonnes de partition :

SELECT metadata$filename FROM @exttable_part_stage/;

+----------------------------------------+
| METADATA$FILENAME                      |
|----------------------------------------|
| files/logs/2018/08/05/0524/log.parquet |
| files/logs/2018/08/27/1408/log.parquet |
+----------------------------------------+

Créez la table externe partitionnée.

La colonne de partition date_part convertit YYYY/MM/DD dans la pseudocolonne METADATA$FILENAME en tant que date utilisant TO_DATE , DATE.

La commande SQL spécifie également Parquet comme type de format de fichier :

Amazon S3

CREATE EXTERNAL TABLE exttable_part(
 date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
   || '/' || SPLIT_PART(metadata$filename, '/', 4)
   || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
 timestamp bigint AS (value:timestamp::bigint),
 col2 varchar AS (value:col2::varchar))
 PARTITION BY (date_part)
 LOCATION=@exttable_part_stage/logs/
 AUTO_REFRESH = true
 FILE_FORMAT = (TYPE = PARQUET);

Google Cloud Storage

CREATE EXTERNAL TABLE exttable_part(
  date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
    || '/' || SPLIT_PART(metadata$filename, '/', 4)
    || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
  timestamp bigint AS (value:timestamp::bigint),
  col2 varchar AS (value:col2::varchar))
  PARTITION BY (date_part)
  LOCATION=@exttable_part_stage/logs/
  AUTO_REFRESH = true
  FILE_FORMAT = (TYPE = PARQUET);

Microsoft Azure

CREATE EXTERNAL TABLE exttable_part(
  date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
    || '/' || SPLIT_PART(metadata$filename, '/', 4)
    || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
  timestamp bigint AS (value:timestamp::bigint),
  col2 varchar AS (value:col2::varchar))
  PARTITION BY (date_part)
  INTEGRATION = 'MY_INT'
  LOCATION=@exttable_part_stage/logs/
  AUTO_REFRESH = true
  FILE_FORMAT = (TYPE = PARQUET);

Actualisez les métadonnées de la table externe :
```
ALTER EXTERNAL TABLE exttable_part REFRESH;
```

Lorsque vous interrogez la table externe, filtrez les données en fonction de la colonne de partition à l’aide d’une clause WHERE :

SELECT timestamp, col2 FROM exttable_part WHERE date_part = to_date('08/05/2018');

Table externe simple : AUTO_REFRESH avec Amazon SNS¶

Créez une table externe non partitionnée dans le schéma actuel dont les métadonnées sont actualisées automatiquement lorsqu’elles sont déclenchées par des notifications d’événements reçues d’Amazon SNS :

CREATE OR REPLACE EXTERNAL TABLE ext_table
     WITH LOCATION = @mystage/path1/
     FILE_FORMAT = (TYPE = JSON)
     AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';

Vue matérialisée sur une table externe¶

Créez une vue matérialisée basée sur une sous-requête des colonnes de la table externe créée dans l’exemple Table externe partitionnée :

CREATE MATERIALIZED VIEW exttable_part_mv
  AS
  SELECT col2 FROM exttable_part;

Pour obtenir une syntaxe générale, des notes d’utilisation et d’autres exemples relatifs à cette commande SQL, voir CREATE MATERIALIZED VIEW.

CREATE EXTERNAL TABLE¶

Syntaxe¶

Paramètres requis¶

Paramètres facultatifs¶

Paramètres de partitionnement¶

Paramètres du fournisseur Cloud (cloudProviderParams)¶

Options de type de format (formatTypeOptions)¶

TYPE = CSV¶

TYPE = JSON¶

TYPE = AVRO¶

TYPE = ORC¶

TYPE = PARQUET¶

TYPE = XML¶

Notes sur l’utilisation¶

Exemples¶

Table externe simple¶

Table externe partitionnée¶

Table externe simple : AUTO_REFRESH avec Amazon SNS¶

Vue matérialisée sur une table externe¶

Paramètres du fournisseur Cloud (`cloudProviderParams`)¶

Options de type de format (`formatTypeOptions`)¶