Uma tabela derivada é uma consulta com resultados usados como se fosse uma tabela física no banco de dados. Uma tabela derivada nativa é baseada em uma consulta definida por você usando termos do LookML. Isso é diferente de uma tabela derivada baseada em SQL, que é baseada em uma consulta definida com termos SQL. Em comparação com tabelas derivadas baseadas em SQL, as tabelas derivadas nativas são muito mais fáceis de ler e entender conforme 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 tabelas derivadas nativas, você não precisa criar uma consulta SQL. Em vez disso, use o parâmetro explore_source para especificar o recurso Explorar, 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 a partir de uma consulta SQL Runner, conforme descrito na página de documentação Como usar o SQL Runner para criar tabelas derivadas.
Como usar uma exploração para começar a definir suas tabelas derivadas nativas
Começando com o recurso Explorar, o Looker pode gerar LookML para toda a tabela derivada ou a maior parte dela. Basta criar uma guia "Explorar" e selecionar todos os campos que você quer incluir na sua tabela derivada. Em seguida, para gerar a tabela derivada LookML:
Selecione o menu de engrenagem Explore Actions e clique em Get LookML.
Clique na guia Derived Table para ver o LookML para criar uma tabela derivada nativa para o Explore.
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 superior da lista de arquivos do projeto no Looker IDE 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 rotula 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érie nas configurações de visualização ou usar umview_labelcom uma versão plural do seu nome de visualização.
Como definir uma tabela derivada nativa no LookML
Independentemente de você usar tabelas derivadas declaradas em SQL ou LookML nativo, a saída de uma consulta de 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 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 mais os 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 escrever manualmente a consulta no SQL. Em vez disso, ela é criada pelo Looker usando a ferramenta Explorar 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
}
}
Como usar instruções include para ativar campos de referência
No arquivo de visualização da tabela derivada nativa, 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 um Explorar no arquivo de visualização da tabela derivada nativa, também é necessário incluir o arquivo que contém a definição de 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 de Como criar arquivos de exploração. Dessa forma, em seu arquivo nativo de visualização em tabela derivada, você pode incluir um único arquivo de exploração e não o arquivo de modelo inteiro. Nesse caso:
- O arquivo de visualização da tabela derivada nativa precisa incluir o arquivo "Explorar". Por exemplo:
include: "/explores/order_items.explore.lkml" - O arquivo de exploração deve incluir os arquivos de visualização necessários. Por exemplo:
include: "/views/order_items.view.lkml"
include: "/views/users.view.lkml" - O modelo deve incluir o arquivo "Explorar". Por exemplo:
include: "/explores/order_items.explore.lkml"
Os arquivos de exploração detectam a conexão do modelo em que estão incluídos. Considere esse fato ao incluir arquivos de exploração em modelos configurados com uma conexão diferente do modelo pai do arquivo de exploração. Se o esquema da conexão do modelo incluído for diferente do esquema da conexão do 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
Na coluna user_id, o nome da coluna corresponde ao nome do campo especificado na exploração original.
Na tabela de saída, o nome da coluna não precisa ser o mesmo dos campos na guia "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 é o 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 chamada lifetime_value do campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nomes de coluna implícitos
Se o parâmetro field for deixado de fora da declaração de coluna, 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 em "Explorar" do parâmetro explore_source. Cada parâmetro derived_column tem um parâmetro sql que especifica como construir o valor.
Seu cálculo de sql pode usar qualquer coluna especificada usando os 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 abaixo produz a mesma tabela derivada do exemplo anterior, mas adiciona uma coluna average_customer_order 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 da janela SQL
Alguns dialetos de banco de dados são compatíveis com funções de janela, especialmente para criar números de sequência, chaves primárias, totais cumulativos e em execução e outros cálculos úteis de várias linhas. Depois que a consulta principal é executada, qualquer declaração derived_column é executada em uma passagem 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 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 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 quiséssemos criar uma tabela derivada do valor de um cliente nos últimos 90 dias. Queremos os mesmos cálculos que fizemos acima, mas queremos incluir apenas as compras dos últimos 90 dias.
Adicionamos 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. O parâmetro dev_filters permite especificar filtros que o Looker aplica somente às versões de desenvolvimento da tabela derivada, o que significa que é possível criar versões menores e filtradas da tabela para iterar e testar sem aguardar a criação da tabela completa 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 rapidamente no Modo de desenvolvimento para mais informações.
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 a seguir 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 do explore_source subjacente.
O from_field especifica o campo para receber o filtro, se houver um filtro no tempo de execução.
No exemplo bind_filters acima, o Looker usará qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date e o 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 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 você quiser:
sorts: [order_items.count: desc]
limit: 10
Lembre-se: uma exploração pode exibir as linhas em uma ordem diferente da classificação subjacente.
Como 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"
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 que você especificar. Consulte a página de documentação de 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 de tabela derivada nativa, a tabela derivada nativa não executará nenhuma conversão de fuso horário em dados com base em tempo. Em vez disso, os dados baseados em tempo usarão como padrão seu fuso horário do banco de dados.
Se a tabela derivada nativa não for persistente, você poderá definir o valor do fuso horário como "query_timezone" para usar automaticamente o fuso horário da consulta em execução no momento.