Tutorial de reconhecimento agregado

Para mais detalhes, consulte a página de documentação Reconhecimento agregado.

Introdução

Esta página é um guia para implementar o reconhecimento agregado em um cenário prático, incluindo a identificação de oportunidades de implementação, o valor que gera reconhecimento agregado e um fluxo de trabalho simples para implementá-lo em um modelo real. Esta página não é uma explicação detalhada de todos os recursos agregados de reconhecimento ou casos extremos, nem é um catálogo completo de todos os recursos.

O que é o reconhecimento agregado?

No Looker, você consulta principalmente tabelas ou visualizações brutas no seu banco de dados. Às vezes, são tabelas derivadas persistentes (TDPs) do Looker.

Muitas vezes, você pode encontrar tabelas ou conjuntos de dados muito grandes que, para ter bom desempenho, precisam de tabelas de agregação ou visualizações completas.

É comum criar tabelas de agregação como orders_daily, com dimensionalidade limitada. Eles precisam ser tratados e modelados separadamente em "Explore" e não ficam bem posicionados no modelo. Essas limitações levam a experiências ruins dos usuários quando eles precisam escolher entre várias Análises detalhadas para os mesmos dados.

Agora, com o reconhecimento agregado do Looker, você pode criar tabelas de agregação em vários níveis de granularidade, dimensionalidade e agregação, além de informar ao Looker como usá-las nas Análises atuais. As consultas usam essas tabelas de visualização completa quando o Looker considera apropriado, sem qualquer entrada do usuário. Isso reduzirá o tamanho da consulta e os tempos de espera, além de melhorar a experiência do usuário.

OBSERVAÇÃO:as tabelas de agregação do Looker são um tipo de tabela derivada persistente (TDP). Isso significa que as tabelas de agregação têm os mesmos requisitos de banco de dados e conexão que as TDPs.

Para conferir se o dialeto do banco de dados e a conexão do Looker aceitam TDPs, consulte os requisitos listados na página de documentação Tabelas derivadas no Looker.

Para ver se o dialeto do seu banco de dados é compatível com o reconhecimento agregado, consulte a página de documentação Reconhecimento agregado.

O valor do reconhecimento agregado

Existem várias propostas de valor agregadas com ofertas de reconhecimento que agregam mais valor com seu modelo atual do Looker:

• Melhoria de desempenho:implementar o reconhecimento agregado torna as consultas dos usuários mais rápidas. O Looker vai usar uma tabela menor se ela tiver dados necessários para concluir a consulta do usuário.
• Economia de custos:alguns dialetos cobram pelo tamanho da consulta em um modelo de consumo. Ao ter tabelas menores, você tem um custo por consulta de usuário reduzido.
• Melhoria na experiência do usuário:além de oferecer uma experiência aprimorada que recupera respostas mais rapidamente, a consolidação elimina a criação redundante de Análises.
• Redução do uso do LookML:substituir as estratégias atuais de reconhecimento agregado com base em líquidos por uma implementação nativa e flexível leva a uma maior resiliência e a menos erros.
• Capacidade de aproveitar o LookML atual:as tabelas de agregação usam o objeto query, que reutiliza a lógica de modelo atual em vez de duplicar a lógica com SQL personalizado explícito.

Exemplo básico

Veja a seguir uma implementação muito simples em um modelo do Looker para demonstrar como o reconhecimento agregado pode ser leve. Considerando uma tabela flights hipotética no banco de dados com uma linha para cada voo registrado pela FAA, podemos modelar essa tabela no Looker com visualizações e Análises próprias. Este é o LookML de uma tabela de agregação que podemos definir para o Explore:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Com essa tabela de agregação, os usuários podem consultar a Análise do flights, e o Looker usa automaticamente a tabela de agregação definida acima para responder a consultas. O usuário não precisa informar o Looker sobre nenhuma condição especial. Ele só vai usar a tabela se ela for adequada para os campos selecionados.

Os usuários com permissões see_sql podem acessar os comentários na guia SQL de uma Análise para conferir qual tabela de agregação vai ser usada em uma consulta. Confira um exemplo de guia SQL do Looker para uma consulta que usa a tabela de agregação flights:flights_by_week_and_carrier in teach_scratch:

Consulte a página de documentação Reconhecimento agregado para saber como determinar se as tabelas de agregação são usadas em uma consulta.

Identificar oportunidades

Para maximizar os benefícios do reconhecimento agregado, você precisa identificar onde esse reconhecimento pode desempenhar um papel na otimização ou impulsionar os valores mencionados acima.

Identifique painéis com um alto tempo de execução

