As tabelas derivadas abrem um mundo de possibilidades analíticas avançadas, mas podem ser difíceis de abordar, implementar e solucionar problemas. Este manual contém os casos de uso mais conhecidos de tabelas derivadas no Looker.
Esta página contém os seguintes exemplos:
- Como criar uma mesa às 3h todos os dias
- Como anexar novos dados a uma tabela grande
- Como usar funções de janela SQL
- Como criar colunas derivadas para valores calculados
- Estratégias de otimização
- Como usar TDPs para testar otimizações
UNIONduas tabelas- Fazer uma soma de uma soma (dimensionar uma medida)
- Tabelas de visualização completa com reconhecimento agregado
Recursos de tabelas derivadas
Nestes manuais, presumimos que você tenha uma compreensão introdutória do LookML e das tabelas derivadas. Você precisa saber criar visualizações e editar o arquivo de modelo. Se você quiser se atualizar sobre algum desses tópicos, confira os seguintes recursos:
- Tabelas derivadas
- Termos e conceitos do LookML
- Como criar tabelas derivadas nativas
- Referência de parâmetro
derived_table - Como armazenar consultas em cache e recriar TDPs com grupos de dados
Construindo uma mesa às 3h todos os dias
Os dados neste exemplo chegam às 2h todos os dias. Os resultados de uma consulta nesses dados serão os mesmos se ela for executada às 3h ou às 21h. Portanto, faz sentido criar a tabela uma vez por dia e permitir que os usuários extraiam os resultados de um cache.
Incluir seu 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 quando acionar e recriar a tabela derivada.
Para mais exemplos de expressões de gatilho, consulte a documentação 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 grupo de dados 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 anexando dados novos à tabela, em vez de recriar a tabela inteira.
O próximo exemplo se baseia no exemplo da tabela orders para mostrar como a tabela é criada de maneira 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 é definido como created_at, que é o incremento de tempo em que novos dados precisam ser consultados e anexados à 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 dados de chegada tardia.
Como usar funções de janela SQL
Alguns dialetos de banco de dados suportam funções de janela, especialmente para criar números de sequência, chaves primárias, totais em execução e cumulativos e outros cálculos úteis de várias linhas. Depois que a consulta principal é executada, todas as declarações derived_column são executadas em uma passagem separada.
Se o dialeto do seu banco de dados for compatível com funções de janela, você poderá usá-los em sua tabela derivada nativa. Crie um parâmetro derived_column com um parâmetro sql que contenha a função de janela. Ao se referir a valores, você deve usar o nome da coluna conforme definido em sua 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 especificada com parâmetros column. As colunas derivadas não podem incluir funções de agregação, mas podem incluir cálculos que podem ser executados em uma única linha da tabela.
Neste exemplo, criamos uma coluna average_customer_order, que é calculada a partir das colunas lifetime_customer_value e lifetime_number_of_orders da 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 em seu banco de dados, você deve otimizá-las usando as seguintes estratégias, conforme compatível com seu dialeto:
Por exemplo, para adicionar persistência, defina a TDP para ser recriada quando o grupo de dados orders_datagroup for acionado e, em seguida, poderá adicionar índices em customer_id e first_order, conforme 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 é preciso fazer isso para melhorar o desempenho da consulta.
Como usar TDPs para testar otimizações
É possível usar as TDPs para testar diferentes indexação, distribuições e outras opções de otimização sem precisar de muito suporte de seus desenvolvedores DBA ou ETL.
Considere um caso em que você tem uma tabela, mas quer testar índices diferentes. Seu LookML inicial para a visualização pode ser semelhante ao seguinte:
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 suas consultas de teste e compare os resultados. Se os resultados forem favoráveis, peça à equipe de DBA ou ETL para adicionar os índices à tabela original.
UNION duas tabelas
Você pode executar um operador SQL UNION ou UNION ALL nas duas tabelas derivadas se o dialeto SQL oferecer suporte a isso. Os operadores UNION e UNION ALL combinam os conjuntos de resultados de duas consultas.
Este exemplo mostra como uma tabela derivada baseada em SQL fica 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. É preciso considerar o desempenho ao usar UNION em comparação com UNION ALL, já que o servidor de banco de dados precisa fazer um trabalho extra para remover as linhas duplicadas.
Como somar uma soma (dimensionar uma medida)
Como regra geral no SQL (e, consequentemente, no Looker), não é possível agrupar uma consulta pelos resultados de uma função agregada (representada no Looker como medidas). Só é possível agrupar por campos não agregados (representados no Looker como dimensões).
Para agrupar por um agregado (para obter a soma de uma soma, por exemplo), você precisa "dimensionar" uma medida. Uma maneira de fazer isso é usando uma tabela derivada, que cria efetivamente uma subconsulta do agregado.
Começando com uma Análise, o Looker pode gerar LookML para todas ou a maioria das tabelas derivadas. Basta criar um Explore e selecionar todos os campos que você quer incluir na tabela derivada. Em seguida, para gerar o LookML nativo (ou baseado em SQL) da tabela derivada, siga estas etapas:
Clique no menu de engrenagem da Análise e selecione Acessar LookML.
Para acessar o LookML de criação de uma tabela derivada nativa para a Análise, clique na guia Tabela derivada.
Copie o LookML.
Agora que você copiou o LookML gerado, cole-o em um arquivo de visualização seguindo estas etapas:
No Modo de desenvolvimento, navegue até os arquivos do projeto.
Clique em + na parte de cima da lista de arquivos de 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 da pasta e selecione Criar visualização.
Defina o nome da visualização para algo significativo.
Opcionalmente, altere os nomes das colunas, especifique colunas derivadas e adicione filtros.
Tabelas de visualização completa com reconhecimento agregado
No Looker, é possível encontrar conjuntos de dados ou tabelas muito grandes que, para ter um bom desempenho, exigem tabelas de agregação ou visualizações completas.
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 ao Looker como usá-las nas Análises atuais. As consultas vão usar essas tabelas quando o Looker considerar apropriado, sem qualquer entrada do usuário. Isso reduzirá o tamanho da consulta, reduzirá os tempos de espera e melhorará a experiência do usuário.
Confira a seguir uma implementação muito simples em um modelo do Looker para demonstrar como o reconhecimento agregado pode ser leve. Com uma tabela de voos hipotéticos no banco de dados com uma linha para cada voo registrado na FAA, é possível modelar essa tabela no Looker com visualização e Análise próprias. Confira a seguir o LookML de uma tabela de agregação que pode ser definida para 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, um usuário pode consultar a Análise do flights, e o Looker a usa automaticamente para responder. Para conferir mais detalhes sobre o reconhecimento agregado, acesse o tutorial de reconhecimento agregado.