Interrogation des données dans des fichiers mis en zone de préparation

Snowflake prend en charge l’utilisation de SQL standard pour interroger des fichiers de données situés dans une zone de préparation interne (c.-à-d. Snowflake) ou externe nommée (c.-à-d. Amazon S3, Google Cloud Storage ou Microsoft Azure). Ceci peut être utile pour inspecter/visualiser le contenu des fichiers préparés, en particulier avant le chargement ou après le déchargement des données.

De plus, en référençant des colonnes de métadonnées dans un fichier préparé, une requête de données préparées peut renvoyer des informations supplémentaires, telles que le nom du fichier et les numéros de ligne (concernant le fichier).

Snowflake prend en charge les requêtes de données préparées pour permettre la transformation de données durant le chargement.

Note

Cette fonctionnalité est principalement destinée à effectuer des requêtes simples uniquement, en particulier lors du chargement et/ou de la transformation de données, et n’est pas destinée à remplacer le chargement des données dans les tables et à effectuer des requêtes sur les tables.

Dans ce chapitre :

Syntaxe et paramètres de requête

Interrogez les fichiers de données préparés à l’aide d’une instruction SELECT avec la syntaxe suivante :

SELECT [<alias>.]$<file_col_num>[:<element>] [ , [<alias>.]$<file_col_num>[:<element>] , ...  ]
  FROM { <internal_location> | <external_location> }
  [ ( FILE_FORMAT => '<namespace>.<named_file_format>', PATTERN => '<regex_pattern>' ) ]
  [ <alias> ]
Copy

Pour la syntaxe de transformation de données pendant un chargement, voir COPY INTO <table>.

Important

La liste des objets renvoyés pour une zone de préparation externe peut inclure un ou plusieurs « blobs de répertoire » ; essentiellement, les chemins qui se terminent par une barre oblique (/), par exemple :

LIST @my_gcs_stage;

+---------------------------------------+------+----------------------------------+-------------------------------+
| name                                  | size | md5                              | last_modified                 |
|---------------------------------------+------+----------------------------------+-------------------------------|
| my_gcs_stage/load/                    |  12  | 12348f18bcb35e7b6b628ca12345678c | Mon, 11 Sep 2019 16:57:43 GMT |
| my_gcs_stage/load/data_0_0_0.csv.gz   |  147 | 9765daba007a643bdff4eae10d43218y | Mon, 11 Sep 2019 18:13:07 GMT |
+---------------------------------------+------+----------------------------------+-------------------------------+
Copy

Ces blobs sont répertoriés lorsque des répertoires sont créés dans la console Google Cloud Platform plutôt que d’utiliser un autre outil fourni par Google.

Les instructions SELECT qui font référence à une zone de préparation peuvent échouer lorsque la liste d’objets inclut des blobs de répertoire. Pour éviter les erreurs, nous vous recommandons d’utiliser la correspondance de motifs de fichier pour identifier les fichiers à inclure (c’est-à-dire la clause PATTERN) lorsque la liste de fichiers d’une zone de préparation inclut des blobs de répertoire.

Paramètres requis

[alias.]$file_col_num[:element] [ , [alias.]$file_col_num[:element] , ...  ]

Spécifie un ensemble explicite de champs/colonnes dans les fichiers de données préparés dans un emplacement interne ou externe, où :

alias

Spécifie l’alias « de table » facultatif défini, le cas échéant, dans la clause FROM.

file_col_num

Indique le numéro de position du champ/colonne (dans le fichier) qui contient les données à charger (1 pour le premier champ, 2 pour le second champ, etc.).

element

Spécifie le chemin et le nom de l’élément d’une valeur répétitive (s’applique uniquement aux fichiers de données semi-structurées).

internal_location ou external_location

