Como o Looker gera SQL

Se você conhece o Looker a partir de um plano de SQL, provavelmente tem curiosidade sobre como ele gera SQL. Essencialmente, o Looker é uma ferramenta que gera consultas SQL e as envia para uma conexão de banco de dados. O Looker formula consultas SQL com base em um projeto LookML que descreve a relação entre tabelas e colunas no banco de dados. Ao entender como o Looker gera consultas, você entenderá melhor como o código do LookML se traduz em consultas SQL eficientes.

Cada parâmetro do LookML controla algum aspecto de como o Looker gera SQL alterando a estrutura, o conteúdo ou o comportamento da consulta. Nesta página, descrevemos os princípios de como o Looker gera SQL, mas não abrange todos os elementos do LookML em detalhes. A página de documentação de referência rápida do LookML é um bom lugar para começar a ver informações sobre os parâmetros do LookML.

Como visualizar a consulta

Em um acesso ou uma exploração, é possível usar a guia SQL da seção Dados para ver o que o Looker envia ao banco de dados para conseguir as informações. Você também pode usar os links na parte inferior para visualizar sua consulta no SQL Runner ou ver o plano de explicação do banco de dados para a consulta. Para saber mais sobre o SQL Runner, consulte a página de documentação Princípios básicos do SQL Runner. Para mais informações sobre como otimizar uma consulta usando o SQL Runner, consulte a postagem Como otimizar o SQL com EXPLAIN.

Forma canônica de uma consulta do Looker

As consultas SQL do Looker sempre assumem a forma a seguir.

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

O projeto LookML define todas as dimensões, medidas, explorações e visualizações referenciadas na fórmula acima. As expressões de filtro são especificadas no Looker pelo usuário para moldar consultas ad hoc. As expressões de filtro também podem ser declaradas diretamente no LookML para serem aplicadas a todas as consultas.

Componentes fundamentais de uma consulta do Looker

Todas as consultas do Looker são representadas por esses parâmetros fundamentais aplicados a um projeto do LookML, como mostrado na fórmula acima.

O Looker usa os seguintes parâmetros para gerar uma consulta SQL completa:

  • model: o nome do modelo do LookML a ser segmentado, que especifica o banco de dados de destino
  • explore: o nome da consulta "Explorar" que preenche a cláusula FROM do SQL
  • Campos: os parâmetros dimension e measure a serem incluídos na consulta, que preenchem a cláusula SELECT do SQL
  • filter: expressões de filtro do Looker para aplicar em zero ou mais campos, que preenchem as cláusulas SQL WHERE e HAVING
  • Ordem de classificação: o campo pelo qual a classificação será classificada e a ordem de classificação, que preenche a cláusula ORDER BY do SQL

Esses parâmetros são exatamente os elementos que um usuário especifica ao criar uma consulta na página Explorar do Looker. Esses mesmos elementos aparecem em todos os modos de execução de consultas com o Looker: no SQL gerado, no URL que representa a consulta, na API Looker e assim por diante.

E as visualizações especificadas pelas cláusulas LEFT JOIN? As cláusulas JOIN são preenchidas com base na estrutura do modelo LookML, que especifica como as visualizações são mescladas ao Explorars. Ao criar consultas SQL, o Looker inclui cláusulas JOIN somente quando necessário. Quando os usuários estão criando uma consulta no Looker, eles não precisam especificar como as tabelas são agrupadas, porque essas informações são codificadas no modelo, um dos benefícios mais poderosos do Looker para os usuários comerciais.

Um exemplo de consulta e o SQL resultante

Vamos criar uma consulta no Looker para demonstrar como ela é gerada de acordo com o padrão acima. Considere uma loja de comércio eletrônico com mesas para acompanhar usuários e pedidos. Os campos e as relações da tabela são mostrados abaixo.

Vamos encontrar o número de pedidos (contagem de pedidos) agrupados por estado (estado de USERS) e filtrados por data de criação do pedido (data de criação de ORDERS).

Veja abaixo o resultado da consulta na página "Explorar" do Looker.

Clique na guia SQL para ver o SQL gerado e executado pelo Looker.

Observe a semelhança com a fórmula canônica mencionada acima. O SQL do Looker mostra algumas características do código gerado por máquina (por exemplo, COALESCE(users.state,'') AS "_g1"), mas sempre se ajusta à fórmula.

