Categorias:

Funções de tabela

INFER_SCHEMA

Detecta automaticamente o esquema de metadados do arquivo em um conjunto de arquivos de dados preparados que contêm dados semiestruturados e recupera as definições das colunas.

A função GENERATE_COLUMN_DESCRIPTION se baseia na saída da função INFER_SCHEMA para simplificar a criação de novas tabelas, tabelas externas ou exibições (usando o comando CREATE <objeto> apropriado) com base nas definições das colunas dos arquivos preparados.

Você pode executar o comando CREATE TABLE, CREATE EXTERNAL TABLE ou CREATE ICEBERG TABLE com a cláusula USING TEMPLATE para criar uma nova tabela ou tabela externa com as definições de coluna derivadas da saída da função INFER_SCHEMA.

Nota

Esta função oferece suporte a arquivos Apache Parquet, Apache Avro, ORC, JSON e CSV.

Sintaxe

INFER_SCHEMA(
  LOCATION => '{ internalStage | externalStage }'
  , FILE_FORMAT => '<file_format_name>'
  , FILES => ( '<file_name>' [ , '<file_name>' ] [ , ... ] )
  , IGNORE_CASE => TRUE | FALSE
  , MAX_FILE_COUNT => <num>
  , MAX_RECORDS_PER_FILE => <num>
  , KIND => '<kind_name>'
)
Copy

Onde:

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>][/<filename>]
  | @~[/<path>][/<filename>]
Copy
externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>][/<filename>]
Copy

Argumentos

LOCATION => '...'

Nome do estágio interno ou externo onde os arquivos são armazenados. Opcionalmente, inclua um caminho para um ou mais arquivos no local de armazenamento em nuvem; caso contrário, a função INFER_SCHEMA digitaliza arquivos em todos os subdiretórios do estágio:

@[namespace.]int_stage_name[/path][/filename]

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

@[namespace.]ext_stage_name[/path][/filename]

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

@~[/path][/filename]

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

Nota

Esta função SQL oferece suporte somente a estágios nomeados (internos ou externos) e estágios do usuário. Ela não oferece suporte aos estágios da tabela.

FILES => ( 'file_name' [ , 'file_name' ] [ , ... ] )

Especifica uma lista de um ou mais arquivos (separados por vírgulas) em um conjunto de arquivos preparados que contêm dados semiestruturados. Os arquivos já devem ter sido preparados no local interno do Snowflake ou no local externo especificado no comando. Se qualquer um dos arquivos especificados não puder ser encontrado, a consulta será abortada.

O número máximo de nomes de arquivos que podem ser especificados é 1.000.

Nota

Somente para estágios externos (Amazon S3, Google Cloud Storage ou Microsoft Azure), o caminho do arquivo é definido concatenando o URL na definição do estágio e a lista de nomes de arquivos resolvidos.

No entanto, o Snowflake não insere um separador implícito entre o caminho e os nomes dos arquivos. Você deve incluir explicitamente um separador (/) no final do URL na definição do estágio ou no início de cada nome de arquivo especificado neste parâmetro.

FILE_FORMAT => 'file_format_name'

Nome do objeto de formato de arquivo que descreve os dados contidos nos arquivos preparados. Para obter mais informações, consulte CREATE FILE FORMAT.

IGNORE_CASE => TRUE | FALSE

Especifica se os nomes de colunas detectados de arquivos preparados são tratados de forma a diferenciar maiúsculas e minúsculas. Por padrão, o valor é FALSE, o que significa que Snowflake preserva a caixa de caracteres alfabéticos ao obter nomes de colunas. Se você especificar o valor como TRUE, os nomes de colunas serão tratados de forma a não diferenciar maiúsculas e minúsculas, e todos os nomes de coluna serão obtidos como letras maiúsculas.

MAX_FILE_COUNT => num

Especifica o número máximo de arquivos verificados do estágio. Esta opção é recomendada para um grande número de arquivos que possuem esquema idêntico entre arquivos. Observe que esta opção não consegue determinar quais arquivos serão verificados. Se você quiser verificar arquivos específicos, use a opção FILES.

