PDTs incrementais

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

Um PDT incremental é um PDT que o Looker cria anexando dados novos à tabela em vez de recriar a tabela na íntegra:

Se o dialeto oferecer suporte a TDPs incrementais, é possível transformar os seguintes tipos de TDPs em TDPs incrementais:

• Tabelas de agregação
• PDTs baseadas em LookML (nativas)
• TDPs baseadas em SQL

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

Observe o seguinte para PDTs incrementais:

• 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). TDPs incrementais 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 da tabela precisa ser definida usando o parâmetro sql para ser usado como uma TDP incremental. Não é possível criar de forma incremental PDTs baseados em SQL que são definidos com o parâmetro sql_create ou create_process. Como você pode ver no Exemplo 1 desta página, o Looker usa um comando INSERT ou MERGE para criar os incrementos de uma PDT incremental. A tabela derivada não pode ser definida usando instruções DDL (linguagem de definição de dados) personalizadas, porque o Looker não poderia 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 no 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 ordenação, índices ou qualquer estratégia de otimização compatível com seu dialeto. A otimização da tabela de origem é altamente recomendada porque cada vez que a tabela incremental é atualizada, o Looker consulta a tabela de origem para determinar os valores mais recentes da coluna baseada no tempo usada para a chave de incremento. Se a tabela de origem não estiver otimizada para essas consultas, a consulta do Looker para os valores mais recentes poderá ser lenta e cara.

Como definir um PDT incremental

É possível usar os seguintes parâmetros para transformar uma TDP em uma TDP incremental:

• increment_key (obrigatório para tornar o PDT um PDT incremental): define o período de tempo em que novos registros precisam ser consultados.
• {% incrementcondition %} Filtro líquido (necessário para transformar uma TDP com base em SQL em uma TDP incremental). Não se aplica a TDPs baseadas em LookML: conecta a chave de incremento à coluna de tempo do banco de dados em que a chave de incremento se baseia. Consulte a página de documentação de increment_key para mais informações.
• increment_offset (opcional): um número inteiro que define o número de períodos anteriores (na granularidade da chave de incremento) que 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 criado e anexado ao PDT.

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

Aqui está um exemplo simples de um arquivo de visualização que define uma PDT incremental baseada no 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
  }
}

Esta tabela será construída por completo na primeira vez que uma consulta for executada nela. Depois disso, o PDT será recriado em incrementos de um dia (increment_key: departure_date), voltando três dias (increment_offset: 3).

A chave de incremento se baseia na dimensão departure_date, que é, na verdade, o período de 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ão e o período são definidos na visualização flights, que é o explore_source desse PDT. 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 PDT são independentes da estratégia de persistência dela:

• A estratégia de persistência do PDT incremental determina apenas quando o PDT é incrementado. O criador de TDP não modifica a TDP incremental a menos que a estratégia de persistência da tabela seja acionada ou a TDP seja acionada manualmente com o comando Recriar tabelas derivadas e Run em uma Análise.
• Quando o PDT é incrementado, o builder determina quando os dados mais recentes foram adicionados à tabela, em termos do incremento de tempo mais recente (o período definido pelo parâmetro increment_key). Com base nisso, o builder de PDT trunca os dados até o início do incremento de tempo mais recente na tabela e depois cria o incremento mais recente a partir daí.
• Se o PDT tiver um parâmetro increment_offset, o criador de PDT 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 atual (o período definido pelo parâmetro increment_key).

Os cenários de exemplo a seguir ilustram como as PDTs 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 estas propriedades:

• Chave de incremento: data
• Compensação 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 é gerada automaticamente uma vez por mês. Isso significa que, em 1o de junho, por exemplo, a última linha da tabela foi adicionada em 1o de maio.
• Como essa PDT tem uma chave de incremento com base na data, o criador de PDT truncará 1o de maio até o início do dia e recriará os dados de 1o de maio até o dia atual, 1o de junho.
• Além disso, essa PDT tem um deslocamento de incremento de 3. Assim, o Criador de PDT também recria os dados dos três períodos anteriores (dias) antes de 1º de maio. O resultado é que os dados são reconstruídos para 28, 29, 30 de abril e até o dia atual, 1o de junho.

Em termos de SQL, este é o comando que o criador de PDT vai executar em 1º de junho para determinar as linhas do PDT 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)

Este é o comando SQL que o builder do PDT 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 um PDT com estas propriedades:

• Estratégia de persistência: acionada uma vez por dia
• Chave de incremento: mês
• Compensação de incremento: 0

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

• A estratégia de persistência diária significa que a tabela é gerada automaticamente uma vez por dia. Em 1o de junho, a última linha da tabela foi adicionada em 31 de maio.
• Como a chave de incremento é baseada no mês, o builder de PDT 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 1o de junho.
• Como essa PDT não tem deslocamento de incremento, nenhum período anterior é recriado.

Veja como esta tabela será atualizada em 2 de junho:

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

Exemplo 3

Este exemplo usa um PDT com estas propriedades:

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

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

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

• A estratégia de persistência diária significa que a tabela é gerada automaticamente uma vez por dia. Por exemplo, no dia 1º de junho, a última linha da tabela foi adicionada em 31 de maio.
• Como a chave de incremento é baseada no mês, o Criador de PDT 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, esse PDT tem um deslocamento de incremento de 3. Isso significa que o criador de PDT também recria os dados dos três períodos anteriores (meses) antes de maio. O resultado é que os dados são reconstruídos a partir de fevereiro, março, abril e até o dia atual, 1o de junho.

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

