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>' ]
  [ 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>' ]

-- 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>' ]

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
-- 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 = ORC ... )
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '<string>' [ , '<string>' ... ]
-- 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.

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.

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)
Apache Parquet

Soit vous faites référence aux noms des éléments en utilisant la notation des données semi-structurées soit vous faites référence à l’ID du champ Parquet pour une colonne spécifique.

Par exemple, en utilisant l’ID du champ, ajoutez une colonne VARCHAR nommée mycol qui fait référence à la deuxième colonne dans les fichiers Parquet en zone de préparation :

mycol VARCHAR as (value:"2"::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

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.

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.

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.

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

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

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 ( nom_balise = 'valeur_balise' [ , nom_balise = 'valeur_balise' , ... ] )

Spécifie le nom de la balise (c’est-à-dire la clé) et la valeur 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. Le nombre maximum de clés de balises uniques pouvant être définies sur un objet est de 20.

Note

Pour une table ou une vue et ses colonnes, le nombre total de clés de balises uniques qui peuvent être définies est 20.

Par exemple, si une colonne unique d’une table a 10 clés de balises uniques définies sur la colonne, Snowflake permet 10 clés de balises uniques supplémentaires à définir soit sur cette colonne, soit sur d’autres colonnes de la table, soit sur la table elle-même, soit sur une combinaison de la table et de ses colonnes. Une fois que la limite de 20 clés de balises uniques est atteinte, aucune clé de balise supplémentaire ne peut être définie sur la table ou ses colonnes.

COMMENT = 'litté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

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.

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 :

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 :

nom_col_part type_col AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION:nom_col_part::type_données )

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.

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

Google Cloud Storage

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 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 = 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 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 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 (\\5e) 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 = '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 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 (\\5e) 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 = 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 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

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

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 pendant le chargement des données.

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.

  • Chargement des données ORC dans des colonnes séparées en utilisant l’option de copie MATCH_BY_COLUMN_NAME.

  • Chargement des 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 = ( 'chaîne1' [ , 'chaîne2' , ... ] )

Chaîne utilisée pour les conversions entrante et sortante de SQL NULL. Snowflake remplace ces chaînes de la source de chargement des 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 s’applique uniquement aux actions suivantes :

  • Interrogation des valeurs d’objets dans des fichiers de données ORC mis en zone de préparation.

  • Chargement des données ORC dans des colonnes séparées en utilisant l’option de copie MATCH_BY_COLUMN_NAME.

  • Chargement des 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 : \\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 à charger n’ont pas été compressés.

Par défaut : AUTO

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.

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 actions de destruction et de création se produisent en une seule opération atomique. 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.

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/

  1. 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/'
      ...
      ;
    
  2. 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 |
    +----------------------------------------+
    
  3. 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);
    
  4. 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).

  1. 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/'
      ...
      ;
    
  2. 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 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);
    
  3. 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 :

Amazon S3

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;

Google Cloud Storage

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)
  INTEGRATION = 'MY_INT'
  LOCATION=@mystage/daily/
  REFRESH_ON_CREATE =  FALSE
  AUTO_REFRESH = FALSE
  FILE_FORMAT = (TYPE = PARQUET)
  TABLE_FORMAT = DELTA;

Microsoft Azure

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)
  INTEGRATION = 'MY_INT'
  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.