Tutorial de consciência agregada

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

Introdução

Esta página é um guia para implementar a consciência agregada em um cenário prático, incluindo a identificação de oportunidades de implementação, os fatores que impulsionam a consciência agregada e um fluxo de trabalho simples para implementá-la 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, elas são tabelas derivadas persistentes (TDPs) do Looker.

É comum encontrar conjuntos de dados ou tabelas muito grandes que, para serem eficientes, exigem tabelas de agregação ou resumos.

Normalmente, é possível criar tabelas de agregação, como uma tabela orders_daily que contém dimensionalidade limitada. Eles precisam ser tratados e modelados separadamente no recurso Explorar e não ficam no modelo de forma adequada. Essas limitações levam a uma experiência ruim quando o usuário 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 agrupamento quando o Looker considerar apropriado, sem nenhuma entrada do usuário. Isso reduz o tamanho da consulta, 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 (TDP, na sigla em inglês). Isso significa que as tabelas agregadas 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 podem oferecer suporte a PDTs, consulte os requisitos listados na página de documentação Tabelas derivadas no Looker.

Para saber se o dialeto do banco de dados oferece suporte à percepção agregada, consulte a página de documentação Percepção agregada.

O valor do reconhecimento agregado

Há várias propostas de valor significativas que agregam ofertas de conscientização para gerar mais valor do seu modelo do Looker:

• Melhoria no desempenho:a implementação da consciência agregada vai acelerar as consultas do usuário. 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 fazer com que o Looker consulte tabelas menores, você terá um custo reduzido por consulta do usuário.
• 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 da guia "Explorar".
• Redução da pegada do LookML:substituir as estratégias de conscientização agregada baseadas em Liquid por uma implementação nativa e flexível aumenta a resiliência e reduz os erros.
• Capacidade de aproveitar o LookML atual:as tabelas agregadas 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

Confira uma implementação muito simples em um modelo do Looker para demonstrar como a consciência agregada 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 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 detalhada 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 Conhecimento agregado para saber como determinar se as tabelas de agregação são usadas em uma consulta.

Como identificar oportunidades

Para maximizar os benefícios do reconhecimento agregado, identifique onde ele pode desempenhar um papel na otimização ou na geraçã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 usados com frequência e 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, abra este 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 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 Análises que são lentas e têm muitas consultas dos usuários

Outra oportunidade para aumentar a consciência é com as análises detalhadas que são muito consultadas pelos usuários e têm uma resposta de consulta abaixo da média.

Você pode usar a Análise detalhada do histórico de atividades do sistema como ponto de partida para identificar oportunidades de otimização das Análises detalhadas. 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álises detalhadas com dados sobre as Análises detalhadas da sua instância, incluindo Análises detalhadas, Modelo, Contagem de execução de consulta, Contagem de usuários e Tempo de execução médio em segundos:

Na Análise de 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 envios programados)
• 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 de Análise detalhada do histórico de atividade do sistema, as análises detalhadas flights e order_items são candidatos prováveis para implementação de consciência agregada.

Identificar campos usados com frequência 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 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, abra este link de Análise de uso do campo de atividade do sistema em um navegador e substitua "hostname" no URL pelo nome da sua instância do Looker. Substitua os filtros de acordo. Uma Análise detalhada com uma visualização em gráfico de barras vai aparecer indicando 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, 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 agregadas.

Dados concretos como esse são úteis, mas há elementos subjetivos que vão orientar seus critérios de seleção. Por exemplo, analisando os quatro campos anteriores, é possível supor que os usuários geralmente analisam o número de voos programados e o número de voos cancelados e que eles querem dividir esses dados por semana e por transportadora. Este é um exemplo de combinação clara, lógica e real de campos e métricas.

Resumo

As etapas desta página de documentação servem como um guia para encontrar dashboards, análises detalhadas 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 tiver a granularidade de uma semana e você tiver uma tabela de resumo diária, o Looker vai usar a tabela agregada em vez da tabela bruta no nível do carimbo de data/hora. Da mesma forma, se você tiver uma tabela agregada consolidada no nível brand e date e um usuário consultar apenas no nível brand, essa tabela ainda será um candidato a ser usado pelo Looker para a consciência agregada.

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, SIM/NÃO e DATA
• Medidas distintas aproximadas : dialetos que podem usar a funcionalidade HyperLogLog.