• Em 2 de junho, a última linha da tabela foi adicionada em 1o de junho.
• O Criador de PDT trunca o mês de volta para 1º de junho e recria os dados do mês de junho, incluindo 2 de junho.
• Além disso, devido ao deslocamento de incremento, o builder de PDT vai recriar os dados dos três meses anteriores antes de junho. O resultado é que os dados são reconstruídos a partir de março, abril, maio, até o dia atual, 2 de junho.

Como testar um PDT incremental no modo de desenvolvimento

Antes de implantar uma nova TDP incremental no ambiente de produção, é possível testar a TDP para garantir que ela seja criada e incrementada. Para testar um PDT 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 do PDT no arquivo de modelo.
   • No mesmo arquivo de modelo, use o parâmetro explore para criar uma Análise para a visualização incremental da PDT.

    include: "/views/e_faa_pdt.view"
    explore: e_faa_pdt {}
   

2. Abra a seção "Explorar" para o PDT. Para fazer isso, selecione o botão Conferir ações de arquivo e selecione um nome de Análise.

Dica: depois de incluir a visualização no arquivo de modelo e criar uma Análise detalhada, você pode navegar diretamente para a Análise detalhada no arquivo de visualização ou modelo no projeto do LookML. Para fazer isso, selecione o menu Ver ações do arquivo ⏷ na parte de cima do arquivo do LookML.

1. Em "Explorar", selecione algumas dimensões ou medições e clique em Executar. O Looker vai criar o PDT inteiro. Se essa for a primeira consulta executada na TDP incremental, o criador de TDPs criará toda a TDP para conseguir os dados iniciais. Se a tabela for grande, a compilação inicial poderá levar um tempo significativo, como seria necessário criar qualquer tabela grande.

2. É possível verificar se o PDT inicial foi criado das seguintes maneiras:

   • Se você tiver a permissão see_logs, poderá verificar se a tabela foi criada no Registro de eventos do PDT. Se você não vir os eventos de criação da TDP no log de eventos da TDP, verifique as informações de status na parte superior da Análise do log de eventos da TDP. Se aparecer "do cache", selecione Limpar cache e Atualize para conferir as informações mais recentes.
   • Caso contrário, você pode conferir os comentários na guia SQL da barra Dados da Análise detalhada. A guia SQL mostra a consulta e as ações que serão realizadas quando você executar a consulta no recurso "Explorar". Por exemplo, se os comentários na guia SQL indicarem -- generate derived table e_incremental_pdt, essa é a ação que será realizada quando você clicar em Executar.

3. Depois de criar a compilação inicial da TDP, solicite uma compilação incremental da TDP usando a ferramenta Recriar tabelas derivadas e Run em "Explore".

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

   • Se você tiver a permissão see_logs, poderá usar o registro de eventos do PDT para conferir eventos create increment complete do PDT incremental. Se você não encontrar esse evento no registro de eventos do PDT e o status da consulta mostrar "do cache", selecione Limpar cache e atualizar para ter 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 o PDT foi incrementado. Por exemplo: -- increment persistent derived table e_incremental_pdt to generation 2

5. Depois de verificar se a TDP foi criada e incrementada corretamente, se você não quiser manter a Análise dedicada à PDT, remova ou comente os parâmetros explore e include da TDP do arquivo de modelo.

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

Dialetos de banco de dados com suporte para TDPs incrementais

Para que o Looker ofereça suporte a PDTs incrementais no projeto, o dialeto do banco de dados precisa oferecer suporte a comandos da linguagem de definição de dados (DDL) que permitem excluir e inserir linhas.

A tabela a seguir mostra quais dialetos oferecem suporte a PDTs incrementais na versão mais recente do Looker. Para o Databricks, as PDTs incrementais têm suporte apenas na versão 12.1 e mais recentes do Databricks:

Dialeto	Compatível?
Actian Avalanche	Não
Amazon Athena	Não
MySQL do Amazon Aurora	Não
Amazon Redshift	Sim
Apache Druid	Não
Apache Druid 0.13 ou mais recente	Não
Apache Druid 0.18 ou superior	Não
Apache Hive 2.3 ou superior	Não
Apache Hive 3.1.2 ou posterior	Não
Apache Spark 3 ou mais recente	Não
ClickHouse	Não
Cloudera Impala 3.1 ou superior	Não
Cloudera Impala 3.1+ com driver nativo	Não
Cloudera Impala com driver nativo	Não
DataVirtuality	Não
Databricks	Sim
Denodo 7	Não
Denodo 8	Não
Dremio	Não
Dremio 11 ou mais recente	Não
Exasol	Não
Bola de fogo	Não
SQL legado do Google BigQuery	Não
SQL padrão do Google BigQuery	Sim
PostgreSQL do Google Cloud	Sim
Google Cloud SQL	Não
Google Spanner	Não
Greenplum	Sim
HyperSQL	Não
IBM Netezza	Não
MariaDB	Não
Microsoft Azure PostgreSQL	Sim
Banco de Dados SQL do Microsoft Azure	Não
Análises do Microsoft Azure Synapse	Sim
Microsoft SQL Server 2008 ou mais recente	Não
Microsoft SQL Server 2012 ou mais recente	Não
Microsoft SQL Server 2016	Não
Microsoft SQL Server 2017 ou posterior	Não
MongoBI	Não
MySQL	Sim
MySQL 8.0.12 ou mais recente	Sim
Oracle	Não
Oracle ADWC	Não
PostgreSQL 9.5 ou mais recente	Sim
PostgreSQL anterior à 9.5	Sim
PrestoDB	Não
PrestoSQL	Não
SAP HANA 2+	Não
SingleStore	Não
SingleStore 7 ou superior	Não
Snowflake	Sim
Teradata	Não
Trino	Não
Vetor	Não
Vertica	Sim