Como o Looker gera SQL

Se você tem experiência com SQL e começou a usar o Looker, provavelmente está curioso para saber 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 da 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 seu código LookML se traduz em consultas SQL eficientes.

Cada parâmetro da 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 Referência rápida do LookML é um bom lugar para começar a buscar informações sobre parâmetros do LookML.

Como ver a consulta

Em um Look salvo ou em uma Análise, use a guia SQL no painel Dados para ver o que o Looker envia ao banco de dados para receber os dados. Você também pode usar os links Abrir no SQL Runner e Explicar no SQL Runner na parte de baixo da guia SQL para ver sua consulta no SQL Runner ou 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 o SQL com EXPLAIN.

Forma canônica 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 do LookML define todas as dimensões, medidas, análises e visualizações referenciadas na consulta SQL. As expressões de filtro são especificadas no Looker pelo usuário para criar 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 da LookML, como visto na consulta de exemplo anterior.

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

• model: o nome do modelo do LookML de destino, que especifica o banco de dados de destino
• explore: o nome da análise detalhada para consulta, 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 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 usado para classificação 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 Análise 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 do LookML, que especifica como as visualizações se juntam às análises. Ao criar consultas SQL, o Looker inclui cláusulas JOIN somente quando necessário. Quando os usuários criam uma consulta no Looker, não precisam especificar como as tabelas são unidas, porque essas informações são codificadas no modelo, um dos benefícios mais poderosos 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, pedidos e usuários, 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 detalhada 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 SQL do Looker mostra algumas características de código gerado por máquina (por exemplo, COALESCE(users.state,'') AS "_g1"), mas sempre se encaixa na fórmula.

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

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, você pode usar o SQL Runner para investigar ou testar a consulta.

Consultas SQL brutas executadas no SQL Runner produzem o mesmo conjunto de resultados. Se o SQL tiver erros, o SQL Runner vai destacar a localização do primeiro erro no comando SQL e incluir a posição dele 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 ao se conectar, o que pode afetar os resultados.

Como analisar componentes de consulta no URL expandido

Depois de executar uma consulta no Looker, examine o URL expandido para conferir os componentes fundamentais de uma consulta do Looker. Comece selecionando Compartilhar no menu de engrenagem do recurso Detalhar para abrir o menu Compartilhar URLs.

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

O URL expandido fornece informações suficientes para recriar a consulta. Por exemplo, este 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

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

Como o Looker estrutura os JOINs

Na consulta de exemplo anterior, observe que o recurso orders aparece na cláusula FROM principal, e as visualizações unidas aparecem nas cláusulas LEFT JOIN. As junções do Looker podem ser escritas de várias maneiras diferentes, o que é explicado com mais detalhes na página Como trabalhar com junções 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. Em 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 métrica. O parâmetro sql especifica uma cláusula SQL para referenciar uma coluna subjacente ou realizar uma função de agregação. Em geral, todos os parâmetros da 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 de LookML para mais informações sobre cada parâmetro.

Exemplos de blocos SQL para dimensões e métricas

O exemplo de código a seguir mostra alguns exemplos de blocos SQL para dimensões e métricas. O operador de substituição da LookML ($) faz com que essas declarações sql pareçam enganosamente 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 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. 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 do 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 da 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});;
}