PDTs incrementais

No Looker, as tabelas derivadas permanentes (PDTs, na sigla em inglês) são gravadas no esquema de rascunho do banco de dados. O Looker persiste e recria um 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.

Um PDT incremental é um PDT criado pelo Looker ao anexar dados novos à tabela em vez de recriá-la totalmente:

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

• Tabelas de agregação
• PDTs baseados em LookML (nativos)
• PDTs baseados em SQL

Na primeira vez que você executa uma consulta em um PDT incremental, o Looker cria todo o PDT para conseguir os dados iniciais. Se a tabela for grande, a compilação inicial poderá levar um tempo significativo, assim como a criação de qualquer tabela grande. Depois que a tabela inicial for criada, as compilações subsequentes serão incrementais e levarão menos tempo, se a PDT incremental estiver configurada estrategicamente.

Observe o seguinte para TDPs incrementais:

• As PDTs incrementais são compatíveis apenas com as que usam uma estratégia de persistência baseada em acionadores (datagroup_trigger, sql_trigger_value ou interval_trigger). As PDTs incrementais 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 da tabela precisa ser definida usando o parâmetro sql para ser usada como uma PDT incremental. As 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 um PDT 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 consegue determinar quais instruções DDL seriam necessárias para criar um incremento preciso.
• A tabela de origem do PDT incremental precisa ser otimizada para consultas baseadas em tempo. Especificamente, a coluna com base no tempo usada para a chave do incremento precisa ter uma estratégia de otimização, como particionamento, classificações, índices ou qualquer outra 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, a 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 em busca dos valores mais recentes pode ser lenta e cara.

Como definir um PDT incremental

Você pode usar os seguintes parâmetros para transformar uma TDP em uma PDT incremental:

