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¶

• O predicado IMMUTABLE WHERE é ignorado durante a atualização inicial, mas se aplica a todas as atualizações subsequentes. No modo de atualização completa, o predicado limita a nova computação apenas às linhas que não correspondem à condição.

• 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