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 para 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 informações sobre os parâmetros do LookML.

Como consultar

Em um Look salvo ou em uma Análise, use a guia SQL no painel Dados para conferir o que o Looker envia ao banco de dados para receber 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 uma consulta usando o SQL Runner, consulte a postagem da comunidade Como otimizar 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, conforme mostrado no exemplo de consulta anterior.

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

• model: o nome do modelo do LookML a ser usado, que especifica o banco de dados de destino
• explore: o nome da consulta "Explore to", 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 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, como no SQL gerado, no URL que representa a consulta e na API Looker.

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 com as 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 combinadas, porque essas informações são codificadas no modelo, um dos benefícios mais importantes do Looker para os usuários corporativos.

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 comércio eletrônico que tem um banco de dados com duas tabelas, pedidos e usuários, para acompanhar 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 qualquer SQL nas 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.

Como 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. Comece selecionando Compartilhar no menu de engrenagem da guia "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 aberto 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

modelo	e_thelook
explore	events
campos a serem consultados e exibidos	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 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 em definições de dimensão e medida. O parâmetro sql especifica uma cláusula SQL para fazer referência a uma coluna subjacente ou realizar 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

O exemplo de código a seguir mostra alguns exemplos de blocos SQL para dimensões e medidas. O operador de substituição LookML ($) faz com que essas declarações sql pareçam ser diferentes do SQL. No entanto, depois que a substituição ocorre, 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}) ;;
}

Como mostrado nas duas últimas dimensões deste 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 SQL para tabelas derivadas

As tabelas derivadas também usam um bloco SQL para especificar a consulta que deriva a tabela. Este é um exemplo de tabela derivada com base em SQL:

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 Análise detalhada

Os parâmetros sql_always_where e sql_always_having do LookML permitem que você restrinja 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 do 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});;
}