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 , DESCRIBE EXTERNAL TABLE

Dans ce chapitre :

Syntaxe¶

-- Partitions computed from expressions
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 ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

-- Partitions added and removed manually
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
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

-- Delta Lake
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
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ TABLE_FORMAT = DELTA ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

Où :

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

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

cloudProviderParams (for Google Cloud Storage) ::=
  [ INTEGRATION = '<integration_name>' ]

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
     ESCAPE_UNENCLOSED_FIELD = '<character>' | NONE
     TRIM_SPACE = TRUE | FALSE
     FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | NONE
     NULL_IF = ( '<string1>' [ , '<string2>' , ... ] )
     EMPTY_FIELD_AS_NULL = TRUE | FALSE
     ENCODING = '<string>'
-- If FILE_FORMAT = ( TYPE = JSON ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     ALLOW_DUPLICATE = TRUE | FALSE
     STRIP_OUTER_ARRAY = TRUE | FALSE
     STRIP_NULL_VALUES = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = AVRO ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If FILE_FORMAT = ( TYPE = ORC ... )
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '<string>' [ , '<string>' ... ]
-- If FILE_FORMAT = ( TYPE = PARQUET ... )
     COMPRESSION = AUTO | SNAPPY | NONE
     BINARY_AS_TEXT = TRUE | FALSE

Syntaxe des variantes¶

CREATE EXTERNAL TABLE … USING TEMPLATE¶

Crée une nouvelle table externe avec les définitions de colonnes dérivées d’un ensemble de fichiers en zone de préparation contenant des données semi-structurées. Cette fonctionnalité est actuellement limitée à Apache Parquet, Apache Avro et aux fichiers ORC.

CREATE [ OR REPLACE ] EXTERNAL TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]

Note

Si l’instruction remplace une table existante du même nom, les autorisations sont copiées à partir de la table à remplacer. S’il n’existe aucune table de ce nom, les autorisations sont copiées à partir de la table source en cours de clonage.

Pour plus d’informations sur COPY GRANTS, voir COPY GRANTS dans ce document.

Paramètres requis¶

table_name

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 et le chemin facultatif où sont stockés les fichiers contenant les données à lire :

@[namespace.]ext_stage_name[/path]

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

Ni les littéraux de chaîne ni les variables SQL ne sont pris en charge.

Où :

namespace est la base de données et/ou le schéma dans lequel réside la zone de préparation externe, sous la forme database_name.schema_name ou schema_name. 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.

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

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 stage_name et vérifiez la valeur de la propriété url. Par exemple, si l’URL de la zone de préparation inclut le chemin a et que l’emplacement de la table externe inclut le chemin b, la table externe lit les fichiers stockés dans stage/a/b.

Notez que la valeur [ WITH ] LOCATION ne peut pas faire référence à des noms de fichiers spécifiques. Pour faire pointer une table externe vers des fichiers individuels en zone de préparation, utilisez le paramètre PATTERN.

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

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

FORMAT_NAME = file_format_name
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¶

col_name

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.

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

col_type

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.

Les colonnes de table externes sont des colonnes virtuelles définies à l’aide d’une expression explicite. Ajouter des colonnes virtuelles sous forme d’expressions en utilisant la colonne VALUE et/ou la pseudo-colonne METADATA$FILENAME :

VALUE

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

CSV

La colonne VALUE structure chaque ligne comme un objet dont les éléments sont identifiés par la position de la colonne (c’est-à-dire {c1: <colonne_1_valeur>, c2: <colonne_2_valeur>, c3: <colonne_1_valeur> ...}).

Par exemple, ajoutez une colonne VARCHAR nommée mycol qui fait référence à la première colonne des fichiers CSV en zone de préparation :

mycol varchar as (value:c1::varchar)

Données semi-structurées

Mettez les noms et les valeurs des éléments entre guillemets. Parcourez le chemin dans la colonne VALUE en utilisant la notation par points.

Par exemple, supposons que l’exemple suivant représente une seule ligne de données semi-structurées dans un fichier en zone de préparation :

{ "a":"1", "b": { "c":"2", "d":"3" } }

Ajoutez une colonne VARCHAR nommée mycol qui fait référence à l’élément répétitif imbriqué c dans le fichier en zone de préparation :

