Tutorial sobre agregar reconhecimento

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 que gera o reconhecimento agregado de valor 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 de reconhecimento agregados ou casos extremos, nem é um catálogo completo de todos os recursos.

O que é reconhecimento agregado?

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

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

Normalmente, você pode criar tabelas de agregação como uma tabela orders_daily que contém dimensionalidade limitada. Elas precisam ser tratadas e modeladas separadamente em "Analisar" e não se encaixam perfeitamente no modelo. Essas limitações causam experiências ruins quando o usuário precisa escolher entre várias Análises para os mesmos dados.

Agora, com o reconhecimento agregado do Looker, é possível pré-criar tabelas de agregação em vários níveis de granularidade, dimensionalidade e agregação, além de informar o Looker sobre como usá-las nas Análises atuais. As consultas vão usar essas tabelas de visualização completa quando o Looker considerar apropriado, sem nenhuma entrada do usuário. Isso reduz o tamanho das consultas, reduz o tempo de espera e melhora a experiência do usuário.

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

Para saber se o dialeto do banco de dados e a conexão do Looker são compatíveis com PDTs, 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

Há várias propostas de valor que oferecem reconhecimento agregado para gerar mais valor com seu modelo atual do Looker:

• Melhoria na performance:implementar o reconhecimento agregado vai tornar as consultas dos usuários mais rápidas. O Looker usará uma tabela menor se ela tiver os 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 usar o Looker para consultar tabelas menores, o custo por consulta de usuário é reduzido.
• Melhoria na experiência do usuário:além de uma experiência aprimorada que recupera respostas mais rapidamente, a consolidação elimina a criação redundante de Análises.
• Redução do consumo do LookML:substituir as estratégias de reconhecimento agregadas atuais baseadas em líquido por implementações flexíveis e nativas leva a um aumento da resiliência e menos erros.
• Capacidade de usar o LookML atual:as tabelas de agregação usam o objeto query, que reutiliza a lógica modelada em vez de duplicar a lógica com um SQL personalizado explícito.

Exemplo básico

Aqui está uma implementação muito simples em um modelo do Looker para demonstrar como o reconhecimento agregado leve pode ser. 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 uma visualização e uma Análise próprias. Este é o LookML de uma tabela de agregação que podemos definir para a Análise:

  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, o usuário pode consultar a Análise do flights, e o Looker vai aproveitar automaticamente a tabela de agregação definida no LookML e usar a tabela agregada para responder a consultas. O usuário não precisa informar o Looker sobre nenhuma condição especial: se a tabela atender aos campos selecionados pelo usuário, o Looker a usará.

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

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

Identificação de oportunidades

Para maximizar os benefícios da percepção agregada, você deve identificar onde o reconhecimento agregado pode desempenhar um papel na otimização ou na condução do valor do reconhecimento agregado.

Identificar painéis com alto ambiente de execução

Uma ótima oportunidade para reconhecimento agregado é criar tabelas de agregação para painéis muito usados com um tempo de execução muito alto. Os usuários podem se informar sobre painéis lentos. No entanto, 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 tempo de execução mais lento que a média. Como atalho, abra este link "Análise do histórico de atividades do sistema" em um navegador e substitua "nome do host" no URL pelo nome da sua instância do Looker. Você verá uma visualização "Explorar" com dados sobre os painéis da instância, incluindo Título, Histórico, Contagem de Análises, Ração do cache em comparação ao banco de dados e O desempenho é pior do que a média:

Neste exemplo, há vários painéis com alta utilização que apresentam desempenho pior do que a média, como o painel Visualizações de amostra. O painel Exemplos de visualizações usa duas Análises. Portanto, uma boa estratégia seria criar tabelas de agregação para ambas.

Identificar as Análises lentas e muito consultadas pelos usuários

Outra oportunidade de reconhecimento agregado é a Análise com muitas consultas dos usuários e resposta a consultas abaixo da média.

Use a Análise do histórico de atividades do sistema como ponto de partida para identificar oportunidades de otimizar as Análises. Você também pode abrir o link "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. Será exibida uma visualização com dados sobre as Análises da instância, incluindo Análise, Modelo, Contagem de execuções de consultas, Contagem de usuários e Tempo de execução médio em segundos:

