Consulta de dados em arquivos preparados

O Snowflake suporta o uso do SQL padrão para consultar arquivos de dados localizados em um estágio interno (ou seja, Snowflake) ou nomeado externo (Amazon S3, Google Cloud Storage ou Microsoft Azure). Isto pode ser útil para inspecionar/visualizar o conteúdo dos arquivos preparados, particularmente antes ou depois de carregar ou descarregar os dados.

Além disso, ao referenciar colunas de metadados em um arquivo preparado, uma consulta de dados preparada pode retornar informações adicionais, tais como nome do arquivo e números de linhas, sobre o arquivo.

O Snowflake utiliza o suporte para consultas de dados preparadas para permitir transformar dados durante o carregamento.

Nota

Esta funcionalidade é principalmente para realizar consultas simples, particularmente ao carregar e/ou transformar dados, e não é destinada a substituir os dados de carregamento em tabelas e realizar consultas nas tabelas.

Neste tópico:

Sintaxe e parâmetros de consulta

Consulte arquivos de dados preparados usando uma instrução SELECT com a seguinte sintaxe:

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

Para a sintaxe de transformação de dados durante um carregamento, consulte COPY INTO <tabela>.

Importante

A lista de objetos retornados para um estágio externo pode incluir um ou mais “blobs de diretório”; essencialmente, caminhos que terminam em um caractere de barra (/), por exemplo:

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

Esses blobs são listados quando os diretórios são criados no console do Google Cloud Platform, em vez de usar qualquer outra ferramenta fornecida pelo Google.

Instruções SELECT que fazem referência a um estágio podem falhar quando a lista de objetos inclui blobs de diretório. Para evitar erros, recomendamos usar a correspondência de padrão de arquivo para identificar os arquivos para inclusão (ou seja, a cláusula PATTERN) quando a lista de arquivos para um estágio inclui blobs de diretório.

Parâmetros obrigatórios

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

Especifica um conjunto explícito de campos/colunas em arquivos de dados preparados em um local interno ou externo, onde:

alias

Especifica o alias opcional “tabela” definido, se houver, na cláusula FROM.

file_col_num

Especifica o número posicional do campo/coluna (no arquivo) que contém os dados a serem carregados (1 para o primeiro campo, 2 para o segundo campo etc.)

element

Especifica o caminho e o nome do elemento de um valor repetido (aplica-se somente a arquivos de dados semiestruturados).

internal_location ou external_location

Especifica o local onde os arquivos de dados são preparados:

  • internal_location é o especificador de URI para o local no Snowflake onde os arquivos contendo dados são preparados:

    @[namespace.]internal_stage_name[/path]

    Os arquivos estão no estágio interno nomeado especificado.

    @[namespace.]%table_name[/path]

    Os arquivos estão no estágio para a tabela especificada.

    @~[/path]

    Os arquivos estão no estágio para o usuário atual.

  • external_location é o especificador de URI para o estágio externo nomeado ou local externo (Amazon S3, Google Cloud Storage ou Microsoft Azure) onde os arquivos contendo dados são preparados:

    @[namespace.]external_stage_name[/path]

    Os arquivos estão no estágio externo nomeado especificado.

Onde:

  • namespace é o banco de dados e/ou esquema no qual reside o estágio interno ou externo. É opcional se um banco de dados e um esquema estiverem em uso atualmente dentro da sessão do usuário; caso contrário, ele é obrigatório.

  • O parâmetro opcional path restringe o conjunto de arquivos a serem consultados aos arquivos sob o prefixo da pasta. Se path for especificado, mas nenhum arquivo for explicitamente nomeado no caminho, todos os arquivos de dados no caminho são consultados.

Nota

  • A cadeia de caracteres do URI para um local de armazenamento externo (Amazon S3, Google Cloud Storage ou Microsoft Azure) deve ser incluída entre aspas simples; entretanto, você pode incluir qualquer cadeia de caracteres de URI entre aspas simples, o que permite caracteres especiais, incluindo espaços, em nomes de local e arquivos. Por exemplo:

    Interno:

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

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

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

  • Os modificadores de caminhos relativos, como /./ e /../, são interpretados literalmente, porque “caminhos” são prefixos literais para um nome. Por exemplo:

    S3:

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

    Nessas instruções COPY, o sistema procura por um arquivo literalmente chamado ./../a.csv no local de armazenamento.

