Conceitos de SQL para tabelas derivadas

Embora as vistas do LookML se baseiem normalmente em tabelas existentes na sua base de dados, também pode criar vistas baseadas em declarações SELECT SQL. No Looker, este tipo de vista chama-se tabela derivada. As tabelas derivadas são consultas cujos resultados são usados como se fossem tabelas reais na base de dados.

Neste guia, vai saber mais sobre os seguintes tópicos:

Como o Looker gera o SQL para tabelas derivadas

Quando consulta tabelas derivadas no LookML, o Looker traduz as suas consultas em declarações SQL como expressões de tabelas comuns (CTEs) ou vistas inline, consoante o dialeto. Por outras palavras, as consultas SQL geradas podem ser semelhantes a um dos seguintes exemplos.

O exemplo seguinte 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 seguinte mostra como o Looker pode gerar uma consulta SQL que usa uma vista 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, define uma consulta SQL diretamente no LookML através do parâmetro sql no parâmetro derived_table de uma vista. Isto permite-lhe definir as colunas da tabela derivada através de SQL.

Por exemplo, o seguinte exemplo de LookML define uma tabela derivada denominada top_100_users que identifica os 100 principais utilizadores com o gasto total mais elevado em todas as respetivas encomendas:

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 top_100_users vista é referenciada numa consulta Explore, o Looker usa esta declaração SQL SELECT no SQL gerado como um CTE ou uma vista inline, consoante o seu dialeto.

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

  • A relação entre as tabelas orders e order_items já está provavelmente definida no modelo LookML. Se os nomes das tabelas subjacentes na sua base de dados forem alterados, os nomes das tabelas têm de ser atualizados em vários locais, incluindo na definição da sua tabela derivada baseada em SQL.
  • O SQL na definição da tabela derivada tem de estar no dialeto correto para a base de dados subjacente. Se os dados forem movidos para uma base de dados diferente, a definição da tabela derivada baseada em SQL tem de ser alterada.

Por estes motivos, as tabelas derivadas nativas são, muitas vezes, uma melhor escolha.

Tabelas derivadas nativas

No Looker, as tabelas derivadas nativas são definidas com LookML. Ao contrário da utilização de tabelas derivadas baseadas em SQL, quando define uma tabela derivada nativa, está a tirar partido do seu modelo LookML das seguintes formas:

  • Se o nome da tabela for alterado na base de dados, só tem de o atualizar uma vez no modelo LookML. Uma vez que a tabela derivada nativa aponta para o objeto LookML existente onde a tabela da base de dados está definida, a tabela derivada nativa vai referir-se automaticamente à tabela adequada.
  • Da mesma forma, se mudar para um dialeto diferente, o LookML da tabela derivada nativa continua a ser válido porque o Looker gera o SQL adequado à ligação da base de dados.

Pode definir manualmente o LookML para a tabela derivada nativa. No entanto, a forma mais fácil de criar uma tabela derivada nativa é fazer com que o Looker crie a tabela derivada a partir de uma consulta de exploração. No seu Explore, selecione os campos que quer usar na sua tabela derivada e use a opção Obter LookML > Tabela derivada no menu de engrenagem do Explore para obter o LookML. O Looker gera o LookML de que precisa para criar uma tabela derivada a partir da sua consulta de exploração, incluindo as definições de campos relevantes do seu modelo LookML necessárias para criar as colunas da sua tabela derivada. Pode copiar o LookML para um ficheiro de visualização no seu projeto para criar a tabela derivada.

O exemplo seguinte mostra uma tabela derivada nativa que identifica os 100 principais utilizadores com o total de gastos mais elevado em todas as respetivas encomendas:

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 consulta uma tabela derivada nativa numa funcionalidade Explorar, todos os detalhes da consulta SQL para a tabela derivada nativa são gerados automaticamente. As definições das colunas de uma vista do LookML com uma tabela derivada nativa são inferidas a partir das definições da exploração subjacente, o que elimina a necessidade de repetir quaisquer definições.

Tabelas derivadas persistentes (PDTs)

No Looker, pode criar tabelas derivadas temporárias e tabelas derivadas persistentes. Depois de criar uma tabela derivada baseada em SQL ou uma tabela derivada nativa, pode adicionar persistência para que o Looker escreva a tabela num esquema temporário na sua base de dados e a regenere na programação que especificar. Para mais informações, consulte o artigo Tabelas derivadas no Looker.