Como criar tabelas derivadas nativas

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. Ela é 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 à medida que você modela os 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, você não precisa criar uma consulta SQL. Em vez disso, use o parâmetro explore_source para especificar a Análise em que a tabela derivada, as colunas desejadas e outras características serão usadas.

O Looker também pode criar 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 uma Análise para começar a definir as tabelas derivadas nativas

Começando com uma Análise, o Looker pode gerar LookML para toda a tabela derivada ou a maior parte dela. Basta criar uma Análise 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:

1. Selecione o menu de engrenagem Abrir ações e selecione Instalar o LookML.

   

2. Clique na guia Derived Table para consultar o LookML a fim de criar uma tabela derivada nativa para a Análise.

   

3. Copie o LookML.

Agora que você copiou o LookML gerado, cole-o em um arquivo de visualização:

1. No Modo de Desenvolvimento, navegue até os arquivos do projeto.

2. Clique em + na parte de cima da lista de arquivos de projeto no ambiente de desenvolvimento integrado do Looker e selecione Criar visualização. Como alternativa, é possível clicar no menu de uma pasta e selecionar Criar visualização no menu para criar o arquivo dentro da pasta.

3. Defina o nome da visualização como algo significativo.

4. Também é possível mudar os nomes das colunas, especificar colunas derivadas e adicionar filtros.

Quando você usa uma medida de type: count em uma Análise, a visualização rotula os valores resultantes com o nome da visualização, e não com a palavra Contagem. Para evitar confusão, coloque o nome da visualização no plural. Para mudar o nome da visualização, selecione Mostrar nome completo do campo em Série nas configurações da visualização ou use o parâmetro view_label com uma versão plural do nome da visualização.

Como definir uma tabela derivada nativa no LookML

Independentemente de você usar 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 se baseia 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 order_items especificada 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 dessa tabela.

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. Se você não tiver a instrução include, o ambiente de desenvolvimento integrado do Looker não vai sugerir automaticamente nomes de campo nem verificar as referências deles à medida que você cria a tabela derivada nativa. Em vez disso, use o LookML Validator para verificar os campos mencionados na tabela derivada nativa.

No entanto, se você quiser ativar a sugestão automática e a verificação de campo imediata no Looker IDE ou se tiver um projeto complexo do LookML com várias Análises com o mesmo nome ou potencial para referências circulares, use o parâmetro include para apontar para o local da definição da Análise.

As Análises geralmente são definidas em um arquivo de modelo, mas, no caso de tabelas derivadas nativas, é mais simples criar um arquivo separado para a Análise. Os arquivos do LookML Explore têm a extensão de arquivo .explore.lkml, conforme descrito na documentação Criar arquivos do Explore. Dessa forma, no arquivo de visualização em tabela derivada nativo, é possível incluir um único arquivo de exploração, e não o arquivo de modelo inteiro.

Observação: quando você cria um arquivo Explorar, ele precisa ter a extensão .explore.lkml no final do nome do arquivo. Se você estiver fazendo upload do arquivo, use a extensão .explore.lkml completa. Se você estiver usando o Looker IDE para criar o arquivo, escolha a opção Arquivos genéricos do LookML. O Looker adicionará .lkml ao final do nome do arquivo se a extensão não estiver lá, mas você ainda precisará adicionar manualmente a parte .explore da extensão ao nome do arquivo.

Se você quiser criar um arquivo de Análise separado e usar o parâmetro include para apontar para o 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 do Explore. Estes são alguns exemplos:
  • include: "/explores/order_items.explore.lkml"
• O arquivo de "Análise" precisa incluir os arquivos de visualização necessários. Estes são alguns exemplos:
  • include: "/views/order_items.view.lkml"
  • include: "/views/users.view.lkml"
• O modelo precisa incluir o arquivo de Análise. Estes são alguns exemplos:
  • include: "/explores/order_items.explore.lkml"

Observação: os arquivos da Análise vão detectar a conexão do modelo em que os arquivos da Análise estão incluídos. Considere esse fato ao incluir arquivos de Análise em modelos configurados com uma conexão diferente do modelo pai do arquivo de Análise. Se o esquema da conexão do modelo incluído for diferente do esquema da conexão do modelo pai, isso poderá causar erros de consulta.

Como definir colunas derivadas da tabela nativa

Como mostrado no exemplo anterior, 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 na Análise original.

Muitas vezes, o nome da coluna na tabela de saída é diferente do nome dos campos da Análise original. O exemplo anterior gerou um cálculo do valor da vida útil por usuário usando a Análise order_items. Na tabela de saída, total_revenue é na verdade o lifetime_customer_value de um cliente.

Com a declaração column, é possível 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 a partir 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 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, 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 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 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 a seguir produz a mesma tabela derivada que o 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 as funções de janela SQL

Alguns dialetos de banco de dados oferecem suporte a funções de janela, especialmente para criar números sequenciais, chaves primárias, totais cumulativos e em execução 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 o dialeto do banco de dados for compatível com 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(), 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

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 compras dos últimos 90 dias.

Basta adicionar um filtro ao derived_table para filtrar 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.

Observação: no parâmetro filters, use o formato view_name.field_name para definir o escopo do nome do campo com o nome da visualização. É necessário incluir o nome da visualização do campo, mesmo que ele esteja na visualização base da Análise.

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. 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 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 com mais rapidez no Modo de Desenvolvimento para saber mais.

Usar filtros com modelo

Você pode usar bind_filters para incluir filtros com modelos:

bind_filters: {
  to_field: users.created_date
  from_field: filtered_lookml_dt.filter_date
}

Esse processo é essencialmente igual a usar o código abaixo em um bloco sql:

{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}

O to_field é o campo ao qual o filtro é aplicado. O to_field precisa ser um campo do explore_source subjacente.

O from_field especifica o campo de origem do filtro, se houver um durante a execução.

No exemplo anterior do bind_filters, o Looker usa qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date e o aplica 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 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.

Como classificar e limitar tabelas derivadas nativas

Também é possível classificar e limitar as tabelas derivadas, se quiser:

sorts: [order_items.count: desc]
limit: 10

Lembre-se de que 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 são convertidos para o fuso horário especificado. Consulte a página de documentação de valores timezone para uma lista de 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 nenhuma conversão de fuso horário nos dados baseados em tempo. Em vez disso, os dados baseados em tempo vão usar o fuso horário do banco de dados por padrão.

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.