mycol varchar as (value:"b"."c"::varchar)

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 un exemple, voir Partitions ajoutées automatiquement à partir d’expressions de colonnes de partitions (dans cette rubrique).

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.

Note

Si l’emplacement spécifié contient près d’un million de fichiers ou plus, nous vous recommandons de définir REFRESH_ON_CREATE = FALSE. Après avoir créé la table externe, actualisez les métadonnées de manière incrémentielle en exécutant les instructions ALTER EXTERNAL TABLE … REFRESH qui spécifient les sous-chemins de l’emplacement (c’est-à-dire les sous-ensembles de fichiers à inclure dans l’actualisation) jusqu’à ce que les métadonnées incluent tous les fichiers de l’emplacement.
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

La configuration de ce paramètre sur TRUE n’est pas prise en charge par les tables externes partitionnées lorsque les partitions sont ajoutées manuellement par le propriétaire de l’objet (c’est-à-dire lorsque PARTITION_TYPE = USER_SPECIFIED).
Vous devez configurer une notification d’événement pour votre emplacement de stockage 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, consultez les instructions relatives à votre service de stockage dans le Cloud :
- Amazon S3
  
  Actualisation automatique des tables externes Amazon S3
- Google Cloud Storage
  
  Actualisation automatique des tables externes pour Google Cloud Storage
- Microsoft Azure
  
  Actualisation automatique des tables externes pour Azure Blob Storage
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 = 'regex_pattern'

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.

AWS_SNS_TOPIC = 'string'

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.

TABLE_FORMAT = DELTA

Identifie la table externe comme faisant référence à un Delta Lake sur l’emplacement de stockage dans le Cloud. Un Delta Lake sur stockage Cloud Amazon S3, Google Cloud Storage ou Microsoft Azure est pris en charge.

Note

Cette fonctionnalité en avant-première est disponible pour tous les comptes.

Lorsque ce paramètre est défini, 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/00000000000000000000.json, _delta_log/00000000000000000010.checkpoint.parquet, etc.

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.

Note

La zone de préparation externe et le chemin facultatif spécifiés dans [ WITH ] LOCATION = doivent contenir les fichiers de données et les métadonnées d”une table Delta Lake uniquement. C’est-à-dire que l’emplacement de stockage spécifié ne peut contenir qu’un seul répertoire __delta_log.
L’ordre des notifications d’événements déclenchés par des opérations DDL dans le stockage dans le Cloud n’est pas garanti. Par conséquent, la possibilité d’actualisation automatique n’est pas disponible pour les tables externes qui font référence à des fichiers Delta Lake. REFRESH_ON_CREATE et AUTO_REFRESH doivent être définis sur FALSE.

Exécutez périodiquement une instruction ALTER EXTERNAL TABLE … REFRESH pour enregistrer tout fichier ajouté ou supprimé.
La valeur FILE_FORMAT doit spécifier Parquet comme type de fichier.
Pour des performances optimales, nous recommandons de définir des colonnes de partition pour la table externe.
Les paramètres suivants ne sont pas pris en charge lors de la référence à Delta Lake :
- AWS_SNS_TOPIC = 'string'
- PATTERN = 'regex_pattern'

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

ROW ACCESS POLICY <policy_name> ON (VALUE)

Spécifie la politique d’accès aux lignes à définir sur la table.

Spécifiez la colonne VALUE lorsque vous appliquez une politique d’accès aux lignes à une table externe.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Spécifie le nom de la balise et la valeur de la chaîne de la balise.

La valeur de la balise est toujours une chaîne de caractères et le nombre maximum de caractères pour la valeur de la balise est 256.

Pour plus de détails sur la spécification des balises dans une instruction, voir Quotas de balises pour les objets et les colonnes.

COMMENT = 'string_literal'

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.

part_col_name col_type AS part_expr

Définit une ou plusieurs colonnes de partition dans la table externe.

Le format d’une définition de colonne de partition diffère selon que les partitions sont calculées et ajoutées automatiquement à partir d’une expression dans chaque colonne de partition ou que les partitions sont ajoutées manuellement.

Ajoutée à partir d’une expression

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.

