PDTs incrementais

No Looker, as tabelas derivadas persistentes (PDTs) são gravadas no esquema de rascunho do seu banco de dados. O Looker persiste e recria uma TDP com base na estratégia de persistência. Quando uma TDP é acionada para ser recriada, o Looker recria a tabela inteira por padrão.

Uma TDP incremental é uma TDP que o Looker cria anexando dados novos à tabela, em vez de recriá-la por completo:

Uma tabela grande com as três linhas inferiores destacadas para mostrar um pequeno número de novas linhas sendo adicionadas à tabela.

Se o dialeto for compatível com TDPs incrementais, será possível transformar os seguintes tipos de TDPs em incrementais:

Na primeira vez que você executa uma consulta em uma TDP incremental, o Looker cria toda a TDP para receber os dados iniciais. Se a tabela for grande, a criação inicial pode levar um tempo significativo, assim como a criação de qualquer tabela grande. Depois que a tabela inicial é criada, as próximas serão incrementais e vão levar menos tempo, se a TDP incremental for configurada de forma estratégica.

Observe o seguinte para TDPs incrementais:

  • As TDPs incrementais são compatíveis apenas com TDPs que usam uma estratégia de persistência baseada em gatilho (datagroup_trigger, sql_trigger_value ou interval_trigger). Elas não são compatíveis com TDPs que usam a estratégia de persistência persist_for.
  • Para TDPs baseadas em SQL, a consulta de tabela precisa ser definida usando o parâmetro sql para ser usada como uma TDP incremental. As TDPs baseadas em SQL definidas com o parâmetro sql_create ou create_process não podem ser criadas de forma incremental. Como você pode ver no Exemplo 1 nesta página, o Looker usa um comando INSERT ou MERGE para criar os incrementos de uma TDP incremental. A tabela derivada não pode ser definida usando instruções personalizadas de linguagem de definição de dados (DDL), já que o Looker não seria capaz de determinar quais instruções DDL seriam necessárias para criar um incremento preciso.
  • A tabela de origem da TDP incremental precisa ser otimizada para consultas baseadas em tempo. Especificamente, a coluna baseada em tempo usada para a chave de incremento precisa ter uma estratégia de otimização, como particionamento, chaves de classificação, índices ou qualquer estratégia de otimização compatível com seu dialeto. A otimização da tabela de origem é altamente recomendada porque, sempre que a tabela incremental é atualizada, o Looker consulta a tabela de origem para determinar os valores mais recentes da coluna baseada em tempo usada para a chave de incremento. Se a tabela de origem não for otimizada para essas consultas, a consulta do Looker para os valores mais recentes poderá ser lenta e cara.
  • As funções de janela SQL não são compatíveis diretamente com TDPs incrementais. Para usar uma função de janela com um PDT incremental, crie o PDT incremental como uma tabela base ou intermediária sem a função de janela e, em seguida, faça referência a esse PDT incremental em uma tabela derivada ou PDT separado que aplique a função de janela.

Como definir uma TDP incremental

Use os seguintes parâmetros para transformar uma TDP em uma TDP incremental:

  • increment_key (obrigatório para tornar a TDP incremental): define o período em que os novos registros devem ser consultados.
  • {% incrementcondition %} Filtro Liquid (necessário para transformar uma TDP baseada em SQL em uma TDP incremental; não relevante para TDPs baseadas em LookML): conecta a chave de incremento à coluna de tempo do banco de dados em que a chave de incremento se baseia. Para mais informações, consulte a página de documentação do increment_key.
  • increment_offset (opcional): um número inteiro que define quantos períodos anteriores (na granularidade da chave de incremento) são recriados para cada build incremental. O parâmetro increment_offset é útil no caso de dados que chegam atrasados, em que períodos anteriores podem ter novos dados que não foram incluídos quando o incremento correspondente foi originalmente criado e anexado à TDP.

Consulte a página de documentação do parâmetro increment_key para ver exemplos de como criar TDPs incrementais com base em tabelas derivadas nativas persistentes, tabelas derivadas persistentes com base em SQL e tabelas de agregação.

