Evolução do esquema da tabela

Os dados semiestruturados tendem a evoluir com o tempo. Os sistemas que geram dados adicionam novas colunas para acomodar informações adicionais, o que requer que as tabelas subsequentes evoluam de acordo.

A estrutura das tabelas no Snowflake pode evoluir automaticamente para oferecer suporte à estrutura de novos dados recebidos das fontes de dados. O Snowflake oferece suporte ao seguinte:

  • Adição automática de novas colunas.

  • Descarte automático da restrição NOT NULL de colunas que estão faltando em novos arquivos de dados.

Para habilitar a evolução do esquema da tabela, faça o seguinte:

  • Se você estiver criando uma nova tabela, defina o parâmetro ENABLE_SCHEMA_EVOLUTION como TRUE quando você usa o comando CREATE TABLE.

  • Para uma tabela existente, modifique a tabela usando o comando ALTER TABLE e defina o parâmetro ENABLE_SCHEMA_EVOLUTION como TRUE.

O carregamento de dados de arquivos evolui as colunas da tabela quando todas as condições a seguir forem verdadeiras:

  • A instrução COPY INTO <tabela> inclui a opção MATCH_BY_COLUMN_NAME.

  • A função usada para carregar os dados tem o privilégio EVOLVE SCHEMA ou OWNERSHIP na tabela.

Além disso, para a evolução do esquema com CSV, quando usado com MATCH_BY_COLUMN_NAME e PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH deve ser definido como falso.

A evolução do esquema é um recurso independente, mas pode ser usado em conjunto com o suporte para recuperar as definições de coluna de um conjunto de arquivos no armazenamento em nuvem. Em combinação, esses recursos permitem que pipelines de dados contínuos criem novas tabelas a partir de um conjunto de arquivos de dados no armazenamento em nuvem e, em seguida, modifiquem colunas das tabelas à medida que o esquema de novos arquivos de dados de origem evolui com adições ou exclusões de colunas.

Notas de uso

  • Este recurso oferece suporte a arquivos Apache Avro, Apache Parquet, CSV, JSON eORC.

  • Este recurso é limitado às instruções COPY INTO <tabela> e carregamentos de dados do Snowpipe. Operações INSERT não podem evoluir o esquema da tabela de destino automaticamente.

  • Cargas de dados do Snowpipe Streaming não são compatíveis com a evolução do esquema.

  • Por padrão, esse recurso está limitado a adicionar no máximo 10 colunas ou evoluir no máximo 1 esquema por operação de COPY. Para solicitar mais de 10 colunas adicionadas ou 1 esquema por operação de COPY, entre em contato com o suporte Snowflake.

  • Não há limite para descartar restrições de coluna NOT NULL.

Exemplos

O exemplo a seguir cria uma tabela com definições de coluna derivadas de um conjunto de dados Parquet. Com a evolução automática do esquema de tabela habilitada para a tabela, outros carregamentos de dados de arquivos Parquet com pares de nome/valor adicionais adicionam automaticamente colunas à tabela:

Observe que o estágio mystage e o formato de arquivo my_parquet_format mencionados na instrução já devem existir. Um conjunto de arquivos já deve ser preparado no local de armazenamento em nuvem referenciado na definição do estágio.

Este exemplo se baseia em um exemplo no tópico INFER_SCHEMA:

-- Create table t1 in schema d1.s1, with the column definitions derived from the staged file1.parquet file.
USE SCHEMA d1.s1;

CREATE OR REPLACE TABLE t1
  USING TEMPLATE (
    SELECT ARRAY_AGG(object_construct(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/file1.parquet',
          FILE_FORMAT=>'my_parquet_format'
        )
      ));

-- Row data in file1.parquet.
+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
| a    | b    | c    |
+------+------+------+

-- Describe the table.
-- Note that column c2 is required in the Parquet file metadata. Therefore, the NOT NULL constraint is set for the column.
DESCRIBE TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| COL1 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL2 | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL3 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

-- Use the SECURITYADMIN role or another role that has the global MANAGE GRANTS privilege.
-- Grant the EVOLVE SCHEMA privilege to any other roles that could insert data and evolve table schema in addition to the table owner.

GRANT EVOLVE SCHEMA ON TABLE d1.s1.t1 TO ROLE r1;

-- Enable schema evolution on the table.
-- Note that the ENABLE_SCHEMA_EVOLUTION property can also be set at table creation with CREATE OR REPLACE TABLE
ALTER TABLE t1 SET ENABLE_SCHEMA_EVOLUTION = TRUE;

-- Load a new set of data into the table.
-- The new data drops the NOT NULL constraint on the col2 column.
-- The new data adds the new column col4.
COPY INTO t1
  FROM @mystage/file2.parquet
  FILE_FORMAT = (type=parquet)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Row data in file2.parquet.
+------+------+------+
| col1 | COL3 | COL4 |
|------+------+------|
| d    | e    | f    |
+------+------+------+

-- Describe the table.
DESCRIBE TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| COL1 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL2 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL3 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL4 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
-- Note that since MATCH_BY_COLUMN_NAME is set as CASE_INSENSITIVE, all column names are retrieved as uppercase letters.
Copy
Linguagem: Português