Livros de receitas do Looker: como aproveitar ao máximo as tabelas derivadas no Looker

As tabelas derivadas abrem um mundo de possibilidades analíticas avançadas, mas podem ser difíceis de abordar, implementar e resolver problemas. Este guia contém os casos de uso mais comuns de tabelas derivadas no Looker.

Esta página contém os seguintes exemplos:

Recursos de tabelas derivadas

Para usar esses guias, é preciso ter um conhecimento básico de LookML e tabelas derivadas. Você precisa saber criar visualizações e editar o arquivo do modelo. Se quiser relembrar algum desses tópicos, confira os seguintes recursos:

Criar uma tabela às 3h diariamente

Os dados neste exemplo chegam às 2h todos os dias. Os resultados de uma consulta nesses dados serão os mesmos, seja às 3h ou às 21h. Portanto, faz sentido criar a tabela uma vez por dia e permitir que os usuários extraiam resultados de um cache.

Incluir o datagroup no arquivo de modelo permite reutilizá-lo com várias tabelas e análises. Esse datagroup contém um parâmetro sql_trigger_value que informa ao datagroup quando acionar e recriar a tabela derivada.

Para mais exemplos de expressões de gatilho, consulte a documentação de sql_trigger_value.


## in the model file

datagroup: standard_data_load {
  sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
  max_cache_age: "24 hours"
}

explore: orders {
…

Adicione o parâmetro datagroup_trigger à definição de derived_table no arquivo de visualização e especifique o nome do datagroup que você quer usar. Neste exemplo, o datagroup é standard_data_load.


view: orders {
 derived_table: {
  indexes: ["id"]
  datagroup_trigger: standard_data_load
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

…
}

Como anexar novos dados a uma tabela grande

Uma TDP incremental é uma tabela derivada persistente que o Looker cria adicionando ao final dados novos à tabela, em vez de recriá-la por completo.

O próximo exemplo se baseia no exemplo da tabela orders para mostrar como a tabela é criada de forma incremental. Novos dados de pedidos chegam todos os dias e podem ser anexados à tabela atual quando você adiciona um parâmetro increment_key e um parâmetro increment_offset.


view: orders {
 derived_table: {
    indexes: ["id"]
    increment_key: "created_at"
    increment_offset: 3
    datagroup_trigger: standard_data_load
    distribution_style: all
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;  }

…
}

O valor increment_key está definido como created_at, que é o incremento de tempo em que os dados novos precisam ser consultados e adicionados ao final da TDP neste exemplo.

O valor increment_offset é definido como 3 para especificar o número de períodos anteriores (na granularidade da chave de incremento) que são recriados para considerar os dados que chegam atrasados.

Como usar funções de janela SQL

Alguns dialetos de banco de dados são compatíveis com funções de janela, especialmente para criar números de sequência, chaves primárias, totais acumulados e cumulativos e outros cálculos úteis de várias linhas. Depois que a consulta primária é executada, todas as declarações derived_column são executadas em uma passagem separada.

Se o dialeto do banco de dados for compatível com funções analíticas, use-as na tabela derivada nativa. Crie um parâmetro derived_column com um parâmetro sql que contenha sua função de janela. Ao se referir a valores, use o nome da coluna conforme definido na tabela derivada nativa.

O exemplo a seguir mostra como criar uma tabela derivada nativa que inclui as colunas user_id, order_id e created_time. Em seguida, você usaria uma coluna derivada com uma função de janela SQL ROW_NUMBER() para calcular uma coluna que contém o número de sequência do pedido de um cliente.

view: user_order_sequences {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: order_id {
        field: order_items.order_id
      }
      column: created_time {
        field: order_items.created_time
      }
      derived_column: user_sequence {
        sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
      }
    }
  }
  dimension: order_id {
    hidden: yes
  }
  dimension: user_sequence {
    type: number
  }
}

Como criar colunas derivadas para valores calculados

É possível adicionar parâmetros derived_column para especificar colunas que não existem na Análise do parâmetro explore_source. Cada parâmetro derived_column tem um parâmetro sql que especifica como construir o valor.

O cálculo de sql pode usar qualquer coluna que você tenha especificado com os parâmetros column. As colunas derivadas não podem incluir funções agregadas, mas podem incluir cálculos que podem ser realizados em uma única linha da tabela.

Este exemplo cria uma coluna average_customer_order, que é calculada com base nas colunas lifetime_customer_value e lifetime_number_of_orders na tabela derivada nativa.

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: users.id
      }
      column: lifetime_number_of_orders {
        field: order_items.count
      }
      column: lifetime_customer_value {
        field: order_items.total_profit
      }
      derived_column: average_customer_order {
        sql:  lifetime_customer_value / lifetime_number_of_orders ;;
      }
    }
  }

  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
  dimension: average_customer_order {
    type: number
  }
}

Estratégias de otimização

Como as TDPs são armazenadas no banco de dados, é preciso otimizá-las usando as seguintes estratégias, conforme o dialeto:

Por exemplo, para adicionar persistência, você pode definir a TDP para ser recriada quando o datagroup orders_datagroup for acionado e, em seguida, adicionar índices em customer_id e first_order, como mostrado a seguir:

