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 comuns de tabelas derivadas no Looker.
Esta página contém os seguintes exemplos:
- Criar uma tabela à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 a soma de uma soma (dimensionar uma medida)
- Tabelas de visualização completa com reconhecimento agregado
Recursos de tabela derivados
Nestes manuais, presume-se que você tenha uma compreensão introdutória do LookML e das tabelas derivadas. Você precisa se sentir confortável em criar visualizações e editar o arquivo de modelo. Se você quiser se atualizar sobre qualquer um desses tópicos, confira os recursos a seguir:
- Tabelas derivadas
- Termos e conceitos do LookML
- Como criar tabelas derivadas nativas
- Referência do parâmetro
derived_table - Como armazenar consultas em cache e recriar TDPs com grupos de dados (em inglês)
Montando 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, quer ela seja realizada à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.
Inclua o grupo de dados no arquivo de modelo para reutilizar com várias tabelas e Análises. Esse grupo de dados contém um parâmetro sql_trigger_value que informa quando acionar e reconstruir a tabela derivada.
Para mais exemplos de expressões de acionadores, consulte a documentação do 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 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 PDT incremental é uma tabela derivada persistente que o Looker cria anexando dados novos à tabela, em vez de recriar a tabela inteira.
O próximo exemplo usa o 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 ao adicionar os parâmetros increment_key e 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 para o qual os dados novos precisam ser consultados e anexados à PDT 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 de chegada tardia.
Como usar as funções de janela SQL
Alguns dialetos de banco de dados oferecem suporte a funções de janela, especialmente para criar números sequenciais, chaves primárias, totais cumulativos e em execução 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.
Caso seu dialeto de banco de dados seja compatível com funções de janela, elas podem ser usadas na 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, 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 criar o valor.
O cálculo de sql pode usar qualquer coluna especificada com os parâmetros column. As colunas derivadas não podem incluir funções de agregação, 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 a partir das 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 seu banco de dados, otimize-as usando as seguintes estratégias, conforme compatível com seu dialeto:
Por exemplo, para adicionar persistência, defina a PDT para ser recriada quando o grupo de dados orders_datagroup for acionado e, em seguida, adicione í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 você para melhorar o desempenho da consulta.
Como usar TDPs para testar otimizações
É possível usar as PDTs para testar diferentes indexações, distribuições e outras opções de otimização sem precisar de um grande volume de 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 semelhante a este:
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 o PDT. Em seguida, execute as consultas de teste e compare os resultados. Se os resultados forem favoráveis, peça para a equipe de DBA ou ETL adicionar os índices à tabela original.
UNION duas tabelas
É possível executar um operador SQL UNION ou UNION ALL em ambas as tabelas derivadas, caso seu dialeto SQL seja compatível. Os operadores UNION e UNION ALL combinam os conjuntos de resultados de duas consultas.
Este exemplo mostra a aparência de uma tabela derivada baseada 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 a serem consideradas ao usar UNION em vez de UNION ALL, já que o servidor de banco de dados precisa fazer mais trabalho para remover as linhas duplicadas.
Fazer a soma de uma soma (dimensionar 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 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 tomar a soma de uma soma, por exemplo), é preciso "dimensionalizar" de uma medida. Uma forma de fazer isso é usar uma tabela derivada, que cria efetivamente uma subconsulta da agregação.
Começando com uma Análise, o Looker pode gerar o LookML para toda ou a maior parte da sua 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 baseada em SQL), siga estas etapas:
Clique no menu de engrenagem do recurso Explorar e selecione Instalar o LookML.
Para ver o LookML a fim de criar 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 de uma pasta e selecione Criar visualização.
Defina o nome da visualização como algo significativo.
Também é possível mudar os nomes das colunas, especificar colunas derivadas e adicionar filtros.
Tabelas de visualização completa com reconhecimento agregado
No Looker, você pode encontrar conjuntos de dados ou tabelas muito grandes que, para ter um bom desempenho, exigem tabelas de agregação ou agrupamentos.
Com o reconhecimento agregado do Looker, você pode 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 eles podem ser usados 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.
Confira a seguir uma implementação muito simples em um modelo do Looker para demonstrar como o reconhecimento agregado leve pode ser. Considerando uma tabela de voos hipotéticos no banco de dados com uma linha para cada voo registrado pela FAA, é possível modelar essa tabela no Looker com uma visualização própria e uma Análise. Confira a seguir o LookML de uma tabela de agregação que pode ser definida 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, que será usada automaticamente pelo Looker para responder a consultas. Para instruções mais detalhadas sobre o reconhecimento agregado, acesse o Tutorial de reconhecimento agregado.