Créer des tables dynamiques¶

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.

Exemples d’utilisation des limites d’immuabilité¶

L’exemple suivant crée une table dynamique et la définit comme immuable en utilisant le paramètre IMMUTABLE WHERE.

CREATE DYNAMIC TABLE my_dynamic_table (id1 INT)
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  IMMUTABLE WHERE ( <expr> )
  AS
    SELECT id AS id1 FROM staging_table;

Les colonnes référencées dans la condition IMMUTABLE WHERE doivent être constituées de colonnes de la table dynamique, et non de colonnes de la table de base. Par exemple, l’expression IMMUTABLE WHERE dans l’exemple ci-dessus ne peut utiliser que id1, pas id.

Vous ne pouvez définir qu’une seule condition IMMUTABLE WHERE sur une table dynamique. Pour remplacer un prédicat existant, vous utilisez la commande ALTER DYNAMIC TABLE comme indiqué dans l’exemple suivant :

-- Set or replace an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table SET IMMUTABLE WHERE ( <expr> );

-- Remove an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table UNSET IMMUTABLE;

Pour afficher les limites d’immuabilité de vos tables dynamiques, exécutez la commande SHOW DYNAMIC TABLES. La colonne immutable_where indique la limite IMMUTABLE WHERE définie sur la table, ou NULL si aucune n’est définie.

Pour plus d’informations sur les coûts de calcul, voir Coût de calcul des contraintes d’immuabilité.

CREATE DYNAMIC TABLE joined_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (timestamp_col < CURRENT_TIMESTAMP() - INTERVAL '1 day')
  AS
    SELECT F.primary_key primary_key, F.timestamp_col timestamp_col, D.value dim_value
    FROM fact_table F
    LEFT OUTER JOIN dimension_table D USING (primary_key);

CREATE TABLE staging_data (raw TEXT, ts TIMESTAMP);

CREATE DYNAMIC TABLE parsed_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '7 days')
  AS
    SELECT parse_json(raw):event_id::string event_id, parse_json(raw):name::string name, parse_json(raw):region::string region, ts
    FROM staging_data WHERE region = 'US';

-- Delete old staging data using Task
CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
  AS
    DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';

CREATE FUNCTION my_udtf(x varchar)
  RETURNS TABLE (output VARCHAR)
  LANGUAGE PYTHON
  AS $$ ... $$;

CREATE DYNAMIC TABLE udtf_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
  AS
    SELECT ts, data, output, join_key FROM input_table, TABLE(my_udtf(data));

CREATE DYNAMIC TABLE incremental_join_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
  AS
    SELECT * FROM udtf_dt JOIN dim_table USING (join_key);

Limitations et considérations lors de la définition de limites d’immuabilité¶

Toutes les limitations des tables dynamiques classiques s’appliquent toujours.

En outre :

• Il ne peut y avoir qu’un seul prédicat IMMUTABLE WHERE sur une table dynamique. En définir un autre avec une commande ALTER DYNAMIC TABLE … SET IMMUTABLE WHERE remplace tout prédicat existant.

• Les limites IMMUTABLE WHERE ne peuvent pas contenir les éléments suivants :

  • Sous-requêtes.

  • Fonctions non déterministes, à l’exception des fonctions d’horodatage telles que CURRENT_TIMESTAMP() ou CURRENT_DATE(). Si vous utilisez des fonctions d’horodatage, vous devez les utiliser pour que la région immuable se développe au fil du temps. Par exemple, TIMESTAMP_COL < CURRENT_TIMESTAMP() is allowed but TIMESTAMP_COL > CURRENT_TIMESTAMP() ne l’est pas.

  • Fonctions définies par l’utilisateur et fonctions externes

  • Colonnes de métadonnées ; par exemple, celles commençant par METADATA$.

  • Colonnes qui sont le résultat d’agrégats, de fonctions de fenêtre ou de fonctions non déterministes, ou colonnes qui sont passées via un opérateur de fonction de fenêtre. Dans les tables dynamiques suivantes, uniquement col3 peut être utilisé dans les prédicats IMMUTABLE WHERE :

    CREATE DYNAMIC TABLE aggregates TARGET_LAG = '1 minute' WAREHOUSE = mywh
      AS SELECT col1, SUM(col2) AS col2 FROM input_table
      GROUP BY col3;
    
    CREATE DYNAMIC TABLE window_fns TARGET_LAG = '1 minute' WAREHOUSE = mywh
      AS SELECT col3, SUM(col4) OVER (PARTITION BY col3 ORDER BY col4) AS col2, col5 FROM input_table;
    

    Copy

Les limites IMMUTABLE WHERE sont copiées pendant le clonage et la réplication sans limitations.

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.

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;

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;

+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
|---------+-------------+------------|

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;

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 classiques 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.

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.

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.