PDTs incrementais

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

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

Se o dialeto for compatível com PDTs incrementais, você poderá transformar os seguintes tipos de PDTs em PDTs incrementais:

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

Na primeira vez que você executa uma consulta em uma PDT incremental, o Looker cria toda a PDT para receber os dados iniciais. Se a tabela for grande, a criação inicial pode levar um tempo considerável, assim como a criação de qualquer tabela grande. Depois que a tabela inicial é criada, as criações subsequentes são incrementais e levam menos tempo, se a PDT incremental for configurada de forma estratégica.

Observações sobre PDTs 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 PDTs baseadas em SQL, a consulta de tabela precisa ser definida usando o parâmetro sql para ser usada como uma PDT incremental. PDTs 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 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 personalizadas de linguagem de definição de dados (DDL), porque o Looker não conseguiria determinar quais instruções DDL seriam necessárias para criar um incremento preciso.
• A tabela de origem da PDT 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, sortkeys, índices ou qualquer outra estratégia 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 incremental. 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 uma TDP incremental

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

• increment_key (obrigatório para transformar o TDP em um TDP incremental): define o período em que novos registros devem ser consultados.
• Filtro do Liquid {% incrementcondition %} (necessário para transformar uma PDT baseada em SQL em uma PDT incremental; não aplicável a PDTs baseadas em LookML): conecta a chave incremental à coluna de tempo do banco de dados em que ela se baseia. Consulte a página de documentação do increment_key para mais informações.
• increment_offset (opcional): um número inteiro que define a quantidade 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 originalmente criado e anexado à PDT.

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

Confira um exemplo simples de um arquivo de visualização que define uma PDT 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 PDT 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 é a explore_source para essa 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 de increment_key e increment_offset de uma PDT são independentes da estratégia de persistência dela:

• A estratégia de persistência da PDT incremental determina apenas quando a PDT é incrementada. O criador de PDTs não modifica a PDT incremental, a menos que a estratégia de persistência da tabela seja acionada ou que a PDT seja acionada manualmente com a opção Recriar tabelas derivadas e executar em uma análise detalhada.
• Quando a PDT é incrementada, o builder 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 criador de PDTs 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 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 os TDPs incrementais são atualizados, mostrando a interação de increment_key, increment_offset e a estratégia de persistência.

Exemplo 1

Este exemplo usa uma PDT com estas propriedades:

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

Veja 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 PDT tem uma chave incremental baseada na data, o criador de PDT vai truncar 1º de maio de volta ao início do dia e reconstruir os dados de 1º de maio até o dia atual, 1º de junho.
• Além disso, essa TDP tem um incremento de 3. Assim, o criador de PDT também recria os dados dos três períodos anteriores (dias) a 1º de maio. O resultado é que os dados são recriados para os dias 28, 29 e 30 de abril e até o dia 1º de junho.

Em termos de SQL, este é o comando que o criador de PDT vai executar em 1º de junho para determinar as linhas da 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 criador de 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 uma PDT com estas propriedades:

• Estratégia de persistência: acionada uma vez por dia
• Chave de incremento: mês
• Aumentar deslocamento: 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 criador de PDT vai truncar de 31 de maio até o início do mês e reconstruir os dados de todo o mês de maio e até o dia atual, incluindo 1º de junho.
• Como essa TDP não tem um incremento de ajuste, nenhum período anterior é recriado.

Veja 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 criador de PDT vai truncar até o início de junho e reconstruir os dados a partir de 1º de junho até o dia atual, os dados serão reconstruídos apenas para 1º e 2 de junho.
• Como essa TDP não tem um incremento de ajuste, nenhum período anterior é recriado.

Exemplo 3

Este exemplo usa uma PDT com estas propriedades:

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

Esse cenário ilustra uma configuração inadequada para uma TDP incremental, já que é uma TDP de acionamento diário com um ajuste 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 examinar como forma de entender como as PDTs 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. Por exemplo, 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 criador de PDT vai truncar de 31 de maio até o início do mês e reconstruir os dados de todo o mês de maio e até o dia atual, incluindo 1º de junho.
• Além disso, essa TDP tem um 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 recriados de fevereiro, março, abril e até o dia atual, 1º de junho.

Veja 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 PDT vai truncar o mês até 1º de junho e reconstruir os dados de junho, incluindo o dia 2.
• Além disso, devido ao ajuste de incremento, o criador de PDT 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 PDT 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 detalhada para a TDP:

   • Em um arquivo de modelo associado, use o parâmetro include para incluir o arquivo de visualização da PDT no arquivo de modelo.
   • No mesmo arquivo de modelo, use o parâmetro explore para criar uma análise detalhada da visualização da PDT incremental.

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

