Conceitos de SQL para tabelas derivadas

Embora as visualizações do LookML sejam normalmente baseadas em tabelas atuais no banco de dados, também é possível criar visualizações com base em instruções SELECT do SQL. No Looker, esse tipo de visualização é chamado de tabela derivada. As tabelas derivadas são consultas cujos resultados são usados como se fossem tabelas reais no banco de dados.

Neste guia, você vai aprender sobre os seguintes tópicos:

Como o Looker gera o SQL para tabelas derivadas

Ao consultar tabelas derivadas no LookML, o Looker traduz suas consultas em instruções SQL como expressões de tabela comum (CTEs) ou visualizações inline, dependendo do dialeto. Em outras palavras, as consultas SQL geradas podem ser semelhantes a um dos exemplos a seguir.

O exemplo a seguir mostra como o Looker pode gerar uma consulta SQL que usa uma CTE para consultar uma tabela derivada:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

O exemplo a seguir mostra como o Looker pode gerar uma consulta SQL que usa uma visualização inline para consultar uma tabela derivada:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

Tabelas derivadas baseadas em SQL

Para criar uma tabela derivada com base em SQL, defina uma consulta SQL diretamente no LookML usando o sql parâmetro no derived_table parâmetro de uma visualização. Isso permite definir as colunas da tabela derivada usando SQL.

Por exemplo, o exemplo de LookML a seguir define uma tabela derivada chamada top_100_users que identifica os 100 principais usuários com o maior gasto total em todos os pedidos:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

Quando a visualização top_100_users é referenciada em uma consulta de análise, o Looker usa essa instrução SELECT do SQL no SQL gerado como uma CTE ou uma visualização inline, dependendo do dialeto.

O uso de SQL para definir tabelas derivadas pode ter algumas limitações. No exemplo da seção Tabelas derivadas baseadas em SQL, por exemplo, as seguintes considerações se aplicam:

  • A relação entre as tabelas orders e order_items provavelmente já está definida no modelo LookML. Se os nomes das tabelas subjacentes no banco de dados mudarem, os nomes das tabelas precisarão ser atualizados em vários lugares, incluindo na definição da tabela derivada com base em SQL.
  • O SQL na definição da tabela derivada precisa estar no dialeto correto para o banco de dados subjacente. Se os dados forem movidos para um banco de dados diferente, a definição da tabela derivada com base em SQL precisará ser alterada.

Por esses motivos, as tabelas derivadas nativas costumam ser uma opção melhor.

Tabelas derivadas nativas

No Looker, as tabelas derivadas nativas são definidas com o LookML. Ao contrário do uso de tabelas derivadas baseadas em SQL, quando você define uma tabela derivada nativa, está aproveitando o modelo LookML das seguintes maneiras:

  • Se o nome da tabela mudar no banco de dados, você só precisará atualizá-lo uma vez no modelo LookML. Como a tabela derivada nativa aponta para o objeto do LookML atual em que a tabela do banco de dados está definida, ela vai se referir automaticamente à tabela apropriada.
  • Da mesma forma, se você mudar para um dialeto diferente, o LookML da tabela derivada nativa ainda será válido porque o Looker gera o SQL adequado à conexão do banco de dados.

É possível definir o LookML para a tabela derivada nativa manualmente. No entanto, a maneira mais fácil de criar uma tabela derivada nativa é fazer com que o Looker crie a tabela derivada de uma consulta de análise. Na análise, selecione os campos que você quer usar na tabela derivada e use a opção Receber LookML > Tabela derivada no menu de engrenagem da análise para receber o LookML. O Looker gera o LookML necessário para criar uma tabela derivada da consulta de análise, incluindo as definições de campo relevantes do modelo LookML que são necessárias para criar as colunas da tabela derivada. É possível copiar o LookML para um arquivo de visualização no projeto para criar a tabela derivada.

O exemplo a seguir mostra uma tabela derivada nativa que identifica os 100 principais usuários com o maior gasto total em todos os pedidos:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Ao consultar uma tabela derivada nativa em uma análise, todos os detalhes da consulta SQL para a tabela derivada nativa são gerados automaticamente. As definições das colunas de uma visualização do LookML com uma tabela derivada nativa são inferidas das definições da análise subjacente, eliminando a necessidade de repetir qualquer definição.

Tabelas derivadas persistentes (TDPs)

No Looker, é possível criar tabelas derivadas temporárias e tabelas derivadas persistentes. Depois de criar uma tabela derivada com base em SQL ou uma tabela derivada nativa, você pode adicionar persistência para que o Looker grave a tabela em um esquema de rascunho no banco de dados e a regenere na programação especificada. Para mais informações, consulte Tabelas derivadas no Looker.