Conceitos de SQL para tabelas derivadas

Embora as visualizações do LookML geralmente sejam baseadas em tabelas existentes no banco de dados, também é possível criar visualizações com base em instruções SQL SELECT. 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

Quando você consulta tabelas derivadas em 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 baseada em SQL, defina uma consulta SQL diretamente na LookML usando o parâmetro sql no parâmetro derived_table de uma visualização. Isso permite definir as colunas da tabela derivada usando SQL.

Por exemplo, o LookML de amostra a seguir define uma tabela derivada chamada top_100_users que identifica os 100 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 do recurso Detalhar, o Looker usa essa instrução SQL SELECT no SQL gerado como uma CTE ou uma visualização inline, dependendo do seu dialeto.

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

  • A relação entre as tabelas orders e order_items provavelmente já está definida no modelo do LookML. Se os nomes das tabelas no banco de dados mudarem, eles precisarão ser atualizados em vários lugares, incluindo na definição da tabela derivada baseada 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 baseada 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 a LookML. Ao contrário das tabelas derivadas baseadas em SQL, quando você define uma tabela derivada nativa, aproveita o modelo LookML das seguintes maneiras:

  • Se o nome da tabela mudar no banco de dados, basta atualizar uma vez no modelo do LookML. Como sua tabela derivada nativa aponta para o objeto LookML em que a tabela de banco de dados está definida, ela se refere automaticamente à tabela apropriada.
  • Da mesma forma, se você mudar para um dialeto diferente, a LookML da tabela derivada nativa ainda será válida porque o Looker gera o SQL adequado à sua conexão de banco de dados.

Você pode definir manualmente a LookML para a tabela derivada nativa. No entanto, a maneira mais fácil de criar uma tabela derivada nativa é fazer com que o Looker crie a tabela derivada com base em uma consulta do Explorar. 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 a LookML. O Looker gera a LookML necessária para criar uma tabela derivada da sua consulta de análise detalhada, incluindo as definições de campo relevantes do seu modelo LookML que são necessárias para criar as colunas da tabela derivada. Você pode copiar a LookML para um arquivo de visualização no seu projeto e criar a tabela derivada.

O exemplo a seguir mostra uma tabela derivada nativa que identifica os 100 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
    }
  }
}

Quando você consulta uma tabela derivada nativa em uma análise detalhada, 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 detalhada subjacente, eliminando a necessidade de repetir qualquer definição.

Tabelas derivadas persistentes (PDTs)

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