Indique l’emplacement où les fichiers de données sont stockés :

  • internal_location est le spécificateur URI pour l’emplacement dans Snowflake où les fichiers contenant des données sont mis en zone de préparation :

    @[namespace.]internal_stage_name[/path]

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

    @[namespace.]%table_name[/path]

    Les fichiers sont préparés pour la table spécifiée.

    @~[/path]

    Les fichiers sont mis en zone de préparation pour l’utilisateur actuel.

  • external_location est le spécificateur URI pour la zone de préparation externe ou l’emplacement externe nommé (c.-à-d. Amazon S3, Google Cloud Storage ou Microsoft Azure) où sont mis en zone de préparation les fichiers contenant des données :

    @[namespace.]external_stage_name[/path]

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

Où :

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

  • Le paramètre path facultatif restreint l’ensemble des fichiers interrogés aux fichiers situés sous le préfixe de dossier. Si path est spécifié, mais qu’aucun fichier n’est explicitement nommé dans le chemin, tous les fichiers de données du chemin sont interrogés.

Note

  • La chaîne URI pour un emplacement de stockage externe (c.-à-d. Amazon S3, Google Cloud Storage ou Microsoft Azure) doit être entourée de guillemets simples. Cependant, vous pouvez inclure n’importe quelle chaîne URI entre guillemets simples, ce qui permet l’insertion de caractères spéciaux, y compris des espaces, dans les noms de fichier et d’emplacement. Par exemple :

    Interne:

    '@~/path 1/file 1.csv'

    '@%my table/path 1/file 1.csv'

    '@my stage/path 1/file 1.csv'

  • Les modificateurs de chemins d’accès relatifs tels que /./ et /../ sont interprétés littéralement, car les « chemins » sont des préfixes littéraux pour un nom. Par exemple :

    S3:

    COPY INTO mytable FROM @mystage/./../a.csv

    Dans ces instructions COPY, le système recherche un fichier littéralement nommé ./../a.csv dans l’emplacement de stockage.

Paramètres facultatifs

( FILE_FORMAT => 'namespace.named_file_format' )

Spécifie un format de fichier nommé qui décrit le format des fichiers de données préparés à interroger.

Notez que ce paramètre est facultatif si l’une des conditions suivantes est vraie :

  • Les fichiers sont formatés dans le format de fichier par défaut (CSV) avec les délimiteurs par défaut : , (comme délimiteur de champ) et le nouveau caractère de ligne (comme délimiteur d’enregistrement).

  • Les fichiers se trouvent dans une zone de préparation interne ou externe, et la définition de zone de préparation décrit le format de fichier.

Si vous faites référence à un format de fichier dans l’espace de noms actuel de votre session utilisateur, vous pouvez omettre les guillemets simples autour de l’identificateur de format.

Sinon, ce paramètre est nécessaire. Pour plus de détails, voir Formats de fichier (dans ce chapitre).

namespace spécifie facultativement la base de données et/ou le schéma de la table, sous la forme de 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.

Si l’identificateur contient des espaces, des caractères spéciaux ou des caractères majuscules et minuscules, toute la chaîne doit être délimitée par des guillemets doubles. Les identificateurs entre guillemets doubles sont également sensibles à la casse.

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.

alias

Spécifie un alias « de table » pour l’emplacement interne/externe où les fichiers sont mis en zone de préparation.

Formats de fichier

Pour analyser un fichier de données préparé, il est nécessaire de décrire son format de fichier. Le format de fichier par défaut est le texte UTF-8 délimité par des caractères (c.-à-d. CSV), avec le caractère virgule (,) comme délimiteur de champ et le nouveau caractère de ligne comme délimiteur d’enregistrement. Si les données source sont dans un autre format (JSON, Avro, etc.), vous devez spécifier le type de format de fichier correspondant (et les options).

Pour spécifier explicitement les options de format de fichier, définissez-les via l’une des méthodes suivantes :

Interrogation de fichiers de données préparés:

En tant qu’options de format de fichier spécifiées pour un format de fichier ou un objet zone de préparation nommé. Le format de fichier/d’objet zone de préparation nommé peut alors être référencé dans l’instruction SELECT.

Chargement de colonnes à partir de fichiers de données préparés:
  • En tant qu’options de format de fichier spécifiées directement dans COPY INTO <table>.

  • En tant qu’options de format de fichier spécifiées pour un format de fichier ou un objet zone de préparation nommé. Le format de fichier/d’objet zone de préparation nommé peut alors être référencé dans l’instruction COPY INTO <table>.

