Créer des tables dynamiques

Ce sujet présente les concepts clés de la création de tables dynamiques.

Avant de commencer, assurez-vous de disposer des privilèges permettant de créer des tables dynamiques et vérifiez que tous les objets utilisés par la requête de table dynamique ont le suivi des modifications activé.

Certaines limitations peuvent s’appliquer à la création de tables dynamiques. Pour une liste complète, voir Limites des tables dynamiques.

Activer le suivi des modifications

Lors de la création d’une table dynamique avec un mode d’actualisation incrémentielle, si le suivi des modifications n’est pas déjà activé sur les tables interrogées, Snowflake tente automatiquement d’activer le suivi des modifications sur ces tables. Afin que les actualisations incrémentielles puissent être prises en charge, il faut que le suivi des modifications soit activé avec une rétention Time Travel différente de zéro sur tous les objets sous-jacents utilisés par une table dynamique.

La table dynamique évolue en même temps que les objets de base. Si vous recréez un objet de base, vous devez réactiver le suivi des modifications.

Note

Snowflake ne tente pas automatiquement d’activer le suivi des modifications sur les tables dynamiques créées avec le mode d’actualisation complète.

Pour activer le suivi des modifications sur un objet de base de données spécifique, utilisez les commandes ALTER TABLE, ALTER VIEW, et autres commandes similaires sur cet objet. L’utilisateur qui crée la table dynamique doit disposer du privilège OWNERSHIP pour pouvoir activer le suivi des modifications sur tous les objets sous-jacents.

Pour vérifier si le suivi des modifications est activé, utilisez les commandes SHOW VIEWS, SHOW TABLES et autres commandes similaires sur les objets sous-jacents et inspectez la colonne change_tracking.

Objets de base pris en charge

Les tables dynamiques prennent en charge les objets de base suivants :

  • Tables

  • Tables Apache Iceberg™ gérées par Snowflake

  • Tables Apache Iceberg™ gérées en externe

Exemple : Créer une table dynamique simple

Supposons que vous souhaitiez créer une table dynamique contenant l”product_id et les colonnes product_name de la table nommée staging_table, et que vous décidiez des points suivants :

  • Vous souhaitez que les données de votre table dynamiques aient au maximum 20 minutes de retard sur les données de staging_table.

  • Vous souhaitez utiliser l’entrepôt mywh pour les ressources de calcul nécessaires pour l”actualisation.

  • Vous souhaitez que le mode d’actualisation soit automatiquement sélectionné.

  • Vous souhaitez que la table dynamique soit actualisée de manière synchrone lors de sa création.

  • Vous souhaitez que le mode d’actualisation soit sélectionné automatiquement et que la table dynamique soit actualisée de manière synchrone lors de sa création.

Pour créer cette table dynamique, exécutez l’instruction SQL CREATE DYNAMIC TABLE suivante :

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = auto
  INITIALIZE = on_create
  AS
    SELECT product_id, product_name FROM staging_table;
Copy

Pour une liste complète des paramètres et de la syntaxe des variantes, voir la référence CREATE DYNAMIC TABLE.

Créer des tables dynamiques qui lisent depuis des tables Apache Iceberg™ gérées par Snowflake ou gérées en externe

Créer une table dynamique à partir d’une table Iceberg est similaire à la création d’une table dynamique à partir d’une table standard. Exécutez l’instruction CREATE DYNAMIC TABLE SQL comme vous le feriez pour une table standard, en utilisant comme objet de base une table gérée par Snowflake ou une table gérée par un catalogue externe.

Les tables dynamiques qui lisent depuis une table Iceberg gérée par Snowflake en tant que table de base sont utiles si vous souhaitez que vos pipelines fonctionnent sur des données dans une table Iceberg gérée par Snowflake ou si vous souhaitez que vos pipelines s’exécutent sur des tables Iceberg écrites par d’autres moteurs. Notez que les moteurs externes ne peuvent pas écrire dans les tables Iceberg gérées par Snowflake. Elles sont en lecture-écriture pour Snowflake et en lecture seule pour les moteurs externes.

