Utiliser des limites d’immuabilité sur les tables dynamiques¶

Exemple : Utiliser IMMUTABLE WHERE pour empêcher le recalcul des anciennes données si une table de dimension change¶

Dans l’exemple suivant, la mise à jour d’une ligne dans la table de dimension entraîne le retraitement des faits du dernier jour qui se joignent à elle, plutôt que l’ensemble des faits historiques :

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);

Exemple : Table dynamique à conservation illimitée et table de base à conservation limitée¶

L’exemple suivant crée une table de mise en zone de préparation avec une fenêtre limitée de données récemment ajoutées et une table dynamique stockant l’ensemble des données analysées et filtrées.

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';

Exemple : Utiliser IMMUTABLE WHERE pour activer les actualisations incrémentielles en aval lorsque la table dynamique en amont s’exécute en mode d’actualisation complet¶

Certaines constructions de requêtes, telles que les fonctions de table définies par l’utilisateur en Python (UDTFs) nécessitent qu’une table dynamique fonctionne en mode d’actualisation complet. En règle générale, cette limitation empêche le traitement incrémentiel. Pour que les tables en aval restent incrémentielles, vous pouvez définir une région immuable à l’aide de la clause IMMUTABLE WHERE comme indiqué dans l’exemple suivant. De cette manière, les tables en aval bénéficient toujours d’optimisations des performances, même lorsque la table en amont nécessite des actualisations complètes.

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é¶

• The IMMUTABLE WHERE predicate is ignored during the initial refresh but applies to all subsequent refreshes. In full refresh mode, the predicate limits recomputation to only the rows that don’t match the condition.

• Les flux et les tables dynamiques à actualisation incrémentielle peuvent être lus à partir de tables dynamiques à actualisation complète qui ont des limites d’immuabilité sur celles-ci.

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

• Toutes les limitations des tables dynamiques classiques s’appliquent toujours. En outre, les limitations suivantes s’appliquent :

  • Il ne peut y avoir qu’un seul prédicat IMMUTABLE WHERE sur une table dynamique. Définir un autre prédicat 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 de sorte que la région immuable ne puisse pas rétrécir 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 col3, 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

  • 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> );
    

    Copy

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

    Copy