TDPs 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 sua estratégia de persistência. Quando uma TDP é acionada para recriação, por padrão, o Looker recria a tabela inteira.

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

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

• Tabelas de agregação
• TDPs 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, a compilação inicial pode levar um tempo significativo, como seria necessário criar qualquer tabela grande. Depois de criar a tabela inicial, os próximos serão incrementais e levarão menos tempo se a PDT incremental for configurada estrategicamente.

Observe o seguinte para TDPs 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. As TDPs com base em SQL definidas com o parâmetro sql_create ou create_process não podem ser criadas de forma incremental. Como é possível observar 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 que é 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 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 uma TDP incremental

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

• increment_key (obrigatório para que a PDT seja uma TDP incremental): define o período em que os novos registros devem 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 do 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 com chegada tardia, 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 que mostram como criar TDPs incrementais de tabelas derivadas nativas persistentes, tabelas derivadas persistentes baseadas em SQL e tabelas de agregação.

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, a PDT é recriada em incrementos de um dia (increment_key: departure_date), voltando por 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ões e o período são definidos na visualização flights, que é o explore_source da 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 incremental determina apenas quando a TDP aumenta. O builder da 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 a opção Recriar tabelas derivadas e executar em uma Análise.
• Quando a PDT aumenta, o builder dela determina quando os dados mais recentes foram adicionados à tabela com base no incremento de tempo mais atual (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 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 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

Veja como a 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 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 builder da 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 TDP também recria os dados dos três períodos anteriores (dias) antes de 1o 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 builder de PDT vai executar em 1o de junho para determinar as linhas da PDT atual que devem 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 de PDT vai executar em 1o 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 estas propriedades:

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

Veja como esta tabela será atualizada em 1o de junho:

• A estratégia de persistência diária significa que a tabela é criada 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 1o de junho.
• Como o builder de PDT vai 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 PDT não tem deslocamento de incremento, nenhum período anterior é recriado.

Exemplo 3

Este exemplo usa uma TDP com estas propriedades:

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

Este 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 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 como forma de entender como as TDPs incrementais funcionam.

Veja como esta tabela será atualizada em 1o de junho:

• A estratégia de persistência diária significa que a tabela é criada automaticamente uma vez por dia. Em 1o de junho, por exemplo, 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.
• Além disso, essa 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 (meses) anteriores antes de maio. O resultado é que os dados são reconstruídos de fevereiro, março, abril e até o dia atual, 1o de junho.

Veja como esta 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 truncará o mês até 1o 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 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 uma TDP 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 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 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 o Explorar da TDP. Para fazer isso, selecione o botão See file actions e escolha um nome para a Análise.

Dica: depois de incluir a visualização no arquivo de modelo e criar uma Análise, você pode navegar diretamente para ela pelo arquivo de visualização ou de 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 criará toda a PDT. 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 pode levar um tempo significativo, como seria necessário criar qualquer tabela grande.

2. Verifique 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 log de eventos de 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 o cache e atualizar para ver informações mais recentes.
   • Caso contrário, consulte 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 em "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 o build inicial da TDP, solicite um build incremental da TDP usando a opção Recriar tabelas derivadas e executar em "Analisar".

4. É possível usar 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 log de eventos de TDP para ver eventos create increment complete para a TDP incremental. Se você não vir esse evento no log de eventos de PDT e o status da consulta indicar "do cache", selecione Limpar o cache e atualizar para ver 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 PDT foi incrementada. 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. Para mais informações, consulte a seção Tabelas mantidas no Modo de Desenvolvimento da página de documentação Tabelas derivadas no Looker.

Dialetos de banco de dados com suporte para TDPs incrementais

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

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