Uma tabela derivada é uma consulta com resultados 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 com base em SQL, que se baseia em uma consulta definida com termos SQL. Em comparação com as tabelas derivadas baseadas em SQL, as tabelas derivadas nativas são muito mais fáceis de ler e entender enquanto você modela seus dados. Para mais informações, consulte a seção Tabelas derivadas nativas e tabelas derivadas baseadas em SQL da página de documentação Tabelas derivadas no Looker.
Tanto as tabelas derivadas nativas quanto as 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 na qual se baseará a tabela derivada, as colunas desejadas e outras características desejadas.
Também é possível fazer com que o Looker crie a tabela derivada do LookML de uma consulta do SQL Runner, conforme descrito na página de documentação Como usar o SQL Runner para criar tabelas derivadas.
Usar uma Análise para começar a definir as tabelas derivadas nativas
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 a tabela derivada nativa do LookML, siga estas etapas:
Selecione o menu de engrenagem Explore Actions e selecione Get LookML.
Clique na guia Derived Table para consultar o LookML de como criar uma tabela derivada nativa para a Análise.
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 em + na parte de cima da lista de arquivos de projeto no ambiente de desenvolvimento integrado do Looker e selecione Criar visualização. Se preferir, você pode clicar no menu de uma pasta e selecionar Criar visualização no menu para criar o arquivo dentro da pasta.
Defina o nome da visualização para algo significativo.
Opcionalmente, altere os nomes das colunas, especifique colunas derivadas e adicione filtros.
Quando você usa uma medida de
type: countem uma Análise, os valores resultantes são rotulados com o nome da visualização em vez da palavra Contagem. Para evitar confusão, coloque o nome da visualização em plural. É possível mudar o nome da visualização selecionando Mostrar nome completo do campo em Série nas configurações de visualização ou usando o parâmetroview_labelcom uma versão plural do nome da visualização.
Como definir uma tabela derivada nativa no LookML
Quer você use tabelas derivadas declaradas em SQL ou em LookML nativo, a saída de uma consulta de derived_table será 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 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. Você não precisa escrever a consulta em SQL manualmente. Em vez disso, o Looker a cria usando o order_items especificado da Análise e alguns dos campos dela (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
}
}
Como 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 e definir as colunas e outras características desejadas para a tabela derivada nativa. Como você está apontando para uma Análise no arquivo de visualização da tabela derivada nativa, também é necessário incluir o arquivo que contém a definição dela. As Análises geralmente são definidas em um arquivo de modelo. No entanto, no caso de tabelas derivadas nativas, é mais limpo criar um arquivo separado para a Análise usando a extensão .explore.lkml, conforme descrito na documentação Como criar arquivos do Explore. Dessa forma, no arquivo nativo derivado de visualização em tabela, é possível incluir um único arquivo do Explore, e não o arquivo de modelo inteiro. Nesse caso:
- O arquivo de visualização da tabela derivada nativa precisa incluir o arquivo do Explore. Exemplo:
include: "/explores/order_items.explore.lkml"
- O arquivo do Explore precisa incluir os arquivos de visualização necessários. Exemplo:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- O modelo precisa incluir o arquivo do Explore. Exemplo:
include: "/explores/order_items.explore.lkml"
Os arquivos do Explore detectarão a conexão do modelo em que estão incluídos. Considere isso ao incluir arquivos do Explore em modelos configurados com uma conexão diferente do modelo pai do arquivo Explore. Se o esquema para a conexão do modelo incluído for diferente do esquema para a conexão do modelo pai, isso pode causar erros de consulta.
Definir colunas nativas derivadas da tabela
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 dela corresponde ao nome do campo especificado na Análise original.
Muitas vezes, você vai querer um nome de coluna na tabela de saída diferente do nome dos campos no Explore original. O exemplo anterior produziu um cálculo do valor da vida útil por usuário usando o Explore order_items. Na tabela de saída, total_revenue é o lifetime_customer_value do cliente.
A declaração column permite declarar 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ícitas
Se o parâmetro field for deixado de fora 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 {}
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 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.
O exemplo a seguir produz a mesma tabela derivada do exemplo anterior, mas adiciona uma coluna average_customer_order calculada, 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: 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 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 seu dialeto de 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 desejada. Ao se referir a valores, você deve usar o nome da coluna conforme definido em sua 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(), ela 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 os mesmos cálculos do exemplo anterior, mas só quer incluir as compras dos últimos 90 dias.
Basta adicionar um filtro ao derived_table que filtra as transações nos últimos 90 dias. O parâmetro filters para 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 da tabela derivada.
Além disso, é possível usar o subparâmetro dev_filters de explore_source com uma tabela derivada nativa. O parâmetro dev_filters permite especificar filtros que o Looker aplica apenas às 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 age 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 saber mais.
Como usar filtros com modelo
Você pode usar bind_filters para incluir filtros com modelo:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Isso é basicamente 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 subjacente.
O from_field especifica o campo que vai receber o filtro, se houver um durante a execução.
No exemplo bind_filters anterior, o Looker usa qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date e o aplica ao campo users.created_date.
Você também pode usar o subparâmetro bind_all_filters de explore_source para transmitir todos os filtros de ambiente de execução de uma Análise para uma subconsulta de tabela derivada nativa. Consulte a página de documentação do parâmetro explore_source para mais informações.
Classificar e limitar tabelas nativas derivadas
Também é possível classificar e limitar as tabelas derivadas, se quiser:
sorts: [order_items.count: desc]
limit: 10
Lembre-se: uma Análise pode exibir as linhas em uma ordem diferente da classificação subjacente.
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 serão convertidos para o fuso horário especificado. Consulte a página de documentação de valores timezone para ver uma lista dos fusos horários compatíveis.
Se você não especificar um fuso horário na definição de tabela derivada nativa, a tabela derivada nativa não realizará nenhuma conversão de fuso horário nos dados com base em tempo. Em vez disso, os dados baseados em tempo usarão 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 no momento.