Interrogation de métadonnées de fichiers préparés

Snowflake génère automatiquement des métadonnées pour des fichiers (c’est-à-dire Snowflake) ou externes (Amazon S3, Google Cloud Storage ou Microsoft Azure). Ces métadonnées sont « stockées » dans des colonnes virtuelles qui peuvent être :

Dans ce chapitre :

Colonnes de métadonnées

Actuellement, les colonnes de métadonnées suivantes peuvent être interrogées ou copiées dans des tables :

METADATA$FILENAME

Nom du fichier de données préparé auquel la ligne actuelle appartient. Inclut le chemin d’accès au fichier de données dans la zone de préparation.

METADATA$FILE_ROW_NUMBER

Numéro de ligne pour chaque enregistrement dans le fichier de données préparé du conteneur.

Limites de requête

Exemples de requête

Exemple 1 : Interrogation de colonnes de métadonnées d’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 des colonnes de métadonnées, ainsi que des colonnes de données régulières, 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
    
  • /tmp/data2.csv contient deux enregistrements :

    e|f
    g|h
    

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 a data file
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
-- Note that the table alias is provided to make the statement easier to read and is not required
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1 | $2 |
|-------------------+--------------------------+----+----|
| data2.csv.gz      |                        1 | e  | f  |
| data2.csv.gz      |                        2 | g  | h  |
| data1.csv.gz      |                        1 | a  | b  |
| data1.csv.gz      |                        2 | c  | d  |
+-------------------+--------------------------+----+----+

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 t;

+-------------------+--------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1  | $2   |
|-------------------+--------------------------+-----+------|
| data2.csv.gz      |                        1 | e|f | NULL |
| data2.csv.gz      |                        2 | g|h | NULL |
| data1.csv.gz      |                        1 | a|b | NULL |
| data1.csv.gz      |                        2 | c|d | NULL |
+-------------------+--------------------------+-----+------+

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 l’exemple suivant pour plus de détails.

Exemple 2 : Interrogation de colonnes de métadonnées d’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 colonnes de métadonnées, ainsi que des objets, dans le fichier :

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

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 a data file
PUT file:///tmp/data1.json @mystage2;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, parse_json($1) FROM @mystage2/data1.json.gz;

+-------------------+--------------------------+----------------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | PARSE_JSON($1) |
|-------------------+--------------------------+----------------|
| data1.json.gz     |                        1 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x1", |
|                   |                          |     "c": "y1"  |
|                   |                          |   }            |
|                   |                          | }              |
| data1.json.gz     |                        2 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x2", |
|                   |                          |     "c": "y2"  |
|                   |                          |   }            |
|                   |                          | }              |
+-------------------+--------------------------+----------------+

Exemple 3 : Chargement de colonnes de métadonnées dans une table

La commande COPY INTO <table> prend en charge la copie de métadonnées à partir de fichiers de données préparés vers une table cible. Utilisez la syntaxe de transformation de données (c.-à-d. une liste SELECT) dans votre instruction COPY. Pour plus d’informations sur la transformation des données à l’aide d’une instruction COPY, voir Transformation des données pendant un chargement.

L’exemple suivant charge les colonnes de métadonnées et les colonnes de données classiques à partir de Exemple 1 : Interrogation de colonnes de métadonnées d’un fichier CSV vers une table :

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number varchar,
  col1 varchar,
  col2 varchar
);

COPY INTO table1(filename, file_row_number, col1, col2)
  FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1/data1.csv.gz (file_format => myformat) t);

SELECT * FROM table1;

+--------------+-----------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | COL1 | COL2 |
|--------------+-----------------+------+------|
| data1.csv.gz | 1               | a    | b    |
| data1.csv.gz | 2               | d    | e    |
+--------------+-----------------+------+------+