Criar tabelas derivadas nativas

Uma tabela derivada é uma consulta cujos resultados são usados como se a tabela derivada fosse uma tabela física na base de dados. Uma tabela derivada nativa baseia-se numa consulta que define através de termos do LookML. Isto é diferente de uma tabela derivada baseada em SQL, que se baseia numa consulta que define 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 compreender à medida que modela os seus dados. Consulte a secçã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 através do parâmetro derived_table ao nível da vista. No entanto, com as tabelas derivadas nativas, não precisa de criar uma consulta SQL. Em alternativa, use o parâmetro explore_source para especificar a análise detalhada na qual basear a tabela derivada, as colunas pretendidas e outras características desejadas.

Também pode fazer com que o Looker crie o LookML da tabela derivada a partir de uma consulta do SQL Runner, conforme descrito na página de documentação Usar o SQL Runner para criar tabelas derivadas.

Usar uma exploração para começar a definir as tabelas derivadas nativas

Começando com uma exploração, o Looker pode gerar LookML para toda ou a maioria da sua tabela derivada. Basta criar uma exploração e selecionar todos os campos que quer incluir na sua tabela derivada. Depois, para gerar o LookML da tabela derivada nativa, siga estes passos:

1. Selecione o menu de roda dentada Explorar ações e selecione Obter LookML.

   

2. Clique no separador Tabela derivada para ver o LookML para criar uma tabela derivada nativa para a exploração.

   

3. Copie o LookML.

Agora que copiou o LookML gerado, cole-o num ficheiro de visualização:

1. No modo de programação, navegue para os ficheiros do projeto.

2. Clique em + na parte superior da lista de ficheiros do projeto no IDE do Looker e selecione Criar vista. Em alternativa, pode clicar no menu de uma pasta e selecionar Criar vista no menu para criar o ficheiro na pasta.

3. Defina o nome da vista para algo significativo.

4. Opcionalmente, altere os nomes das colunas, especifique colunas derivadas e adicione filtros.

Quando usa uma medida de type: count numa análise detalhada, a visualização etiqueta os valores resultantes com o nome da visualização em vez da palavra Contagem. Para evitar confusões, use o plural no nome da vista. Pode alterar o nome da vista selecionando Mostrar nome completo do campo em Séries nas definições de visualização ou usando o parâmetro view_label com uma versão no plural do nome da vista.

Definir uma tabela derivada nativa no LookML

Quer use tabelas derivadas declaradas em SQL ou LookML nativo, o resultado de uma consulta de derived_table's é 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 seguinte consulta SQL 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 baseia-se numa exploração, inclui campos de medidas e dimensões, adiciona todos os filtros aplicáveis e também pode especificar uma ordem de ordenação. Uma tabela derivada nativa contém todos estes elementos, além dos nomes de saída das colunas.

O exemplo simples seguinte produz uma tabela derivada com três colunas: user_id, lifetime_customer_value e lifetime_number_of_orders. Não precisa de escrever manualmente a consulta em SQL. Em alternativa, o Looker cria a consulta por si usando a funcionalidade Explorar especificada order_items e alguns dos campos dessa funcionalidade (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 declarações include para permitir a referência a campos

No ficheiro de visualização da tabela derivada nativa, usa o parâmetro explore_source para apontar para uma exploração e definir as colunas e outras caraterísticas da tabela derivada nativa.

No ficheiro de visualização da tabela derivada nativa, não tem de usar o parâmetro include para indicar o ficheiro que contém a definição do Explore. Se não tiver a declaração include, o IDE do Looker não sugere automaticamente nomes de campos nem valida as referências de campos à medida que cria a tabela derivada nativa. Em alternativa, pode usar a validação de LookML para validar os campos aos quais está a fazer referência na sua tabela derivada nativa.

No entanto, se quiser ativar a sugestão automática e a validação imediata de campos no IDE do Looker ou se tiver um projeto LookML complexo com várias explorações com o mesmo nome ou potencial para referências circulares, pode usar o parâmetro include para indicar a localização da definição da exploração.

As explorações são frequentemente definidas num ficheiro de modelo, mas, no caso das tabelas derivadas nativas, é mais simples criar um ficheiro separado para a exploração. Os ficheiros de exploração do LookML têm a extensão de ficheiro .explore.lkml, conforme descrito na documentação sobre a criação de ficheiros de exploração. Desta forma, no ficheiro de visualização da tabela derivada nativa, pode incluir um único ficheiro de exploração e não o ficheiro de modelo completo.

Nota: quando cria um ficheiro de exploração, o ficheiro tem de ter a extensão .explore.lkml no final do nome do ficheiro. Se estiver a carregar o ficheiro, tem de usar a extensão .explore.lkml completa. Se estiver a usar o IDE do Looker para criar o ficheiro, use a opção Ficheiros genéricos do LookML. O Looker adiciona .lkml ao final do nome do ficheiro se a extensão não estiver presente, mas ainda tem de adicionar manualmente a parte .explore da extensão ao nome do ficheiro.

Se quiser criar um ficheiro de exploração separado e usar o parâmetro include para apontar para o ficheiro de exploração no ficheiro de visualização da tabela derivada nativa, certifique-se de que os ficheiros LookML cumprem os seguintes requisitos:

• O ficheiro de visualização da tabela derivada nativa deve incluir o ficheiro do Explore. Por exemplo:
  • include: "/explores/order_items.explore.lkml"
• O ficheiro do Explore deve incluir os ficheiros de visualização de que precisa. Por exemplo:
  • include: "/views/order_items.view.lkml"
  • include: "/views/users.view.lkml"
• O modelo deve incluir o ficheiro do Explorar. Por exemplo:
  • include: "/explores/order_items.explore.lkml"

Nota: os ficheiros de exploração vão ter em conta a ligação do modelo no qual estão incluídos. Tenha em atenção este facto quando incluir ficheiros de exploração em modelos configurados com uma ligação diferente do modelo principal do ficheiro de exploração. Se o esquema da ligação do modelo de inclusão diferir do esquema da ligação do modelo principal, podem ocorrer erros de consulta.

Definir colunas de tabelas derivadas nativas

Conforme mostrado no exemplo anterior, usa column para especificar as colunas de saída da tabela derivada.

Especificar os nomes das colunas

Para a coluna user_id, o nome da coluna corresponde ao nome do campo especificado no Explorar original.

Frequentemente, vai querer um nome de coluna diferente na tabela de saída do nome dos campos no Explorar original. O exemplo anterior produziu um cálculo do valor do cliente por utilizador através da ferramenta order_itemsExplorar. Na tabela de resultados, total_revenue é realmente o lifetime_customer_value de um cliente.

A declaração column suporta a declaração de um nome de saída diferente do campo de entrada. Por exemplo, o código seguinte indica ao Looker que "crie uma coluna de saída denominada 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 omitido de uma declaração de coluna, é assumido como <explore_name>.<field_name>. Por exemplo, se tiver especificado explore_source: order_items, então

column: user_id {
  field: order_items.user_id
}

é equivalente a

column: user_id {}

Criar colunas derivadas para valores calculados

Pode 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 sql pode usar quaisquer colunas que tenha especificado através de parâmetros column. As colunas derivadas não podem incluir funções de agregação, mas podem incluir cálculos que podem ser realizados numa única linha da tabela.

O exemplo seguinte produz a mesma tabela derivada que o exemplo anterior, exceto que 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
  }
}