MAX_RECORDS_PER_FILE => num

Especifica o número máximo de registros verificados por arquivo. Esta opção se aplica apenas aos arquivos CSV e JSON. Recomenda-se usar esta opção para arquivos grandes. Mas observe que esta opção pode afetar a precisão da detecção do esquema.

KIND => 'kind_name'

Especifica o tipo de esquema de metadados de arquivo que pode ser verificado a partir do estágio. Por padrão, o valor é STANDARD, o que significa que o esquema de metadados do arquivo que pode ser verificado a partir do estágio é para tabelas Snowflake e a saída são tipos de dados Snowflake. Se você especificar o valor como ICEBERG, o esquema será para as tabelas do Apache Iceberg e a saída será para os tipos de dados do Iceberg.

Nota

Se você estiver inferindo arquivos Parquet para criar tabelas Iceberg, recomendamos enfaticamente que defina KIND => 'ICEBERG'. Caso contrário, as definições de coluna retornadas pela função podem estar incorretas.

Saída

A função retorna as seguintes colunas:

Nome da coluna

Tipo de dados

Descrição

COLUMN_NAME

TEXT

Nome de uma coluna nos arquivos preparados.

TYPE

TEXT

Tipo de dados da coluna.

NULLABLE

BOOLEAN

Especifica se as linhas na coluna podem armazenar NULL em vez de um valor. Atualmente, a nulidade inferida de uma coluna pode se aplicar a um arquivo de dados, mas não a outros no conjunto escaneado.

EXPRESSION

TEXT

Expressão da coluna no formato $1:COLUMN_NAME::TYPE (principalmente para tabelas externas). Se IGNORE_CASE for especificado como TRUE, a expressão da coluna estará no formato GET_IGNORE_CASE ($1, COLUMN_NAME)::TYPE.

FILENAMES

TEXT

Nomes dos arquivos que contêm a coluna.

ORDER_ID

NUMBER

Ordem das colunas nos arquivos preparados.

Notas de uso

  • Para arquivos CSV, você pode definir nomes de colunas usando a opção de formato de arquivo PARSE_HEADER = [ TRUE | FALSE ].

    • Se a opção estiver definida como TRUE, os cabeçalhos da primeira linha serão usados para determinar os nomes das colunas.

    • O valor padrão FALSE retornará os nomes das colunas como c , onde é a posição da coluna. Observe que a opção SKIP_HEADER não é suportada com PARSE_HEADER = TRUE.

    • A opção PARSE_HEADER não é suportada para tabelas externas.

  • Para ambos os arquivos CSV e JSON, as seguintes opções de formato de arquivo não são suportadas atualmente: DATE_FORMAT, TIME_FORMAT e TIMESTAMP_FORMAT.

  • A opção de formato de arquivo JSON TRIM_SPACE não é suportada.

  • As anotações científicas (por exemplo, 1E2) em arquivos JSON são recuperadas como tipo de dados REAL.

  • Todas as variações de tipos de dados de carimbo de data/hora são recuperadas como TIMESTAMP_NTZ sem nenhuma informação de fuso horário.

  • Para ambos os arquivos CSV e JSON, todas as colunas são identificadas como NULLABLE.

  • Tanto para KIND => 'STANDARD' quanto para KIND => 'ICEBERG', quando o arquivo especificado no estágio contém tipos de dados aninhados, somente o primeiro nível de aninhamento é suportado; níveis mais profundos não são suportados.

Exemplos

Definições da coluna Snowflake

Recupere as definições de coluna do Snowflake para arquivos Parquet no estágio mystage:

-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
  TYPE = parquet;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+
Copy

Similar ao exemplo anterior, mas especifique um único arquivo Parquet no estágio mystage:

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/geography/cities.parquet'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+
Copy

Recupere as definições de coluna do Snowflake para arquivos Parquet no estágio mystage com IGNORE_CASE especificado como TRUE. Na saída retornada, todos os nomes de colunas são obtidos como letras maiúsculas.

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      , IGNORE_CASE=>TRUE
      )
    );