view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      ...
    }
    datagroup_trigger: orders_datagroup
    indexes: ["customer_id", "first_order"]
  }
}

Se você não adicionar um índice (ou um equivalente para seu dialeto), o Looker vai avisar que você precisa fazer isso para melhorar o desempenho da consulta.

Como usar TDPs para testar otimizações

É possível usar TDPs para testar diferentes indexações, distribuições e outras opções de otimização sem precisar de muito suporte dos desenvolvedores de DBA ou ETL.

Considere um caso em que você tem uma tabela, mas quer testar índices diferentes. O LookML inicial da visualização pode ser assim:

view: customer {
  sql_table_name: warehouse.customer ;;
}

Para testar estratégias de otimização, use o parâmetro indexes para adicionar índices ao LookML, conforme mostrado a seguir:

view: customer {
  # sql_table_name: warehouse.customer
  derived_table: {
    sql: SELECT * FROM warehouse.customer ;;
    persist_for: "8 hours"
    indexes: [customer_id, customer_name, salesperson_id]
  }
}

Consulte a visualização uma vez para gerar a TDP. Em seguida, execute as consultas de teste e compare os resultados. Se os resultados forem satisfatórios, peça para o DBA ou a equipe de ETL adicionar os índices à tabela original.

UNION duas tabelas

É possível executar um operador SQL UNION ou UNION ALL em ambas as tabelas derivadas se o dialeto SQL for compatível. Os operadores UNION e UNION ALL combinam os conjuntos de resultados de duas consultas.

Este exemplo mostra como é uma tabela derivada com base em SQL com um UNION:

view: first_and_second_quarter_sales {
  derived_table: {
    sql:
       SELECT * AS sales_records
       FROM sales_records_first_quarter
       UNION
       SELECT * AS sales_records
       FROM sales_records_second_quarter ;;
   }
}

A instrução UNION no parâmetro sql produz uma tabela derivada que combina os resultados das duas consultas.

A diferença entre UNION e UNION ALL é que UNION ALL não remove linhas duplicadas. Há considerações de desempenho que você precisa ter em mente ao usar UNION em vez de UNION ALL, já que o servidor de banco de dados precisa realizar um trabalho extra para remover as linhas duplicadas.

Como somar uma soma (dimensionalizar uma medida)

Como regra geral no SQL e, por extensão, no Looker, não é possível agrupar uma consulta pelos resultados de uma função de agregação (representada no Looker como medições). Só é possível agrupar por campos não agregados (representados no Looker como dimensões).

Para agrupar por uma agregação (para somar uma soma, por exemplo), é preciso "dimensionalizar" uma medida. Uma maneira de fazer isso é usar uma tabela derivada, que cria uma subconsulta da agregação.

Começando com uma Análise, o Looker pode gerar o LookML para toda ou a maior parte da tabela derivada. Basta criar uma Análise e selecionar todos os campos que você quer incluir na tabela derivada. Em seguida, para gerar o LookML da tabela derivada nativa (ou com base em SQL), siga estas etapas:

  1. Clique no menu de engrenagem da Análise e selecione Acessar o LookML.

  2. Para ver o LookML para criar uma tabela derivada nativa para a Análise, clique na guia Tabela derivada.

  3. Copie o LookML.

Agora que você copiou o LookML gerado, cole-o em um arquivo de visualização seguindo estas etapas:

  1. No Modo de Desenvolvimento, acesse os arquivos do projeto.

  2. Clique no + na parte de cima da lista de arquivos do projeto no ambiente de desenvolvimento integrado do Looker e selecione Criar visualização. Como alternativa, para criar o arquivo dentro da pasta, clique no menu de uma pasta e selecione Criar visualização.

  3. Defina um nome significativo para a visualização.

  4. Se quiser, mude os nomes das colunas, especifique as colunas derivadas e adicione filtros.

Tabelas de rollup com reconhecimento de agregação

No Looker, é comum encontrar conjuntos de dados ou tabelas muito grandes que, para terem um bom desempenho, exigem tabelas de agregação ou rollups.

Com o reconhecimento de agregação do Looker, é possível pré-construir tabelas de agregação para vários níveis de granularidade, dimensionalidade e agregação, além de informar ao Looker como usá-las nas Análises existentes. As consultas vão usar essas tabelas de rollup quando o Looker considerar apropriado, sem qualquer entrada do usuário. Isso vai reduzir o tamanho da consulta, diminuir o tempo de espera e melhorar a experiência do usuário.

Veja a seguir uma implementação muito simples em um modelo do Looker para demonstrar como o reconhecimento de agregados pode ser leve. Considerando uma tabela hipotética de voos no banco de dados com uma linha para cada voo registrado pela FAA, é possível modelar essa tabela no Looker com uma visualização e uma Análise próprias. Veja a seguir o LookML de uma tabela de agregação que você pode 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, um usuário pode consultar a Análise flights, e o Looker vai usar automaticamente a tabela de agregação para responder às consultas. Para um tutorial mais detalhado sobre o reconhecimento agregado, acesse o Tutorial de reconhecimento agregado.