`part_col_name`	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.
`col_type`	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 `part_expr` pour la colonne.
`part_expr`	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

Ajoutée manuellement

Requis : définissez également la valeur du paramètre PARTITION_TYPE sur USER_SPECIFIED.

Une définition de colonne de partition est une expression qui analyse les métadonnées de la colonne interne (masquée) METADATA$EXTERNAL_TABLE_PARTITION. Essentiellement, la définition ne fait que définir le type de données de la colonne. Le format de la définition de la colonne de partition est le suivant :

part_col_name col_type AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION):part_col_name::data_type )

Par exemple, supposons que les colonnes col1, col2, et col3 contiennent respectivement des données de type varchar, number et timestamp (fuseau horaire) :

col1 varchar as (parse_json(metadata$external_table_partition):col1::varchar),
col2 number as (parse_json(metadata$external_table_partition):col2::number),
col3 timestamp_tz as (parse_json(metadata$external_table_partition):col3::timestamp_tz)

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

Note

La longueur maximale des noms de colonnes de partition spécifiés par l’utilisateur est de 32 caractères.

PARTITION_TYPE = USER_SPECIFIED

Définit le type de partition pour la table externe comme défini par l’utilisateur. Le propriétaire de la table externe (c’est-à-dire le rôle qui possède le privilège OWNERSHIP sur la table externe) doit ajouter manuellement des partitions aux métadonnées externes en exécutant les instructions ALTER EXTERNAL TABLE …. ADD PARTITION.

