Uma tabela derivada é uma consulta cujos resultados são usados como se fosse uma tabela física no banco de dados. Uma tabela derivada nativa é baseada em uma consulta definida usando termos do LookML. Ela é diferente de uma tabela derivada baseada em SQL, que tem como base 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 à medida que você modela seus dados. Consulte a seção Tabelas derivadas nativas e tabelas 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 as tabelas derivadas nativas, não é necessário criar uma consulta SQL. Em vez disso, use o parâmetro explore_source para especificar a exploração em que a tabela derivada será baseada, as colunas desejadas e outras características desejadas.
Também é possível fazer com que o Looker crie a tabela derivada LookML de uma consulta do SQL Runner, conforme descrito na página de documentação Como usar o SQL Runner para criar tabelas derivadas.
Como usar o recurso Explorar para começar a definir as tabelas derivadas nativas
A partir de uma exploração, o Looker pode gerar o LookML em toda a tabela derivada ou na maioria dela. Crie uma guia "Explorar" e selecione todos os campos que você quer incluir na tabela derivada. Em seguida, para gerar a tabela nativa nativa LookML, siga estas etapas:
Clique no menu de engrenagem "Explorar" e selecione Instalar LookML.
Clique na guia Tabelas derivadas para ver o LookML e criar uma tabela derivada nativa para a guia Explorar.
Copiar o LookML
Agora que você copiou o LookML gerado, cole-o em um arquivo de visualização:
No Modo de desenvolvimento, acesse os arquivos do projeto.
Clique em + na parte superior da lista de arquivos do projeto no ambiente de desenvolvimento integrado do Looker e selecione Criar vista. Também é possível 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 como algo significativo.
Também é possível alterar os nomes das colunas, especificar colunas derivadas e adicionar filtros.
Quando você usa uma medida de
type: countem uma exploração, a visualização identifica os valores resultantes com o nome da visualização, em vez da palavra Contagem. Para evitar confusão, recomendamos pluralizar o nome da visualização, selecionando Mostrar nome do campo completo em Séries nas configurações de visualização ou usar umview_labelcom uma versão plural do nome da visualização.
Como definir uma tabela derivada nativa no LookML
Se você usar tabelas derivadas declaradas em SQL ou LookML nativo, o resultado de uma consulta derived_table's será uma tabela com um conjunto de colunas. Quando a tabela derivada é expressa em SQL, os nomes das colunas de saída ficam implícitos pela consulta SQL. Por exemplo, a consulta SQL abaixo 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 exploração, inclui campos de medida e dimensão, adiciona todos 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 para as colunas.
O exemplo simples abaixo produz uma tabela derivada com três colunas: user_id, lifetime_customer_value e lifetime_number_of_orders. Não é necessário programar manualmente a consulta em SQL. Em vez disso, o Looker cria a consulta para você usando o recurso Explorar order_items especificado e alguns dos campos "Explorar" (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 nativa da tabela derivada, você usa o parâmetro explore_source para apontar para uma exploração e definir as colunas e outras características desejadas para a tabela derivada nativa. Como você está apontando para uma exploração no arquivo de visualização da tabela derivada nativa, também é preciso incluir o arquivo que contém a definição do recurso Explorar. As explorações 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 exploração usando a extensão de arquivo .explore.lkml, conforme descrito na documentação sobre Como criar arquivos de exploração. Dessa forma, no arquivo de visualização de tabela derivada nativa, é possível incluir um único arquivo "Explorar", não o arquivo inteiro do modelo. Nesse caso:
- O arquivo de visualização nativo da tabela derivada precisa incluir o arquivo "Explorar". Por exemplo:
include: "/explores/order_items.explore.lkml" - O arquivo "Explorar" deve 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 do Explorar. Por exemplo:
include: "/explores/order_items.explore.lkml"
Os arquivos de exploração escutam a conexão do modelo a que estão incluídos. Considere isso ao incluir arquivos "Explorar" em modelos configurados com uma conexão diferente do modelo pai do arquivo "Explorar". Se o esquema da conexão de modelo's for diferente do esquema da conexão de modelo pai, isso pode causar erros de consulta.
Como definir colunas de tabelas derivadas nativas
Conforme mostrado no exemplo acima, use column para especificar as colunas de saída da tabela derivada.
Como especificar os nomes das colunas
Para a coluna user_id, o nome da coluna corresponde ao nome do campo especificado em"Explorar"original.
Muitas vezes, convém ter um nome de coluna diferente na tabela de saída do que os nomes dos campos em "Explorar" original. No exemplo acima, estamos produzindo um cálculo do valor da vida útil por usuário usando a exploração order_items. Na tabela de saída, total_revenue é um lifetime_customer_value do cliente.
A declaração column é compatível com a declaração de um nome de saída diferente do campo de entrada. Por exemplo, o código abaixo diz: "criar uma coluna de saída com o nome lifetime_value no campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomes de coluna implícitos
Se um parâmetro field não for especificado em uma declaração de coluna, ele será considerado <explore_name>.<field_name>. Por exemplo, se você especificar explore_source: order_items,
column: user_id {
field: order_items.user_id
}
é equivalente a
column: user_id {}
Como criar colunas derivadas para valores calculados
Você pode adicionar parâmetros derived_column para especificar colunas que não existem no parâmetro explore_source e Explorar. Cada parâmetro derived_column tem um parâmetro sql que especifica como criar o valor.
Seu cálculo de sql pode usar todas as colunas que você especificou com parâmetros column. As colunas derivadas não podem incluir funções agregadas, mas podem incluir cálculos que podem ser realizados em uma única linha da tabela.
O exemplo abaixo produz a mesma tabela derivada do exemplo anterior, mas adiciona uma coluna average_customer_order calculada, 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 as funções de janela SQL
Alguns dialetos do banco de dados oferecem suporte a funções de janela, especialmente para criar números sequenciais, chaves primárias, totais em execução e cumulativos e outros cálculos úteis de várias linhas. Após a execução da consulta principal, todas as declarações derived_column são executadas em uma passagem separada.
Se o dialeto do banco de dados for compatível com funções de janela, você poderá usá-las na tabela derivada nativa. Crie um parâmetro derived_column com um parâmetro sql que contenha a função de janela desejada. Ao fazer referência a valores, use o nome da coluna conforme definido na tabela derivada nativa.
O exemplo abaixo 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 sequencial de um pedido do 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 queremos criar uma tabela derivada do valor de um cliente nos últimos 90 dias. Queremos os mesmos cálculos que fizemos acima, mas só queremos incluir 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 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 da tabela derivada.
Além disso, é possível usar o subparâmetro dev_filters de explore_source com uma tabela derivada nativa. Com o parâmetro dev_filters, é possível especificar filtros que o Looker aplica somente às versões de desenvolvimento da tabela derivada, o que significa que é possível compilar versões menores e filtradas da tabela para iterar e testar sem esperar pela tabela completa ser criada após cada mudança.
O parâmetro dev_filters atua em conjunto com o 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 rapidamente no modo de desenvolvimento para ver mais informações.
Usar filtros com modelo
É possível usar bind_filters para incluir filtros modelo:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
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 recebimento do filtro se houver um filtro no momento da execução.
No exemplo do bind_filters acima, o Looker 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 subparâmetro bind_all_filters de explore_source para transmitir todos os filtros de ambiente de execução de uma exploração para 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 desejado:
sorts: [order_items.count: desc]
limit: 10
Lembre-se: uma ferramenta Explorar pode exibir as linhas em uma ordem diferente da classificação subjacente.
Converter tabelas derivadas nativas em diferentes fusos horários
É possível especificar o fuso horário da tabela derivada nativa usando o subparâmetro timezone:
timezone: "America/Los_Angeles"
Ao usar o subparâmetro timezone, todos os dados com base no horário na tabela derivada nativa serão convertidos para o fuso horário especificado. Consulte a página de documentação Valores do timezone para ver uma lista dos fusos horários compatíveis.
Se você não especificar um fuso horário na definição da tabela derivada nativa, a tabela derivada nativa não vai realizar conversões de fuso horário em dados com base no tempo. Em vez disso, o padrão será o fuso horário do banco de dados.
Se a tabela derivada nativa não for permanente, será possível definir o valor do fuso horário como "query_timezone" para usar automaticamente o fuso horário da consulta em execução.