Não é possível usar a consciência agregada para 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 medida 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 de agregação não precisa ser uma correspondência exata para ser usada em uma consulta. Você pode atender a muitas consultas de usuários em uma única tabela de agregação e ainda ter 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, a criação de uma única tabela agregada para flights_by_week_and_carrier vai resultar em um uso mais frequente de tabelas agregadas do que duas tabelas agregadas diferentes para flights_by_week e flights_by_carrier.

Confira um exemplo de tabela agregada 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 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ões.

Equilíbrio entre a aplicabilidade e a performance

O exemplo a seguir mostra uma consulta de Análise dos campos "Flights Depart Week", "Flights Details Carrier", "Flights Count" e "Flights Detailed Cancelled Count" da tabela agregada flights_by_week_and_carrier:

A execução dessa consulta na tabela de banco de dados original levou 15,8 segundos e analisou 38 milhões de linhas sem nenhuma mesclagem 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 pivotagem da consulta levou 9,8 segundos.

Na Análise de uso de campos de atividade do sistema, podemos conferir 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 como referência para o 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 foram os seguintes:

Origem	Quando a consulta foi executada	Linhas verificadas
Tabela 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 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 (8s * 2750 = 22.000s). A tabela de agregação levou 17,9 segundos para ser criada.

Analisando esses resultados, vale a pena parar um pouco e avaliar os retornos que podem ser obtidos com:

• Otimizar modelos/análises detalhadas maiores e mais complexos com desempenho "aceitável" e que podem melhorar o desempenho com práticas de modelagem melhores

X

• Usar a consciência agregada para otimizar modelos mais simples que são usados com mais frequência e têm um desempenho ruim

Você vai notar um retorno menor para seus esforços à medida que tenta extrair o máximo da performance do Looker e do seu banco de dados. Você precisa estar sempre ciente das expectativas de desempenho de referência, principalmente dos usuários de negócios, e das limitações impostas pelo banco de dados (como simultaneidade, limites de consulta, custo etc.). Não espere que a consciência agregada supere essas limitações.

Além disso, ao projetar uma tabela de agregação, lembre-se de que ter mais campos resulta em uma tabela de agregação maior e mais lenta. Tabelas maiores podem otimizar mais consultas e, portanto, serem usadas em mais situações, mas não são tão rápidas quanto as 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;;
      }
    }
  }

Isso vai resultar na tabela agregada sendo usada para qualquer combinação de dimensão mostrada e para qualquer uma das medidas incluídas. Assim, essa tabela pode ser usada para responder a muitas consultas diferentes do usuário. No entanto, para usar essa tabela em um SELECT simples de carrier e count, seria necessário verificar 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, adicionar 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 como exemplo a Análise detalhada Voos que identificamos como uma oportunidade de otimização, a melhor estratégia seria criar três tabelas agregadas diferentes:

• 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 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 as tabelas de agregação para as consultas dos usuários.

Persistência

Para que a agregação seja possível, as tabelas de agregação precisam ser mantidas no banco de dados. É recomendável alinhar a regeneração automática dessas tabelas agregadas à sua política de armazenamento em cache usando datagroups. 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, use o parâmetro sql_trigger_value como alternativa. O exemplo a seguir mostra um valor genérico baseado em 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 de período

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

Este diagrama mostra o cronograma 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 aparecer na tabela agregada Pedidos, porque foram recebidos depois que ela foi criada:

No entanto, o Looker pode agrupar dados novos à 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 agrupar dados novos em uma tabela agregada, se um usuário filtrar um período que se sobrepõe ao final da agregação e da tabela de 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 Conhecimento agregado para saber mais detalhes e as condições que precisam ser atendidas para unir dados novos a consultas de tabelas agregadas.

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 usando tabelas agregadas é adequada e eficaz.
2. Projete tabelas agregadas que ofereçam a maior cobertura para consultas comuns do usuário, mas que sejam pequenas o suficiente para reduzir o tamanho dessas consultas.
3. Crie as tabelas agregadas no modelo do Looker, combinando a persistência da tabela com a persistência do cache do recurso "Explorar".