Como o Looker gera SQL

Se você conhece o Looker com experiência em SQL, provavelmente está curioso sobre como o Looker 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. Entenda como o Looker gera consultas e entenda melhor como o código LookML se traduz em consultas SQL eficientes.

Cada parâmetro 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 abordamos todos os elementos LookML em detalhes. Consulte a página de documentação de referência do LookML para ver todos os detalhes.

Como visualizar a consulta

Em um acesso salvo ou um recurso Explorar, é possível usar a guia SQL na seção Dados para ver o que o Looker envia ao banco de dados para receber os dados. Use os links na parte inferior para ver a consulta no SQL Runner ou ver o plano de explicação do banco de dados da consulta. Para mais informações 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 o artigo Como otimizar SQL com EXPLAIN da Central de Ajuda.

Formulário canônico de uma consulta do Looker

As consultas SQL do Looker sempre têm o seguinte formato.

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 na 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 LookML, como visto na fórmula acima.

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

• model: o nome do modelo LookML a ser segmentado, que especifica o banco de dados de destino
• explore: o nome da consulta "explorar para", que preenche a cláusula SQL FROM
• Campos: os parâmetros dimension e measure a serem incluídos na consulta, que preencherão a cláusula SELECT do SQL.
• filter: expressões de filtro do Looker para aplicar a zero ou mais campos, que preenchem as cláusulas SQL WHERE e HAVING
• Ordem de classificação: o campo por onde classificar 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 às"explores". Ao criar consultas SQL, o Looker inclui cláusulas JOIN somente quando necessário. Ao criar uma consulta no Looker, os usuários não precisam especificar como as tabelas são mescladas, já que essas informações são codificadas no modelo, um dos benefícios mais poderosos dos usuários do Looker.

Uma consulta de exemplo e o SQL resultante

Vamos criar uma consulta no Looker para demonstrar como ela é gerada de acordo com o padrão acima. Considere ter uma loja de e-commerce com tabelas para rastrear usuários e pedidos. Os campos e as relações de tabela são mostrados abaixo.

Vamos ver 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 dos pedidos).

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 indicada acima. O SQL do Looker exibe algumas características do código gerado por máquina (por exemplo, COALESCE(users.state,'') AS "_g1"), mas sempre se encaixa na 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>

Experimente 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 SQL Runner, onde é possível executar qualquer SQL que quiser nas conexões de banco de dados que você configurou no Looker.

Como cada consulta gerada pelo Looker resulta em um comando SQL completo e funcional, use o SQL Runner para investigar ou jogar com a consulta.

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

Se o SQL tiver 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.

Observação:se as configurações de conexão especificarem um fuso horário do banco de dados, o Looker emitirá um SET TIMEZONE após a conexão, o que poderá afetar os resultados.

Examinar componentes de consultas no URL

Após executar uma consulta no Looker, é possível analisar o URL de compartilhamento expandido para ver os componentes fundamentais de uma consulta do Looker. Comece selecionando Compartilhar no menu de engrenagem do Explorar:

Se estiver começando em um Look, clique no link Look From Here para abrir a consulta em um Explore.

A janela Compartilhar URLs será exibida e mostrará o URL expandido:

Por exemplo, a consulta acima produz o seguinte URL de compartilhamento expandido:

https://docsexamples.dev.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 informações suficientes para recriar a consulta:

modelo	e_thelook
explorar	events
campos a serem consultados e exibidos	fields=users.state,users.count
ordenar campo e ordem	sorts=users.count+desc
filtrar campos e valores	f[users.created_year]=2020

Como o Looker estrutura JOINs

No SQL de consulta acima, observe que a exploração orders aparece na cláusula FROM principal e as visualizações mescladas aparecem nas cláusulas LEFT JOIN. As junções do Looker podem ser escritas de várias maneiras diferentes, o que é 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 automaticamente. Em algum momento, o modelo de dados precisa fornecer detalhes específicos para que o Looker acesse as tabelas subjacentes e calcule 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 bloqueio SQL mais comum é sql, usado nas definições de dimensões e medidas. O parâmetro sql especifica uma cláusula SQL para fazer referência a uma coluna subjacente ou para executar uma função agregada. Em geral, todos os parâmetros LookML iniciados por sql_ esperam uma expressão SQL de alguma forma. Por exemplo: sql_always_where, sql_on e sql_table_name. A Referência do LookML fornece detalhes sobre cada parâmetro.

Exemplo de bloqueios de SQL para dimensões e medidas

Veja abaixo alguns exemplos de bloqueios de SQL para dimensões e medidas. O operador de substituição LookML ($) faz com que essas declarações sql apareçam de forma enganosa, diferente do SQL. No entanto, após a substituição, a string resultante é 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. O código usado em 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});;
}