Veja um exemplo simples de um arquivo de visualização que define uma TDP incremental baseada em LookML:

view: flights_lookml_incremental_pdt {
  derived_table: {
    indexes: ["id"]
    increment_key: "departure_date"
    increment_offset: 3
    datagroup_trigger: flights_default_datagroup
    distribution_style: all
    explore_source: flights {
      column: id {}
      column: carrier {}
      column: departure_date {}
    }
  }

  dimension: id {
    type: number
  }
  dimension: carrier {
    type: string
  }
   dimension: departure_date {
    type: date
  }
}

Essa tabela será criada por completo na primeira vez que uma consulta for executada nela. Depois disso, a TDP será recriada em incrementos de um dia (increment_key: departure_date), voltando três dias (increment_offset: 3).

A chave de incremento é baseada na dimensão departure_date, que é o período date do grupo de dimensões departure. Consulte a página de documentação do parâmetro dimension_group para uma visão geral de como os grupos de dimensões funcionam. O grupo de dimensões e o período são definidos na visualização flights, que é o explore_source para essa TDP. Veja como o grupo de dimensões departure é definido no arquivo de visualização flights:

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

Interação de parâmetros de incremento e estratégia de persistência

As configurações increment_key e increment_offset de uma TDP são independentes da estratégia de persistência:

  • A estratégia de persistência da TDP incremental determina apenas quando a TDP é incrementada. O builder de TDP não modifica a TDP incremental, a menos que a estratégia de persistência da tabela seja acionada ou que a TDP seja acionada manualmente com a opção Recriar tabelas derivadas e executar em uma Análise.
  • Quando a TDP é incrementada, o criador de TDPs determina quando os dados mais recentes foram adicionados à tabela, em termos do incremento de tempo mais atual (o período definido pelo parâmetro increment_key). Com base nisso, o builder de TDP vai truncar os dados até o início do incremento de tempo mais recente na tabela e, em seguida, criar o incremento mais recente a partir daí.
  • Se a TDP tiver um parâmetro increment_offset, o builder da TDP também vai recriar o número de períodos anteriores especificados no parâmetro increment_offset. Os períodos anteriores começam no início do incremento de tempo mais recente (o período definido pelo parâmetro increment_key).

Os exemplos de cenários a seguir ilustram como as TDPs incrementais são atualizadas, mostrando a interação de increment_key, increment_offset e a estratégia de persistência.

Exemplo 1

Este exemplo usa uma TDP com as seguintes propriedades:

  • Chave de incremento: data
  • Deslocamento de incremento: 3
  • Estratégia de persistência: acionada uma vez por mês, no primeiro dia do mês.

Confira como essa tabela será atualizada:

  • Uma estratégia de persistência mensal significa que a tabela é criada automaticamente uma vez por mês. Isso significa que, em 1º de junho, por exemplo, a última linha da tabela terá sido adicionada em 1º de maio.
  • Como essa TDP tem uma chave de incremento baseada em data, o builder de TDP vai truncar 1º de maio de volta para o início do dia e recriar os dados de 1º de maio até o dia atual, 1º de junho.
  • Além disso, essa TDP tem um deslocamento de incremento de 3. Portanto, o builder de TDP também recria os dados dos três períodos (dias) anteriores a 1º de maio. Como resultado, os dados são recriados para os dias 28, 29 e 30 de abril e até o dia atual, 1º de junho.

Em termos de SQL, este é o comando que o criador de TDPs vai executar em 1º de junho para determinar as linhas da TDP atual que precisam ser recriadas:

## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))

## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)

E este é o comando SQL que o builder de TDP vai executar em 1º de junho para criar o incremento mais recente:

## Example SQL for BigQuery:

MERGE INTO [pdt_name] USING (SELECT [columns]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
   AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
   THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]

## Example SQL for other dialects:

START TRANSACTION;
DELETE FROM [pdt_name]
   WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
   SELECT [columns]
   FROM [source_table]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;

Exemplo 2

Este exemplo usa uma TDP com as seguintes propriedades:

  • Estratégia de persistência: acionada uma vez por dia
  • Chave de incremento: mês
  • Deslocamento de incremento: 0