Ne définissez pas ce paramètre si les partitions sont ajoutées automatiquement aux métadonnées de la table externe lors de l’évaluation des expressions dans les colonnes de partition.

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

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 part_col_name = 'filter_value'

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`)¶

Google Cloud Storage

INTEGRATION = integration_name
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 Google Pub/Sub. 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 Google Cloud Storage.

Microsoft Azure

INTEGRATION = integration_name
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 à interroger. 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’une interrogation.

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 interrogez des fichiers compressés via Brotli, utilisez explicitement `BROTLI` au lieu de `AUTO`.
`GZIP`
`BZ2`
`BROTLI`	Doit être spécifié lors de l’interrogation de fichiers compressés par 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 n’ont pas été compressés.

RECORD_DELIMITER = 'character' | NONE

Un ou plusieurs caractères qui séparent les enregistrements dans un fichier d’entrée. Accepte les séquences d’échappement courantes ou les caractères à un octet ou à plusieurs octets suivants :

Caractères à un octet

Valeurs octales (préfixées par \\) ou les valeurs hexadécimales (préfixées par \x ou 0x). Par exemple, pour les enregistrements délimités par le caractère accent circonflexe (^), spécifiez la valeur octale (\\136) ou hexadécimale (0x5e).

Caractères multi-octets

Valeurs hexagonales (préfixées par \x). Par exemple, pour les enregistrements délimités par le caractère cent (¢), spécifiez la valeur hexadécimale (\xC2\xA2).

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 spécifié doit être un caractère UTF-8 valide et non une séquence aléatoire d’octets. Notez également que le délimiteur est limité à un maximum de 20 caractères.

Accepte également une valeur de NONE.

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

FIELD_DELIMITER = 'character' | 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 ou les caractères à un octet ou à plusieurs octets suivants :

Caractères à un octet

Valeurs octales (préfixées par \\) ou les valeurs hexadécimales (préfixées par \x ou 0x). Par exemple, pour les enregistrements délimités par le caractère accent circonflexe (^), spécifiez la valeur octale (\\136) ou hexadécimale (0x5e).

Caractères multi-octets

Valeurs hexagonales (préfixées par \x). Par exemple, pour les enregistrements délimités par le caractère cent (¢), spécifiez la valeur hexadécimale (\xC2\xA2).

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 spécifié doit être un caractère UTF-8 valide et non une séquence aléatoire d’octets. Notez également que le délimiteur est limité à un maximum de 20 caractères.

Accepte également une valeur de NONE.

Valeur par défaut : virgule (,)

SKIP_HEADER = integer

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 à interroger.

Par défaut : 0

SKIP_BLANK_LINES = TRUE | FALSE

Utilisation: Interrogation de 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

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

Chaîne de caractères à un octet utilisée comme caractère d’échappement pour les valeurs de champs non délimitées uniquement. Un caractère d’échappement appelle une autre interprétation sur les caractères suivants dans une séquence de caractères. Vous pouvez utiliser le caractère ESCAPE pour interpréter les instances des caractères FIELD_DELIMITER ou RECORD_DELIMITER dans les données comme des littéraux. Le caractère d’échappement peut également être utilisé pour échapper les instances de lui-même dans les données.

Accepte les séquences d’échappement, les valeurs octales ou les valeurs hexadécimales courantes.

Spécifie le caractère d’échappement pour les champs non délimités uniquement.

Note

La valeur par défaut est \\. Si une ligne d’un fichier de données se termine par une barre oblique inverse (\), ce caractère échappe le caractère de nouvelle ligne ou de retour chariot spécifié pour l’option de format de fichier RECORD_DELIMITER. Par conséquent, cette ligne et la suivante sont traitées comme une seule ligne de données. Pour éviter ce problème, définissez la valeur sur NONE.
Cette option de format de fichier ne prend en charge que les caractères à un seul octet. Notez que le codage des caractères UTF-8 représente les caractères ASCII d’ordre supérieur comme des caractères à plusieurs octets. Si votre fichier de données est codé avec le jeu de caractères UTF-8, vous ne pouvez pas spécifier un caractère ASCII d’ordre supérieur comme valeur d’option.

En outre, si vous spécifiez un caractère ASCII d’ordre élevé, nous vous recommandons de définir l’option de format de fichier ENCODING = 'string' comme codage de caractères pour vos fichiers de données afin de garantir la bonne interprétation du caractère.

Valeur par défaut : barre oblique inverse (\\)

TRIM_SPACE = TRUE | FALSE

Booléen qui spécifie s’il faut supprimer les espaces blancs des champs.

Par exemple, si votre logiciel de base de données externe contient des champs entre guillemets, mais insère un espace d’en-tête, Snowflake lit l’espace d’en-tête plutôt que le caractère guillemet comme début du champ (c’est-à-dire que les guillemets sont interprétés comme faisant partie de la chaîne des données du champ). Définissez cette option sur TRUE pour supprimer les espaces indésirables lors de l’interrogation des données.

Comme autre exemple, si les espaces d’en-tête ou de fin entourent des guillemets qui délimitent les chaînes de caractères, vous pouvez supprimer les espaces environnants en utilisant cette option et le caractère guillemet en utilisant l’option FIELD_OPTIONALLY_ENCLOSED_BY. Notez que tous les espaces entre les guillemets sont préservés. Par exemple, en supposant que FIELD_DELIMITER = '|' et FIELD_OPTIONALLY_ENCLOSED_BY = '"' :

|"Hello world"|    /* returned as */  >Hello world<
|" Hello world "|  /* returned as */  > Hello world <
| "Hello world" |  /* returned as */  >Hello world<

Notez que les parenthèses dans cet exemple ne sont pas affichées ; elles sont utilisées pour délimiter le début et la fin des chaînes affichées.

Par défaut : FALSE

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

Caractère utilisé pour délimiter des chaînes. La valeur peut être NONE, un caractère guillemet simple (') ou un caractère guillemet double ("). Pour utiliser le caractère guillemet simple, utilisez la représentation octale ou hexadécimale (0x27) ou le double échappement en guillemet simple ('').

Lorsqu’un champ contient ce caractère, effectuez un échappement en utilisant le même caractère. Par exemple, si la valeur est le caractère de guillemet double et qu’un champ contient la chaîne A "B" C, effectuez un échappement des guillemets doubles comme suit :

A ""B"" C

Par défaut : NONE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

Chaîne utilisée pour les conversions entrante et sortante de SQL NULL :

Lors de l’interrogation des données, Snowflake remplace ces valeurs dans les données retournées par des valeurs SQL NULL. Pour spécifier plus d’une chaîne, mettez la liste des chaînes entre parenthèses et utilisez des virgules pour séparer chaque valeur.

Notez que Snowflake convertit toutes les instances de la valeur en NULL, quel que soit le type de données. Par exemple, si 2 est spécifié comme valeur, toutes les instances de 2 sous forme de chaîne ou de nombre sont converties.

Par exemple :

NULL_IF = ('\\N', 'NULL', 'NUL', '')

Notez que cette option peut inclure des chaînes vides.

Par défaut : \\N (comme NULL, ce qui suppose que la valeur ESCAPE_UNENCLOSED_FIELD est \\)

EMPTY_FIELD_AS_NULL = TRUE | FALSE

Spécifie s’il faut insérer des valeurs SQL NULL pour les champs vides dans un fichier d’entrée, qui sont représentées par deux délimiteurs successifs (par ex., ,,).

Si l’option est définie sur FALSE, Snowflake essaie de placer un champ vide dans le type de colonne correspondant. Une chaîne vide est renvoyée pour les colonnes de type STRING. Pour les autres types de colonnes, la requête renvoie une erreur.

Par défaut : TRUE

ENCODING = 'string'

Chaîne (constante) qui spécifie le jeu de caractères des données sources lors de l’interrogation des données.

Jeu de caractères

Valeur ENCODING

Langues acceptées

Remarques

Big5

BIG5

Chinois traditionnel

EUC-JP

EUCJP

Japonais

EUC-KR

EUCKR

Coréen

GB18030

GB18030

Chinois

IBM420

IBM420

Arabe

IBM424

IBM424

Hébreu

ISO-2022-CN

ISO2022CN

Chinois simplifié

ISO-2022-JP

ISO2022JP

Japonais

ISO-2022-KR

ISO2022KR

Coréen

ISO-8859-1

ISO88591

Allemand, anglais, danois, français, italien, norvégien, néerlandais, portugais, suédois

ISO-8859-2

ISO88592

Tchèque, hongrois, polonais, roumain

ISO-8859-5

ISO88595

Russe

ISO-8859-6

ISO88596

Arabe

ISO-8859-7

ISO88597

Grec

ISO-8859-8

ISO88598

Hébreu

ISO-8859-9

ISO88599

Turc

ISO-8859-15

ISO885915

Allemand, anglais, danois, français, italien, norvégien, néerlandais, portugais, suédois

Identique à ISO-8859-1 à l’exception des 8 caractères, y compris le symbole monétaire Euro.

KOI8-R

KOI8R

Russe

Shift_JIS

SHIFTJIS

Japonais

UTF-8

UTF8

Toutes les langues

Pour charger des données à partir de fichiers délimités (CSV, TSV, etc.), UTF-8 est la valeur par défaut. . . Pour charger des données à partir de tous les autres formats de fichier pris en charge (JSON, Avro, etc.), ainsi que pour décharger des données, UTF-8 est le seul jeu de caractères pris en charge.

UTF-16

UTF16

Toutes les langues

UTF-16BE

UTF16BE

Toutes les langues

UTF-16LE

UTF16LE

Toutes les langues

UTF-32

UTF32

Toutes les langues

UTF-32BE

UTF32BE

Toutes les langues

UTF-32LE

UTF32LE

Toutes les langues

windows-1250

WINDOWS1250

Tchèque, hongrois, polonais, roumain

windows-1251

WINDOWS1251

Russe

windows-1252

WINDOWS1252

Allemand, anglais, danois, français, italien, norvégien, néerlandais, portugais, suédois

windows-1253

WINDOWS1253

Grec

windows-1254

WINDOWS1254

Turc

windows-1255

WINDOWS1255

Hébreu

windows-1256

WINDOWS1256

Arabe

Par défaut : UTF8

Note

Snowflake stocke toutes les données en interne dans le jeu de caractères UTF-8. Les données sont converties en UTF-8.

TYPE = JSON¶

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les fichiers de données à retourner. 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’une interrogation.

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 interrogez 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 n’ont pas été compressés.

Par défaut : AUTO

ALLOW_DUPLICATE = TRUE | FALSE

Booléen qui indique d’autoriser les noms de champs d’objets dupliqués (seul le dernier sera conservé).

Par défaut : FALSE

STRIP_OUTER_ARRAY = TRUE | FALSE

Booléen qui demande à l’analyseur JSON de supprimer les crochets extérieurs (comme [ ]).

Par défaut : FALSE

STRIP_NULL_VALUES = TRUE | FALSE

Booléen qui demande à l’analyseur JSON de supprimer les champs d’objets ou les éléments de tableau contenant des valeurs null. Par exemple, lorsque défini sur TRUE :

Avant

Après

[null]

[]

[null,null,3]

[,,3]

{"a":null,"b":null,"c":123}

{"c":123}

{"a":[1,null,2],"b":{"x":null,"y":88}}

{"a":[1,,2],"b":{"y":88}}

Par défaut : FALSE

TYPE = AVRO¶

Chaîne (constante) qui spécifie l’algorithme de compression actuel pour les fichiers de données à interroger. 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’une interrogation.

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 interrogez 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 à interroger n’ont pas été compressés.

Par défaut : AUTO

TYPE = ORC¶

TRIM_SPACE = TRUE | FALSE

Booléen qui spécifie s’il faut supprimer les espaces blancs de début et de fin des chaînes.

Par exemple, si votre logiciel de base de données externe contient des champs entre guillemets, mais insère un espace d’en-tête, Snowflake lit l’espace d’en-tête plutôt que le caractère guillemet comme début du champ (c’est-à-dire que les guillemets sont interprétés comme faisant partie de la chaîne des données du champ). Définissez cette option sur TRUE pour supprimer les espaces indésirables.

Cette option de format de fichier s’applique uniquement aux actions suivantes :

Interrogation des valeurs d’objets dans des fichiers de données ORC mis en zone de préparation.
Interrogation des données ORC dans des colonnes séparées en utilisant l’option de copie MATCH_BY_COLUMN_NAME.
Interrogation de données ORC dans des colonnes distinctes en spécifiant une requête dans l’instruction COPY (c’est-à-dire une transformation COPY).

Par défaut : FALSE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

Chaîne utilisée pour les conversions entrante et sortante de SQL NULL. Snowflake remplace ces chaînes de la source de données par SQL NULL. Pour spécifier plus d’une chaîne, mettez la liste des chaînes entre parenthèses et utilisez des virgules pour séparer chaque valeur.

Notez que Snowflake convertit toutes les instances de la valeur en NULL, quel que soit le type de données. Par exemple, si 2 est spécifié comme valeur, toutes les instances de 2 sous forme de chaîne ou de nombre sont converties.

Par exemple :

NULL_IF = ('\\N', 'NULL', 'NUL', '')

Notez que cette option peut inclure des chaînes vides.

Cette option de format de fichier est appliquée lors de l’interrogation des valeurs d’objets dans des fichiers de données ORC mis en zone de préparation.

Par défaut : \\N (comme NULL, ce qui suppose que la valeur ESCAPE_UNENCLOSED_FIELD est \\)

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 n’ont pas été compressés.

Par défaut : AUTO

BINARY_AS_TEXT = TRUE | FALSE

Booléen qui spécifie s’il faut interpréter les colonnes sans type de données logiques défini comme du texte UTF-8. Lorsqu’il est réglé sur FALSE, Snowflake interprète ces colonnes comme des données binaires.

Par défaut : TRUE

Exigences en matière de contrôle d’accès¶

Un rôle utilisé pour exécuter cette commande SQL doit avoir les privilèges suivants définis au minimum ainsi :

Privilège	Objet	Remarques
CREATE EXTERNAL TABLE	Schéma
CREATE STAGE	Schéma	Requis si vous créez une nouvelle zone de préparation.
USAGE	Zone de préparation	Requis si vous faites référence à une zone de préparation existante.
USAGE	Format de fichier

Notez que l’exploitation d’un objet dans un schéma requiert également le privilège USAGE sur la base de données et le schéma parents.

Pour obtenir des instructions sur la création d’un rôle personnalisé avec un ensemble spécifique de privilèges, voir Création de rôles personnalisés.

Pour des informations générales sur les rôles et les privilèges accordés pour effectuer des actions SQL sur des objets sécurisables, voir Contrôle d’accès dans Snowflake.

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.

Vous ne pouvez pas accéder aux données conservées dans les classes de stockage dans le Cloud d’archives qui doivent être restaurées avant de pouvoir être récupérées. Ces classes de stockage d’archives comprennent, par exemple, la classe de stockage Amazon S3 Glacier Flexible Retrieval ou Glacier Deep Archive, ou Microsoft Azure Archive Storage.
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.
- METADATA$FILE_ROW_NUMBER : nombre de lignes pour chaque enregistrement du fichier de données en 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.
Politiques :
- Vous ne pouvez pas ajouter une politique de masquage à une colonne de table externe lors de la création de la table externe car une politique de masquage ne peut pas être attachée à une colonne virtuelle.
- Vous pouvez ajouter une politique d’accès aux lignes à une table externe lors de la création de la table externe.
Utiliser OR REPLACE équivaut à utiliser DROP EXTERNAL TABLE sur la table externe existante, puis à créer une nouvelle table externe avec le même nom.

Les instructions CREATE OR REPLACE <objet> sont atomiques. Autrement dit, lorsque l’objet est remplacé, la suppression de l’ancien objet et la création du nouvel objet sont traitées en une seule transaction.

Cela signifie que toutes les requêtes simultanées à l’opération CREATE OR REPLACE EXTERNAL TABLE utilisent soit l’ancienne soit la nouvelle version de la table externe.
Concernant les métadonnées :

Attention

Les clients doivent s’assurer qu’aucune donnée personnelle (autre que pour un objet utilisateur), donnée sensible, donnée à exportation contrôlée ou autre donnée réglementée n’est saisie comme métadonnée lors de l’utilisation du service Snowflake. Pour plus d’informations, voir Champs de métadonnées dans Snowflake.
Lors de la création d’une table externe avec une politique d’accès aux lignes ajoutée à la table externe, utilisez la fonction POLICY_CONTEXT pour simuler une requête sur la table externe protégée par une politique d’accès aux lignes.
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.

Exemples¶

Partitions ajoutées automatiquement à partir d’expressions de colonnes de partition¶

Créez une table externe avec des partitions calculées à partir d’expressions dans les définitions des colonnes de partition.

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 s1 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 s1 URL='s3://mybucket/files/logs/' ... ;
Google Cloud Storage
CREATE STAGE s1 URL='gcs://mybucket/files/logs/' ... ;
Microsoft Azure
CREATE STAGE s1 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 @s1/;

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

Les tables externes pour le stockage dans le Cloud Amazon S3 et Microsoft Azure comprennent le paramètre nécessaire pour actualiser automatiquement les métadonnées lorsque cela est déclenché par des notifications d’événements provenant du service de messagerie Cloud respectif :

Amazon S3

CREATE EXTERNAL TABLE et1(
 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=@s1/logs/
 AUTO_REFRESH = true
 FILE_FORMAT = (TYPE = PARQUET)
 AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';

Google Cloud Storage

CREATE EXTERNAL TABLE et1(
  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=@s1/logs/
  AUTO_REFRESH = true
  FILE_FORMAT = (TYPE = PARQUET);

Microsoft Azure

CREATE EXTERNAL TABLE et1(
  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=@s1/logs/
  AUTO_REFRESH = true
  FILE_FORMAT = (TYPE = PARQUET);

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

Lorsque vous interrogez la table externe, filtrez les données en fonction de la colonne de partition à l’aide d’une clause WHERE. Snowflake analyse uniquement les fichiers des partitions spécifiées qui correspondent aux conditions de filtrage :

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

Partitions ajoutées manuellement¶

Créez une table externe avec des partitions définies par l’utilisateur (c’est-à-dire que les partitions sont ajoutées manuellement par le propriétaire de la table externe).

Créez une zone de préparation externe nommée s2 pour l’emplacement de stockage où sont stockés les fichiers de données :

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

Amazon S3
CREATE STAGE s2 URL='s3://mybucket/files/logs/' ... ;
Google Cloud Storage
CREATE STAGE s2 URL='gcs://mybucket/files/logs/' ... ;
Microsoft Azure
CREATE STAGE s2 URL='azure://mycontainer/files/logs/' ... ;
Créez la table externe partitionnée. La table externe comprend trois colonnes de partition avec des types de données différents.

Notez que les noms de colonnes dans les expressions de partition sont sensibles à la casse. Les règles suivantes s’appliquent :
- Le nom d’une colonne de partition doit être en majuscules, sauf si le nom de la colonne est compris entre guillemets. Vous pouvez également utiliser GET_IGNORE_CASE au lieu du caractère : sensible à la casse dans l’expression SQL.
- Si le nom d’une colonne est compris entre guillemets (par ex., « Colonne1 »), le nom de la colonne de partition doit également être compris entre guillemets et correspondre exactement au nom de la colonne.
La syntaxe pour chacun des trois services de stockage dans le Cloud (Amazon S3, Google Cloud Storage et Microsoft Azure) est identique, car les métadonnées de la table externe ne sont pas actualisées :
```
create external table et2(
  col1 date as (parse_json(metadata$external_table_partition):COL1::date),
  col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
  col3 number as (parse_json(metadata$external_table_partition):COL3::number))
  partition by (col1,col2,col3)
  location=@s2/logs/
  partition_type = user_specified
  file_format = (type = parquet);
