TDPs incrementais

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

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

Se seu dialeto for compatível com TDPs incrementais, você pode 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 TDP incremental, o Looker cria toda a TDP para coletar os dados iniciais. Se a tabela for grande, a criação inicial poderá 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 TDP incremental for definida estrategicamente.

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). 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 usada como uma TDP incremental. TDPs baseadas em SQL que são definidas com os parâmetros sql_create ou create_process não podem ser criadas de forma incremental. Como mostrado no Exemplo 1 desta 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 de linguagem de definição de dados (DDL) personalizadas, 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 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 tornar a TDP uma TDP incremental): define o período em que os novos registros precisam ser consultados.
• {% incrementcondition %} Filtro líquido (necessário para transformar uma TDP baseada em SQL em uma TDP incremental, não aplicável a TDPs com base 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 atrasados, em que os períodos anteriores podem ter novos dados que não foram incluídos quando o incremento correspondente foi criado originalmente e anexado à TDP.

Consulte a página de documentação do parâmetro increment_key para conferir exemplos de como criar TDPs incrementais a partir de tabelas derivadas nativas persistentes, tabelas derivadas persistentes baseadas 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 totalmente 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 é, na verdade, o período date do grupo de dimensões departure. Consulte a página de documentação do parâmetro dimension_group para ter 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 dessa 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 da TDP são independentes da estratégia de persistência da TDP:

• A estratégia de persistência da TDP incremental determina apenas quando a TDP aumenta. 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 a opção Recriar tabelas derivadas e execução em uma Análise.
• Quando a TDP aumenta, o criador de TDP determina quando os dados mais recentes foram adicionados anteriormente à tabela, em termos do incremento de tempo mais atual (o período definido pelo parâmetro increment_key). Com base nisso, o criador de TDP 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 de 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 TDPs incrementais são atualizadas, mostrando a interação de increment_key, increment_offset e a estratégia de persistência.

Exemplo 1

Neste exemplo, uma TDP é usada com estas propriedades:

• Increment key: data
• Deslocamento de incremento: 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 1o de junho, por exemplo, a última linha da tabela será adicionada em 1o de maio.
• Como essa TDP tem uma chave de incremento com base na data, o criador de TDP truncará o dia 1o de maio de volta ao início do dia e recriará os dados de 1o de maio até o dia atual, 1o de junho.
• Além disso, essa TDP tem um deslocamento de incremento de 3. Portanto, o criador de TDPs também recria os dados dos três períodos (dias) anteriores antes de 1o de maio. O resultado é que os dados são recriados para 28, 29, 30 de abril e até o dia atual de 1o de junho.

Em termos de SQL, este é o comando que o criador de TDPs executará em 1o de junho para determinar as linhas da TDP existente 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)

E este é o comando SQL que o builder de TDPs 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

Neste exemplo, uma TDP é usada com estas propriedades:

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

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

• Com a estratégia de persistência diária, a tabela é criada automaticamente uma vez por dia. No dia 1o de junho, a última linha da tabela será adicionada no dia 31 de maio.
• Como a chave de incremento é baseada no mês, o criador de TDP será truncado de 31 de maio até o início do mês e recriará os dados de maio até o dia atual, incluindo 1o de junho.
• Como essa TDP não tem deslocamento de incremento, nenhum período anterior é recriado.

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

• No dia 2 de junho, a última linha da tabela será adicionada em 1o de junho.
• Como o criador de TDPs será truncado de volta para o início de junho e depois recriará os dados a partir de 1o de junho até o dia atual, os dados serão recriados apenas para 1o e 2 de junho.
• Como essa TDP não tem deslocamento de incremento, nenhum período anterior é recriado.

Exemplo 3

Neste exemplo, uma TDP é usada com estas propriedades:

• Increment key: mês
• Deslocamento 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 uma compensação 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 que deve ser examinado como forma de entender como as TDPs incrementais funcionam.

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

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

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

• No dia 2 de junho, a última linha da tabela será adicionada em 1o de junho.
• O criador de TDP vai truncar o mês para 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 criador de TDP recriará os dados dos três meses anteriores antes de 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 TDP para garantir que ela seja criada e incrementada. Para testar uma TDP incremental no Modo de Desenvolvimento:

1. Crie um Explore 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 opçã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, é possível navegar diretamente para ela pelo arquivo de visualização ou do modelo no projeto do LookML. Para isso, selecione o menu Ver ações do arquivo ⏷ na parte de cima do arquivo LookML.

1. Em "Explorar", selecione algumas dimensões ou medidas e clique em Executar. O Looker cria toda a TDP. Se esta for a primeira consulta executada na TDP incremental, o criador de TDP cria toda a TDP para obter os dados iniciais. Se a tabela for grande, a criação inicial poderá 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 de TDP. Se a TDP não for exibida no log de eventos da TDP, verifique as informações de status na parte superior do Explorar log de eventos da TDP. Se aparecer "Do cache", selecione Limpar o cache e atualizar para acessar informações mais recentes.
   • Caso contrário, consulte os comentários na guia SQL da barra Dados da opção "Explorar". A guia SQL mostra a consulta e as ações que serão realizadas quando você executar a consulta em "Explore". Por exemplo, se os comentários na guia SQL disserem -- generate derived table e_incremental_pdt,essa será a ação que será realizada quando você clicar em Executar.

3. Depois de criar o build inicial da TDP, solicite um build incremental usando a opção Recriar tabelas derivadas e executar em "Explorar".

4. Você pode usar 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 de TDP para ver eventos create increment complete da TDP incremental. Se você não vir esse evento no log de eventos de TDP 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 opção "Explorar". Nesse caso, os comentários indicarão 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 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 do seu arquivo de modelo.

Depois que a TDP for criada no Modo de Desenvolvimento, a mesma tabela será usada para produção depois que você implantar as alterações, a menos que você faça outras mudanças 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 TDPs incrementais no projeto, o dialeto do banco de dados precisa aceitar comandos de 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 TDPs incrementais na versão mais recente do Looker. No Databricks, as TDPs incrementais são aceitas apenas na versão 12.1 ou mais recente do Databricks:

Dialeto	Compatível?
Avalanche	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 superior	No
Apache Spark 3 ou mais recente	No
ClickHouse	No
Cloudera Impala 3.1 ou mais recente	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
Dremio	No
Dremio 11 ou mais recente	No
Exasol	No
Fio de fogo	No
SQL legado do Google BigQuery	No
SQL padrão do Google BigQuery	Sim
PostgreSQL no Google Cloud	Sim
Google Cloud SQL	No
Google Spanner	No
Greenplum	Sim
HyperSQL	No
IBM Netezza	No
MariaDB	No
PostgreSQL do Microsoft Azure	Sim
Banco de dados SQL do Microsoft Azure	No
Análise do Microsoft Azure Synapse	Sim
Microsoft SQL Server 2008 ou mais recente	No
Microsoft SQL Server 2012 ou mais recente	No
Microsoft SQL Server 2016	No
Microsoft SQL Server 2017 ou mais recente	No
MongoBI	No
MySQL	Sim
MySQL 8.0.12 ou superior	Sim
Oracle	No
ADWC do Oracle	No
PostgreSQL 9.5 ou mais recente	Sim
PostgreSQL anterior à versão 9.5	Sim
PrestoDB	No
PrestoSQL	No
SAP HANA 2 ou posterior	No
SingleStore	No
SingleStore 7 ou mais recente	No
Snowflake	Sim
Teradata	No
Trino	No
Vetor	No
Vertica	Sim