Como o Looker gera SQL

Se você chegou ao Looker com experiência em SQL, provavelmente tem curiosidade sobre como o Looker gera SQL. Basicamente, o Looker é uma ferramenta que gera consultas SQL e as envia por uma conexão de banco de dados. O Looker formula consultas SQL com base em um projeto do LookML que descreve a relação entre tabelas e colunas no banco de dados. Ao entender como o Looker gera consultas, você vai entender melhor como o código LookML se transforma 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. Esta página descreve os princípios de como o Looker gera SQL, mas não aborda todos os elementos do LookML em detalhes. A página de documentação de referência rápida do LookML é um bom ponto de partida para obter informações sobre os parâmetros do LookML.

Como visualizar a consulta

Em um Look salvo ou em uma Análise, é possível usar a guia SQL no painel Dados para conferir o que o Looker envia ao banco de dados para coletar os dados. Também é possível usar os links Abrir no SQL Runner e Explicar no SQL Runner na parte inferior da guia SQL para visualizar sua consulta no SQL Runner ou ver o plano de explicação do banco de dados para a consulta.

Para mais informações sobre o SQL Runner, consulte a página de documentação Noções básicas do SQL Runner. Para mais informações sobre como otimizar consultas usando o SQL Runner, acesse a postagem na Comunidade Como otimizar o SQL com EXPLAIN.

Forma canônica de uma consulta do Looker

As consultas SQL do Looker sempre assumem a seguinte forma.

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, medições, Análises e visualizações referenciadas na consulta SQL. 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 aplicação em 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 na consulta de exemplo anterior.

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

• model: o nome do modelo LookML a ser alvo, que especifica o banco de dados de destino.
• explore: o nome da Análise a ser consulta, que preenche a cláusula SQL FROM
• Campos: os parâmetros dimension e measure a serem incluídos na consulta, que preenchem a cláusula SQL SELECT
• filter: expressões de filtro do Looker a serem aplicadas a zero ou mais campos, que preenchem as cláusulas SQL WHERE e HAVING
• Ordem de classificação: o campo a ser classificado 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 se mesclam às Análises. 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, porque essas informações são codificadas no modelo. Esse é um dos benefícios mais úteis do Looker para 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 anterior. Considere uma loja de e-commerce que tem um banco de dados com duas tabelas, orders e users, para rastrear usuários e pedidos.

orders
id INT
created_at DATETIME
users_id INT
status VARCHAR(255)
traffic_source VARCHAR(15)

users
id INT
email VARCHAR(255)
first_name VARCHAR(255)
last_name VARCHAR(255)
created_at DATETIME
zip INT
country VARCHAR(255)
state VARCHAR(255)
city VARCHAR(255)
age INT
traffic_source VARCHAR(15)

Vamos encontrar o número de pedidos (Contagem de pedidos) agrupados por estado (Estado dos USUÁRIOS) e filtrados pela data de criação do pedido (Data de criação dos PEDIDOS) em uma Análise do Looker.

Para conferir a consulta SQL gerada e executada pelo Looker, clique na guia SQL no painel Dados.

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

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

Teste mais consultas no Looker para provar que a estrutura da consulta é sempre a mesma.

Como executar SQL bruto no SQL Runner do Looker

O Looker inclui um recurso chamado SQL Runner em que é possível executar o SQL que você quiser em conexões de banco de dados configuradas no Looker.

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

As consultas SQL brutas executadas no SQL Runner produzem o mesmo conjunto de resultados. Se o SQL contiver algum erro, o SQL Runner destacará o local 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 vai emitir um SET TIMEZONE durante a conexão, o que pode afetar os resultados.

Examinar componentes de consulta no URL expandido

Depois de executar uma consulta no Looker, você pode examinar o URL expandido para conferir os componentes fundamentais de uma consulta do Looker. Primeiro selecione Compartilhar no menu de engrenagem do recurso Explorar para abrir o menu Compartilhar URLs.

Observação: se você estiver começando em um Look, clique no link Explorar daqui para abrir a consulta em uma Análise.

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

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

model	e_thelook
explore	events
campos para consultar e exibir	fields=users.state,users.count
campo de classificação e ordem	sorts=users.count+desc
filtrar campos e valores	f[users.created_year]=2020

Como o Looker estrutura as funções JOIN

Na consulta de exemplo anterior, observe que a Análise orders aparece na cláusula principal FROM, e as visualizações mescladas aparecem nas cláusulas LEFT JOIN. As mesclagens do Looker podem ser escritas de várias maneiras diferentes, o que é explicado com mais detalhes na página Como trabalhar com mesclagens no LookML.

Os 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 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 medições. O parâmetro sql especifica uma cláusula SQL para referenciar uma coluna subjacente ou executar uma função agregada. 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.

Exemplos de blocos SQL para dimensões e medições

Confira abaixo alguns exemplos de blocos SQL para dimensões e medições. O operador de substituição LookML ($) faz com que essas declarações sql pareçam ser diferentes do SQL. No entanto, após a substituição, a string resultante é um 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 neste exemplo, 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 deve corresponder ao dialeto SQL usado pelo banco de dados.

Exemplo de bloco de SQL para tabelas derivadas

As tabelas derivadas também usam um bloco SQL para especificar a consulta que deriva a tabela. Confira 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 de SQL para filtrar uma Análise

Os parâmetros LookML sql_always_where e sql_always_having permitem restringir os dados disponíveis a 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 fazer referência a 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});;
}