Uso de restrições de imutabilidade em tabelas dinâmicas¶

Exemplo: Uso de IMMUTABLE WHERE para impedir a recomputação de dados antigos se uma tabela de dimensões mudar¶

No exemplo a seguir, a atualização de uma linha na tabela de dimensões faz com que sejam reprocessados os fatos do último dia, em vez de todos os fatos históricos:

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

Exemplo: Tabela dinâmica de retenção ilimitada e tabela base de retenção limitada¶

O exemplo a seguir cria uma tabela de preparação com uma janela limitada de dados adicionados recentemente e uma tabela dinâmica que armazena todos os dados analisados e filtrados:

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

Exemplo: uso de IMMUTABLE WHERE para habilitar atualizações incrementais downstream quando a tabela dinâmica upstream é executada no modo de atualização completa¶

Algumas construções de consulta, como funções de tabela definidas pelo usuário (User-Defined Table Functions, UDTFs) em Python, exigem uma tabela dinâmica para execução no modo de atualização completa. Normalmente, essa limitação impede o processamento incremental. Para permitir que as tabelas downstream permaneçam incrementais, você pode definir uma região imutável usando a cláusula IMMUTABLE WHERE, conforme mostrado no exemplo a seguir. Dessa forma, as tabelas downstream ainda se beneficiam das otimizações de desempenho, mesmo quando a tabela upstream requer atualizações completas.

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

Limitações e considerações ao definir restrições de imutabilidade¶

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

• Fluxos e tabelas dinâmicas de atualização incremental podem ler tabelas dinâmicas de atualização completas que têm restrições de imutabilidade.

• As restrições IMMUTABLE WHERE são copiadas durante a clonagem e a replicação sem limitações.

• Todas as limitações das tabelas dinâmicas regulares ainda se aplicam. Além disso, as seguintes limitações são aplicadas:

  • Só pode haver um único predicado IMMUTABLE WHERE em uma tabela dinâmica. A definição de outro predicado com um comando ALTER DYNAMIC TABLE … SET IMMUTABLE WHERE substitui um predicado existente.

  • As restrições IMMUTABLE WHERE não podem conter os seguintes itens:

    • Subconsultas.

    • Funções não determinísticas, exceto para funções de carimbo de data/hora, como CURRENT_TIMESTAMP() ou CURRENT_DATE(). Se você usar funções de carimbo de data/hora, deverá usá-las para que a região imutável não diminua com o tempo. Por exemplo, TIMESTAMP_COL < CURRENT_TIMESTAMP() is allowed but TIMESTAMP_COL > CURRENT_TIMESTAMP() não faz isso.

    • Funções definidas pelo usuário e externas.

    • Colunas de metadados; por exemplo, aquelas que começam com METADATA$.

    • Colunas que são o resultado de agregados, funções de janela ou não determinísticas, ou colunas que são passadas por um operador de função de janela. Nas tabelas dinâmicas a seguir, somente col3 pode ser usado em predicados 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

  • Você só pode definir uma única condição IMMUTABLE WHERE em uma tabela dinâmica. Para substituir um predicado existente, use o comando ALTER DYNAMIC TABLE, como mostrado no exemplo a seguir:

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