SELECT
   <dimension>,<dimension>,...
   <measure>,<measure>,...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>,<dimension>,<dimension>,...
ORDER BY <dimension> | <measure>
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
LIMIT <limit>

Faça mais consultas no Looker para provar que a estrutura de consulta é sempre a mesma.

Como executar SQL bruto no SQL Runner do Looker

O Looker inclui um recurso chamado executor do SQL em que você pode executar qualquer SQL nas conexões de banco de dados configuradas no Looker.

Como cada consulta gerada pelo Looker resulta em um comando SQL funcional e completo, é possível usar o SQL Runner para investigar ou brincar com a consulta.

Consultas SQL brutas executadas no SQL Runner produzem o mesmo conjunto de resultados

Se o SQL contiver erros, o SQL Runner destacará a localização do primeiro erro no comando SQL e incluirá a posição do erro na mensagem de erro.

Como examinar componentes de consulta no URL

Depois de executar uma consulta no Looker, é possível examinar o URL de compartilhamento expandido para ver os componentes fundamentais de uma consulta do Looker. Comece selecionando Compartilhar no menu de roda dentada "Explorar":

Se você estiver começando por Look, clique no link Look From Here para abrir a consulta.

A partir daí, a janela Compartilhar URLs é exibida, mostrando o URL expandido:

O URL fornece informações suficientes para recriar a consulta. Por exemplo, para um URL de compartilhamento expandido como este:

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500

O URL fornece as seguintes informações:

modelo e_thelook
explore events
campos para consultar e exibir fields=users.state,users.count
classificar campo e ordem sorts=users.count+desc
filtrar campos e valores f[users.created_year]=2020

Como o Looker organiza JOINs

No SQL de consulta acima, observe que a exploração orders aparece na cláusula FROM principal e que as visualizações unidas aparecem nas cláusulas LEFT JOIN. As vinculações do Looker podem ser gravadas de várias maneiras diferentes. Isso é explicado em mais detalhes na página Como trabalhar com mesclagens no LookML.

Blocos SQL especificam cláusulas SQL personalizadas

Nem todos os elementos de uma consulta do Looker são gerados por máquina. Em algum momento, o modelo de dados precisa fornecer detalhes específicos para que o Looker acesse as tabelas subjacentes e calcule os valores derivados. No LookML, os blocos SQL são snippets de código SQL fornecidos pelo modelador de dados, que o Looker usa para sintetizar expressões SQL completas.

O parâmetro de bloco SQL mais comum é sql, usado nas definições de dimensões e medidas. O parâmetro sql especifica uma cláusula SQL para referenciar uma coluna subjacente ou executar uma função de agregação. Em geral, todos os parâmetros do LookML que começam com sql_ esperam uma expressão SQL de alguma forma. Por exemplo: sql_always_where, sql_on e sql_table_name. Consulte a Referência do LookML para mais informações sobre cada parâmetro.

Exemplo de blocos SQL para dimensões e medidas

Veja abaixo alguns exemplos de blocos SQL para dimensões e medidas. O operador de substituição LookML ($) faz com que essas declarações sql apareçam de forma enganosa ao contrário do SQL. No entanto, após a substituição, a string resultante é o SQL puro, que o Looker injeta na cláusula SELECT da consulta.

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;  # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

Conforme mostrado nas duas últimas dimensões acima, os blocos SQL podem usar funções compatíveis com o banco de dados subjacente (como as funções CONCAT e DATEDIFF do MySQL, neste caso). O código usado nos blocos SQL precisa corresponder ao dialeto SQL usado pelo banco de dados.

Exemplo de bloco SQL para tabelas derivadas

As tabelas derivadas também usam um bloco SQL para especificar a consulta que deriva a tabela. Veja um exemplo abaixo:

view: user_order_facts {
  derived_table: {
    sql:
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }

  # later, dimension declarations reference the derived column(s)…
  dimension: lifetime_orders {
    type: number
  }
}

Exemplo de bloco SQL para filtrar uma exploração

Os parâmetros LookML sql_always_where e sql_always_having permitem restringir os dados disponíveis para uma consulta injetando um bloco SQL nas cláusulas SQL WHERE ou HAVING. Neste exemplo, o operador de substituição LookML ${view_name.SQL_TABLE_NAME} é usado para referenciar uma tabela derivada:

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}