Uma tabela derivada é uma consulta cujos resultados são usados como se a tabela derivada fosse uma tabela física no banco de dados. Uma tabela derivada nativa é baseada em uma consulta que você define usando termos do LookML. Isso é diferente de uma tabela derivada baseada em SQL, que é baseada em uma consulta definida com termos SQL. Comparadas com as tabelas derivadas baseadas em SQL, as tabelas nativas são muito mais fáceis de ler e entender ao modelar seus dados. Consulte a seção Tabelas derivadas nativas e tabelas derivadas baseadas em SQL da página de documentação Tabelas derivadas no Looker para mais informações.
As tabelas derivadas nativas e baseadas em SQL são definidas no LookML usando o parâmetro derived_table no nível da visualização. No entanto, com tabelas derivadas nativas, não é necessário criar uma consulta SQL. Em vez disso, use o parâmetro explore_source para especificar a análise em que a tabela derivada será baseada, as colunas e outras características.
Também é possível fazer com que o Looker crie o LookML da tabela derivada a partir de uma consulta do SQL Runner, conforme descrito na página de documentação Usar o SQL Runner para criar tabelas derivadas.
Como usar uma análise para começar a definir tabelas derivadas nativas
Começando com uma análise detalhada, o Looker pode gerar 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, siga estas etapas:
Selecione o menu de engrenagem Abrir ações e selecione Obter o LookML.
Clique na guia Tabela derivada para conferir o LookML para criar uma tabela derivada nativa para a Análise detalhada.
Copie o LookML.
Agora que você copiou o LookML gerado, cole-o em um arquivo de visualização:
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 Criar visualização. Como alternativa, clique no menu de uma pasta e selecione Criar visualização para criar o arquivo dentro da pasta.
Defina o nome da visualização como algo significativo.
Opcionalmente, mude os nomes das colunas, especifique as colunas derivadas e adicione filtros.
Quando você usa uma medida de
type: countem uma Análise detalhada, a visualização rotula os valores resultantes com o nome da visualização em vez da palavra Contagem. Para evitar confusão, use o plural no nome da visualização. Para mudar o nome da visualização, selecione Mostrar o nome completo do campo em Série nas configurações de visualização ou use o parâmetroview_labelcom uma versão pluralizada do nome da visualização.
Como definir uma tabela derivada nativa no LookML
Seja usando tabelas derivadas declaradas em SQL ou o LookML nativo, a saída de uma consulta derived_table é uma tabela com um conjunto de colunas. Quando a tabela derivada é expressa em SQL, os nomes das colunas de saída são implícitos pela consulta SQL. Por exemplo, a consulta SQL a seguir terá as colunas de saída user_id, lifetime_number_of_orders e lifetime_customer_value:
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
No Looker, uma consulta é baseada em uma Análise, inclui campos de medida e dimensão, adiciona os filtros aplicáveis e também pode especificar uma ordem de classificação. Uma tabela derivada nativa contém todos esses elementos, além dos nomes de saída das colunas.
O exemplo simples a seguir produz uma tabela derivada com três colunas: user_id, lifetime_customer_value e lifetime_number_of_orders. Não é necessário escrever a consulta manualmente em SQL. O Looker cria a consulta para você usando a order_items de análise detalhada especificada e alguns dos campos dessa análise (order_items.user_id, order_items.total_revenue e order_items.order_count).
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
Usar instruções include para ativar campos de referência
No arquivo de visualização da tabela derivada nativa, use o parâmetro explore_source para apontar para uma Análise detalhada e definir as colunas e outras características da tabela derivada nativa.
No arquivo de visualização da tabela derivada nativa, não é necessário usar o parâmetro include para apontar para o arquivo que contém a definição da Análise detalhada. Se você não tiver a instrução include, o ambiente de desenvolvimento integrado do Looker não vai sugerir automaticamente nomes de campos nem verificar suas referências de campo ao criar a tabela derivada nativa. Em vez disso, use o validador da LookML para verificar os campos que você está referenciando na tabela derivada nativa.
No entanto, se você quiser ativar a sugestão automática e a verificação imediata de campo no ambiente de desenvolvimento integrado do Looker ou se tiver um projeto complexo do LookML com várias análises com o mesmo nome ou com potencial de referências circulares, use o parâmetro include para apontar para o local da definição da análise.
As análises detalhadas geralmente são definidas em um arquivo de modelo, mas, no caso de tabelas derivadas nativas, é mais fácil criar um arquivo separado para a análise detalhada. Os arquivos de Análises do LookML têm a extensão .explore.lkml, conforme descrito na documentação Criar arquivos de Análises. Dessa forma, no arquivo de visualização de tabela derivada nativa, é possível incluir um único arquivo de Análise detalhada e não o arquivo de modelo inteiro.
Se você quiser criar um arquivo de análise separado e usar o parâmetro include para apontar para o arquivo de análise no arquivo de visualização da tabela derivada nativa, verifique se os arquivos do LookML atendem aos seguintes requisitos:
- O arquivo de visualização da tabela derivada nativa precisa incluir o arquivo da Análise. Por exemplo:
include: "/explores/order_items.explore.lkml"
- O arquivo da Análise precisa incluir os arquivos de visualização necessários. Por exemplo:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- O modelo precisa incluir o arquivo da Análise. Por exemplo:
include: "/explores/order_items.explore.lkml"
Como definir colunas de tabelas derivadas nativas
Conforme mostrado no exemplo anterior, use column para especificar as colunas de saída da tabela derivada.
Como especificar os nomes das colunas
Na coluna user_id, o nome da coluna corresponde ao nome do campo especificado na Análise detalhada original.
Muitas vezes, você vai querer um nome de coluna diferente na tabela de saída do que o nome dos campos na Análise detalhada original. O exemplo anterior produziu um cálculo do valor da vida útil por usuário usando a Análise order_items. Na tabela de saída, total_revenue é o lifetime_customer_value de um cliente.
A declaração column aceita um nome de saída diferente do campo de entrada. Por exemplo, o código a seguir instrui o Looker a "criar uma coluna de saída chamada lifetime_value do campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomes de colunas implícitos
Se o parâmetro field for omitido de uma declaração de coluna, ele será considerado <explore_name>.<field_name>. Por exemplo, se você especificou explore_source: order_items,
column: user_id {
field: order_items.user_id
}
é equivalente a
column: user_id {}
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 todas as colunas especificadas 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.
O exemplo a seguir produz a mesma tabela derivada do exemplo anterior, mas adiciona uma coluna average_customer_order calculada, 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: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
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 desejada. Ao se referir a valores, use o nome da coluna conforme definido na tabela derivada nativa.
O exemplo a seguir cria uma tabela derivada nativa que inclui as colunas user_id, order_id e created_time. Em seguida, usando uma coluna derivada com uma função de janela SQL ROW_NUMBER(), ele calcula 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 adicionar filtros a uma tabela derivada nativa
Suponha que você queira criar uma tabela derivada do valor de um cliente nos últimos 90 dias. Você quer fazer os mesmos cálculos que fez no exemplo anterior, mas só quer incluir as compras dos últimos 90 dias.
Basta adicionar um filtro ao derived_table que filtra transações nos últimos 90 dias. O parâmetro filters de uma tabela derivada usa a mesma sintaxe usada para criar uma medida filtrada.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Os filtros serão adicionados à cláusula WHERE quando o Looker gravar o SQL para a tabela derivada.
Além disso, é possível usar o subatributo dev_filters de explore_source com uma tabela derivada nativa. O parâmetro dev_filters permite especificar filtros que o Looker aplica apenas a versões de desenvolvimento da tabela derivada. Isso significa que você pode criar versões menores e filtradas da tabela para iterar e testar sem esperar que a tabela completa seja criada após cada alteração.
O parâmetro dev_filters atua em conjunto com o parâmetro filters para que todos os filtros sejam aplicados à versão de desenvolvimento da tabela. Se dev_filters e filters especificarem filtros para a mesma coluna, dev_filters terá precedência para a versão de desenvolvimento da tabela.
Consulte Como trabalhar mais rápido no modo de desenvolvimento para mais informações.
Como usar filtros com modelos
Use bind_filters para incluir filtros com modelos:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Isso é essencialmente o mesmo que usar o código abaixo em um bloco sql:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
O to_field é o campo em que o filtro é aplicado. O to_field precisa ser um campo da explore_source.
O from_field especifica o campo de onde o filtro será extraído, se houver um filtro no momento da execução.
No exemplo de bind_filters anterior, o Looker vai usar qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date e aplicar o filtro ao campo users.created_date.
Também é possível usar o subatributo bind_all_filters de explore_source para transmitir todos os filtros de execução de uma Análise a uma subconsulta de tabela derivada nativa. Consulte a página de documentação do parâmetro explore_source para mais informações.
Como classificar e limitar tabelas derivadas nativas
Também é possível classificar e limitar as tabelas derivadas, se necessário:
sorts: [order_items.count: desc]
limit: 10
Lembre-se de que uma Análise detalhada pode mostrar as linhas em uma ordem diferente da classificação.
Como converter tabelas derivadas nativas em fusos horários diferentes
É possível especificar o fuso horário da tabela derivada nativa usando o subparâmetro timezone:
timezone: "America/Los_Angeles"
Quando você usa o subparâmetro timezone, todos os dados baseados em tempo na tabela derivada nativa são convertidos para o fuso horário especificado. Consulte a página de documentação Valores de timezone para conferir uma lista dos fusos horários aceitos.
Se você não especificar um fuso horário na definição da tabela derivada nativa, ela não vai realizar a conversão de fuso horário em dados baseados em tempo. Em vez disso, os dados baseados em tempo vão usar o fuso horário do banco de dados.
Se a tabela derivada nativa não for persistente, defina o valor do fuso horário como "query_timezone" para usar automaticamente o fuso horário da consulta em execução.