```

Ajoutez des partitions pour les colonnes de partitions :

ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';

Snowflake ajoute les partitions aux métadonnées de la table externe. L’opération ajoute également aux métadonnées tout nouveau fichier de données se trouvant dans l’emplacement spécifié :

+---------------------------------------+----------------+-------------------------------+
|                       file            |     status     |          description          |
+---------------------------------------+----------------+-------------------------------+
| mycontainer/files/logs/2022/01/24.csv | REGISTERED_NEW | File registered successfully. |
| mycontainer/files/logs/2022/01/25.csv | REGISTERED_NEW | File registered successfully. |
+---------------------------------------+----------------+-------------------------------+

Lorsque vous interrogez la table externe, filtrez les données en fonction des colonnes de partition à l’aide d’une clause WHERE. Cet exemple renvoie les enregistrements dans l’ordre où ils sont stockés dans les fichiers de données en zone de préparation :

SELECT col1, col2, col3 FROM et1 WHERE col1 = TO_DATE('2022-01-24') AND col2 = 'a' ORDER BY METADATA$FILE_ROW_NUMBER;

Table externe qui fait référence à des fichiers dans un Delta Lake¶

Créez une table externe partitionnée nommée ext_twitter_feed qui référence les fichiers Delta Lake au format Parquet dans la zone de préparation externe mystage et le chemin daily.

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

CREATE EXTERNAL TABLE ext_twitter_feed(
 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=@mystage/daily/
 REFRESH_ON_CREATE =  FALSE
 AUTO_REFRESH = FALSE
 FILE_FORMAT = (TYPE = PARQUET)
 TABLE_FORMAT = DELTA;

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 Partitions ajoutées automatiquement à partir d’expressions de colonnes de partition :

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

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

Table externe créée avec des définitions de colonnes détectées¶

Créez une table externe dont les définitions de colonne sont dérivées d’un ensemble de fichiers en zone de préparation qui contiennent des données Avro, Parquet ou ORC.

Notez que la zone de préparation mystage et le format de fichier my_parquet_format référencés dans l’instruction doivent déjà exister. Un ensemble de fichiers doit déjà être en zone de préparation dans l’emplacement de stockage Cloud référencé dans la définition de la zone de préparation.

Cet exemple s’appuie sur un exemple de la rubrique INFER_SCHEMA :

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage',
          FILE_FORMAT=>'my_parquet_format'
        )
      )
    )
    LOCATION=@mystage
    FILE_FORMAT=my_parquet_format
    AUTO_REFRESH=false;

CREATE EXTERNAL TABLE¶

Syntaxe¶

Syntaxe des variantes¶

CREATE EXTERNAL TABLE … USING TEMPLATE¶

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¶

Exigences en matière de contrôle d’accès¶

Notes sur l’utilisation¶

Exemples¶

Partitions ajoutées automatiquement à partir d’expressions de colonnes de partition¶

Partitions ajoutées manuellement¶

Table externe qui fait référence à des fichiers dans un Delta Lake¶

Vue matérialisée sur une table externe¶

Table externe créée avec des définitions de colonnes détectées¶

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

Options de type de format (`formatTypeOptions`)¶