2. Abra a análise detalhada da PDT. Para fazer isso, selecione o botão Ver ações do arquivo e escolha 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 ela no arquivo de visualização ou de modelo do projeto do LookML. Para fazer isso, selecione o menu Ver ações do arquivo ⏷ na parte de cima do arquivo LookML.

1. Na análise detalhada, selecione algumas dimensões ou métricas e clique em Executar. Em seguida, o Looker vai criar toda a PDT. Se esta for a primeira consulta executada na PDT incremental, o criador de PDT vai criar toda a PDT para receber os dados iniciais. Se a tabela for grande, a criação inicial pode levar um tempo considerável, assim como a criação de qualquer tabela grande.

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

   • Se você tiver a permissão see_logs, poderá verificar se a tabela foi criada consultando o registro de eventos da PDT. Se você não encontrar os eventos de criação da TDP no log de eventos da TDP, verifique as informações de status na parte de cima da análise detalhada do log de eventos da TDP. Se aparecer "do cache", selecione Limpar 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 detalhada. A guia SQL mostra a consulta e as ações que serão realizadas quando você a executar na análise detalhada. 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, solicite um build incremental usando a opção Recriar e executar tabelas derivadas na análise detalhada.

4. Você pode usar os mesmos métodos de antes para verificar se a PDT é criada de forma incremental:

   • Se você tiver a permissão see_logs, use o registro de eventos da PDT para conferir os eventos create increment complete da 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 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 o PDT foi incrementado. Por exemplo: -- increment persistent derived table e_incremental_pdt to generation 2

5. Depois de verificar se a PDT está sendo criada e incrementada corretamente, se você não quiser manter a análise detalhada dedicada a ela, remova ou comente os parâmetros explore e include da PDT no arquivo de modelo.

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

Como solucionar problemas de TDPs incrementais

Nesta seção, descrevemos alguns problemas comuns que podem ocorrer ao usar PDTs incrementais, além de etapas para solucionar e resolver esses problemas.

A PDT incremental não é criada após uma mudança de esquema

Se a PDT incremental for uma tabela derivada baseada 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 alteração do tipo de dados de uma coluna) poderão fazer com que a PDT 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 SELECT 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.

Dialetos de banco de dados compatíveis com PDTs incrementais

Para que o Looker ofereça suporte a PDTs incrementais no seu projeto, o dialeto do banco de dados precisa ser compatível com comandos da linguagem de definição de dados (DDL) que permitem 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. Para o Databricks, as PDTs incrementais são compatíveis apenas com a versão 12.1 e mais recentes do Databricks.

Dialeto	Compatível?
Actian Avalanche	Não
Amazon Athena	Não
Amazon Aurora MySQL	Não
Amazon Redshift	Sim
Amazon Redshift 2.1+	Sim
Amazon Redshift Serverless 2.1+	Sim
Apache Druid	Não
Apache Druid 0.13+	Não
Apache Druid 0.18+	Não
Apache Hive 2.3+	Não
Apache Hive 3.1.2+	Não
Apache Spark 3+	Não
ClickHouse	Não
Cloudera Impala 3.1+	Não
Cloudera Impala 3.1+ with Native Driver	Não
Cloudera Impala with Native Driver	Não
DataVirtuality	Não
Databricks	Sim
Denodo 7	Não
Denodo 8 & 9	Não
Dremio	Não
Dremio 11+	Não
Exasol	Não
Google BigQuery Legacy SQL	Não
Google BigQuery Standard SQL	Sim
Google Cloud PostgreSQL	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
Microsoft Azure SQL Database	Não
Microsoft Azure Synapse Analytics	Sim
Microsoft SQL Server 2008+	Não
Microsoft SQL Server 2012+	Não
Microsoft SQL Server 2016	Não
Microsoft SQL Server 2017+	Não
MongoBI	Não
MySQL	Sim
MySQL 8.0.12+	Sim
Oracle	Não
Oracle ADWC	Não
PostgreSQL 9.5+	Sim
PostgreSQL pre-9.5	Sim
PrestoDB	Não
PrestoSQL	Não
SAP HANA	Não
SAP HANA 2+	Não
SingleStore	Não
SingleStore 7+	Não
Snowflake	Sim
Teradata	Não
Trino	Não
Vector	Não
Vertica	Sim