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

Snowflake génère automatiquement des métadonnées pour des fichiers dans des zones de préparation internes (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 :

Interrogées à l’aide d’une instruction standard SELECT.
Chargées dans une table, avec les colonnes de données régulières, à l’aide de la commande COPY INTO <table>. Pour obtenir des informations générales sur l’interrogation de fichiers de données préparés, voir Interrogation des données dans des fichiers mis en zone de préparation.

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: Nombre de lignes pour chaque enregistrement du fichier de données en zone de préparation.
METADATA$FILE_CONTENT_KEY: Somme de contrôle du fichier de données en zone de préparation auquel la ligne actuelle appartient.
METADATA$FILE_LAST_MODIFIED: Horodatage de la dernière modification du fichier de données en zone de préparation auquel la ligne actuelle appartient. Renvoyé comme TIMESTAMP_NTZ.
METADATA$START_SCAN_TIME: Horodatage du début de l’opération pour chaque enregistrement du fichier de données en zone de préparation. Renvoyé comme TIMESTAMP_LTZ.

Limites de requête¶

Les métadonnées ne peuvent pas être insérées dans les lignes de table existantes.
Les colonnes de métadonnées ne peuvent être interrogées que par nom. Et donc, elles ne sont pas incluses dans la sortie des instructions suivantes :

Exemples de requête¶

Exemple 1 : interrogation de colonnes de métadonnées pour 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
```
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 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, METADATA$FILE_CONTENT_KEY, METADATA$FILE_LAST_MODIFIED, METADATA$START_SCAN_TIME, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | METADATA$FILE_CONTENT_KEY | METADATA$FILE_LAST_MODIFIED |      METADATA$START_SCAN_TIME | $1 | $2 |
|-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----|
| data2.csv.gz      |                        1 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:15:57.000 |  2023-02-02 01:31:00.713 +0000| e  | f  |
| data2.csv.gz      |                        2 | aa387aabb2ccedaaaaac123b8 |     2022-05-01 10:05:35.000 |  2023-02-02 01:31:00.755 +0000| g  | h  |
| data1.csv.gz      |                        1 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 10:15:26.000 |  2023-02-02 01:31:00.778 +0000| a  | b  |
| data1.csv.gz      |                        2 | 2289aab2abcdeaacaaac348d0 |     2022-09-10 11:15:55.000 |  2023-02-02 01:31:00.778 +0000| c  | d  |
+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----+

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

+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | METADATA$FILE_CONTENT_KEY | METADATA$FILE_LAST_MODIFIED |      METADATA$START_SCAN_TIME | $1  | $2   |
|-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+-----+------|
| data2.csv.gz      |                        1 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:15:57.000 |  2023-02-02 01:31:00.713 +0000| e|f | NULL |
| data2.csv.gz      |                        2 | aa387aabb2ccedaaaaac123b8 |     2022-05-01 10:05:35.000 |  2023-02-02 01:31:00.755 +0000| g|h | NULL |
| data1.csv.gz      |                        1 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 10:15:26.000 |  2023-02-02 01:31:00.778 +0000| a|b | NULL |
| data1.csv.gz      |                        2 | 2289aab2abcdeaacaaac348d0 |     2022-09-10 11:15:55.000 |  2023-02-02 01:31:00.778 +0000| c|d | 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 l’exemple suivant pour plus de détails.

Exemple 2 : interrogation de colonnes de métadonnées pour 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"}}

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

Copy

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 lors d’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 pour un fichier CSV dans une table :

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number int,
  file_content_key varchar,
  file_last_modified timestamp_ntz,
  start_scan_time timestamp_ltz,
  col1 varchar,
  col2 varchar
);

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

SELECT * FROM table1;

+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | FILE_CONTENT_KEY          | FILE_LAST_MODIFIED      |  START_SCAN_TIME              | COL1 | COL2 |
|--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+
| data1.csv.gz | 1               | 39ab11bb2cdeacdcdac1234d9 | 2022-08-03 10:15:26.000 | 2023-02-02 01:31:00.778 +0000 | a    | b    |
| data1.csv.gz | 2               | 2289aab2abcdeaacaaac348d0 | 2022-09-10 11:15:55.000 | 2023-02-02 01:31:00.778 +0000 | c    | d    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+

Copy