Tables dynamiques qui lisent à partir de tables Iceberg gérées par des catalogues externes (non Snowflake), par exemple AWS Glue et écrites par des moteurs comme Apache Spark, sont utiles pour traiter les données de data lakes externes. Vous pouvez créer des tables dynamiques au-dessus de données gérées en externe, en les traitant en continu dans Snowflake sans dupliquer ou ingérer les données.

Limites et considérations relatives à l’utilisation de tables Iceberg

Toutes les limites de tables dynamiques classiques et tables Iceberg dynamiques s’appliquent toujours.

En outre :

  • Toutes les limites des tables de base Iceberg s’appliquent. Pour plus d’informations, voir Considérations et limites.

  • Vous pouvez créer une table dynamique qui lit à partir de tables natives Snowflake, de tables Iceberg gérées par Snowflake et de tables Iceberg gérées en externe.

  • Les tables dynamiques suivent les modifications au niveau du fichier pour les tables de base Iceberg gérées en externe, contrairement aux autres tables de base qui suivent les modifications au niveau des lignes. Les opérations fréquentes de copie sur écriture (par exemple, les mises à jour ou les suppressions) sur les tables Iceberg gérées en externe peuvent avoir un impact sur les performances des actualisations incrémentielles.

Créer des tables dynamiques à l’aide du remplissage

Le remplissage est une opération à faible coût et à zéro copie qui rend les données sources immédiatement disponibles dans une table dynamique. Vous pouvez créer une table dynamique avec des données initiales remplies à partir d’une table ou d’une table dynamique, tout en définissant une requête d’actualisation personnalisée pour les mises à jour ultérieures.

Avec des limites d’immuabilité, seule la région immuable est remplie et reste inchangée, même si la région immuable ne correspond plus à la source. La région mutable est calculée à partir de la définition de la table dynamique et des tables de base, comme d’habitude.

Only data defined by the IMMUTABLE WHERE immutability constraint can be backfilled because the backfill data must remain unchanged, even if it differs from the upstream source.

Exemples d’utilisation du remplissage

Les exemples suivants montrent comment créer de nouvelles tables dynamiques à partir de tables avec des données remplies.

Chaque nom de colonne doit se trouver dans la table de remplissage avec les types de données compatibles et doit apparaître dans le même ordre que dans la table de remplissage. Les propriétés de la table et les privilèges de la table de remplissage ne sont pas copiés.

Si les paramètres de Time Travel AT | BEFORE sont spécifiés, les données de la table de remplissage sont copiées à l’heure spécifiée.

Exemple : Remplissage à partir d’une partie de la table

L’exemple suivant remplit la région immuable de my_dynamic_table à partir de my_backfill_table et de la région mutable de la définition de la table dynamique.

Dans ce scénario, si la table dynamique est créée avec un mode d’actualisation incrémentiel, la réinitialisation supprime toutes les lignes qui sont mutables et ne remplit que la région mutable. Si la table dynamique est créée avec le mode d’actualisation complet, une actualisation complète est déclenchée avec le même effet.

CREATE DYNAMIC TABLE my_dynamic_table (day TIMESTAMP, totalSales NUMBER)
  IMMUTABLE WHERE (day < '2025-01-01')
  BACKFILL FROM my_backfill_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = 'mywh'
  AS SELECT DATE_TRUNC('day', ts) AS day, sum(price)
    FROM my_base_table
    GROUP BY day;
Copy

Exemple : Récupérer ou modifier des données dans une table dynamique via le remplissage

Vous ne pouvez pas modifier directement les données ou la définition d’une table dynamique. Pour récupérer ou corriger des données, procédez comme suit :

  1. Cloner la table dynamique ver une table standard.

  2. Modifiez la table clonée selon vos besoins.

  3. Effectuez le remplissage depuis la table modifiée vers une nouvelle table dynamique.

Dans l’exemple suivant, my_dynamic_table agrège les données de ventes quotidiennes de la tables de base sales :

CREATE OR REPLACE TABLE sales(item_id INT, ts TIMESTAMP, sales_price FLOAT);

