Como o Looker gera SQL

Se você tem experiência com SQL, 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 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 do LookML é traduzido 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 lugar para começar a encontrar informações sobre os parâmetros do LookML.

Visualizar a consulta

Em um Look salvo ou em uma Análise, você pode usar 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 visualizar a consulta no SQL Runner ou conferir 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, 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 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 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 segmentado, que especifica o banco de dados de destino
• explore: o nome da Análise a ser consultada, 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áuszas WHERE e HAVING do SQL
• Ordem de classificação: o campo a ser classificado e a ordem de classificação, que preenchem 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 são unidas às Análises. 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 unidas, porque essas informações são codificadas no modelo, um dos benefícios mais poderosos do Looker para 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 e-commerce que tenha 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 SQL do Looker exibe algumas características de código gerado por máquina (por exemplo, COALESCE(users.state,'') AS "_g1"), mas sempre se ajusta à fórmula.

Faça mais consultas no Looker para comprovar que a estrutura da consulta é sempre a mesma.

Executar SQL bruto no SQL Runner do Looker

O Looker inclui um recurso chamado SQL Runner, 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 completo e funcional, você pode 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 erros, o SQL Runner vai 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 na 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. Comece selecionando Compartilhar no menu de engrenagem da Análise para abrir o menu Compartilhar URLs.

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

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 exibir	fields=users.state,users.count
campo e ordem de classificação	sorts=users.count+desc
campos e valores de filtro	f[users.created_year]=2020

Como o Looker estrutura JOINs

No exemplo de consulta anterior, observe que a orders Análise aparece na cláusula FROM principal e as visualizações unidas aparecem nas cláusção LEFT JOIN. As junções do Looker podem ser escritas de muitas maneiras diferentes, o que é explicado com mais detalhes na página 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. 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 medição. 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.

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

O exemplo de código a seguir fornece alguns exemplos de blocos SQL para dimensões e medições. O operador de substituição do LookML ($) faz com que essas sql declarações 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 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 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

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