• increment_key (necessário para tornar a PDT incremental): define o período de consulta dos novos registros.
• {% incrementcondition %} Filtro líquido (necessário para tornar uma PDT baseada em SQL incremental e não aplicável a PDTs baseadas em LookML): conecta a chave de incremento à coluna de tempo do banco de dados em que a chave de incremento é baseada. Consulte a página da 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 (com 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 originalmente criado e anexado ao PDT.

Consulte a página de documentação do parâmetro increment_key para ver exemplos de como criar PDTs incrementais a partir de tabelas derivadas nativas permanentes, tabelas derivadas permanentes baseadas em SQL e tabelas agregadas.

Veja um exemplo simples de um arquivo de visualização que define um PDT incremental com base 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á totalmente construída 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 é, na verdade, o prazo 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 de increment_key e increment_offset são independentes da estratégia de persistência do PDT:

• A estratégia de persistência do PDT incremental determina apenas quando o PDT aumenta. O criador de TDP 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 exploração.
• Quando o PDT incrementar, o builder de PDT determinará quando os dados mais recentes foram adicionados anteriormente à tabela, em termos de incremento de tempo mais atual (o período definido pelo parâmetro increment_key). Com base nisso, o builder de TDP trunca os dados até o início do incremento de tempo mais recente na tabela e, em seguida, cria o incremento mais recente.
• Se a PDT tiver um parâmetro increment_offset, o builder de PDT também recriará o número de períodos anteriores especificado no parâmetro increment_offset. Os períodos anteriores voltam do 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 um PDT com estas propriedades:

• Incrementar chave: data
• Incrementar deslocamento: 3
• Estratégia de persistência: acionada uma vez por mês no primeiro dia do mês.

Veja como esta 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, a última linha da tabela foi adicionada em 1o de maio.
• Como essa TDP tem uma chave de aumento com base na data, o criador de conteúdo será truncado de 1o de maio até o início do dia e recriará os dados de 1o de maio até 1o de junho atual.
• Além disso, essa TDP tem um deslocamento de incremento de 3. Assim, o criador de TDP também recria os dados dos três períodos (dias) anteriores a 1o de maio. O resultado é que os dados são recriados dos dias 28, 29 e 30 de abril até o dia atual de 1o de junho.

Em termos de SQL, este é o comando que o builder de PDT executará no dia 1o de junho para determinar as linhas da TDP existente 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 PDT executará no dia 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 um PDT com estas propriedades:

• Estratégia de persistência: acionada uma vez por dia.
• Incrementar chave: mês
• Incrementar offset: 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 criador de PDT será truncado de 31 de maio até o início do mês e recriará os dados de todo o mês até o dia atual, incluindo 1o de junho.
• Como essa PDT não tem deslocamento de incremento, nenhum período anterior é reconstruído.

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 criador de TDPs será truncado para o início do mês de junho e depois recriado os dados a partir de 1o de junho até o dia atual, eles serão recriados somente em 1o de junho e 2 de junho.
• Como essa PDT não tem deslocamento de incremento, nenhum período anterior é reconstruído.

Exemplo 3

Este exemplo usa um PDT com estas propriedades:

• Incrementar chave: mês
• Incrementar deslocamento: 3
• Estratégia de persistência: acionada uma vez por dia.

Esse cenário ilustra uma configuração insatisfatória para uma TDP incremental, porque é uma PDT 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 examinar como uma maneira de compreender como PDTs 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 será adicionada em 31 de maio.
• Como a chave de incremento é baseada no mês, o criador de PDT será truncado de 31 de maio até o início do mês e recriará os dados de todo o mês 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 meses anteriores (meses) antes de maio. Como resultado, os dados são recriados de fevereiro, março e abril 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 do 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 criador de TDP recria 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 um PDT incremental no modo de desenvolvimento

Antes de implantar uma nova PDT incremental no seu ambiente de produção, teste a PDT para ter certeza de que ela foi criada e incrementada. Para testar um PDT incremental no modo de desenvolvimento:

1. Crie uma exploração para o PDT:

   • 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 exploração para a visualização incremental do PDT.

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

2. Abra a ferramenta Explorar para o PDT. Para fazer isso, selecione o botão Ver ações do arquivo e escolha um nome.

Dica: depois de incluir a visualização no arquivo de modelo e criar uma exploração, é possível navegar diretamente para ela a partir do arquivo de visualização ou do modelo no projeto do LookML. Para fazer isso, selecione o menu Ver ações do arquivo ⏷ na parte superior do arquivo LookML.

1. Em "Explorar", selecione algumas dimensões ou medidas e clique em Executar. O Looker vai criar todo o PDT. Se esta for a primeira consulta executada no PDT incremental, o builder do PDT criará todo o PDT para obter os dados iniciais. Se a tabela for grande, a compilação inicial poderá levar um tempo significativo, assim como a criação de qualquer tabela grande.

2. Verifique se a TDP inicial foi criada das seguintes maneiras:

   • Se você tiver a permissão see_logs, poderá verificar se a tabela foi criada procurando no Log de eventos do PDT. Se o PDT não criar eventos no log de eventos da PDT, verifique as informações de status na parte superior da ferramenta. Se estiver escrito "do cache", selecione Limpar o cache e atualizar para ver as informações mais recentes.
   • Caso contrário, você pode analisar os comentários na guia SQL da barra Dados do Explorar. A guia SQL mostra a consulta e as ações que serão realizadas quando ela for executada em "Explorar". Por exemplo, se os comentários na guia SQL exibirem -- generate derived table e_incremental_pdt, essa será a ação que será executada quando você clicar em Executar.

3. Depois de criar a versão inicial da PDT, solicite uma versão incremental usando a opção Rebuild Derived Tables & Run do Explorar.

4. Você pode usar os mesmos métodos de antes para verificar se o PDT foi criado de modo incremental:

   • Se você tiver a permissão see_logs, poderá usar o Log de eventos do PDT para ver os eventos create increment complete do PDT incremental. Se você não vir esse evento no log de eventos do PDT e o status da consulta for "do cache", selecione Limpar cache e atualizar para receber informações mais recentes.
   • Veja os comentários na guia SQL da barra Dados da guia Explorar. Nesse caso, os comentários indicarão que a PDT foi incrementada. Por exemplo: -- increment persistent derived table e_incremental_pdt to generation 2

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

Depois que o PDT for criado no modo de desenvolvimento, a mesma tabela será usada na produção após a implantação das alterações, a menos que você faça outras alterações na definição da tabela. Consulte a seção Tabelas persistentes no modo de desenvolvimento da página de documentação Tabelas derivadas no Looker para mais informações.

Dialetos de banco de dados compatíveis com PDTs incrementais

Para que o Looker seja compatível com PDTs incrementais no projeto do Looker, o dialeto do banco de dados precisa ser compatível com comandos da linguagem de definição de dados (DDL, na sigla em inglês) 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:

Observação :para o Databricks, os PDTs incrementais são compatíveis apenas com o Databricks versão 12.1 e mais recentes.