Veja como essa tabela será atualizada em 1º de junho:

  • A estratégia de persistência diária significa que a tabela é criada automaticamente uma vez por dia. Em 1º de junho, a última linha da tabela terá sido adicionada em 31 de maio.
  • Como a chave de incremento é baseada no mês, o builder de TDP vai truncar de 31 de maio até o início do mês e recriar os dados de todo o mês de maio até o dia atual, incluindo 1º de junho.
  • Como essa TDP não tem um deslocamento de incremento, nenhum período anterior é recriado.

Confira como essa tabela será atualizada em 2 de junho:

  • Em 2 de junho, a última linha da tabela terá sido adicionada em 1º de junho.
  • Como o builder de TDP vai truncar até o início do mês de junho e, em seguida, recriar os dados a partir de 1º de junho até o dia atual, os dados serão recriados apenas para 1º e 2 de junho.
  • Como essa TDP não tem um deslocamento de incremento, nenhum período anterior é recriado.

Exemplo 3

Este exemplo usa uma TDP com as seguintes propriedades:

  • Chave de incremento: mês
  • Deslocamento de incremento: 3
  • Estratégia de persistência: acionada uma vez por dia

Esse cenário ilustra uma configuração ruim para uma TDP incremental, já que é uma TDP de acionamento diário com um deslocamento de três meses. Isso significa que pelo menos três meses de dados serão recriados todos os dias, o que seria um uso muito ineficiente de uma TDP incremental. No entanto, é um cenário interessante para entender como as TDPs incrementais funcionam.

Veja como essa tabela será atualizada em 1º de junho:

  • A estratégia de persistência diária significa que a tabela é criada automaticamente uma vez por dia. Em 1º de junho, por exemplo, a última linha da tabela terá sido adicionada em 31 de maio.
  • Como a chave de incremento é baseada no mês, o builder de TDP vai truncar de 31 de maio até o início do mês e recriar os dados de todo o mês de maio até o dia atual, incluindo 1º de junho.
  • Além disso, essa TDP tem um deslocamento de incremento de 3. Isso significa que o builder de TDP também recria os dados dos três períodos (meses) anteriores a maio. O resultado é que os dados são recriados de fevereiro, março, abril e até o dia atual, 1º de junho.

Confira como essa tabela será atualizada em 2 de junho:

  • Em 2 de junho, a última linha da tabela terá sido adicionada em 1º de junho.
  • O criador de TDP vai truncar o mês de volta para 1º de junho e recriar os dados do mês de junho, incluindo 2 de junho.
  • Além disso, devido ao deslocamento de incremento, o builder de TDP vai recriar os dados dos três meses anteriores a junho. O resultado é que os dados são recriados de março, abril, maio e até o dia atual, 2 de junho.

Como testar uma TDP incremental no Modo de Desenvolvimento