Na Análise histórica, é possível identificar os seguintes tipos de Análises na sua instância:

• Análises que são consultadas pelos usuários, em oposição às consultas da API ou de entregas programadas.
• Análises que são consultadas com frequência
• Análises com baixo desempenho (em relação a outras Análises)

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

Identifique os campos muito usados nas consultas.

Por fim, é possível 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 dos campos de atividade do sistema para entender os campos selecionados nas Análises que você identificou como lentos e muito usados. Como atalho, você pode abrir este link para análise de uso dos campos 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ê verá 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:

No exemplo de Análise de atividade do sistema mostrado na imagem, é possível observar que flights.count e flights.depart_week são os dois campos mais selecionados para a Análise. Portanto, esses campos são bons candidatos para campos a serem incluídos em tabelas de agregação.

Dados concretos como esse são úteis, mas existem elementos subjetivos que guiarão seus critérios de seleção. Por exemplo, analisando os quatro campos anteriores, você pode presumir com segurança que os usuários geralmente analisam o número de voos programados e o número de voos que foram cancelados e que querem dividir esses dados por semana e por companhia. Esse é um exemplo de uma combinação clara, lógica e real de campos e métricas.

Resumo

As etapas nesta página de documentação servem como um guia para encontrar dashboards, Análises e campos que precisam ser considerados para otimização. Também vale a pena entender que os três podem ser mutuamente exclusivos: os dashboards problemáticos podem não ser alimentados pelas Análises problemáticas, e criar tabelas de agregação com os campos mais usados pode não ajudar esses painéis. É possível que essas sejam três implementações distintas de reconhecimento agregado.

Como projetar tabelas de agregação

Depois de identificar oportunidades de reconhecimento agregado, você pode criar tabelas de agregação que melhor atendam a essas oportunidades. Consulte a página de documentação Reconhecimento agregado para obter informações sobre os campos, medidas e prazos suportados nas tabelas de agregação, bem como outras diretrizes para projetar tabelas de agregação.

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 sua 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 agregada no nível brand e date e um usuário consultar somente 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: medidas do tipo NUMBER, STRING, YESNO e DATE
• Aproximar medidas distintas : dialetos que podem usar a funcionalidade HyperLogLog

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

• Medidas distintas:como a distinção só pode ser calculada em dados atômicos e não agregados, as medidas *_DISTINCT não são compatíveis fora das aproximações que usam o HyperLogLog.
• Medidas com base em cardinalidade:assim como em outras medidas, as medianas e os percentis não podem ser pré-agregados e não são aceitos. 

OBSERVAÇÃO:se você conhece uma consulta de usuário em potencial com tipos de medidas que não são compatíveis com o reconhecimento agregado, é melhor criar uma tabela agregada 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 compatíveis com o reconhecimento agregado.

Granularidade de tabela agregada

Antes de criar tabelas para combinações de dimensões e medidas, determine padrões comuns de uso e seleção de campo para criar 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 a tabela seja usada na consulta. Mas, como observado anteriormente, a tabela de agregação não precisa ser uma correspondência exata para uma consulta a pelo usado para a consulta. Você pode abordar muitas possíveis consultas de usuários em uma única tabela agregada e ainda obter grandes ganhos de desempenho.

No exemplo de identificação de campos muito usados em consultas, há duas dimensões, flights.depart_week e flights.carrier, que são selecionadas com muita frequência, assim como duas medidas, flights.count e flights.cancelled_count. Portanto, seria lógico criar uma tabela de agregação que use os quatro campos. Além disso, criar uma única tabela de agregação para flights_by_week_and_carrier resultará em um uso mais frequente da tabela agregada do que duas tabelas de agregação diferentes para flights_by_week e flights_by_carrier.

Aqui está um exemplo de tabela de agregação que podemos criar para consultas sobre os 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;;
      }
    }
  }

Seus usuários comerciais e evidências casuais, assim como dados da Atividade do sistema do Looker, podem ajudar a orientar seu processo de tomada de decisões.