Uma ótima oportunidade de reconhecimento agregado é criar tabelas de agregação para painéis muito usados com um ambiente de execução muito alto. É possível que você informe seus usuários sobre painéis lentos, mas, se você tiver o see_system_activity, também poderá usar a Análise do histórico de atividades do sistema do Looker para encontrar painéis com um ambiente de execução mais lento que o normal. Como atalho, você pode abrir este link da Análise do histórico de atividades do sistema em um navegador e substituir "nome do host" no URL pelo nome da sua instância do Looker. Você verá uma visualização com dados sobre os painéis da sua instância, incluindo Título, Histórico, Contagem de explorações, Ração do cache vs. banco de dados e Está com desempenho pior do que a média:

Neste exemplo, há vários painéis com alta utilização que têm desempenho pior do que a média, como o painel Exibições de amostra. O painel Samples usa dois Explores. Uma boa estratégia é criar tabelas de agregação para elas.

Identificar Explores que estão lentos e consultados com frequência pelos usuários

Outra oportunidade de reconhecimento agregado é com as Análises detalhadas que são muito consultadas pelos usuários e têm respostas abaixo da média.

Você pode usar a Análise do histórico de atividades do sistema como ponto de partida para identificar oportunidades de otimização das Análises. Como atalho, você pode abrir o link da Análise do histórico de atividades do sistema em um navegador e substituir "nome do host" no URL pelo nome da sua instância do Looker. Você verá uma visualização com dados sobre as Análises da sua instância, incluindo Análise, Modelo, Contagem de execuções de consultas, Contagem de usuários e Tempo médio de execução em segundos:

No recurso "Explorar" do histórico, é possível identificar os seguintes tipos de Análises na sua instância:

• Análises que são consultadas pelos usuários (em vez de consultas da API ou de entregas programadas)
• Explores consultados com frequência
• Análises detalhadas com baixa performance (em relação a outras Análises)

No exemplo anterior da Análise de histórico de atividades do sistema, as Análises flights e order_items são prováveis candidatos para implementação do reconhecimento agregado.

Identificar os campos que são muito usados em consultas

Por fim, você pode identificar outras oportunidades no nível dos dados entendendo os campos que os usuários geralmente incluem em consultas e filtros.

Use a Análise de uso do campo de atividade do sistema para entender os campos que costumam ser selecionados nas Análises identificadas acima. Como atalho, você pode abrir este link para a Análise de uso do campo de atividade do sistema em um navegador e substituir "nome do host" no URL pelo nome da sua instância do Looker. Substitua os filtros adequadamente. Você vai encontrar uma Análise com uma visualização de gráfico de barras que indica o número de vezes que um campo foi usado em uma consulta:

Na Análise de atividades do sistema mostrada acima, flights.count e flights.depart_week são os dois campos mais selecionados para a Análise. Portanto, esses são bons candidatos para campos para incluir em tabelas de agregação.

Dados concretos como esses são úteis, mas há elementos subjetivos que orientam seus critérios de seleção. Por exemplo, ao analisar os quatro campos anteriores, é possível presumir com segurança que os usuários geralmente analisam o número de voos agendados e o número de voos cancelados, além de quererem detalhar esses dados por semana e por transportadora. Esse é um exemplo de uma combinação clara, lógica e real de campos e métricas.

Resumo

As etapas acima servem como um guia para encontrar painéis, Análises e campos que precisam ser considerados para otimização. Também é importante entender que os três podem ser mutuamente exclusivos: os painéis problemáticos podem não ser alimentados pelos "Explores" problemáticos, e criar tabelas de agregação com os campos usados com frequência pode não ser útil para esses painéis. É possível que sejam três implementações diferentes de reconhecimento agregado.

Como projetar tabelas de agregação

Depois de identificar as oportunidades de reconhecimento agregado, crie tabelas de agregação que melhor atendam a essas oportunidades. Consulte a página de documentação Reconhecimento agregado para ver informações sobre os campos, medidas e períodos aceitos nas tabelas de agregação, além de outras diretrizes para criar tabelas desse tipo.

OBSERVAÇÃO: as tabelas de agregação não precisam ser uma correspondência exata para que sua consulta seja usada. Se a consulta estiver na granularidade da semana e você tiver uma tabela de visualização completa diária, o Looker vai usar a tabela de agregação em vez da tabela bruta no nível do carimbo de data/hora. Da mesma forma, se você tiver uma tabela de agregação agrupada no nível brand e date e um usuário fizer uma consulta apenas no nível brand, essa tabela ainda poderá ser usada pelo Looker para reconhecimento agregado.

O reconhecimento agregado é compatível com as seguintes medidas:

• Medidas padrão: medidas do tipo SUM, COUNT, AVERAGE, MIN e MAX
• Medidas compostas: medições do tipo NUMBER, STRING, YESNO e DATE
• Medidas distintas aproximadas : dialetos que podem usar a funcionalidade HyperLogLog

O reconhecimento agregado não é compatível com as seguintes medidas:

