As tabelas derivadas abrem um mundo de possibilidades de análise avançada, mas podem ser difíceis de abordar, implementar e resolver problemas. Este livro de receitas contém os casos de uso mais conhecidos de tabelas derivadas no Looker.
Esta página contém os seguintes exemplos:
- Criar uma tabela às 3h da manhã todos os dias
- Como anexar novos dados a uma tabela grande
- Como usar as funções de janela do SQL
- Criar colunas derivadas para valores calculados
- Estratégias de otimização
- Como usar TDPs para testar otimizações
UNIONduas tabelas- Como fazer a soma de uma soma (dimensionalizar uma medida)
- Tabelas de resumo com conhecimento agregado
Recursos de tabelas derivadas
Esses guias assumem 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 relembrar algum desses tópicos, confira estes recursos:
- Tabelas derivadas
- Termos e conceitos do LookML
- Como criar tabelas derivadas nativas
- Referência do parâmetro
derived_table - Armazenamento de consultas em cache e recriação de PDTs com grupos de dados
Criar uma tabela às 3h diariamente
Os dados neste exemplo são recebidos às 2h todos os dias. Os resultados de uma consulta sobre esses dados serão os mesmos, seja executada à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.
Ao incluir seu grupo de dados no arquivo de modelo, você pode reutilizá-lo com várias tabelas e análises detalhadas. 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 a ela, em vez de recriar a tabela por completo.
O próximo exemplo se baseia na tabela orders para mostrar como ela é criada de forma incremental. Os novos dados de pedidos chegam todos os dias e podem ser anexados à tabela atual quando você adiciona um parâmetro 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 que os dados novos sejam consultados e anexados ao PDT neste exemplo.
O valor increment_offset é definido como 3 para especificar o número de períodos de tempo anteriores (na granularidade da chave de incremento) que são recriados para considerar os dados que chegam com atraso.
Como usar funções de janela do SQL
Alguns dialetos de banco de dados oferecem suporte a funções de janela, principalmente para criar números de sequência, chaves primárias, totais correntes e cumulativos e outros cálculos úteis com 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 banco de dados oferecer suporte a funções de janela, elas poderão 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, use 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
}
}
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 sql pode usar qualquer coluna especificada usando 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 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, você precisa otimizar as TDPs usando as seguintes estratégias, conforme o suporte do seu dialeto:
Por exemplo, para adicionar persistência, defina o PDT para ser recriado quando o grupo de dados orders_datagroup for acionado e 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 que isso precisa ser feito para melhorar a performance da consulta.
Como usar PDTs para testar otimizações
É possível usar PDTs 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 parecido com 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 nas duas tabelas derivadas se o dialeto SQL oferecer suporte a ele. 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 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. 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.
Como fazer a soma de 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 medidas). Só é possível agrupar por campos não agregados (representados no Looker como dimensões).
Para agrupar por um agregado (para fazer a soma de uma soma, por exemplo), é necessário "dimensionar" uma medida. Uma maneira de fazer isso é usar uma tabela derivada, que cria uma subconsulta do agregado.
Começando com uma análise detalhada, o Looker pode gerar o LookML para todas ou a maioria das suas tabelas derivadas. Basta criar uma análise detalhada 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 da Análise e selecione Instalar o LookML.
Para conferir o LookML para criar uma tabela derivada nativa para a Análise detalhada, 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 no + na parte de cima da lista de arquivos do projeto no ambiente de desenvolvimento integrado do Looker e selecione Create View. 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.
Opcionalmente, mude os nomes das colunas, especifique as colunas derivadas e adicione filtros.
Tabelas de resumo com informações agregadas
No Looker, você pode encontrar conjuntos de dados ou tabelas muito grandes que, para serem eficientes, exigem tabelas de agregação ou resumos.
Com a consciência agregada do Looker, você pode pré-construir tabelas agregadas 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. 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.
Confira a seguir uma implementação muito simples em um modelo do Looker para demonstrar como a consciência agregada leve pode ser. 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 a própria visualização e análise detalhada. 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, um usuário pode consultar a Análise detalhada flights, e o Looker vai usar automaticamente a tabela de agregação para responder às consultas. Para um tutorial mais detalhado sobre a consciência agregada, acesse o tutorial sobre consciência agregada.