Conceitos de SQL para tabelas derivadas

Embora as visualizações do LookML geralmente sejam baseadas em tabelas existentes no seu banco de dados, você também pode 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

Quando você consulta tabelas derivadas no LookML, o Looker traduz suas consultas em instruções SQL como expressões de tabela comuns (CTEs) ou visualizações inline, dependendo do dialeto. Em outras palavras, as consultas SQL geradas podem se parecer com um dos exemplos a seguir.

O exemplo a seguir mostra como o Looker pode gerar uma consulta SQL que usa um 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 no LookML usando o parâmetro sql no parâmetro derived_table de uma visualização. Assim, você pode 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 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 detalhada, o Looker usa essa instrução SELECT SQL 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 seu banco de dados mudarem, eles precisarão ser atualizados em vários lugares, inclusive na definição da tabela derivada baseada em SQL.
  • O SQL na definição da tabela derivada precisa estar no dialeto correto do banco de dados. Se os dados forem movidos para outro banco de dados, a definição da tabela derivada baseada em SQL precisará ser alterada.

Por esses motivos, as tabelas derivadas nativas geralmente são uma opção melhor.

Tabelas derivadas nativas

No Looker, as tabelas derivadas nativas são definidas com o LookML. Ao definir uma tabela derivada nativa, você aproveita o modelo do LookML das seguintes maneiras:

  • Se o nome da tabela mudar no banco de dados, você só vai precisar atualizar uma vez no modelo do LookML. Como a tabela derivada nativa aponta para o objeto LookML em que a tabela do banco de dados é definida, ela se refere automaticamente à tabela adequada.
  • 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 apropriado para sua conexão de 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 é pedir que o Looker crie a tabela derivada a partir de uma consulta da Análise detalhada. Na Análise detalhada, selecione os campos que você quer usar na tabela derivada e use a opção Get LookML > Derived Table no menu de engrenagem da Análise detalhada para receber o LookML. O Looker gera o LookML necessário para criar uma tabela derivada com base na consulta da Análise, incluindo as definições de campo relevantes do modelo do LookML que são necessárias para criar as colunas da tabela derivada. Você pode 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 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 do Explore, eliminando a necessidade de repetir definições.

Tabelas derivadas persistentes (TDPs)

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