Exemples de requête

Exemple 1 : Interrogation de colonnes dans un fichier CSV

L’exemple suivant illustre la mise en zone de préparation de plusieurs fichiers de données CSV (avec le même format de fichier), puis l’interrogation de colonnes de données dans les fichiers.

Cet exemple suppose que les fichiers portent les noms suivants et qu’ils se trouvent dans le répertoire racine d’un environnement macOS ou Linux :

  • /tmp/data1.csv contient deux enregistrements :

    a|b
    c|d
    
    Copy
  • /tmp/data2.csv contient deux enregistrements :

    e|f
    g|h
    
    Copy

Pour préparer et interroger les fichiers :

-- Create a file format.
CREATE OR REPLACE FILE FORMAT myformat TYPE = 'csv' FIELD_DELIMITER = '|';

-- Create an internal stage.
CREATE OR REPLACE STAGE mystage1;

-- Stage the data files.
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file.
-- Optionally apply pattern matching to the set of files in the stage and optional path.
-- Note that the table alias is provided to make the statement easier to read and is not required.
SELECT t.$1, t.$2 FROM @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;

+----+----+
| $1 | $2 |
|----+----|
| a  | b  |
| c  | d  |
| e  | f  |
| g  | h  |
+----+----+

SELECT t.$1, t.$2 FROM @mystage1 t;

+-----+------+
| $1  | $2   |
|-----+------|
| a|b | NULL |
| c|d | NULL |
| e|f | NULL |
| g|h | NULL |
+-----+------+
Copy

Note

Le format de fichier est requis dans cet exemple pour analyser correctement les champs des fichiers préparés. Dans la deuxième requête, le format de fichier est omis, ce qui fait que le délimiteur de champ | est ignoré et que les valeurs renvoyées pour $1 et $2.

Cependant, si le format de fichier est inclus dans la définition de zone de préparation, vous pouvez l’omettre dans l’instruction SELECT. Voir Exemple 3 : Interrogation d’éléments dans un fichier JSON.

Exemple 2 : Appel des fonctions lors de l’interrogation d’un fichier de données préparé

Obtenez le code ASCII du premier caractère de chaque colonne dans les fichiers de données préparés dans l’exemple 1 : Interrogation de colonnes dans un fichier CSV :

SELECT ascii(t.$1), ascii(t.$2) FROM @mystage1 (file_format => myformat) t;

+-------------+-------------+
| ASCII(T.$1) | ASCII(T.$2) |
|-------------+-------------|
|          97 |          98 |
|          99 |         100 |
|         101 |         102 |
|         103 |         104 |
+-------------+-------------+
Copy

Note

Si le format de fichier est inclus dans la définition de zone de préparation, vous pouvez l’omettre dans l’instruction SELECT. Voir Exemple 3 : Interrogation d’éléments dans un fichier JSON.

Exemple 3 : Interrogation d’éléments dans un fichier JSON

Cet exemple illustre la mise en zone de préparation d’un fichier de données JSON contenant les objets suivants, puis l’interrogation des éléments individuels dans les objets du fichier :

{"a": {"b": "x1","c": "y1"}},
{"a": {"b": "x2","c": "y2"}}
Copy

Cet exemple suppose que le fichier est nommé /tmp/data1.json et qu’il se trouve dans le répertoire racine dans un environnement macOS ou Linux.

Pour mettre en zone de préparation et interroger le fichier :

-- Create a file format
CREATE OR REPLACE FILE FORMAT my_json_format TYPE = 'json';

-- Create an internal stage
CREATE OR REPLACE STAGE mystage2 FILE_FORMAT = my_json_format;

-- Stage the data file
PUT file:///tmp/data1.json @mystage2;

-- Query the repeating a.b element in the staged file
SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz;

+--------------------+
| PARSE_JSON($1):A.B |
|--------------------|
| "x1"               |
| "x2"               |
+--------------------+
Copy