CREATE EXTERNAL TABLE¶

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 fonction prend en charge les fichiers Apache Parquet, Apache Avro, ORC, JSON et CSV. La prise en charge des fichiers CSV et JSON est actuellement disponible en avant-première.

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

Copy

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

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

Copy

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.

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 à 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 = 'string' | 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 = 'string' | 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').

Note

Pour les caractères autres que ASCII, vous devez utiliser la valeur de la séquence d’octets hexagonale pour obtenir un comportement déterministe.

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<

Copy

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

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.

Valeur par défaut : \N (c’est-à-dire NULL, 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
IBM949	IBM949	Coréen
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
fenêtres-874	WINDOWS874	Thaïlandais
windows-949	WINDOWS949	Coréen
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¶

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 à 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 (c’est-à-dire [ ]).

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

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booléen qui spécifie s’il faut remplacer les caractères UTF-8 non valides par le caractère de remplacement Unicode (�). Cette option permet de remplacer un caractère par un autre.

S’il est défini sur TRUE, Snowflake remplace les caractères UTF-8 non valides par le caractère de remplacement Unicode.

Si FALSE est défini, l’opération de chargement génère une erreur lorsqu’un codage de caractères UTF-8 non valide est détecté.

Par défaut : FALSE

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

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booléen qui spécifie s’il faut remplacer les caractères UTF-8 non valides par le caractère de remplacement Unicode (�). Cette option permet de remplacer un caractère par un autre.

S’il est défini sur TRUE, Snowflake remplace les caractères UTF-8 non valides par le caractère de remplacement Unicode.

Si FALSE est défini, l’opération de chargement génère une erreur lorsqu’un codage de caractères UTF-8 non valide est détecté.

Par défaut : FALSE

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

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booléen qui spécifie s’il faut remplacer les caractères UTF-8 non valides par le caractère de remplacement Unicode (�). Cette option permet de remplacer un caractère par un autre.

S’il est défini sur TRUE, Snowflake remplace les caractères UTF-8 non valides par le caractère de remplacement Unicode.

Si FALSE est défini, l’opération de chargement génère une erreur lorsqu’un codage de caractères UTF-8 non valide est détecté.

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.

Valeur par défaut : \N (c’est-à-dire NULL)

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

Note

Snowflake vous recommande de définir BINARY_AS_TEXT sur FALSE pour éviter tout problème de conversion éventuel.

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Booléen qui spécifie s’il faut remplacer les caractères UTF-8 non valides par le caractère de remplacement Unicode (�). Cette option permet de remplacer un caractère par un autre.

S’il est défini sur TRUE, Snowflake remplace les caractères UTF-8 non valides par le caractère de remplacement Unicode.

Si FALSE est défini, l’opération de chargement génère une erreur lorsqu’un codage de caractères UTF-8 non valide est détecté.

Par défaut : FALSE

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/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s1
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s1
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   ALTER EXTERNAL TABLE et1 REFRESH;
   

   Copy

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/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s2
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s2
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

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

   Copy

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

   Copy

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

   Copy

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;

Copy

L’utilisation de * pour ARRAY_AGG(OBJECT_CONSTRUCT()) peut entraîner une erreur si le résultat renvoyé est supérieur à 16 MB. Évitez d’utiliser * pour les jeux de résultats plus importants et n’utilisez que les colonnes nécessaires, COLUMN NAME, TYPE, et NULLABLE, pour la requête, comme l’illustre l’exemple suivant. La colonne facultative ORDER_ID peut être incluse lorsque vous utilisez WITHIN GROUP (ORDER BY order_id).

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

Copy