Évolution du schéma de table

Les données semi-structurées ont tendance à évoluer dans le temps. Les systèmes qui génèrent des données ajoutent de nouvelles colonnes pour accueillir des informations supplémentaires, ce qui oblige les tables en aval à évoluer en conséquence.

La structure des tables dans Snowflake peut évoluer automatiquement pour prendre en charge la structure des nouvelles données reçues des sources de données. Snowflake prend en charge ce qui suit :

  • L’ajout automatique de nouvelles colonnes.

  • La suppression automatique de la contrainte NOT NULL pour les colonnes manquantes dans les nouveaux fichiers de données.

Pour activer l’évolution des schémas de table, procédez comme suit :

  • Si vous créez une nouvelle table, définissez le paramètre ENABLE_SCHEMA_EVOLUTION sur TRUE lorsque vous utilisez la commande CREATE TABLE.

  • Pour une table existante, modifiez la table à l’aide de la commande ALTER TABLE et définissez le paramètre ENABLE_SCHEMA_EVOLUTION sur TRUE.

Le chargement de données à partir de fichiers fait évoluer les colonnes des tables lorsque toutes les conditions suivantes sont réunies :

  • L’instruction COPY INTO <table> inclut l’option MATCH_BY_COLUMN_NAME.

  • Le rôle utilisé pour charger les données dispose du privilège EVOLVE SCHEMA ou OWNERSHIP sur la table.

En outre, pour l’évolution du schéma avec CSV, lorsqu’il est utilisé avec MATCH_BY_COLUMN_NAME et PARSE_HEADER, ERROR_ON_COLUMN_COUNT_MISMATCH doit être défini comme faux.

L’évolution du schéma est une fonction autonome, mais elle peut être utilisée conjointement avec la prise en charge de la récupération des définitions de colonnes à partir d’un ensemble de fichiers dans le stockage dans le Cloud. Combinées, ces fonctions permettent aux pipelines de données continus de créer de nouvelles tables à partir d’un ensemble de fichiers de données dans le stockage dans le Cloud, puis de modifier les colonnes des tables à mesure que le schéma des nouveaux fichiers de données sources évolue avec des ajouts ou des suppressions de colonnes.

Notes sur l’utilisation

  • Cette fonction prend en charge les fichiers Apache Avro, Apache Parquet, CSV, JSON et ORC.

  • Cette fonction est limitée aux instructions COPY INTO <table> et aux chargements de données Snowpipe. INSERT ne peut pas faire évoluer automatiquement le schéma de la table cible.

  • Les chargements de données Snowpipe Streaming ne sont pas pris en charge avec l’évolution du schéma.

  • Par défaut, cette fonction est limitée à l’ajout d’un maximum de 10 colonnes ou à l’évolution d’un seul schéma par opération COPY. Pour demander plus de 10 colonnes ajoutées ou un schéma par opération COPY, contactez le support Snowflake.

  • Il n’y a pas de limite à la suppression des contraintes de colonne NOT NULL.

Exemples

L’exemple suivant crée une table avec des définitions de colonnes dérivées d’un ensemble de données Parquet. Si l’évolution automatique du schéma de table est activée, les chargements de données supplémentaires provenant de fichiers Parquet et comportant des paires nom/valeur supplémentaires ajoutent automatiquement des colonnes à la table :

Notez que la zone de préparation mystage et le format de fichier my_parquet_format référencés dans l’instruction doivent déjà exister. Un ensemble de fichiers doit déjà être en zone de préparation dans l’emplacement de stockage Cloud référencé dans la définition de la zone de préparation.

Cet exemple s’appuie sur un exemple de la rubrique 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
Langage: Français