+-------------+---------+----------+----------------------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION                             | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+---------------------------------------------|----------+
| CONTINENT   | TEXT    | True     | GET_IGNORE_CASE ($1, CONTINENT)::TEXT  | geography/cities.parquet | 0        |
| COUNTRY     | VARIANT | True     | GET_IGNORE_CASE ($1, COUNTRY)::VARIANT | geography/cities.parquet | 1        |
+-------------+---------+----------+---------------------+---------------------------------------------+----------+
Copy

Recupere as definições de coluna do Snowflake para os arquivos JSON no estágio mystage:

-- Create a file format that sets the file type as JSON.
CREATE FILE FORMAT my_json_format
  TYPE = json;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/json/'
      , FILE_FORMAT=>'my_json_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.json     | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.json     | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.json     | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+
Copy

Cria uma tabela usando o esquema detectado de arquivos preparados JSON.

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/json/',
          FILE_FORMAT=>'my_json_format'
        )
      ));
Copy

Nota

Usar * para ARRAY_AGG(OBJECT_CONSTRUCT()) pode resultar em erro se o resultado retornado for maior que 16MB. Recomenda-se evitar o uso de * para conjuntos de resultados maiores e usar apenas as colunas obrigatórias, COLUMN NAME, TYPE e NULLABLE, para a consulta. A coluna opcional ORDER_ID pode ser incluída ao usar WITHIN GROUP (ORDER BY order_id).

Recuperar as definições das colunas para os arquivos CSV no estágio mystage e carregue os arquivos CSV usando MATCH_BY_COLUMN_NAME:

-- Create a file format that sets the file type as CSV.
CREATE FILE FORMAT my_csv_format
  TYPE = csv
  PARSE_HEADER = true;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/csv/'
      , FILE_FORMAT=>'my_csv_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.csv      | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.csv      | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.csv      | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+

-- Load the CSV file using MATCH_BY_COLUMN_NAME.
COPY INTO mytable FROM @mystage/csv/
  FILE_FORMAT = (
    FORMAT_NAME= 'my_csv_format'
  )
  MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;
Copy

Definições da coluna Iceberg

Recuperar as definições de coluna Iceberg para arquivos Parquet no estágio mystage:

-- Create a file format that sets the file type as Parquet.
  CREATE OR REPLACE FILE FORMAT my_parquet_format
    TYPE = PARQUET
    USE_VECTORIZED_SCANNER = TRUE;

-- Query the INFER_SCHEMA function.
SELECT *
FROM TABLE(
  INFER_SCHEMA(
    LOCATION=>'@mystage'
    , FILE_FORMAT=>'my_parquet_format'
    , KIND => 'ICEBERG'
    )
  );
Copy

Saída:

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| id          | INT     | False    | $1:id::INT          | sales/customers.parquet   | 0       |
| custnum     | INT     | False    | $1:custnum::INT     | sales/customers.parquet   | 1       |
+-------------+---------+----------+---------------------+--------------------------+----------+

Cria uma tabela Apache Iceberg™ usando o esquema detectado a partir de arquivos JSON preparados.

 -- Create a file format that sets the file type as Parquet.
 CREATE OR REPLACE FILE FORMAT my_parquet_format
   TYPE = PARQUET
   USE_VECTORIZED_SCANNER = TRUE;

-- Create an Iceberg table.
CREATE ICEBERG TABLE myicebergtable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    WITHIN GROUP (ORDER BY order_id)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage',
          FILE_FORMAT=>'my_parquet_format',
          KIND => 'ICEBERG'
        )
      ))
... {rest of the ICEBERG options}
;
Copy

Nota

Usar * para ARRAY_AGG(OBJECT_CONSTRUCT()) pode resultar em um erro se o resultado retornado for maior que 16MB. Recomendamos evitar o uso de * para conjuntos de resultados maiores e usar apenas as colunas necessárias, COLUMN NAME, TYPE e NULLABLE, para a consulta. A coluna opcional ORDER_ID pode ser incluída ao usar WITHIN GROUP (ORDER BY order_id).

Linguagem: Português