Como equilibrar aplicabilidade e performance

O exemplo a seguir mostra uma consulta "Explorar" dos campos "Voos de partida semana", "Detalhes dos voos", "Companhia de voos", "Contagem de voos" e "Contagem de voos detalhada cancelada" da tabela agregada flights_by_week_and_carrier:

A execução dessa consulta na tabela do 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 do usuário, levou 29,5 segundos.

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

Na Análise de uso dos campos de atividade do sistema, podemos ver 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 estimarmos modestamente que 25% dessas consultas usaram os quatro campos da maneira mais simples (seleção simples, sem pivô), 3.379 x 8, 6 segundos = eliminado o tempo agregado de espera do usuário de 8 horas e 4 minutos.

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

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

Origem	Tempo da consulta	Linhas verificadas
Mesa de base	13,1 segundos	285 mil
Tabela de agregação	5,1 segundos	138 mil
Delta (link em inglês)	8 segundos	147.000

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

Analisando esses resultados, vale a pena parar um pouco para avaliar os retornos potencialmente ganhos com:

• Otimização de modelos/explorações maiores e mais complexos que têm uma performance "aceitável" e podem melhorar o desempenho com as melhores práticas 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.

Você vai notar um retorno menor para seus esforços enquanto tenta aproveitar melhor o desempenho do Looker e do seu banco de dados. Esteja sempre atento às expectativas de desempenho de referência, principalmente de usuários comerciais, e às limitações impostas pelo seu banco de dados (como simultaneidade, limites de consulta, custo etc.). Não espere que o reconhecimento agregado supere 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 sã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 mostrada e qualquer uma das medidas incluídas. Portanto, essa tabela pode 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 uma verificação de uma tabela de 885.000 linhas. Por outro lado, a mesma consulta só exigiria uma verificação de 4.592 linhas se a tabela fosse baseada em duas dimensões. A tabela de 885 mil linhas ainda é uma redução de 97% no tamanho da tabela (em relação aos 38 milhões de linhas anteriores), mas adicionar mais uma dimensão aumenta o tamanho da tabela para 20 milhões de linhas. Dessa forma, os retornos diminuem à medida que você inclui mais campos na tabela de agregação para aumentar a aplicabilidade a mais consultas.

Como criar tabelas de agregação

Usando o 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 o LookML da tabela de agregação de uma consulta da Análise ou de um dashboard e adicionar o LookML aos seus arquivos de projeto do Looker.

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

Persistência

Para serem acessíveis para o 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, aproveitando grupos de dados. Use o mesmo grupo de dados para uma tabela de agregação usada para a Análise associada. Se não for possível usar grupos de dados, outra opção é usar o parâmetro sql_trigger_value. Um valor genérico e baseado na data para sql_trigger_value é mostrado abaixo:

sql_trigger_value: SELECT CURRENT_DATE() ;;

Com isso, as tabelas de agregação serão criadas automaticamente à meia-noite todos os dias.

Lógica do período

Quando o Looker cria uma tabela de agregação, ela inclui dados até o momento da criação da tabela. Quaisquer dados que tenham sido posteriormente anexados à tabela base no banco de dados normalmente seriam excluídos dos resultados de uma consulta usando 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 agregada Orders foi criada. Há dois pedidos recebidos hoje que não vão estar presentes na tabela agregada Orders porque eles foram recebidos após a criação da tabela de agregação:

No entanto, o Looker pode UNIONar dados novos à tabela de agregação quando um usuário fizer 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 unir dados novos a uma tabela agregada, se um usuário filtrar por um período que se sobrepõe ao fim da tabela agregada e da tabela base, os pedidos recebidos após a criação da tabela agregada serão incluídos nos resultados do usuário. Consulte a página de documentação Reconhecimento agregado para ver detalhes e as condições que precisam ser atendidas para unir dados novos e agregar consultas de tabelas.

Resumo

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

1. Identifique oportunidades em que a otimização com tabelas de agregação é apropriada e impactante.
2. Crie tabelas de agregação que forneçam o máximo de cobertura para consultas comuns do usuário, 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 da Análise.