Se tem experiência em SQL e começou a usar o Looker, provavelmente tem curiosidade em saber como o Looker gera SQL. Fundamentalmente, o Looker é uma ferramenta que gera consultas SQL e as envia para uma ligação à base de dados. O Looker formula consultas SQL com base num projeto do LookML que descreve a relação entre tabelas e colunas na base de dados. Ao compreender como o Looker gera consultas, vai compreender melhor como o seu código LookML se traduz em consultas SQL eficientes.
Cada parâmetro do LookML controla algum aspeto da forma 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 detalhe. 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.
Ver a consulta
Num Look guardado ou num Explore, pode usar o separador SQL no painel Dados para ver o que o Looker envia para a base de dados para obter os dados. Também pode usar os links Abrir em execução de SQL e Explicar em execução de SQL na parte inferior do separador SQL para ver a sua consulta na execução de SQL ou para ver o plano de explicação da base de dados para a consulta.
Para saber mais sobre a execução de SQL, consulte a página de documentação Noções básicas da execução de SQL. Para mais informações sobre como otimizar uma consulta através do SQL Runner, consulte a publicação da comunidade Como otimizar o SQL com EXPLAIN.
Forma canónica de uma consulta do Looker
As consultas SQL do Looker têm sempre 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, Explores e vistas que são referenciadas na consulta SQL. As expressões de filtro são especificadas no Looker pelo utilizador para moldar as consultas ad hoc. As expressões de filtro também podem ser declaradas diretamente no LookML para aplicação a todas as consultas.
Componentes fundamentais de uma consulta do Looker
Todas as consultas do Looker são representadas por estes parâmetros fundamentais aplicados a um projeto do LookML, como se pode ver 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 a segmentar, que especifica a base de dados de destinoexplore: o nome da análise detalhada a consultar, que preenche a cláusula SQLFROM- Campos: os parâmetros
dimensionemeasurea incluir na consulta, que preenchem a cláusulaSELECTde SQL filter: expressões de filtro do Looker a aplicar a zero ou mais campos, que preenchem as cláusulasWHEREeHAVINGde SQL- Ordem de ordenação: o campo pelo qual ordenar e a ordem de ordenação, que preenche a cláusula
ORDER BYdo SQL
Estes parâmetros são precisamente os elementos que um utilizador especifica quando cria uma consulta na página Explorar do Looker. Estes mesmos elementos são apresentados 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 quanto às 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 vistas se juntam aos Explores. Ao criar consultas SQL, o Looker inclui cláusulas JOIN apenas quando necessário. Quando os utilizadores criam uma consulta no Looker, não têm de especificar como as tabelas se juntam, porque estas informações estão codificadas no modelo, uma das vantagens mais poderosas do Looker para os utilizadores empresariais.
Um exemplo de consulta e o SQL resultante
Vamos criar uma consulta no Looker para demonstrar como a consulta é gerada de acordo com o padrão anterior. Considere uma loja de comércio eletrónico que tem uma base de dados com duas tabelas, encomendas e utilizadores, para acompanhar os utilizadores e as encomendas.
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 encomendas (Contagem de ENCOMENDAS) agrupadas por estado (Estado dos UTILIZADORES) e filtradas pela data de criação da encomenda (Data de criação das ENCOMENDAS) numa exploração do Looker.
Para ver a consulta SQL gerada e executada pelo Looker, clique no separador 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
Tenha em atenção a semelhança com a fórmula de consulta canónica. O SQL do Looker apresenta algumas caraterísticas de código gerado por máquina (por exemplo, COALESCE(users.state,'') AS "_g1"), mas adapta-se sempre à fórmula.
Faça experiências com mais consultas no Looker para comprovar que a estrutura de consulta é sempre a mesma.
Executar SQL não processado no executador de SQL do Looker
O Looker inclui uma funcionalidade denominada SQL Runner, onde pode executar qualquer SQL que pretender nas ligações de base de dados que configurou no Looker.
Uma vez que cada consulta gerada pelo Looker resulta num comando SQL completo e funcional, pode usar o SQL Runner para investigar ou experimentar a consulta.
As consultas SQL não processadas executadas no SQL Runner produzem o mesmo conjunto de resultados. Se o SQL contiver erros, o SQL Runner realça a localização do primeiro erro no comando SQL e inclui a posição do erro na mensagem de erro.
Examinar os componentes de consulta no URL expandido
Depois de executar uma consulta no Looker, pode examinar o URL expandido para ver os componentes fundamentais de uma consulta do Looker. Comece por selecionar Partilhar no menu de engrenagem do Explorar para abrir o menu Partilhar 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
| modelo | e_thelook |
| explorar | events |
| campos a consultar e apresentar | fields=users.state,users.count |
| campo e ordem de ordenação | sorts=users.count+desc |
| campos e valores de filtros | f[users.created_year]=2020 |
Como o Looker estrutura as junções
No exemplo de consulta anterior, repare que a funcionalidade orders Explorar aparece na cláusula FROM principal e as vistas unidas aparecem nas cláusulas LEFT JOIN. As associações do Looker podem ser escritas de muitas formas diferentes, o que é explicado mais detalhadamente na página Trabalhar com associações 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 tem de fornecer detalhes específicos para que o Looker aceda às tabelas subjacentes e calcule os valores derivados. No LookML, os blocos SQL são fragmentos 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ões e medidas. O parâmetro sql especifica uma cláusula SQL para referenciar uma coluna subjacente ou executar uma função de agregação. 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 medidas
O seguinte exemplo de código apresenta alguns exemplos de blocos SQL para dimensões e medidas. O operador de substituição do LookML ($) faz com que estas declarações sql pareçam enganadoramente diferentes do SQL. No entanto, depois de ocorrer a substituição, 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}) ;;
}
Conforme mostrado nas duas últimas dimensões neste exemplo, os blocos SQL podem usar funções suportadas pela base de dados subjacente (como as funções CONCAT e DATEDIFF do MySQL neste caso). O código que usa nos blocos SQL tem de corresponder ao dialeto de SQL usado pela base 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. Segue-se um exemplo de uma tabela derivada baseada 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 exploração
Os parâmetros do LookML sql_always_where e sql_always_having permitem restringir os dados disponíveis para uma consulta através da injeção de 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 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});;
}