Antes de implantar uma nova TDP incremental no ambiente de produção, teste-a para garantir que ela seja criada e incrementada. Para testar uma TDP incremental no Modo de Desenvolvimento:

  1. Crie uma Análise para a TDP:

    • Em um arquivo de modelo associado, use o parâmetro include para incluir o arquivo de visualização da TDP no arquivo de modelo.
    • No mesmo arquivo de modelo, use o parâmetro explore para criar uma Análise para a visualização da TDP incremental.
     include: "/views/e_faa_pdt.view"
     explore: e_faa_pdt {}
    
  2. Abra a Análise da TDP. Para fazer isso, selecione o botão Ver ações do arquivo e escolha o nome de uma Análise.

  1. Na Análise, selecione algumas dimensões ou medições e clique em Executar. Em seguida, o Looker vai criar toda a TDP. Se essa for a primeira consulta que você executou na TDP incremental, o builder de TDP vai criar toda a TDP para receber os dados iniciais. Se a tabela for grande, a criação inicial pode levar um tempo significativo, assim como a criação de qualquer tabela grande.

  2. É possível verificar se a TDP inicial foi criada das seguintes maneiras:

    • Se você tiver a permissão see_logs, poderá verificar se a tabela foi criada consultando o log de eventos da TDP. Se você não encontrar os eventos de criação de TDP no log de eventos de TDP, verifique as informações de status na parte de cima da Análise do log de eventos de TDP. Se aparecer "do cache", selecione Limpar o cache e atualizar para receber informações mais recentes.
    • Caso contrário, confira os comentários na guia SQL da barra Dados da Análise. A guia SQL mostra a consulta e as ações que serão realizadas quando você executar a consulta na Análise. Por exemplo, se os comentários na guia SQL disserem -- generate derived table e_incremental_pdt, essa será a ação realizada quando você clicar em Executar.
  3. Depois de criar o build inicial da TDP, use a opção Recriar e executar tabelas derivadas na Análise para solicitar um build incremental da TDP.

  4. Use os mesmos métodos de antes para verificar se a TDP é criada de forma incremental:

    • Se você tiver a permissão see_logs, poderá usar o log de eventos da TDP para ver os eventos create increment complete da TDP incremental. Se você não encontrar esse evento no log de eventos da TDP e o status da consulta indicar "do cache", selecione Limpar cache e atualizar para receber informações mais recentes.
    • Confira os comentários na guia SQL da barra Dados da Análise. Nesse caso, os comentários vão indicar que a TDP foi incrementada. Por exemplo: -- increment persistent derived table e_incremental_pdt to generation 2
  5. Depois de verificar se a TDP foi criada e está sendo incrementada corretamente, se você não quiser manter a Análise dedicada para a TDP, remova ou comente os parâmetros explore e include da TDP no arquivo de modelo.

Depois que a TDP for criada no Modo de Desenvolvimento, a mesma tabela será usada para produção quando você implantar as alterações, a menos que faça outras alterações na definição da tabela. Para mais informações, consulte a seção Tabelas persistentes no Modo de Desenvolvimento da página de documentação Tabelas derivadas no Looker.

Solução de problemas de TDPs incrementais

Nesta seção, descrevemos alguns problemas comuns que podem ocorrer ao usar TDPs incrementais, bem como as etapas para solucionar e resolver esses problemas.

Falha na criação da TDP incremental após a mudança de esquema

Se a TDP incremental for uma tabela derivada com base em SQL e o parâmetro sql incluir um caractere curinga como SELECT *, as mudanças no esquema do banco de dados subjacente (como adição, remoção ou mudança no tipo de dados da coluna) poderão fazer com que a TDP falhe com o seguinte erro:

SQL Error in incremental PDT: Query execution failed

Para resolver esse problema, edite a instrução SELECT no parâmetro sql para selecionar colunas individuais. Por exemplo, se a cláusula de seleção for SELECT *, mude para SELECT column1, column2, ....

Se o esquema mudar e você quiser recriar a TDP incremental do zero, use a chamada de API start_pdt_build e inclua o parâmetro full_force_incremental.

As funções de janela retornam resultados inesperados em uma TDP incremental

Não é possível usar diretamente as funções de janela SQL em uma TDP incremental. Como as versões incrementais processam apenas as linhas do incremento de tempo mais recente e qualquer increment_offset configurado, definir uma função de janela diretamente em uma TDP incremental (como em um parâmetro derived_column ou sql) pode resultar em valores ausentes ou cálculos inesperados nos limites da partição quando a tabela é incrementada.

Para usar uma função de janela com uma TDP incremental, defina a TDP incremental como uma TDP base ou intermediária sem a função de janela e, em seguida, faça referência a essa TDP incremental em uma tabela derivada separada ou em uma TDP regular que aplique a função de janela.

Dialetos de banco de dados compatíveis com TDPs incrementais

Para que o Looker ofereça suporte a TDPs incrementais no seu projeto, o dialeto do banco de dados precisa aceitar comandos de linguagem de definição de dados (DDL) que permitam excluir e inserir linhas.

A tabela a seguir mostra quais dialetos são compatíveis com PDTs incrementais na versão mais recente do Looker. No caso do Databricks, as PDTs incrementais são compatíveis apenas com a versão 12.1 e mais recentes:

Dialeto Compatível?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica