- Catégories :
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¶
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 formenom_basededonnées.nom_schéma
ounom_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èsa
et que l’emplacement de la table externe inclut le chemin d’accèsb
, la table externe lit les fichiers stockés dans lazone 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
etTYPE
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êmenom_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
NOT
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¶
COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
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 deAUTO
.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 par0x
). 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 deNONE
.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 par0x
). 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 deNONE
.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¶
COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
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 deAUTO
.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¶
COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
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 deAUTO
.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¶
N/A
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¶
N/A
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 externemystage
. 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
convertitYYYY/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.