• Medidas distintas:como a distinção pode ser calculada apenas em dados atômicos não agregados, as medidas *_DISTINCT não são compatíveis fora desses valores aproximados que usam o HyperLogLog.
• Medidas com base na cardinalidade:assim como as medidas distintas, as medianas e os percentis não podem ser pré-agregados e não são compatíveis. 

OBSERVAÇÃO:se você souber de uma possível consulta de usuário com tipos de medidas que não são compatíveis com o reconhecimento agregado, convém criar uma tabela de agregação que seja uma correspondência exata de uma consulta. Uma tabela de agregação que é uma correspondência exata da consulta pode ser usada para responder a uma consulta com tipos de medidas que, de outra forma, não seriam aceitos para reconhecimento agregado.

Granularidade da tabela de agregação

Antes de criar tabelas para combinações de dimensões e medidas, determine padrões comuns de uso e seleção de campo para fazer tabelas de agregação que serão usadas com a maior frequência possível com o maior impacto. Todos os campos usados na consulta (selecionados ou filtrados) precisam estar na tabela de agregação para que ela seja usada na consulta. Mas, como observado anteriormente, a tabela de agregação não precisa ser uma correspondência exata para uma consulta usada para a consulta. É possível lidar com várias consultas de usuários em potencial em uma única tabela de agregação e ainda ter grandes ganhos de desempenho.

No exemplo acima sobre identificação de campos que são muito usados em consultas, há duas dimensões selecionadas com muita frequência (flights.depart_week e flights.carrier), além de duas medidas (flights.count e flights.cancelled_count). Portanto, seria lógico criar uma tabela de agregação que use todos esses quatro campos. Além disso, criar uma única tabela de agregação para flights_by_week_and_carrier resulta em um uso mais frequente dessa tabela do que duas diferentes para flights_by_week e flights_by_carrier.

Veja um exemplo de tabela de agregação que podemos criar para consultas nos campos comuns:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Os usuários comerciais, as evidências e os dados da atividade do sistema do Looker podem ajudar a orientar seu processo de tomada de decisões.

Equilíbrio entre aplicabilidade e desempenho

O exemplo a seguir mostra uma consulta do recurso "Explorar" dos campos "Semana de partida dos voos", "Transportadora dos detalhes dos voos", "Contagem de voos" e "Contagem detalhada cancelada de voos" na tabela de agregação flights_by_week_and_carrier:

A execução dessa consulta da tabela de banco de dados original levou 15,8 segundos e verificou 38 milhões de linhas sem mesclagens usando o Amazon Redshift. A rotação da consulta, que seria uma operação normal de um usuário, levou 29,5 segundos.

Após implementar a tabela de agregação flights_by_week_and_carrier, a consulta subsequente levou 7,2 segundos e verificou 4.592 linhas. Esta é uma redução de 99,98% no tamanho da tabela. A rotação da consulta levou 9,8 segundos.

Com a Análise de uso do campo de atividade do sistema, é possível saber com que frequência os usuários incluem esses campos nas consultas. Neste exemplo, flights.count foi usado 47.848 vezes, flights.depart_week foi usado 18.169 vezes, flights.cancelled_count foi usado 16.570 vezes e flights.carrier foi usado 13.517 vezes.

Mesmo se estimamos muito pouco que 25% dessas consultas usaram os quatro campos da maneira mais simples (seleção simples, sem tabela dinâmica), 3.379 x 8,6 segundos = 8 horas, 4 minutos de tempo de espera do usuário eliminado.

OBSERVAÇÃO:o modelo de exemplo usado aqui é muito básico. Esses resultados não devem ser usados como comparativos de mercado ou referências para seu modelo.

Depois de aplicar exatamente o mesmo fluxo ao nosso modelo de e-commerce order_items, a Análise mais usada na instância, os resultados vão ficar os seguintes:

Origem.	Tempo da consulta	Linhas verificadas
Mesa de base	13,1 segundos	285.000
Tabela de agregação	5,1 segundos	138.000
Delta	8 segundos	147.000

Os campos usados na consulta e na tabela de agregação subsequente foram brand, created_date, orders_count e total_revenue, usando duas junções. Os campos foram usados 11.000 vezes no total. Estimando o mesmo uso combinado de aproximadamente 25%, a economia agregada para os usuários seria de 6 horas, 6 minutos (8 s * 2750 = 22.000s). A tabela de agregação levou 17,9 segundos para ser criada.

Analisando esses resultados, vale a pena dar um passo atrás e avaliar os retornos potencialmente obtidos com:

• Otimização de modelos/Análises detalhadas maiores e mais complexas com desempenho "aceitável" e que podem ser melhoradas com as práticas recomendadas de modelagem

X

• Usar o reconhecimento agregado para otimizar modelos mais simples que são usados com mais frequência e têm baixa performance

