As tabelas derivadas abrem um mundo de possibilidades analíticas avançadas, mas podem ser difíceis de abordar, implementar e resolver problemas. Este livro de receitas contém os exemplos de utilização mais populares de tabelas derivadas no Looker.
Esta página contém os seguintes exemplos:
- Criar uma tabela às 03:00 todos os dias
- Anexar novos dados a uma tabela grande
- Usar funções de janela SQL
- Criar colunas derivadas para valores calculados
- Estratégias de otimização
- Usar PDTs para testar otimizações
UNIONduas tabelas- Fazer a soma de uma soma (adicionar dimensões a uma medida)
- Agrupe tabelas com notoriedade agregada
Recursos de tabelas derivadas
Estes livros de receitas pressupõem que tem uma compreensão introdutória do LookML e das tabelas derivadas. Deve conseguir criar vistas e editar o ficheiro do modelo. Se quiser relembrar algum destes tópicos, consulte os seguintes recursos:
- Tabelas derivadas
- Termos e conceitos do LookML
- Criar tabelas derivadas nativas
- Referência do parâmetro
derived_table - Colocar em cache as consultas e reconstruir PDTs com grupos de dados
Criar uma tabela às 03:00 todos os dias
Neste exemplo, os dados chegam todos os dias às 02:00. Os resultados de uma consulta sobre estes dados são os mesmos, quer seja executada às 03:00 ou às 21:00. Por conseguinte, faz sentido criar a tabela uma vez por dia e permitir que os utilizadores extraiam resultados de uma cache.
A inclusão do grupo de dados no ficheiro do modelo permite reutilizá-lo com várias tabelas e explorações. Este grupo de dados contém um parâmetro sql_trigger_value que indica ao grupo de dados quando acionar e reconstruir a tabela derivada.
Para ver mais exemplos de expressões de acionadores, 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 derived_table no ficheiro de visualização e especifique o nome do grupo de dados que quer usar. Neste exemplo, o grupo de dados é 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 ;;
}
…
}
Anexar novos dados a uma tabela grande
Uma PDT incremental é uma tabela derivada persistente que o Looker cria anexando dados atualizados à tabela, em vez de reconstruir a tabela na íntegra.
O exemplo seguinte baseia-se no exemplo da tabela orders para mostrar como a tabela é criada de forma incremental. Os novos dados de encomendas são recebidos todos os dias e podem ser anexados à tabela existente quando 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 para o qual os dados atualizados devem ser consultados e anexados à PDT neste exemplo.
O valor increment_offset está definido como 3 para especificar o número de períodos anteriores (na granularidade da chave de incremento) que são reconstruídos para ter em conta os dados recebidos tardiamente.
Usar funções de janela SQL
Alguns dialetos de base de dados suportam funções de janela, especialmente para criar números de sequência, chaves primárias, totais contínuos e cumulativos, e outros cálculos úteis de várias linhas. Após a execução da consulta principal, as declarações derived_column são executadas numa passagem separada.
Se o dialeto da base de dados suportar funções de janela, pode usá-las na tabela derivada nativa. Crie um parâmetro derived_column com um parâmetro sql que contenha a função de janela. Quando se refere a valores, deve usar o nome da coluna conforme definido na tabela derivada nativa.
O exemplo seguinte mostra como criar uma tabela derivada nativa que inclua as colunas user_id, order_id e created_time. Em seguida, usaria uma coluna derivada com uma função de janela SQL ROW_NUMBER() para calcular uma coluna que contenha o número de sequência da encomenda 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
}
}
Criar colunas derivadas para valores calculados
Pode adicionar parâmetros derived_column para especificar colunas que não existem na análise detalhada do parâmetro explore_source. Cada parâmetro derived_column tem um parâmetro sql que especifica como construir o valor.
O cálculo sql pode usar quaisquer colunas que tenha especificado através de column parâmetros. As colunas derivadas não podem incluir funções de agregação, mas podem incluir cálculos que podem ser realizados numa ú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
Uma vez que as PDTs são armazenadas na sua base de dados, deve otimizá-las através das seguintes estratégias, conforme suportado pelo seu dialeto:
Por exemplo, para adicionar persistência, pode definir o PDT para reconstruir quando o grupo de dados orders_datagroup for acionado e, em seguida, pode adicionar índices em customer_id e first_order, conforme mostrado abaixo:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Se não adicionar um índice (ou um equivalente para o seu dialeto), o Looker avisa que o deve fazer para melhorar o desempenho das consultas.
Usar PDTs para testar otimizações
Pode usar PDTs para testar diferentes opções de indexação, distribuições e outras opções de otimização sem precisar de muito apoio técnico do seu DBA ou programadores de ETL.
Considere um caso em que tem uma tabela, mas quer testar diferentes índices. O LookML inicial da vista pode ter o seguinte aspeto:
view: customer {
sql_table_name: warehouse.customer ;;
}
Para testar estratégias de otimização, pode usar 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]
}
}
Consultar a vista uma vez para gerar o PDT. Em seguida, execute as consultas de teste e compare os resultados. Se os resultados forem favoráveis, pode pedir ao DBA ou à equipa de ETL para adicionar os índices à tabela original.
UNION duas tabelas
Pode executar um operador SQL UNION ou UNION ALL em ambas as tabelas derivadas se o seu dialeto de SQL o suportar. Os operadores UNION e UNION ALL combinam os conjuntos de resultados de duas consultas.
Este exemplo mostra o aspeto 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 declaração UNION no parâmetro sql produz uma tabela derivada que combina os resultados de ambas as consultas.
A diferença entre UNION e UNION ALL é que UNION ALL não remove linhas duplicadas. Existem considerações de desempenho a ter em conta quando usa UNION em comparação com UNION ALL, uma vez que o servidor da base de dados tem de fazer trabalho adicional para remover as linhas duplicadas.
Tomar a soma de uma soma (dimensionalizar uma medida)
Como regra geral no SQL e, por extensão, no Looker, não pode agrupar uma consulta pelos resultados de uma função de agregação (representada no Looker como medidas). Só pode agrupar por campos não agregados (representados no Looker como dimensões).
Para agrupar por um valor agregado (para obter a soma de uma soma, por exemplo), tem de "dimensionalizar" uma medida. Uma forma de o fazer é usar uma tabela derivada, que cria efetivamente uma subconsulta do agregado.
Começando com uma exploração, o Looker pode gerar LookML para toda ou a maioria da sua tabela derivada. Basta criar uma exploração e selecionar todos os campos que quer incluir na sua tabela derivada. Depois, para gerar o LookML da tabela derivada nativa (ou baseada em SQL), siga estes passos:
Clique no menu de roda dentada do Explore e selecione Obter LookML.
Para ver o LookML para criar uma tabela derivada nativa para a exploração, clique no separador Tabela derivada.
Copie o LookML.
Agora que copiou o LookML gerado, cole-o num ficheiro de vista seguindo estes passos:
No modo de programação, navegue para os ficheiros do projeto.
Clique em + na parte superior da lista de ficheiros do projeto no IDE do Looker e selecione Criar visualização de propriedade. Em alternativa, para criar o ficheiro na pasta, clique no menu de uma pasta e selecione Criar vista.
Defina o nome da vista para algo significativo.
Opcionalmente, altere os nomes das colunas, especifique colunas derivadas e adicione filtros.
Tabelas de resumo com conhecimento agregado
No Looker, pode encontrar frequentemente tabelas ou conjuntos de dados muito grandes que, para terem um bom desempenho, requerem tabelas de agregação ou resumos.
Com a notoriedade agregada do Looker, pode pré-construir tabelas agregadas a vários níveis de detalhe, dimensionalidade e agregação, e pode informar o Looker sobre como as usar em análises detalhadas existentes. Em seguida, as consultas usam estas tabelas de agregação quando o Looker o considera adequado, sem qualquer introdução do utilizador. Isto reduz o tamanho das consultas, os tempos de espera e melhora a experiência do utilizador.
O exemplo seguinte mostra uma implementação muito simples num modelo do Looker para demonstrar como a agregação simples pode ser. Dada uma tabela de voos hipotética na base de dados com uma linha para cada voo registado através da FAA, pode modelar esta tabela no Looker com a sua própria vista e explorar. Segue-se o LookML de uma tabela agregada que pode definir para a exploração:
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 esta tabela agregada, um utilizador pode consultar a flightsexploração, e o Looker usa automaticamente a tabela agregada para responder a consultas. Para um passo a passo mais detalhado da notoriedade agregada, visite o tutorial de notoriedade agregada.