Consulta de metadados para arquivos preparados¶

O Snowflake gera automaticamente metadados para arquivos em estágios internos (ou seja, Snowflake) ou externos (Amazon S3, Google Cloud Storage ou Microsoft Azure). Estes metadados são “armazenados” em colunas virtuais que podem ser:

Consultadas usando uma instrução padrão SELECT.
Carregadas em uma tabela, junto com as colunas de dados regulares, usando COPY INTO <tabela>. Para obter mais informações gerais sobre como consultar arquivos de dados preparados, consulte Consulta de dados em arquivos preparados.

Neste tópico:

Colunas de metadados¶

Atualmente, as seguintes colunas de metadados podem ser consultadas ou copiadas em tabelas:

METADATA$FILENAME: Nome do arquivo de dados preparado a que pertence a linha atual. Inclui o caminho para o arquivo de dados no estágio.
METADATA$FILE_ROW_NUMBER: Número de linha para cada registro no arquivo de dados preparado.
METADATA$FILE_CONTENT_KEY: Soma de verificação do arquivo de dados preparado a que pertence a linha atual.
METADATA$FILE_LAST_MODIFIED: Último carimbo de data/hora modificado do arquivo de dados preparado ao qual pertence a linha atual. Retornado como TIMESTAMP_NTZ.
METADATA$START_SCAN_TIME: Carimbo de data/hora inicial para cada registro no arquivo de dados preparado. Retornado como TIMESTAMP_LTZ.

Limitações da consulta¶

Os metadados não podem ser inseridos em linhas de tabela existentes.
As colunas de metadados só podem ser consultadas pelo nome; como tal, elas não estão incluídas na saída de nenhuma das seguintes instruções:

Exemplos de consulta¶

Exemplo 1: Consultar colunas de metadados para um arquivo CSV¶

O exemplo a seguir ilustra a preparação de múltiplos arquivos de dados CSV (com o mesmo formato de arquivo) e depois a consulta das colunas de metadados, bem como das colunas de dados regulares, nos arquivos.

Este exemplo considera que os arquivos têm os seguintes nomes e estão localizados no diretório raiz em um ambiente macOS ou Linux:

/tmp/data1.csv contém dois registros:
```
a|b
c|d
```
Copy
/tmp/data2.csv contém dois registros:
```
e|f
g|h
```
Copy

Para preparar e consultar os arquivos:

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

Nota

O formato do arquivo é necessário neste exemplo para analisar corretamente os campos nos arquivos preparados. Na segunda consulta, o formato do arquivo é omitido, fazendo com que o delimitador de campo | seja ignorado e resultando no retorno dos valores para $1 e $2.

Entretanto, se o formato do arquivo estiver incluído na definição do estágio, você pode omiti-lo da instrução SELECT. Consulte o próximo exemplo para detalhes.

Exemplo 2: Consultar colunas de metadados para um arquivo JSON¶

Este exemplo ilustra a preparação de um arquivo de dados JSON contendo os seguintes objetos e depois a consulta das colunas de metadados, assim como os objetos, no arquivo:

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

Copy

Este exemplo assume que o arquivo é chamado /tmp/data1.json e está localizado no diretório raiz em um ambiente macOS ou Linux.

Para preparar e consultar o arquivo:

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

Exemplo 3: Carregar colunas de metadados em uma tabela¶

O comando COPY INTO <tabela> suporta a cópia de metadados de arquivos de dados preparados para uma tabela de destino. Use a sintaxe de transformação de dados (ou seja, uma lista SELECT) em sua instrução COPY. Para obter mais informações sobre a transformação de dados usando uma instrução COPY, consulte Transformação de dados durante um carregamento.

O exemplo a seguir carrega as colunas de metadados e colunas de dados regulares de Exemplo 1: Consultar colunas de metadados para um arquivo CSV em uma tabela:

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