INSERT INTO sales VALUES (1, '2025-05-01 01:00:00', 10.0), (1, '2025-05-01 02:00:00', 15.0), (1, '2025-05-01 03:00:00', 11.0);
INSERT INTO sales VALUES (1, '2025-05-02 00:00:00', 11.0), (1, '2025-05-02 05:00:00', 13.0);


CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  IMMUTABLE WHERE (day <= '2025-05-01')
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;
Copy
+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

Vous pouvez également archiver les anciennes données pour réduire les coûts de stockage :

DELETE FROM sales WHERE ts < '2025-05-02';

ALTER DYNAMIC TABLE my_dynamic_table REFRESH;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;
Copy

Plus tard, vous trouvez une erreur de vente sur 2025-05-01, où sales_count devrait être 2. Pour corriger cela :

  1. Clonez my_dynamic_table vers une table ordinaire :

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
    Copy

  2. Mettez à jour la table clonée :

    UPDATE my_dt_clone_table SET
  sales_count = 2
  WHERE day = '2025-05-01';

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dt_clone_table;
    Copy
    +---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 2           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

  3. Recréez la table dynamique en utilisant le clone modifié comme source de remplissage.

    CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  BACKFILL FROM my_dt_clone_table
  IMMUTABLE WHERE (day <= '2025-05-01')
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;
    Copy

    Cette approche vous permet de récupérer ou de corriger les données d’une table dynamique sans modifier la table de base :

    SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;
    Copy
    +---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 2           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

Exemple : Modifier le schéma d’une table dynamique à l’aide du remplissage

Vous ne pouvez pas modifier directement le schéma d’une table dynamique. Pour mettre à jour le schéma, par exemple, ajouter une colonne, procédez comme suit :

  1. Cloner la table dynamique ver une table standard. L’exemple suivant utilise my_dynamic_table créée à partir de sales (ci-dessus).

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
    Copy

  2. Modifiez le schéma de la table clonée :

    ALTER TABLE my_dt_clone_table ADD COLUMN sales_avg FLOAT;

SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG FROM my_dt_clone_table;
    Copy

  3. Si vous le souhaitez, renseignez les données dans la nouvelle colonne.

  4. Recréez la table dynamique en utilisant le clone modifié comme source de remplissage.

    CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  BACKFILL FROM my_dt_clone_table
  IMMUTABLE WHERE (day <= '2025-05-01')
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count, avg(sales_price) as sales_avg FROM sales
    GROUP BY item_id, day;
    Copy

    Cette approche vous permet de récupérer ou de corriger les données d’une table dynamique sans modifier la table de base :

    SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG, metadata$is_immutable as IMMUTABLE from my_dynamic_table ORDER BY ITEM_ID, DAY;
    Copy
    +---------+------------+-------------+-----------+-----------+
| ITEM_ID | DAY        | SALES_COUNT | SALES_AVG | IMMUTABLE |
|---------+------------+-------------|-----------|-----------|
| 1       | 2025-05-01 | 3           | NULL      | TRUE      |
| 1       | 2025-05-02 | 2           | 12        | FALSE     |
+---------+-------------+------------+-----------+-----------+

Limites et considérations lorsque vous créez une table dynamique avec des données rétroactives

Toutes les limites des tables dynamiques classiques et les limites des contraintes d’immuabilité s’appliquent toujours.

Les limites et considérations supplémentaires suivantes s’appliquent :

  • Actuellement, seules les tables standards et dynamiques peuvent être utilisées pour le remplissage.

  • Vous ne pouvez pas spécifier de politiques ni de balises dans la nouvelle table dynamique, car elles sont copiées à partir de la table de remplissage.

  • Les clés de clustering de la nouvelle table dynamique et de la table de remplissage doivent être identiques.

Bonnes pratiques pour la création de tables dynamiques

Lier des pipelines de tables dynamiques

Lorsque vous définissez une nouvelle table dynamique, plutôt que de définir une grande table dynamique avec de nombreuses instructions imbriquées, utilisez plutôt de petites tables dynamiques avec des pipelines.

