Tutorial de consciência agregada

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 consciência agregada ou casos extremos, nem um catálogo completo de todos os recursos.

O que é consciência agregada?

No Looker, você consulta principalmente 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 levam a uma experiência ruim do usuário quando ele precisa escolher entre várias análises para os mesmos dados.

Agora, com a consciência de agregação do Looker, você pode pré-construir tabelas de agregação para vários níveis de granularidade, dimensionalidade e agregação. Além disso, é possível informar ao Looker como usá-las nas análises detalhadas 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 agregadas do Looker são um tipo de tabela derivada persistente (PDT, na sigla em inglês). 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 da consciência agregada

Há várias propostas de valor significativas 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 vai 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 melhor que recupera respostas mais rapidamente, a consolidação elimina a criação redundante da guia "Explorar".
• 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 existente: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 a própria visualização e análise detalhada. Confira o LookML de uma tabela de agregação que podemos definir para o recurso:

  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, um usuário pode consultar a análise detalhada flights, e o Looker vai usar automaticamente a tabela de agregação definida no LookML para responder às consultas. O usuário não precisa informar o Looker sobre condições especiais: se a tabela for adequada aos campos selecionados, o Looker vai usar essa tabela.

Os usuários com permissões see_sql podem usar os comentários na guia SQL de uma Análise para saber qual tabela agregada 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 Conhecimento agregado para saber como determinar se as tabelas de agregação são usadas em 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 um tempo de execução alto

Uma ótima oportunidade para aumentar a conscientização é criar tabelas agregadas para painéis muito usados com um tempo de execução muito alto. Os usuários podem reclamar de painéis lentos, mas, se você tiver see_system_activity, também poderá usar a Análise do histórico de atividade do sistema do Looker para encontrar painéis com um tempo de execução mais lento do que a média. Como atalho, você pode abrir este link Explorar do histórico de atividades do sistema em um navegador e substituir "nome do host" no URL com o nome da instância do Looker. Você vai encontrar uma visualização de análise detalhada com dados sobre os painéis da sua instância, incluindo Título, Histórico, Contagem de análises detalhadas, Razão de cache x banco de dados e Está com desempenho pior 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 Visualizações de amostra. O painel Visualizações de amostra usa duas análises detalhadas. Portanto, uma boa estratégia seria criar tabelas agregadas 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. Como atalho, abra o link de Análise do histórico de atividade do sistema em um navegador e substitua "hostname" no URL pelo nome da sua instância do Looker. Você vai encontrar uma visualização de Análise detalhada com dados sobre as análises detalhadas da sua instância, incluindo Análise detalhada, Modelo, Contagem de execução de consulta, 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 detalhadas consultadas com frequência
• Análises que estão 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 à implementação do reconhecimento agregado.

Identifique os campos que são 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 detalhada de uso de campos de atividade do sistema para entender os campos selecionados com frequência nas análises detalhadas que você identificou como lentos e muito usados. Como atalho, você pode abrir este link para analisar o uso dos campos de atividade do sistema em um navegador e substituir "nome do host" no URL com o nome da 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 inclusão 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. Este é um exemplo de 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. Vale a pena entender que os três podem ser mutuamente exclusivos: os painéis problemáticos podem não ser alimentados pelas Análises detalhadas problemáticas, e a criação de tabelas agregadas com os campos usados com frequência pode não ajudar esses painéis. É possível que essas sejam três implementações de reconhecimento agregadas distintas.

Como projetar tabelas de agregação

Depois de identificar oportunidades de conscientização agregada, você pode criar tabelas agregadas que atendam melhor a essas oportunidades. Consulte a página de documentação Conhecimento agregado para saber mais sobre os campos, as medidas e os períodos com suporte em tabelas agregadas, além de outras diretrizes para projetar tabelas agregadas.

OBSERVAÇÃO: as tabelas agregadas não precisam ser uma correspondência exata para que a 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.

A consciência agregada é compatível com as seguintes medidas:

• Medidas padrão: medidas do tipo SOMA, CONTAGEM, MÉDIA, MÍNIMO e MÁXIMO.
• Medidas compostas: medidas 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 só pode ser calculada em dados atômicos não agregados, as medidas *_DISTINCT não têm suporte fora dessas aproximações que usam o HyperLogLog.
• Medidas baseadas em cardinalidade: assim como em medidas distintas, as medianas e percentis não podem ser pré-agregadas e não são compatíveis. 

OBSERVAÇÃO: se você souber de uma possível consulta do usuário com tipos de medida que não têm suporte para a consciência agregada, talvez seja necessário criar uma tabela agregada que seja uma correspondência exata de uma consulta. Uma tabela de agregação que corresponde exatamente à consulta pode ser usada para responder a uma consulta com tipos de medição que não teriam suporte para a consciência agregada.

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 campos para criar tabelas agregadas que serão usadas com a maior frequência possível e com o maior impacto. Todos os campos usados na consulta (selecionados ou filtrados) precisam estar na tabela agregada para que ela seja usada na consulta. No entanto, como observado anteriormente, a tabela agregada não precisa ser uma correspondência exata para que uma consulta seja usada. 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, além de duas medidas (flights.count e flights.cancelled_count). Portanto, seria lógico criar uma tabela agregada 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;;
      }
    }
  }

Os usuários da sua empresa e evidências anedóticas, bem como os dados da atividade do sistema do Looker, podem ajudar a orientar seu processo de tomada de decisão.

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 de agregação 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 pivotagem da consulta, que seria uma operação normal do usuário, levou 29,5 segundos.

Depois de implementar a tabela de agregação flights_by_week_and_carrier, a consulta subsequente levou 7,2 segundos e verificou 4.592 linhas. Isso representa 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 que tenhamos estimado modestamente que 25% dessas consultas usaram os quatro campos da maneira mais simples (seleção simples, sem pivot), 3379 x 8,6 segundos = 8 horas e 4 minutos no tempo de espera agregado do usuário eliminado.

OBSERVAÇÃO: o modelo de exemplo usado aqui é muito básico. Esses resultados não devem ser usados como comparativo de mercado ou 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	Quando a consulta foi executada	Linhas verificadas
Mesa de base	13,1 segundos	285.000
Tabela de agregação	5,1 segundos	138 mil
Delta	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 e avaliar os retornos que podem ser obtidos com:

• Otimizar modelos maiores e mais complexos/Análises que tenham "aceitáveis" performance e podem melhorar o desempenho com 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ões 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 tem uma redução de 97% no tamanho (em relação às 38 milhões de linhas anteriores). No entanto, a adição de mais uma dimensão aumenta o tamanho da tabela para 20 milhões de linhas. Assim, os retornos diminuem à medida que você inclui mais campos na tabela agregada 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 detalhada ou de um painel e adicionar o LookML aos arquivos do projeto do Looker.

Depois que você adicionar as tabelas de agregação ao projeto do LookML e implantar as atualizações na produção, as Análises vão utilizar as tabelas de agregação para os usuários consultas.

Persistência

Para que a agregação seja possível, as tabelas de agregação precisam ser mantidas no 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 agregada que é usada na Análise detalhada associada. Se não for possível usar grupos de dados, outra opção é usar o parâmetro sql_trigger_value. Confira a seguir um valor genérico e baseado na data para sql_trigger_value:

sql_trigger_value: SELECT CURRENT_DATE() ;;

Isso vai criar suas tabelas agregadas automaticamente à meia-noite de cada dia.

Lógica do período

Quando o Looker cria uma tabela agregada, ele inclui os dados até o momento em que ela foi criada. 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 Pedidos 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 UNION dados novos na tabela agregada quando um usuário consulta um período que se sobrepõe à tabela agregada, conforme mostrado no mesmo diagrama de linha do tempo:

Como o Looker pode unir dados atualizados 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. 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 conscientização agregada:

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 maior cobertura para consultas comuns do usuário, mantendo-se pequenas o suficiente para reduzir o tamanho dessas consultas de forma suficiente.
3. Crie as tabelas agregadas no modelo do Looker, combinando a persistência da tabela com a persistência do cache do recurso "Explorar".