Uma tabela derivada é uma consulta em que os 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 definida usando termos do LookML. Isso é diferente de uma tabela derivada baseada 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 ao modelar seus dados. Consulte a seção Tabelas derivadas nativas e tabelas derivadas baseadas em SQL na página de documentação Tabelas derivadas no Looker para mais informações.
As tabelas derivadas nativas e baseadas em SQL são definidas na LookML usando o parâmetro derived_table
no nível da visualização. No entanto, com as 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 detalhada em que basear a tabela derivada, as colunas desejadas e outras características.
Também é possível fazer com que o Looker crie a LookML da tabela derivada com base em uma consulta do SQL Runner, conforme descrito na página de documentação Usar o SQL Runner para criar tabelas derivadas.
Usar uma análise detalhada para começar a definir suas tabelas derivadas nativas
Começando com uma análise detalhada, o Looker pode gerar LookML para toda ou a maior parte da sua tabela derivada. Basta criar uma análise detalhada 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 Abrir ações e escolha Receber LookML.
Clique na guia Tabela derivada para conferir o LookML de criação de 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 no botão + na parte de cima da lista de arquivos do projeto no ambiente de desenvolvimento integrado do Looker e selecione Criar visualização. Ou clique no menu de uma pasta e selecione Criar visualização para criar o arquivo dentro dela.
Defina um nome significativo para a visualização.
Se quiser, mude os nomes das colunas, especifique colunas derivadas e adicione filtros.
Quando você usa uma medida de
type: count
em 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 nome completo do campo em Série nas configurações de visualização ou use o parâmetroview_label
com uma versão no plural do nome da visualização.
Como definir uma tabela derivada nativa em LookML
Se você usar tabelas derivadas declaradas em SQL ou LookML nativo, a saída de uma consulta 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 na 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
. Não é necessário escrever manualmente a consulta em SQL. Em vez disso, o Looker cria a consulta para você usando a análise detalhada order_items
especificada 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
}
}
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 IDE do Looker não vai autosuggest nomes de campos nem verificar as referências de campo enquanto você cria a tabela derivada nativa. Em vez disso, use o validador da LookML para verificar os campos que você está referenciando na sua tabela derivada nativa.
No entanto, se você quiser ativar a sugestão automática e a verificação imediata de campos no IDE do Looker ou se tiver um projeto complexo do LookML com várias análises detalhadas do mesmo nome ou potencial para referências circulares, use o parâmetro include
para apontar para o local da definição da análise detalhada.
As análises detalhadas geralmente são definidas em um arquivo de modelo, mas, no caso de tabelas derivadas nativas, é mais limpo criar um arquivo separado para a análise detalhada. Os arquivos de Análise do LookML têm a extensão .explore.lkml
, conforme descrito na documentação sobre Como criar arquivos de Análise. Assim, no arquivo de visualização em tabela derivada nativa, você pode 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 detalhada separado e usar o parâmetro include
para apontar para o arquivo de análise detalhada no arquivo de visualização da sua tabela derivada nativa, verifique se os arquivos LookML atendem aos seguintes requisitos:
- O arquivo de visualização da tabela derivada nativa precisa incluir o arquivo da análise detalhada. Por exemplo:
include: "/explores/order_items.explore.lkml"
- O arquivo da análise detalhada 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"
Definir colunas de tabelas derivadas nativas
Como mostrado no exemplo anterior, você usa column
para especificar as colunas de saída da tabela derivada.
Especificar os nomes das colunas
Para a coluna user_id
, o nome dela corresponde ao nome do campo especificado no recurso original do Google Analytics.
Muitas vezes, você vai querer um nome de coluna diferente na tabela de saída do nome dos campos na análise detalhada original. O exemplo anterior gerou um cálculo do valor da vida útil por usuário usando a análise detalhada order_items
. Na tabela de saída, total_revenue
é um lifetime_customer_value
de um 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í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
, então
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 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 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 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 SQL
Alguns dialetos de banco de dados oferecem suporte a 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. Depois que a consulta principal é executada, todas as declarações derived_column
são executadas em uma transmissão separada.
Se o dialeto do seu banco de dados for compatível com funções de janela, você poderá usá-las na 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, 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()
, 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
}
}
Adicionar filtros a uma tabela derivada nativa
Imagine 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 compras dos últimos 90 dias.
Basta adicionar um filtro ao derived_table
que filtre as transações dos últimos 90 dias. O parâmetro filters
de uma tabela derivada usa a mesma sintaxe que você usa 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, você pode 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 mudança.
O parâmetro dev_filters
funciona 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 na versão de desenvolvimento da tabela.
Consulte Trabalhar mais rápido no modo de desenvolvimento para mais informações.
Como usar filtros com modelos
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 é essencialmente o mesmo que usar o código a seguir em um bloco sql
:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
O to_field
é o campo a que o filtro é aplicado. O to_field
precisa ser um campo do explore_source
subjacente.
O from_field
especifica o campo de onde o filtro será extraído, se houver um filtro no tempo de execução.
No exemplo bind_filters
anterior, o Looker vai usar qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date
e aplicar ao campo users.created_date
.
Também é possível usar o subparâmetro bind_all_filters
de explore_source
para transmitir todos os filtros de tempo de execução de uma análise detalhada para uma subconsulta de tabela derivada nativa. Consulte a página de documentação de parâmetros do explore_source
para mais informações.
Como classificar e limitar tabelas derivadas nativas
Você também pode classificar e limitar as tabelas derivadas, se quiser:
sorts: [order_items.count: desc]
limit: 10
Uma análise detalhada pode mostrar as linhas em uma ordem diferente da classificação subjacente.
Como converter tabelas derivadas nativas para fusos horários diferentes
Você pode especificar o fuso horário da sua 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 ver uma lista dos fusos horários aceitos.
Se você não especificar um fuso horário na definição de tabela derivada nativa, ela não fará nenhuma conversão de fuso horário em dados baseados em tempo. Em vez disso, os dados baseados em tempo serão definidos como o fuso horário do banco de dados.
Se a tabela derivada nativa não for permanente, defina o valor do fuso horário como "query_timezone"
para usar automaticamente o fuso horário da consulta em execução.