Parâmetros opcionais

( FILE_FORMAT => 'namespace.named_file_format' )

Especifica um formato de arquivo nomeado que descreve o formato dos arquivos de dados preparados a serem consultados.

Observe que este parâmetro é opcional se uma das seguintes condições forem verdadeiras:

  • Os arquivos são formatados no formato de arquivo padrão (CSV) com os delimitadores padrão: , (como o delimitador de campo) e o caractere de nova linha (como o delimitador de registro).

  • Os arquivos estão em um estágio interno ou externo e a definição do estágio descreve o formato do arquivo.

Se estiver referenciando um formato de arquivo no namespace atual da sua sessão de usuário, você pode omitir as aspas simples do identificador do formato.

Caso contrário, este parâmetro é obrigatório. Para obter mais detalhes, consulte Formatos de arquivo (neste tópico).

namespace opcionalmente especifica o banco de dados e/ou esquema para a tabela, na forma de database_name.schema_name ou schema_name. É opcional se um banco de dados e um esquema estiverem em uso atualmente dentro da sessão do usuário; caso contrário, ele é obrigatório.

Se o identificador contiver espaços, caracteres especiais ou caracteres com letras maiúsculas e minúsculas, toda a cadeia de caracteres deve ser delimitada por aspas duplas. Os identificadores delimitados por aspas duplas também diferenciam letras maiúsculas de minúsculas.

PATTERN => 'regex_pattern'

Uma cadeia de caracteres de padrão de expressão regular, entre aspas simples, especificando os nomes de arquivos e/ou caminhos no estágio externo a serem combinados.

Dica

Para o melhor desempenho, tente evitar a aplicação de padrões que filtram em um grande número de arquivos.

alias

Especifica uma alias de “tabela” para o local interno/externo onde os arquivos são preparados.

Formatos de arquivo

Para analisar um arquivo de dados preparado, é necessário descrever seu formato de arquivo. O formato padrão do arquivo é o texto delimitado por caracteres UTF-8 (isto é, CSV), com o caractere de vírgula (,) como delimitador de campo e o caractere de nova linha como delimitador de registro. Se os dados de origem estiverem em outro formato (JSON, Avro, etc.), é necessário especificar o tipo de formato de arquivo correspondente (e opções).

Para especificar explicitamente as opções de formato de arquivo, defina-as de uma das seguintes maneiras:

Consulta de arquivos de dados preparados:

Como opções de formato de arquivo especificadas para um formato de arquivo nomeado ou objeto de preparação. O formato do arquivo/objeto de preparação nomeado pode então ser referenciado na instrução SELECT.

Carregamento de colunas a partir de arquivos de dados preparados:
  • Como opções de formato de arquivo especificadas diretamente no COPY INTO <tabela>.

  • Como opções de formato de arquivo especificadas para um formato de arquivo nomeado ou objeto de preparação. O formato do arquivo/objeto de preparação nomeado pode então ser referenciado na instrução COPY INTO <tabela>.

Exemplos de consulta

Exemplo 1: Consultar colunas em 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 dados 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 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

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 Exemplo 3: Consultar elementos em um arquivo JSON.

Exemplo 2: Chamar funções ao consultar um arquivo de dados preparado

Obtenha o código ASCII para o primeiro caractere de cada coluna nos arquivos de dados preparados em Exemplo 1: Consultar as colunas em um arquivo 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

Nota

Se o formato do arquivo estiver incluído na definição do estágio, você pode omiti-lo da instrução SELECT. Consulte Exemplo 3: Consultar elementos em um arquivo JSON.

Exemplo 3: Consultar elementos em um arquivo JSON

Este exemplo ilustra a preparação de um arquivo de dados JSON contendo os seguintes objetos e depois a consulta de elementos individuais nos objetos do 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 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