Vous pouvez configurer une table dynamique pour interroger d’autres tables dynamiques. Par exemple, imaginez un scénario dans lequel votre pipeline de données extrait des données d’une table de mise en zone de préparation pour mettre à jour diverses tables de dimensions (par exemple, customer, product, date et time). En outre, votre pipeline met à jour une table des sales agrégées sur la base des informations provenant de ces tables de dimensions. En configurant les tables de dimensions pour qu’elles interrogent la table de mise en zone de préparation et la table des sales agrégées pour qu’elle interroge les tables de dimensions, vous créez un effet de cascade similaire à un graphique de tâches.

Dans cette configuration, l’actualisation de la table des sales agrégées n’est exécutée qu’une fois que les actualisations des tables de dimension ont été effectuées avec succès. Cela permet de garantir la cohérence des données et d’atteindre les objectifs en matière de délais. Grâce à un processus d’actualisation automatisé, toute modification apportée aux tables sources déclenche des actualisations dans toutes les tables dépendantes au moment opportun.

Comparaison entre les graphiques de tâches et les DAGs de tables dynamiques

Utilisez une table dynamique « contrôleur » pour les graphiques de tâches complexes

Lorsque vous avez un graphique complexe de tables dynamiques avec de nombreuses racines et feuilles et que vous souhaitez effectuer des opérations liées aux performances (par exemple, changement de décalage, actualisation manuelle, suspension) sur le graphique de tâches complet avec une seule commande, procédez comme suit :

  1. Définissez la valeur de TARGET_LAG de toutes vos tables dynamiques sur DOWNSTREAM.

  2. Créez une table dynamique « contrôleur » qui lit toutes les feuilles de votre graphique de tâches. Pour vous assurer que ce contrôleur ne consomme pas de ressources, procédez comme suit :

    CREATE DYNAMIC TABLE controller
  TARGET_LAG = <target_lag>
  WAREHOUSE = <warehouse>
  AS
    SELECT 1 A FROM <leaf1>, …, <leafN> LIMIT 0;
    Copy

  3. Utilisez le contrôleur pour contrôler l’ensemble du graphique. Par exemple :

  • Définissez une nouvelle latence cible pour le graphique de tâches.

    ALTER DYNAMIC TABLE controller SET
  TARGET_LAG = <new_target_lag>;
    Copy

  • Actualisez manuellement le graphique de tâches.

    ALTER DYNAMIC TABLE controller REFRESH;
    Copy

Utiliser des tables dynamiques transitoires pour réduire les coûts de stockage

Les tables dynamiques transitoires conservent les données de manière fiable dans le temps et prennent en charge Time Travel dans la période de conservation des données, mais ne conservent pas les données au-delà de la période Fail-safe. Par défaut, les données des tables dynamiques sont conservées pendant sept jours dans le stockage Fail-safe.

Pour les tables dynamiques avec un taux d’actualisation élevé, cela peut augmenter considérablement la consommation de stockage. Par conséquent, vous ne devez rendre une table dynamique transitoire que si ses données ne nécessitent pas le même niveau de protection et de récupération des données que les tables permanentes.

Vous pouvez créer une table dynamique transitoire ou cloner des tables dynamiques existantes en tables dynamiques transitoires à l’aide de l’instruction CREATE DYNAMIC TABLE.

Dépannage de la création de tables dynamiques

Lorsque vous créez une table dynamique, l’actualisation initiale se produit soit selon une planification (ON_SCHEDULE) ou immédiatement lors de la création (ON_CREATE). Le renseignement initial des données, ou initialisation<label-dynamic_tables_initialization>, dépend du moment où cette actualisation initiale a lieu. Par exemple, pour :code:`ON_CREATE, l’initialisation peut prendre plus de temps si elle déclenche l’actualisation des tables dynamiques en amont.

L’initialisation peut prendre un certain temps, suivant la quantité de données analysées. Pour voir la progression, procédez comme suit :

  1. Connectez-vous à Snowsight.

  2. Dans le menu de navigation, sélectionnez Monitoring » Query History.

  3. Dans le menu déroulant Filters, entrez CREATE DYNAMIC TABLE dans le filtre SQL Text et entrez le nom de votre entrepôt dans le filtre Warehouse.

  4. Sélectionnez la requête avec votre table dynamique sous SQL text et utilisez les onglets Query Details et Query Profile pour suivre la progression.

Langage: Français