Seus esforços vão diminuir à medida que você tentar conseguir o último desempenho do Looker e do seu banco de dados. Você precisa estar sempre ciente das expectativas de desempenho de referência, principalmente dos usuários comerciais, e das limitações impostas pelo seu banco de dados (como simultaneidade, limites de consulta, custo etc.). Não espere a consciência agregada superar essas limitações.

Além disso, ao projetar uma tabela de agregação, lembre-se de que ter mais campos resultará em uma tabela de agregação maior e mais lenta. Tabelas maiores podem otimizar mais consultas e, portanto, ser usadas em mais situações, mas tabelas grandes não serão tão rápidas quanto tabelas menores e mais simples.

Exemplo:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week,flights.distance, flights.arrival_week,flights.cancelled]
        measures: [cancelled_count, count, flights.average_distance, flights.total_distance]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Com isso, a tabela de agregação será usada para qualquer combinação de dimensão exibida e qualquer uma das medidas incluídas. Assim, a tabela poderá ser usada para responder a muitas consultas de usuários diferentes. Mas usar essa tabela para uma consulta SELECT simples de carrier e count exigiria a verificação de uma tabela de 885.000 linhas. Por outro lado, a mesma consulta exigiria apenas uma verificação de 4.592 linhas se a tabela fosse baseada em duas dimensões. A tabela com 885 mil linhas ainda apresenta uma redução de 97% no tamanho da tabela (em relação às 38 milhões de linhas anteriores), mas adicionar mais uma dimensão aumenta o tamanho da tabela para 20 milhões de linhas. Assim, há retornos decrescentes à medida que você inclui mais campos na tabela de agregação para aumentar sua aplicabilidade a mais consultas.

Como criar tabelas de agregação

Com base no nosso exemplo da Análise de Voos, que identificamos como uma oportunidade de otimização, a melhor estratégia seria criar três tabelas de agregação diferentes para ela:

• flights_by_week_and_carrier
• flights_by_month_and_distance
• flights_by_year

A maneira mais fácil de criar essas tabelas de agregação é acessar a tabela de agregação do LookML em uma consulta do Explore ou de um painel e adicionar o LookML aos arquivos do projeto do Looker.

Depois de adicionar as tabelas de agregação ao projeto LookML e implantar as atualizações na produção, as Análises usarão as tabelas de agregação para as consultas dos usuários.

Persistência

Para serem acessíveis para reconhecimento agregado, as tabelas agregadas precisam ser mantidas no seu banco de dados. A prática recomendada é alinhar a nova geração automática dessas tabelas de agregação à sua política de armazenamento em cache utilizando datagroups. Use o mesmo grupo de dados para uma tabela de agregação usada na Análise associada. Se não for possível usar grupos de dados, outra alternativa é usar o parâmetro sql_trigger_value. Um valor genérico baseado em data para sql_trigger_value é mostrado abaixo:

sql_trigger_value: SELECT CURRENT_DATE() ;;

Isso criará suas tabelas de agregação automaticamente à meia-noite de todos os dias.

Lógica de intervalo de tempo

Quando o Looker cria uma tabela de agregação, ela inclui dados até o momento em que essa tabela foi criada. Quaisquer dados anexados posteriormente à tabela base no banco de dados normalmente são excluídos dos resultados de uma consulta que usa essa tabela de agregação.

Este diagrama mostra a linha do tempo de quando os pedidos foram recebidos e registrados no banco de dados, em comparação com o momento em que a tabela de agregação Pedidos foi criada. Dois pedidos recebidos hoje não constam na tabela de agregação Pedidos, já que eles foram recebidos após a criação da tabela de agregação:

No entanto, o Looker pode UNION dados novos à tabela de agregação quando um usuário faz uma consulta por um período que se sobrepõe à tabela de agregação, conforme mostrado no mesmo diagrama de linha do tempo:

Como o Looker pode UNION novos dados a uma tabela de agregação, se um usuário filtrar por um período que se sobrepõe ao final das tabelas de agregação e da base, os pedidos recebidos após a criação da tabela de agregação serão incluídos nos resultados do usuário. Consulte a página de documentação Reconhecimento agregado para conferir os detalhes e as condições que precisam ser atendidas para unir dados novos e agregar consultas de tabelas.

Resumo

Para recapitular, para criar uma implementação de reconhecimento agregado, há três etapas fundamentais:

1. Identificar oportunidades em que a otimização usando tabelas de agregação é apropriada e impactante.
2. Crie tabelas de agregação que vão fornecer a maior cobertura para consultas comuns de usuários, mantendo-se pequenas o suficiente para reduzir o tamanho dessas consultas o suficiente.
3. Crie as tabelas de agregação no modelo do Looker, pareando a persistência da tabela com a do cache do Explore.