Usar funções de janela SQL

Alguns dialetos de base de dados suportam 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. Após a execução da consulta principal, as declarações derived_column são executadas numa passagem separada.

Se o dialeto da sua base de dados suportar funções de janela, pode 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 pretendida. Quando se refere a valores, deve usar o nome da coluna conforme definido na tabela derivada nativa.

O exemplo seguinte 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(), calcula uma coluna que contém o número de sequência da encomenda 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

Suponhamos que quer criar uma tabela derivada do valor de um cliente nos últimos 90 dias. Quer os mesmos cálculos que fez no exemplo anterior, mas só quer incluir compras dos últimos 90 dias.

Basta adicionar um filtro ao derived_table que filtre as transações nos últimos 90 dias. O parâmetro filters de uma tabela derivada usa a mesma sintaxe que usa para criar uma medida filtrada.

Nota: no parâmetro filters, use o formato view_name.field_name para restringir o nome do campo com o nome da vista. Tem de incluir o nome da vista para o campo, mesmo que o campo esteja na vista base do Explorar.

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 são adicionados à cláusula WHERE quando o Looker escreve o SQL para a tabela derivada.

Além disso, pode usar o subparâmetro dev_filters de explore_source com uma tabela derivada nativa. O parâmetro dev_filters permite-lhe especificar filtros que o Looker aplica apenas às versões de desenvolvimento da tabela derivada, o que significa que pode criar versões mais pequenas 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 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 tem precedência para a versão de desenvolvimento da tabela.

Consulte o artigo Trabalhar mais rapidamente no modo de desenvolvimento para mais informações.

Usar filtros baseados em modelos

Pode usar bind_filters para incluir filtros baseados em modelos:

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

Isto é essencialmente o mesmo que usar o seguinte código num 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 tem de ser um campo do explore_source subjacente.

O elemento from_field especifica o campo a partir do qual obter o filtro, se existir um filtro no tempo de execução.

No bind_filters exemplo anterior, o Looker vai usar qualquer filtro aplicado ao campo filtered_lookml_dt.filter_date e aplicá-lo ao campo users.created_date.

Também pode usar o subparâmetro bind_all_filters de explore_source para transmitir todos os filtros de tempo 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.

Ordenar e limitar tabelas derivadas nativas

Também pode ordenar e limitar as tabelas derivadas, se quiser:

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

Lembre-se de que uma análise detalhada pode apresentar as linhas numa ordem diferente da ordenação subjacente.

Converter tabelas derivadas nativas em fusos horários diferentes

Pode especificar o fuso horário para a tabela derivada nativa através do subparâmetro timezone:

timezone: "America/Los_Angeles"

Quando usa o subparâmetro timezone, todos os dados baseados na hora na tabela derivada nativa são convertidos para o fuso horário que especificar. Consulte a página de documentação dos valorestimezone para ver uma lista dos fusos horários suportados.

Se não especificar um fuso horário na definição da tabela derivada nativa, a tabela derivada nativa não efetua qualquer conversão de fuso horário nos dados baseados no tempo. Em alternativa, os dados baseados no tempo são predefinidos para o fuso horário da base de dados.

Se a tabela derivada nativa não for persistente, pode definir o valor do fuso horário como "query_timezone" para usar automaticamente o fuso horário da consulta em execução.