Se você começou a usar o Looker com SQL, provavelmente quer saber como o Looker gera SQL. O Looker é uma ferramenta que gera consultas SQL e as envia em 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ê entende melhor como o código LookML se traduz 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. Nesta página, descrevemos os princípios de como o Looker gera SQL, mas não aborda todos os elementos do LookML em detalhes. A página da documentação de referência rápida do LookML é um bom ponto de partida para se informar 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 extrair os dados. Também é possível usar os links Abrir no SQL Runner e Explain in 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 saber mais sobre o SQL Runner, consulte a página de documentação Noções básicas do SQL Runner. Para saber mais sobre como otimizar uma consulta usando o SQL Runner, confira a postagem na comunidade Como otimizar o SQL com o EXPLAIN (em inglês).
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 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 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 visto no exemplo de consulta anterior.
O Looker usa os seguintes parâmetros para gerar uma consulta SQL completa:
model: o nome do modelo LookML de destino, que especifica o banco de dados de destino.explore: o nome da Análise para consulta, que preenche a cláusula SQLFROM- Campos: os parâmetros
dimensionemeasurea serem incluídos na consulta, que preenchem a cláusulaSELECTdo SQL. filter: expressões de filtro do Looker para aplicar a zero ou mais campos, que preenchem as cláusulas SQLWHEREeHAVING.- Ordem de classificação: o campo usado para classificar e a ordem em que a cláusula
ORDER BYdo SQL é preenchida
Esses parâmetros são precisamente os elementos que um usuário especifica ao criar uma consulta na página Explore 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 com as Análises. Ao criar consultas SQL, o Looker inclui cláusulas JOIN somente quando necessário. Ao criar consultas no Looker, os usuários não precisam especificar como as tabelas são mescladas, porque essas informações são codificadas no modelo, um dos principais benefícios 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 comércio eletrônico que tenha 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 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.
Faça mais consultas no Looker para provar que a estrutura delas é sempre a mesma.
Como executar SQL bruto no SQL Runner do Looker
O Looker tem um recurso chamado SQL Runner, que permite executar o SQL que você quiser 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 brincar com a consulta.
As consultas SQL brutas que são executadas no SQL Runner produzem o mesmo conjunto de resultados. Se o SQL contiver erros, o SQL Runner destacará o local do primeiro erro no comando SQL e incluirá a posição do erro na mensagem de erro.
Análise de componentes de consulta no URL expandido
Depois de executar uma consulta no Looker, é possível analisar o URL expandido para conferir os componentes essenciais de uma consulta do Looker. Para começar, selecione Compartilhar no menu de engrenagem da ferramenta Explorar para abrir o menu Compartilhar URLs.
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 |
| campos e valores de filtro | f[users.created_year]=2020 |
Como o Looker estrutura JOINs
No exemplo de consulta anterior, observe que a Análise orders aparece na cláusula FROM principal e as visualizações unidas aparecem nas cláusulas LEFT JOIN. As mesclagens do Looker podem ser escritas de várias maneiras diferentes. Isso é explicado em mais detalhes na página Como trabalhar com mesclagens no LookML.
Os blocos de 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 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ão e medida. 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 algum formato. 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 medidas
Veja abaixo alguns exemplos de blocos SQL para dimensões e medidas. O operador de substituição LookML ($) faz com que essas declarações sql apareçam erroneamente diferentes de SQL. No entanto, após a substituição, a string resultante é de 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 de SQL podem usar funções compatíveis com o banco de dados subjacente, como as funções CONCAT e DATEDIFF do MySQL, nesse caso. O código usado nos blocos de SQL precisa 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
Com os parâmetros sql_always_where e sql_always_having do LookML, é possível 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 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});;
}